Hibernate对接Oracle数据库时带排序的分页功能失效求助

问题描述 #

在Spring JPA整合Hibernate开发表格类查询场景的带排序分页功能时，Oracle10gDialect会自动生成如下SQL：

select row_.*, rownum rownum_ from ( 
select table_.tablefield1, table_.tablefield2, table_.tablefield3... 
from table table_ where <condition>
order by table_tablefield1 desc 
) row_ where rownum <= 5

该SQL写法中，外层rownum条件会打乱子查询内的order by排序逻辑，导致子查询排序不生效，实际业务场景已复现该问题：不传入排序字段时分页功能运行完全正常，传入排序字段后返回结果排序不符合预期。
此前已在Hibernate ORM官方问题追踪平台提交编号为HHH-14924的缺陷工单，超过6个月未收到反馈，需要在不修改Hibernate自动生成SQL的前提下解决该问题。

运行环境 #

• Spring Boot 2.2.0
• Java 8
• Linux操作系统
• Oracle 19.0.0.0.0

备注：本问题并非「Oracle查询排序后如何限制返回行数」的重复问题，核心限制为无法直接修改Hibernate自动生成的SQL，请在标记重复前核对问题标签。

可行解决方案 #

方案1：更换JPA使用的Oracle方言（优先推荐） #

当前使用的数据库为Oracle 19c，完全兼容Oracle 12c的语法规范，直接将Hibernate方言替换为Oracle12cDialect即可解决问题。Oracle12cDialect会使用12c之后支持的FETCH FIRST n ROWS ONLY标准分页语法生成SQL，不会出现rownum嵌套打乱排序的问题，且不需要修改任何业务代码，无侵入。
修改Spring Boot配置文件中JPA的方言配置即可：

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect

方案2：自定义方言修正分页SQL生成逻辑 #

如果因为历史版本兼容原因无法直接升级到12c方言，可以自定义类继承Oracle10gDialect，重写分页SQL生成的相关方法，将原有两层rownum嵌套的生成分页逻辑，替换为Oracle传统三层嵌套分页写法：最内层做条件查询+排序，中间层基于排序后的结果生成rownum编号，最外层根据分页参数截取指定范围的行，保证rownum生成在排序逻辑执行完成之后，不会干扰排序结果。最后将自定义的方言类配置到JPA的方言参数中即可。

方案3：排序规则追加唯一键兜底 #

如果不方便修改方言配置，可以在所有带排序的分页查询逻辑中，在业务指定的排序字段后，追加表主键/唯一键作为次级排序规则，保证排序结果的全局唯一性，避免Oracle在排序字段值存在重复时返回顺序不稳定，导致分页排序错乱。该方案需要调整所有分页查询的排序传参，侵入性相对较高。

内容的提问来源于stack exchange，提问作者Corzar