前言

在以往寫過幾篇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();

匆忙所寫，不確定有沒有問題，有的話聯系我~