Spring Boot 3.2.0升级至3.2.1后查询执行异常求助

问题原因 #

Spring Boot 3.2.1升级了依赖的Hibernate ORM版本至6.4.2.Final，该版本对JPQL函数的参数类型校验做了严格增强。你的查询中coalesce(:#{#request.name},null)的第二个参数null没有明确的类型标识，Hibernate无法推断其匹配的函数返回类型，因此抛出FunctionArgumentException。

解决方案 #

方案1：简化查询条件（推荐） #

原查询中coalesce(:#{#request.name},null) is null完全等价于:#{#request.name} is null，直接简化条件即可避免类型校验问题：

@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);

方案2：为null指定明确类型 #

如果需要保留coalesce写法，可通过cast为null指定类型（比如字符串类型），让Hibernate能识别参数类型：

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

方案3：降级Hibernate版本 #

在pom.xml中指定兼容的Hibernate版本（即Spring Boot 3.2.0使用的Hibernate版本），绕过严格的类型校验：

<dependencies>
    <!-- 其他依赖保持不变 -->
    <dependency>
        <groupId>org.hibernate.orm</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>6.4.1.Final</version>
    </dependency>
</dependencies>

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