spring data jpa使用native sql进行分页查询时需要使用countQuery

问题发现:
使用data jpa的native sql进行分页查询时,发现一个sql grammar语法报错,再三检查确认自己的sql写的没有问题,sql大致为

select * from (select * from table1 where ) as table2 where

的一个子查询结构,
报错为

from (select *) from table1 where ) as table2 where

附近有语法错误,对比发现多了一个)右括号。
最后发现是分页查询的count查询导致的。

题外话:在使用spring data jpa的@Query的native sql进行分页查询时,可以在方法最后一个参数传入pageable做分页查询,当然也可以在native sql中使用limit offset做分页查询,第一种方式会转换为第二种方式执行。

但分页查询不仅仅返回查询分页的数据,同样会返回符合条件的总个数,也就是totalElements以及totalPages。
那么一定会有一个count查询来干这个事,如果你没写count查询(我就没写),jpa会自己帮你生成一个,但jpa生成的不一定是你想要的,问题发现中的语法错误就是jpa帮我们生成的count查询。
正确的是:

select count(*) from (select * from table1 where ) as table2 where

生成的是:

select count(* from (select *) from table1 where ) as table2 where

jpa生成的count查询右括号匹配到了第二个*,也就导致了语法错误。。。。。

拓展:
关于count的执行问题jpa有逻辑如下:
SimpleJpaRepository(crudRepository的默认实现)下的
spring data jpa使用native sql进行分页查询时需要使用countQuery_第1张图片
spring data jpa使用native sql进行分页查询时需要使用countQuery_第2张图片
我们可以看到第一张图中在做分页是传入的第三个参数就是计数查询,而在第二张图中当查询到的数据是少于pageSize时,也就是总数可以在当前查到的第一页算出时,是不需要去执行后续的count查询的,也算是个简单的优化。
官方注解的话是:

The construction of {@link Page} omits a count query if the total can be determined based on the result size and {@link Pageable}.

总结:而在使用方法名规则或者qbc进行分页查询时是不会出现上述问题的,查询sql和计数sql都是一致生成的。而使用natvie sql时jpa会根据你写的sql语句改写count(看起来只是做了一些简单的字符串匹配),于是出现了上述问题。
在使用native sql进行分页查询时记得使用countQuery

你可能感兴趣的