升级Spring Boot 3.1.1时带distinct的查询启动验证失败求助

问题描述 #

将Spring Boot 3.0.7应用升级至3.1.1后，应用在启动阶段的查询验证环节失败，仅影响SELECT子句包含distinct的分页查询。

受影响的Repository代码（添加distinct后可复现问题） #

@Repository
public interface CountryRepository extends PagingAndSortingRepository<Country, Long> {

    @Query(value = """
                select distinct c.name as name
                from Country c
                where c.name like :name
            """)
    Page<CountryProjection> customFindCountryByName(@Param("name") String name, Pageable pageable);
}

核心报错堆栈 #

Caused by: org.hibernate.query.SemanticException: A query exception occurred [select count(distinct me.name as name) from Country me where me.name like :name]
...
Caused by: org.hibernate.query.sqm.ParsingException: line 1:30 no viable alternative at input 'selectcount(distinctme.nameas'

问题根源 #

Spring Data JPA在处理带distinct的分页查询时，自动生成count统计语句时错误保留了SELECT子句中的别名（as name），生成了非法的HQL语句select count(distinct me.name as name)；同时存在字符串拼接时的空格丢失问题，进一步触发解析器语法错误。

解决方案 #

方案1：手动指定count查询 #

在@Query注解中显式定义countQuery参数，绕过自动生成逻辑：

@Repository
public interface CountryRepository extends PagingAndSortingRepository<Country, Long> {

    @Query(
        value = """
                select distinct c.name as name
                from Country c
                where c.name like :name
            """,
        countQuery = """
                select count(distinct c.name)
                from Country c
                where c.name like :name
            """
    )
    Page<CountryProjection> customFindCountryByName(@Param("name") String name, Pageable pageable);
}

方案2：升级Spring Data JPA版本 #

该问题属于Spring Data JPA 3.1.x的已知bug，升级至3.1.2及以上版本即可修复此类自动生成count查询的语法错误。

内容的提问来源于stack exchange，提问作者Fábio Castilhos