spring-jdbc jar中的 JdbcTemplate 的注意点和常用使用方法

1、使用JdbcTemplate.queryForObject 的注意点

1、queryForObject其实支持的是标量子查询,只能传入一个基本类型的包装类的class,并返回一个基本类型对应包装类型的对象.

2、如果要实现使用queryForObject获得自定义的类的对象,需要使用和方法query相似的重载方法queryForObject(sql,Object[],int[],RowMapper),通过RowMapper填充自定义的类,通过Object[]和int[]设定sql语句的参数参数和参数类型.

3、这样还会存在一些问题,就是如果sql语句查询出0条或者多条数据的话,queryForObject会抛出EmptyResultDataAccessException或IncorrectResultSetColumnCountException:的异常,而如果干脆使用方法query,或者queryForList则可以在编码中处理掉这种问题而无需try-catch

结论： 查询单个对象干脆直接使用： queryForList

1、JdbcTemplate 常用的使用方法

备注： maven 基于

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.12.RELEASE</version>
</dependency>

查询：

• query
• • queryForMap
  • queryForObject
  • queryForRowSet
  • queryForList

1、查询 带查询参数  并返回基本类型 （单个返回不要使用 queryForObject）

/**
 *   查询 重复id 号
*/
	public List<Long> selectRepeatId(Long id,String dataFrom) {
		String sql = "SELECT TOP 1 ID FROM competition_price_store_history WHERE data_from = ? AND ID = ?";
		// 一定要用数组
		Object[] params = new Object[] { dataFrom, id};  
		return jdbcTemplate.queryForList(sql,params,Long.class);
	}

1.1 查询 不带查询参数  并返回基本类型 （单个返回不要使用 queryForObject）

public List<String> qryEventList() {
		String sql = "SELECT distinct [eventID] FROM [dbo].[was_schedule] where eventID is not null";
		return jdbcTemplate.queryForList(sql, String.class);
}

1.2 sqlserver 动态条件查询----- 常用

String sql = "select * from(select row_number()over(order by cpsh.ID desc) as Row,"
				+ " cpsh.ID,SurveryDate,store_nbr,acct_dept,item_nbr,[desc],Size,StorePriceTax,StoreCostTax,CompetitorPriceTax,Competitor,"
				+ "PromPriceWM,PromPriceComp,CompPriceWM,cpsh.data_from,competitorGrossRate,icps.create_time as InsertTime,icps.status AS icpsStatus,icps.comment AS icpsComment,icps.type,compExpirationDate "
				+ "FROM competition_price_store_history AS cpsh INNER JOIN item_change_price_status AS icps on cpsh.ID =icps.competition_price_store_history_id and cpsh.data_from =icps.data_from "
				+ "where 1 = 1 ";
		sqlbBuilder.append(sql);
				if (!StringUtils.isEmpty(storeNbr) ) {
					sqlbBuilder.append(" and cpsh.store_nbr=? ");
					arrayList.add(storeNbr);
				}
				if(!StringUtils.isEmpty(startCreateTime) ) {
					sqlbBuilder.append(" and icps.create_time >=? ");
					arrayList.add(startCreateTime);
				}
				if(!StringUtils.isEmpty(endcreateTime)) {
					sqlbBuilder.append(" and icps.create_time<= ? ");
					arrayList.add(endcreateTime);
				}
				if(!StringUtils.isEmpty(dataFrom)) {
					sqlbBuilder.append(" and cpsh.data_from = ? ");
					arrayList.add(dataFrom);
				}
				if(tempIndex != -1) {
					sqlbBuilder.append(" )as t1  where t1.Row between "+ start +" and "+endIndex);
				}else {
					sqlbBuilder.append(" )as t1 ");
				}
				
				
		String[]  paramsArray = new String[arrayList.size()];	
		paramsArray = arrayList.toArray(paramsArray);
		
		return jdbcTemplate.query(sqlbBuilder.toString(), paramsArray,new RowMapper<ComPriceStoreHistoryDO>() {

			@Override
			public ComPriceStoreHistoryDO mapRow(ResultSet rs, int rowNum) throws SQLException {
				ComPriceStoreHistoryDO compPrice = new ComPriceStoreHistoryDO();
				bindCompPriceStoreHistory(rs, compPrice);				
				return compPrice;
			}
		});

2、查询 带查询参数  并返回自定义类型

使用这个方法：

@Override
    public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
        return query(sql, new RowMapperResultSetExtractor<T>(rowMapper));
    }

例如： （如果参数 不想拼接可以使用  query(String sql, Object[] args, RowMapper<T> rowMapper)）

public List<CompPriceStore> qryCompPriceReport(String date,String storeNbr) {
		
		String sql = " SELECT * FROM CompPriceStore s left join (select club,item_nbr,max(event_expiration_date) event_expiration_date from [dbo].[was_schedule] where '"+date+"' <=event_expiration_date group by club,item_nbr) h "+
					"   on (s.store_nbr=h.club or h.club='All Club') and s.item_nbr=h.item_nbr "+
					"	where h.item_nbr is null";
		if(!storeNbr.isEmpty()) {
			sql = sql + " and (s.store_nbr='" + storeNbr + "' or s.store_nbr='All Club')";
		}
			return jdbcTemplate.query(sql, new RowMapper<CompPriceStore>() {

			@Override
			public CompPriceStore mapRow(ResultSet rs, int rowNum) throws SQLException {
				CompPriceStore compPrice = new CompPriceStore();
				bindCompPriceStore(rs, compPrice);				
				return compPrice;
			}
		});
}

3、queryForMap

       //queryForMap(String sql) ==> Map<String, Object>
        Map<String, Object> score_map = template.queryForMap("select * from t_score where id=1");
        System.out.println(score_map);//{id=1, sid=20181515114, chinese=54, math=54, english=96}


        //queryForMap(String sql,Object... args);
        Map<String, Object> score_map2 = template.queryForMap("select * from t_score where id=?", 1);
        System.out.println(score_map2);//{id=1, sid=20181515114, chinese=54, math=54, english=96}


        //queryForMap(String sql ,Object[] args,int[] argTypes) ==> Map<String, Object>
        Object[] args = new Object[]{1};
        int[] argsType = new int[]{Types.INTEGER};
        Map<String, Object> score_map3 = template.queryForMap("select * from t_score where id=?", args, argsType);
        System.out.println(score_map3);//{id=1, sid=20181515114, chinese=54, math=54, english=96}

3.1 queryForList

//queryForList(String sql,Object... args)  ==>  List<Map<String, Object>>
        List<Map<String, Object>> maps = template.queryForList("select * from t_score where english>=?", 80);
        for (Map<String, Object> m : maps) System.out.println(m);
        //输出
        {id=1, sid=20181515114, chinese=54, math=54, english=96}
        {id=2, sid=20181515101, chinese=55, math=80, english=99}
        {id=7, sid=20181515103, chinese=50, math=20, english=88}

3.2 queryForRowSet

       //queryForRowSet(String sql); ==> SqlRowSet == ResultSet
        SqlRowSet sqlRowSet = template.queryForRowSet("select * from t_score");
        //sqlRowSet == ResultSet
        if(sqlRowSet.isFirst())sqlRowSet.previous();
        while (sqlRowSet.next()){
            System.out.println("id:"+sqlRowSet.getInt(1));
            //todo
        }


//         使用方式同上
        template.queryForRowSet(String sql, Object...args);
        template.queryForRowSet(String sql,Object[] args,int[] argTypes);

插入：

1、单个对象：插入 并且可以获取 自增id

@Resource(name = "jdbcTemplate")
 private JdbcTemplate jdbcTemplate;

public int insertDataToBatch(BatchDO batchDO) {
		// 这个 注入会报错
		NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
		KeyHolder holder = new GeneratedKeyHolder();
		String sql = "INSERT INTO [dbo].[batch]([send_mail_status],[create_time],[operation_user],[type]) VALUES(:sendMailStatus,getdate(),:operationUser,:type)";
		SqlParameterSource parameters = new MapSqlParameterSource()
		.addValue("sendMailStatus", batchDO.getSendMailStatus())
			.addValue("operationUser", batchDO.getOperationUser())
			.addValue("type", batchDO.getType());
		nameJdbc.update(sql,parameters,holder);
		batchDO.setId(holder.getKey().intValue());
		// 获取id 
		return batchDO.getId();
} 

1.2、单个对象： 插入数据

public void addCurrSchedule(String eventID, int club, int itemNbr, BigDecimal itemPrice,
			Date effectiveDate, Date swfEffectiveDate,Date eventEffectiveDate,  String lastOpuser) {
		String sql = "INSERT INTO was_schedule(eventID,club,item_nbr,item_price,effective_date,swf_effective_date,event_expiration_date,last_opuser,ctime) VALUES(?,?,?,?,?,?,?,?,getdate())";
		jdbcTemplate.update(sql, new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setString(1, eventID);
				ps.setInt(2, club);
				ps.setInt(3, itemNbr);
				ps.setBigDecimal(4, itemPrice);
				// 预警字段
				ps.setDate(5, new java.sql.Date(effectiveDate.getTime()));
				// 生效字段
				ps.setDate(6, new java.sql.Date(swfEffectiveDate.getTime()));
				// 结束字段
				ps.setDate(7, new java.sql.Date(eventEffectiveDate.getTime()));
				ps.setString(8, lastOpuser);
			}
		});
}

1.3、单个插入也可以使用： update(String sql, Object... args)

public int addData(String key1,String key2) {
		String sql = "INSERT INTO [dbo].[userRole]([role1],[user1]) VALUES(?,?)";
		Object args[] = {key1,key2};
		return jdbcTemplate.update(sql,args);
		
}

1.4、批量 ：List<Map<String, Object>> list 插入数据

  

/**
	  * 批量
	 * @param keyName
	 * @return
	 */
	public void bathcInsert(String key1,List<Map<String,Object>> key2) {
		String sql = "INSERT INTO [dbo].[new_system_config]([key_name],[key_value]) VALUES(?,?)";
		BatchPreparedStatementSetter batchPreparedStatementSetter = new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				Map<String,Object> temp= key2.get(index);	
				ps.setObject(1, key1);
				ps.setObject(2, temp.get("key"));
			}
			@Override
			public int getBatchSize() {
				return keyValueList.size();
			}
		};
		jdbcTemplate.batchUpdate(sql, batchPreparedStatementSetter);
	}

1.5、批量： List<对象> list 插入数据

public void addBatchCurrSchedule(List<WasSchedule> list, String lastOpUser) {
		String sql = "INSERT INTO was_schedule_upload(eventID,club,item_nbr,item_price,effective_date,swf_effective_date,event_expiration_date,last_opuser,ctime) VALUES(?,?,?,?,?,?,?,?,getdate())";
		BatchPreparedStatementSetter batchPreparedStatementSetter = new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				WasSchedule wasSchedule = list.get(index);
				ps.setString(1, wasSchedule.getEventID());
				ps.setString(2, wasSchedule.getClub());
				ps.setInt(3, wasSchedule.getItem_nbr());
				ps.setBigDecimal(4, wasSchedule.getItem_price());
				ps.setDate(5, new java.sql.Date(wasSchedule.getEffective_date().getTime()));
				ps.setDate(6, new java.sql.Date(wasSchedule.getSwf_effective_date().getTime()));
				ps.setDate(7, new java.sql.Date(wasSchedule.getEvent_expiration_date().getTime()));
				ps.setString(8, lastOpUser);
			}

			@Override
			public int getBatchSize() {
				return list.size();
			}
		};
		jdbcTemplate.batchUpdate(sql, batchPreparedStatementSetter);
}

更新：

1.0、单个对象修改：（4 种方式）

  @Autowired
  private 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() {
            @Override
            public 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,}
                );
 
    }

  

2、JdbcTemplate 常用的使用方法 参考博客

https://blog.youkuaiyun.com/qq_43058685/article/details/104370689?utm_medium=distribute.pc_relevant.none-task-blog-title-11&spm=1001.2101.3001.4242