jdbcTemplate.update()的几种写法 以及NamedParameterJdbcTemplate

api提供了update的几种调用写法

  //方法一直接在sql中拼接好了参数之后调用即可	@Overridepublic int update(final String sql) throws DataAccessException {Assert.notNull(sql, "SQL must not be null");if (logger.isDebugEnabled()) {logger.debug("Executing SQL update [" + sql + "]");}class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider {@Overridepublic Integer doInStatement(Statement stmt) throws SQLException {int rows = stmt.executeUpdate(sql);if (logger.isDebugEnabled()) {logger.debug("SQL update affected " + rows + " rows");}return rows;}@Overridepublic String getSql() {return sql;}}return execute(new UpdateStatementCallback());}//方法二 使用占位符 多参数依次入参@Overridepublic int update(String sql, Object... args) throws DataAccessException {return update(sql, newArgPreparedStatementSetter(args));}//方法三 实现回调接口PreparedStatementSetter 重写里面的方法@Overridepublic int update(String sql, PreparedStatementSetter pss) throws DataAccessException {return update(new SimplePreparedStatementCreator(sql), pss);}//方法四 添加了第两个参数数组，用于显式的指定每个占位符所对应的字段数据类型@Overridepublic int update(String sql, Object[] args, int[] argTypes) throws DataAccessException {return update(sql, newArgTypePreparedStatementSetter(args, argTypes));}

  @Autowiredprivate JdbcTemplate jdbcTemplate;  @Transactional(rollbackFor=Exception.class)public void updateManegerSub(UpdateManagerParam updateManagerParam){StringBuffer sql = new StringBuffer("UPDATE pub_user SET account = ? ,phone = ? ,email = ? ," +"update_date = NOW() ,user_name = ? " );if (StringUtils.isNotEmpty(updateManagerParam.getPassword())){sql.append(" ,password = '" + updateManagerParam.getPassword() +"'");}sql.append("WHERE user_id = ? AND del_flag = ?");//方法一，将sql语句中的参数全部按照密码字段一样拼接好之后，直接调用此方法jdbcTemplate.update(sql.toString());//方法二 使用占位符 多参数依次入参jdbcTemplate.update(sql.toString() ,updateManagerParam.getAccount(),updateManagerParam.getPhone(),updateManagerParam.getEmail(),updateManagerParam.getUserName(),updateManagerParam.getUserId(),UserEntity.DEL_FLAG_NORMAL);//方法三 实现回调接口PreparedStatementSetter 重写里面的方法jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps) throws SQLException {ps.setString(1,updateManagerParam.getAccount());ps.setString(2,updateManagerParam.getPhone());ps.setString(3,updateManagerParam.getEmail());ps.setString(4,updateManagerParam.getUserName());ps.setLong(5,updateManagerParam.getUserId());ps.setString(6,UserEntity.DEL_FLAG_NORMAL);}});//方法四 添加了第两个参数数组，用于显式的指定每个占位符所对应的字段数据类型jdbcTemplate.update(sql.toString(),new Object[]{updateManagerParam.getAccount(),updateManagerParam.getPhone(),updateManagerParam.getEmail(),updateManagerParam.getUserName(),updateManagerParam.getUserId(),UserEntity.DEL_FLAG_NORMAL},new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.BIGINT,Types.VARCHAR,});}

若使用参数绑定，则需要使用 namedParameterJdbcTemplate    https://blog.csdn.net/u013468917/article/details/52219849

  @Autowiredprivate NamedParameterJdbcTemplate namedParameterJdbcTemplate;@Transactional(rollbackFor=Exception.class)public void updateManegerSub(UpdateManagerParam updateManagerParam,Long roleId){Map<String ,Object> params = new HashMap<>();StringBuffer sql = new StringBuffer("UPDATE pub_user SET account = :account ,phone = :phone ,email = :email , " +"update_date = NOW() ,user_name = :user_name " );params.put("account",updateManagerParam.getAccount());params.put("phone",updateManagerParam.getPhone());params.put("email",updateManagerParam.getEmail());params.put("user_name",updateManagerParam.getUserName());if (StringUtils.isNotEmpty(updateManagerParam.getPassword())){sql.append(" ,password = :password ");params.put("password",updateManagerParam.getPassword());}sql.append("WHERE user_id = :user_id AND del_flag = :del_flag ");params.put("user_id",updateManagerParam.getUserId());params.put("del_flag",UserEntity.DEL_FLAG_NORMAL);namedParameterJdbcTemplate.update(sql.toString(),params);}

    @Overridepublic List<OrgSubResponse> fetchAllOrgs() {Long userId = PsUserUtils.getPsUserId();Long managerOrgId = orgManagerDao.fetchOrgIdByManagerId(userId);if (managerOrgId == null) {throw new LogicException(UomConstants.ORG_NOT_MANAGER_ERROR);}String sql = "SELECT org_id AS id, org_name AS name, parent_id AS pId FROM pub_org pu " +" WHERE pu.parent_ids LIKE CONCAT('%/', :orgId, '/%') AND pu.del_flag = '0' " +" ORDER BY IFNULL(pu.sort, 2100000000) ASC ";Map<String, Object> paramMap = new HashMap<String, Object>();paramMap.put("orgId", managerOrgId);List<OrgSubResponse> orgSubResponses = namedParameterJdbcTemplate.query(sql, paramMap, new BeanPropertyRowMapper<OrgSubResponse>(OrgSubResponse.class));if (orgSubResponses == null) {return  null;}for (OrgSubResponse orgSubResponse : orgSubResponses) {if (orgSubResponse.getId().equals(managerOrgId)) {orgSubResponse.setpId(0L);}}return orgSubResponses;}

package com.comtop.map.store.uom.bean.response.org;import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;@ApiModel(description = "取部门下所有子部门，和选部门组件联用")
public class OrgSubResponse {@ApiModelProperty(value = "code")private String code;@ApiModelProperty(value = "icon")private String icon;@ApiModelProperty(value = "name")private String name;@ApiModelProperty(value = "code")private String parentIds;@ApiModelProperty(value = "fullName")private String fullName;@ApiModelProperty(value = "pId")private Long pId;@ApiModelProperty(value = "id")private Long id;public String getCode() {return code;}public void setCode(String code) {this.code = code;}public String getIcon() {return icon;}public void setIcon(String icon) {this.icon = icon;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getParentIds() {return parentIds;}public void setParentIds(String parentIds) {this.parentIds = parentIds;}public String getFullName() {return fullName;}public void setFullName(String fullName) {this.fullName = fullName;}public Long getpId() {return pId;}public void setpId(Long pId) {this.pId = pId;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}
}