在Spring Data JPA中使用@Query注解

前言

在以往写过几篇spring data jpa相关的文章，分别是
Spring Data JPA 使用JpaSpecificationExecutor实现多条件查询（分页查询和非分页查询）
Spring Data JPA实现分页多条件查询2

都是通过代码而不是sql来完成查询的，但是在做复杂情况的查询时，难免会用到@Query写sql语句。

示例

简单示例

在@Query中用:paramName标识参数，再用@Param来指定方法参数与查询语句中的参数之间的映射关系。
例如：

@Query("select r from RelationDO r where r.indexCode in :idList")
List<RelationDO> findByIdListIn(@Param("idList") Collection<String> idList);

只查询部分字段，映射到一个实体类中

注意类的路径写完整

@Query("SELECT new com.xxx.service.impl.bo.RecordBO(e.code, e.day, e.total, e.success, e.fail, e.app) " +"FROM RecordDO e " +"WHERE e.code = :code AND e.day = :day " +"AND e.app in :appCodes")
List<RecordBO> findCalendarDetail(@Param("code") String code,@Param("day") String day,@Param("appCodes") List<String> appCodes);

这里为什么映射了一个新的BO出来呢… 是RecordDO中有一个id字段，在实体类中添加了@Id注解（实体必须有@Id，不然会报错），这个id字段本来设计的是不会重复的，但是后续经过一些改动，它在某些情况下会重复了，这个时候就会有一个问题，我直接select整个RecordDO，id字段重复的它会当成同一条记录（不确定为什么，但是实际跑出来确实是这样），但我又不想再去改表结构，因此这里我select的时候直接省略了id字段，就正常了。（可能不是一个很好的解决方案，但是确实是可以这么做的）
只查询部分字段在表字段较多，所需字段比较少的时候还是可以用的。

只查询部分字段时，也可以使用List<Object[]>接收返回值

例如我现在需要用code和month查出这么一个结果：

[{"day":"20240601","result":[{"rate": 98.77"app": "0001"},{"rate": 95.32"app": "0002"}]},{"day":"20240602","result":[{"rate": 95.65"app": "0001"},{"rate": 96.89"app": "0002"}]},……
]

也就是说要把月份中的每一天抽取出来，再在下面放每个app对应的明细
这个时候写sql：

@Query("SELECT e.day, e.app, e.success, e.total" +"FROM RecordDO e " +"WHERE e.code = :code AND SUBSTRING(e.day, 1, 6) = :month AND e.total > 0")
List<Object[]> findByMonth(@Param("code") String code,@Param("month") String month);

调用上述方法后封装返回数据：

List<CalendarBO> calendarBOS = Lists.newArrayList();
List<Object[]> resultList = recordRepository.findByMonth(code,month);if (!CollectionUtils.isEmpty(resultList)){for (Object[] result : resultList) {String day = (String) result[0];String app = (String) result[1];Integer success = (Integer) result[2];Integer total = (Integer) result[3];double rate = (double) success * 100 / total ;double roundedRate = Math.round(rate * 100.0) / 100.0;CalendarBO.Result result = CalendarBO.Result.builder().app(app).rate(roundedRate).build();// 组装返回内容Optional<CalendarBO> optionalBO = calendarBOS.stream().filter(bo -> bo.getDay().equals(day)).findFirst();// 该日期值不存在则创建 存在则添加不同app的记录if (!optionalBO.isPresent()) {CalendarBO calendarBO = CalendarBO.builder().day(day).result(Collections.singletonList(result)).build();calendarBOS.add(calendarBO);}else {CalendarBO calendarBO = optionalBO.get();List<CalendarBO.Result> results = calendarBO.getResult();results.add(result);calendarBO.setAssessResult(results);}}
}

再复杂一些

通过beginMonth、endMonth和appCodes筛选，需要返回的数据格式如下
这里的pass是有一个标准rate，当data中success/total（rate） > 标准rate时单项视为pass，而total中的total则代表该月份区间共统计次数。

{"total": [{"total": 13,"pass": 13,"app": "0001"},{"total": 13,"pass": 12,"app": "0002"}],"data": [{"code": "101","month": 202406,"result": [{"total": 13,"success": 13,"rate": 100,"app": "0001"},{"total": 12,"success": 11,"rate": 92,"app": "0002"}]},{"code": "102","month": 202406,"result": [{"total": 15,"success": 15,"rate": 100,"app": "0001"}]},……]
}

此时的sql:

@Query("SELECT e.code, e.app, SUBSTRING(e.day, 1, 6), COUNT(e.statId), " +"SUM(CASE WHEN (CAST(e.success AS double) / e.total) >= :rate THEN 1 ELSE 0 END) " +"FROM RecordDO e " +"WHERE e.code = :code" +"    AND SUBSTRING(e.day, 1, 6) BETWEEN :beginMonth AND :endMonth " +"    AND ((:appCodes) IS NULL OR e.app IN (:appCodes)) AND e.total > 0 " +"GROUP BY e.code, e.app, SUBSTRING(e.day, 1, 6)")
List<Object[]> findByCodeGroupBy(@Param("code") String code,@Param("beginMonth") String beginMonth,@Param("endMonth") String endMonth,@Param("appCodes") List<String> appCodes,@Param("rate") Double rate);

这样就直接把总数和pass的计数给取出来了（statId和总数可以对应）
调用上述方法后封装返回数据，和之前基本一致：

// 根据分类计算总数的映射
Map<String, Integer> totalCounts = new HashMap<>();
Map<String, Integer> passCounts = new HashMap<>();
//返回的明细对象
List<DataBO> dataList = new ArrayList<>();//假设已获取到code和标准rate的对应关系passRate
for (Map.Entry<String, Double> entry : passRate.entrySet()) {List<Object[]> resultList = recordRepository.findByCodeGroupBy(entry.getKey(), reqBO.getBeginMonth(),reqBO.getEndMonth(), reqBO.getAppCodeList(), entry.getValue());if (CollectionUtils.isEmpty(resultList)) {continue;}for (Object[] result : resultList) {String code = (String) result[0];String app = (String) result[1];String month = (String) result[2];Long totalLong = (Long) result[3];String total = totalLong.toString();Long successLong = (Long) result[4];String success = successLong.toString();double rateDouble = Double.parseDouble(success) / Double.parseDouble(total);String rate = String.format("%.2f", rateDouble * 100);DataBO.Result result = DataBO.Result.builder().total(total).success(success).rate(rate).app(app).build();//查看dataList中是否该编码和月份的数据已存在 不存在则新建 存在则获取DataBO data = dataList.stream().filter(a -> a.getCode().equals(code) && a.getMonth().equals(month)).findFirst().orElseGet(() -> {DataBO newAccount = new DataBO();newAccount.setCode(code);newAccount.setMonth(month);accountList.add(newAccount);return newAccount;});if (data.getResult() == null) {data.setResult(Lists.newArrayList());}data.getResult().add(result);// 更新统计totalCounts.put(app, totalCounts.getOrDefault(app, 0) + Integer.parseInt(total));passCounts.put(app, passCounts.getOrDefault(app, 0) + Integer.parseInt(success));}
}
//组装统计类
totalCounts.entrySet().stream().map(entry -> {String app = entry.getKey();int total = entry.getValue();int pass = passCounts.getOrDefault(app, 0);TotalCountBO totalCount = new TotalCountBO();totalCount.setAppCode(app);totalCount.setTotal(String.valueOf(total));totalCount.setPass(String.valueOf(pass));return totalCount;}).collect(Collectors.toList());return RespBO.builder().data(dataList).total(totalCounts).build();