Spring JPA操作IBM DB2时offset≥1无法获取记录问题

问题详情 #

• 在Spring Boot应用中基于分页逻辑从IBM DB2数据库获取记录，当offset=0时，无论size设置为多少服务均能正常调用；但offset设为1、2或其他值时，服务直接失败。

抛出的异常 #

Spring数据访问异常 #

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

DB2驱动异常 #

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;ROWS * AT YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINUTE, DRIVER=4.21.29

Hibernate生成的分页SQL（offset=1、size=2时） #

SELECT * FROM (SELECT inner2_.*, rownumber() over (order by order of inner2_) as rownumber_ from (select fields from table_name fetch first rows only) as inner2_) as inner1_ where rownumber_ > 2 order by rownumber_
注：加粗部分为实际查询语法，未加粗部分为实体字段名。

代码调用方式 #

Repository继承自JpaRepository，通过以下代码返回Page<T>：

Repository.findAll(PageRequest.of(offset, size));

解决方法 #

1. 配置正确的Hibernate DB2方言
   DB2不同版本对应不同的Hibernate方言，需在配置文件中指定正确的方言：

   • 针对DB2 LUW（Linux/Unix/Windows），配置：

     spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2Dialect
     

   • 针对DB2 z/OS，配置：

     spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2zDialect
     

   正确的方言会让Hibernate生成符合DB2语法规范的分页SQL，避免语法错误。

2. 手动编写分页查询（方言配置无效时）
   在Repository中自定义符合DB2语法的分页查询：

   • JPQL方式：

     @Query(value = "SELECT t FROM YourEntity t", countQuery = "SELECT COUNT(t) FROM YourEntity t")
     Page<YourEntity> findAllWithPagination(Pageable pageable);
     

   • 原生SQL方式：

     @Query(value = "SELECT * FROM table_name ORDER BY your_sort_column OFFSET ?1 ROWS FETCH NEXT ?2 ROWS ONLY",
           countQuery = "SELECT COUNT(*) FROM table_name",
           nativeQuery = true)
     Page<YourEntity> findAllNativePagination(int offset, int size);
     

   调用时传入对应的offset和size即可。

3. 检查DB2驱动版本
   确保使用的DB2驱动版本与DB2服务器版本兼容，建议使用较新版本的驱动（如db2jcc4.jar的最新稳定版），旧版本驱动可能对新的分页语法支持不足。

内容的提问来源于stack exchange，提问作者Rakshith Rahul B