Spring Data JPA @Query使用原生SQL如何分页查询

种树人20240819

已于 2024-10-14 21:39:08 修改

阅读量436

点赞数 4

文章标签： sql 数据库 java spring boot

于 2024-08-21 10:26:47 首次发布

本文链接：https://blog.csdn.net/kwok924/article/details/141386572

版权

问题描述

在Spring Boot应用程序中使用Spring Data JPA对某个表的记录进行分组统计，要求对两种状态下的个数和金额统计，并将这两种状态下的统计结果合成一条记录，并对统计后的记录进行分页查询。

产品名	总数	完成数	完成金额
产品一	10	6	100
产品二	20	14	260

由于需要用子查询，用JPQL不好实现（用JPQL写了后启动报错，因为JPQL 不支持使用子查询的方式来构建查询。在 JPQL 中，不能直接在 SELECT 语句中嵌套子查询），就想用原生SQL直接查询，至于分页想着直接传入Pageable pageable参数即可，刚开始写的统计方法如下：

@Query(value = "select t1.uuid, t1.title,t1.count as count1,coalesce(t2.count,0) as count2, coalesce(t2.sum_amount,0) as amount from (\n" +
            "  select uuid, title,count(*) as count\n" +
            "  from table_example\n" +
            "  where org = :org and state != 1\n" +
            "  group by uuid, title\n" +
            ") t1 \n" +
            "left join (\n" +
            "  select uuid, title, count(*) as count, sum(amount) as sum_amount\n" +
            "  from table_example\n" +
            "  where org = :org and state in (9, 10, 11)\n" +
            "  group by uuid, title\n" +
            ") t2 on t1.uuid = t2.uuid and t1.title = t2.title", nativeQuery = true)
    Page<Object[]> getStatistics(String org, Pageable pageable);

这段代码运行起来没有报错，同时测试了几个查询也没有问题。但在联调时发现如何满足条件的记录是n条，pageSize小于等于n时查询就报错，大于n时就没问题。

问题分析

Spring Data JPA @Query注解如果为原生SQL时，不支持直接传入Pageable pageable参数对查询结果分页，参见Spring官网的说明：

https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html
在这里插入图片描述

解决方法

方法一

按照Spring Data JPA @Query注解的说明，传入countQuery参数，参数值为查询满足条件记录的原生SQL语句，这样在Service层代码中直接传入Pageable参数就可以获取到分页后的结果，修改后的查询方法如下:

 @Query(value = "select t1.uuid, t1.title,t1.count as count1,coalesce(t2.count,0) as count2, coalesce(t2.sum_amount,0) as amount from (\n" +
            "  select uuid, title,count(*) as count\n" +
            "  from table_example\n" +
            "  where org = :org and state != 1\n" +
            "  group by uuid, title\n" +
            ") t1 \n" +
            "left join (\n" +
            "  select uuid, title, count(*) as count, sum(amount) as sum_amount\n" +
            "  from table_example\n" +
            "  where org = :org and state in (9, 10, 11)\n" +
            "  group by uuid, title\n" +
            ") t2 on t1.uuid = t2.uuid and t1.title = t2.title", 
		countQuery = "select count(*) from (\n" +
            "  select uuid, title,count(*) as count\n" +
            "  from table_example\n" +
            "  where org = :org and state != 1\n" +
            "  group by uuid, title\n" +
            ") t1 \n" +
            "left join (\n" +
            "  select uuid, title, count(*) as count, sum(amount) as sum_amount\n" +
            "  from table_example\n" +
            "  where org = :org and state in (9, 10, 11)\n" +
            "  group by uuid, title\n" +
            ") t2 on t1.uuid = t2.uuid and t1.title = t2.title", 
			nativeQuery = true)
    Page<Object[]> getStatistics(String org, Pageable pageable);

方法二

不使用Spring Data JPA进行分页，自己实现分页，Repository就要提供两个方法，一个方法查询分页记录，另一个方法查询总记录数：

@Query(value = "select t1.uuid, t1.title,t1.count as count1,coalesce(t2.count,0) as count2, coalesce(t2.sum_amount,0) as amount from (\n" +
            "  select uuid, title,count(*) as count\n" +
            "  from table_example\n" +
            "  where org = :org and state != 1\n" +
            "  group by uuid, title\n" +
            ") t1 \n" +
            "left join (\n" +
            "  select uuid, title, count(*) as count, sum(amount) as sum_amount\n" +
            "  from table_example\n" +
            "  where org = :org and state in (9, 10, 11)\n" +
            "  group by uuid, title\n" +
            ") t2 on t1.uuid = t2.uuid and t1.title = t2.title limit :offset, :limit", nativeQuery = true)
    Page<Object[]> getStatistics(String org,  int offset, int limit);
	
	
	 @Query(value = "select count(*) from (\n" +
            "  select uuid, title,count(*) as count\n" +
            "  from table_example\n" +
            "  where org = :org and state != 1\n" +
            "  group by uuid, title\n" +
            ") t1 \n" +
            "left join (\n" +
            "  select uuid, title, count(*) as count, sum(amount) as sum_amount\n" +
            "  from table_example\n" +
            "  where org = :org and state in (9, 10, 11)\n" +
            "  group by uuid, title\n" +
            ") t2 on t1.uuid = t2.uuid and t1.title = t2.title", nativeQuery = true)
    long getCount(String org);

在Service层代码中分别调用这两个方法返回分页结果:

 ...
 int offset = (pageNum - 1) * pageSize;
 List<Object[]> queryResult = repository.getStatistics(org, offset, pageSize);
        long total = repository.getCount(org);
        PageDTO<StatisticsDTO> result = new PageDTO<>();
        result.setPageNum(pageNum);
        result.setPageSize(pageSize);
        result.setTotalPage((int) Math.ceil((double) total / pageSize));
        result.setTotalSize(total);
        result.setData(queryResult.stream().map(item -> {
            StatisticsDTO dto = new StatisticsDTO();
            dto.setUuid((String) item[0]);
            dto.setitle((String) item[1]);
            dto.setCount1(Long.valueOf(String.valueOf(item[2])));
            dto.setCount2(Long.valueOf(String.valueOf(item[3])));
            dto.setAmount((BigDecimal) item[4]);
            return dto;
        }).collect(Collectors.toList()));
        return result;