Spring Boot 3.3.4升级后DB2分页（Pageable）SQL语法错误问题

将Spring Boot项目从2.7.18版本升级到3.3.4版本后，在DB2 9.5中使用分页（Pageable）功能时遇到SQL语法错误。仅当传入的页码大于0时会出现问题，页码为0时查询正常。

预期行为 #

在DB2中，分页查询应与Spring Boot 2.7.18版本表现一致，对任意页码都能正常执行。

实际行为 #

页码大于0时，DB2返回以下SQL语法错误：

2024-10-17 10:17:42,377 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - DB2 SQL Error: SQLCODE=-417, SQLSTATE=42609, SQLERRMC=null, DRIVER=4.12.56
2024-10-17 10:17:42,381 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-417;42609;, DRIVER=4.12.56
2024-10-17 10:17:42,393 ERROR org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [sql code in here] [DB2 SQL Error: SQLCODE=-417, SQLSTATE=42609, SQLERRMC=null, DRIVER=4.12.56] [n/a]; SQL [n/a]] with root cause
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-417, SQLSTATE=42609, SQLERRMC=null, DRIVER=4.12.56

生成的SQL对比 #

Spring Boot 2.7.18版本生成的SQL #

SELECT *
FROM   (SELECT inner2_.*, ROW_NUMBER() OVER (ORDER BY ...) AS rownumber_
        FROM (
            -- Actual query here
        ) AS inner2_) AS inner1_
WHERE  rownumber_ > ?
ORDER  BY rownumber_

Spring Boot 3.3.4（搭配Hibernate 6）生成的SQL #

SELECT *
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ...) rn
        FROM ...) r_0_
WHERE  r_0_.rn <= ? + ? AND r_0_.rn > ?
ORDER  BY r_0_.rn

Repository接口及调用代码 #

public interface OfferRepository extends CrudRepository<Offer, Integer> {

        @Query(value = "select offer from Offer offer join OrbisListCompany company on offer.companyId = company.companyId "
            + " join OrbisListCompanyPublication companyPub on company.companyId = companyPub.companyId join OfferPublication offerPub on offer.offerId = offerPub.offerId "
            + "where offer.companyId = :companyId and  offer.product.productId = :productId and  company.visible = true and companyPub.publicationId = :pubId and offerPub.publicationId = :pubId  ")
        List<Offer> findByPublicationIdAndCompanyIdAndProductIdAndVisible(@Param("pubId") Integer pubId,@Param("companyId") Integer companyId,@Param("productId") Integer productId,@Param("pageable") Pageable pageable);
}

Integer pageablePage = (Objects.isNull(page) || page == 0 ) ? 0 : page-1;
Integer pageSize = Objects.isNull(size) ? PAGE_SIZE : size;
Pageable pageable = PageRequest.of(pageablePage, pageSize);
List<Offer> offers = offerRepo.findByPublicationIdAndCompanyIdAndProductIdAndVisible(pubId, companyId, productId, pageable);

已尝试的解决方法 #

• 根据相关帖子建议修改hibernate dialect属性，使用自定义DB2方言，但问题仍存在。
• 查阅Hibernate 6版本的SQL生成变更，但未找到适用于当前环境的解决方案。

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