Spring Boot 3.2.0升级到3.2.1后出现ORA-00933查询执行错误

我有一个包含大量查询的Java Spring应用，在Spring Boot 3.2.0及之前版本运行正常。最近仅将Spring Boot版本切换到3.2.1后，开始出现如下错误：

org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing 
SQL [select 
        o1_0.org_id,
        o1_0.name 
     from organizations o1_0 
     where (
            ? is null or 
            trim(BOTH from ?)='' or 
            lower(o1_0.name) like lower(trim(BOTH from ?))
     ) 
     offset ? rows 
     fetch first ? rows only] 
[ORA-00933: SQL命令未正确结束]

错误SQL说明 #

上述执行的SQL中文含义：
从organizations表（别名o1_0）中查询org_id和name字段，查询条件满足以下任一情况即可：

• 第一个参数为空
• 第二个参数去除首尾空格后为空
• 表中name字段转小写后，匹配第三个参数去除首尾空格并转小写的值
  最后执行分页逻辑：跳过指定行数，只获取前指定行数的数据。

POM依赖配置 #

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.2.1</version>
    <relativePath />
</parent>

<properties>
    <java.version>21</java.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
</properties>

<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-security</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc11</artifactId>
    </dependency>

    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
    </dependency>

</dependencies>

相关查询语句 #

@Query("select org  "
     + "  from Organization         org     "
     + " where 1 = 1 "
     + "   and ( "
     + "         :#{#request.name} is null "
     + "         or "
     + "         trim(:#{#request.name}) = '' "
     + "         or "
     + "         lower(org.name) like lower(trim(:#{#request.name}))"
     + "       ) "
)
Page<Organization> getPageByCriteria(@Param("request") GetOrganizationsRequestDto request, Pageable pageable);

环境信息 #

• 数据库：Oracle 11g
• 怀疑方向：ojdbc版本自上一Spring Boot版本后未更新导致问题

解决方案 #

1. 核心原因：Oracle 11g不支持OFFSET/FETCH分页语法
   Oracle 11g仅支持ROWNUM分页，OFFSET ... FETCH FIRST ... ONLY是Oracle 12c及以上版本才支持的语法。Spring Boot 3.2.1升级后，Hibernate默认使用了新的分页语法，导致Oracle 11g无法识别。

2. 指定Hibernate使用Oracle 11g兼容方言
   在配置文件中添加：

   # Hibernate 6.x推荐使用OracleLegacyDialect
   spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.OracleLegacyDialect
   # 若使用旧版Hibernate可指定Oracle11gDialect
   # spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle11gDialect
   

3. 调整ojdbc版本适配Oracle 11g
   ojdbc11为Oracle 12c+设计，Oracle 11g建议使用ojdbc8：

   <dependency>
       <groupId>com.oracle.database.jdbc</groupId>
       <artifactId>ojdbc8</artifactId>
   </dependency>
   

4. 手动确认分页查询逻辑
   若自动分页仍有问题，可显式指定count查询，帮助Hibernate生成正确的分页SQL：

   @Query(value = "select org from Organization org where 1=1 "
           + "and (:#{#request.name} is null or trim(:#{#request.name}) = '' or lower(org.name) like lower(trim(:#{#request.name})))",
           countQuery = "select count(org) from Organization org where 1=1 "
           + "and (:#{#request.name} is null or trim(:#{#request.name}) = '' or lower(org.name) like lower(trim(:#{#request.name})))")
   Page<Organization> getPageByCriteria(@Param("request") GetOrganizationsRequestDto request, Pageable pageable);
   

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