在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);
1
2

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

注意类的路径写完整

@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);
1
2
3
4
5
6
7

这里为什么映射了一个新的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"
			}
		]
	},
	……
]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

也就是说要把月份中的每一天抽取出来，再在下面放每个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);
1
2
3
4
5

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

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);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

再复杂一些

通过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"
                }
            ]
        },
        ……
	]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

此时的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);

1
2
3
4
5
6
7
8
9
10
11
12
13

这样就直接把总数和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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

匆忙所写，不确定有没有问题，有的话联系我~