这几天做项目的时候,需求是统计订单表的数据,其中技术持久层的技术是jpa ,用实体映射比较困难。后来先想到jdbc的 JdbcTemplate,可是头说不行,之后用@query 原生语句查询。
1 、 JdbcTemplate实现,较为复杂的sql语句。重点是实体对应的实现。
service里
@Autowired
JdbcTemplate jdbcTemplate;
//计算出昨日流量渠道统计量
public List<OrderChannelQualityDTO> queryByHour(String txNo,Date fromTime,Date toTime) {
//String selectSQL = " select t.id,t.channel_id,t.interface_time,t.trade_time,t.process,t.timeout,t.create_time from t_ar_performance t where t.create_time>=? andt.create_time<=? order by t.create_time desc ,t.channel_id asc ";
String minDate = "round((UNIX_TIMESTAMP(t.update_time)-UNIX_TIMESTAMP(t.created_time))/60)";
String selectSQL = "SELECT SUM(IF(t.status ='success',1,0))as successCount,SUM(IF(t.status ='fail',1,0))as failCount ,"
+ "SUM(CASE WHEN 0<="+minDate+" and "+minDate+"<5 THEN 1 ELSE 0 END) as fiveCount,"
+ "SUM(CASE WHEN 5<="+minDate+" and "+minDate+"<1 THEN 1 ELSE 0 END) as tenCount,"
+ "SUM(CASE WHEN 10<="+minDate+" and "+minDate+"<30 THEN 1 ELSE 0 END) as thirtyContent,"
+ "SUM(CASE WHEN 30<="+minDate+" and "+minDate+"<60 THEN 1 ELSE 0 END) as sixtyContent,"
+ "SUM(CASE WHEN 60<="+minDate+" and "+minDate+"<120 THEN 1 ELSE 0 END) as twohouContent,"
+ "SUM(CASE WHEN 120<="+minDate+" THEN 1 ELSE 0 END) as twohouMoreContent,"
+ "t.resource_id,t.mo,t.province from "
+ "(SELECT a.*,b.resource_id from t_order a LEFT JOIN t_channel b ON a.channel_id = b.id where a.update_time>=? and a.update_time<=? and a.status != 'init') t "
+ "GROUP BY t.mo,t.province,t.resource_id";
List<OrderChannelQualityDTO> list = jdbcTemplate.query(selectSQL, new Object[]{fromTime,toTime},new ARPerformanceDTORowMapper());
if(log.isInfoEnabled()){
//log.info(" sql:{} time:{} return:{} ", selectSQL,startTime,list);
}
return list;
}
private class ARPerformanceDTORowMapper implements RowMapper<OrderChannelQualityDTO> {
@Override
public OrderChannelQualityDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
OrderChannelQualityDTO tarPerformance = new OrderChannelQualityDTO();
tarPerformance.setSuccessCount(rs.getInt("successCount"));
tarPerformance.setFailCount(rs.getInt("failCount"));
tarPerformance.setFiveCount(rs.getInt("fiveCount"));
tarPerformance.setTenCount(rs.getInt("tenCount"));
tarPerformance.setThirtyContent(rs.getInt("thirtyContent"));
tarPerformance.setSixtyContent(rs.getInt("sixtyContent"));
tarPerformance.setTwohourContent(rs.getInt("twohouContent"));
tarPerformance.setTwohourMoreContent(rs.getInt("twohouMoreContent"));
tarPerformance.setResourceId(rs.getString("resource_id"));
tarPerformance.setProvince(rs.getString("province"));
tarPerformance.setMo(rs.getString("mo"));
return tarPerformance;
}
}
2.@query
在持久层内,重点是@query查出是一个List<Object[]> ,没有属性名称对应,只是查出object[],索引值是对应sql的顺序。
String minDate = "round((UNIX_TIMESTAMP(t.update_time)-UNIX_TIMESTAMP(t.created_time))/60)";
@Query(value = "SELECT SUM(IF(t.status ='success',1,0))as successCount,SUM(IF(t.status ='fail',1,0))as failCount ,"
+ "SUM(CASE WHEN 0<="+minDate+" and "+minDate+"<5 THEN 1 ELSE 0 END) as fiveCount,"
+ "SUM(CASE WHEN 5<="+minDate+" and "+minDate+"<1 THEN 1 ELSE 0 END) as tenCount,"
+ "SUM(CASE WHEN 10<="+minDate+" and "+minDate+"<30 THEN 1 ELSE 0 END) as thirtyContent,"
+ "SUM(CASE WHEN 30<="+minDate+" and "+minDate+"<60 THEN 1 ELSE 0 END) as sixtyContent,"
+ "SUM(CASE WHEN 60<="+minDate+" and "+minDate+"<120 THEN 1 ELSE 0 END) as twohouContent,"
+ "SUM(CASE WHEN 120<="+minDate+" THEN 1 ELSE 0 END) as twohouMoreContent,"
+ "t.resource_id,t.mo,t.province from "
+ "(SELECT a.*,b.resource_id from t_order a LEFT JOIN t_channel b ON a.channel_id = b.id where a.update_time>=:fromDate and a.update_time<=:nextDate and a.status != 'init') t "
+ "GROUP BY t.mo,t.province,t.resource_id"
,nativeQuery=true)
List<Object[]> objectFlowList(@Param("fromDate") Date fromDate,@Param("nextDate") Date nextDate);
在 service
//查出昨日流量渠道统计
List<Object[]> channelQualityObjectArrayDTO = channelQualityRepo.objectFlowList(fromDate, nextDate);
List<TChannelQuality> channelQualityList = new ArrayList<TChannelQuality>();
if(channelQualityObjectArrayDTO != null && channelQualityObjectArrayDTO.size() >0){
for(Object[] channelQualityObjectParamDTO :channelQualityObjectArrayDTO){
TChannelQuality channelQuality = new TChannelQuality();
channelQuality.setDay(dateFormat.parse(dateString));
channelQuality.setBizType("flow");
channelQuality.setSuccessCount(getInt((BigDecimal)channelQualityObjectParamDTO[0]));
channelQuality.setFailCount(getInt((BigDecimal)channelQualityObjectParamDTO[1]));
channelQuality.setFiveCount(getInt((BigDecimal)channelQualityObjectParamDTO[2]));
channelQuality.setTenCount(getInt((BigDecimal)channelQualityObjectParamDTO[3]));
channelQuality.setThirtyContent(getInt((BigDecimal)channelQualityObjectParamDTO[4]));
channelQuality.setSixtyContent(getInt((BigDecimal)channelQualityObjectParamDTO[5]));
channelQuality.setTwohourContent(getInt((BigDecimal)channelQualityObjectParamDTO[6]));
channelQuality.setTwohourMoreContent(getInt((BigDecimal)channelQualityObjectParamDTO[7]));
channelQuality.setResourceId(((String)channelQualityObjectParamDTO[8]));
channelQuality.setMo(((String)channelQualityObjectParamDTO[9]));
channelQuality.setProvince(((String)channelQualityObjectParamDTO[10]));
channelQualityList.add(channelQuality);
}
List<TChannelQuality> result =channelQualityRepo.save(channelQualityList);
总结:
JdbcTemplate 是下下策,头让改的时候,竟然问了句为什么JdbcTemplate 不好?感觉好low端。。。。,jpa是一个很强大的持久层框架。