目录
- 前言
- 示例
- 简单示例
- 只查询部分字段，映射到一个实体类中
- 只查询部分字段时，也可以使用List<Object[]>接收返回值
- 再复杂一些
前言
在以往写过几篇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();
匆忙所写，不确定有没有问题，有的话联系我~
