Spring Data JPA中Native SQL CTE查询被Hibernate错误转换求助

问题原因 #

Spring Data JPA处理原生SQL分页查询时，会自动解析提供的SQL来生成对应的count查询以获取总记录数。但对于包含CTE（公共表表达式）、UNION这类复杂结构的SQL，JPA的自动解析逻辑存在缺陷——它错误地将原SQL中的where关键字识别为count函数的参数，生成count(where)这种非法语法，最终触发SQL语法错误。

解决方案 #

针对该问题，有两种可靠的解决方式：

1. 手动指定count查询语句

直接在@Query注解中通过countQuery参数提供正确的count统计SQL，跳过JPA的自动生成逻辑，确保count查询语法完全合规。

修改后的代码示例：

final String CATEGORY_SEARCH_SQL_QUERY = """
             with cte as (
              select * from category
                          where 
                          is_user_specific=false
                          and 
                          category_lablel LIKE LOWER(CONCAT('%', :categoryLabelQuery, '%'))
                          union
                          select *
                          from category
                          where is_user_specific=true
                          and created_by = :user
                          and category_lablel LIKE LOWER(CONCAT('%', :categoryLabelQuery, '%')))
             select * from cte;
             """;

final String CATEGORY_SEARCH_COUNT_QUERY = """
             with cte as (
              select * from category
                          where 
                          is_user_specific=false
                          and 
                          category_lablel LIKE LOWER(CONCAT('%', :categoryLabelQuery, '%'))
                          union
                          select *
                          from category
                          where is_user_specific=true
                          and created_by = :user
                          and category_lablel LIKE LOWER(CONCAT('%', :categoryLabelQuery, '%')))
             select count(*) from cte;
             """;

@Query(value = CATEGORY_SEARCH_SQL_QUERY, countQuery = CATEGORY_SEARCH_COUNT_QUERY, nativeQuery = true)
Page<CategoryEntity> searchforCategory(String categoryLabelQuery, String user, Pageable pageable);

2. 简化SQL结构，避免UNION/CTE

将原有的两个UNION查询合并为单个查询，用OR条件组合逻辑，让JPA能够正确解析并生成合法的count查询，这种方式更简洁。

修改后的代码示例：

final String CATEGORY_SEARCH_SQL_QUERY = """
             select * from category
             where 
             category_lablel LIKE LOWER(CONCAT('%', :categoryLabelQuery, '%'))
             and (
                 is_user_specific=false 
                 or (is_user_specific=true and created_by = :user)
             )
             """;

@Query(value = CATEGORY_SEARCH_SQL_QUERY, nativeQuery = true)
Page<CategoryEntity> searchforCategory(String categoryLabelQuery, String user, Pageable pageable);

注：原SQL的union会自动去重，合并后的OR查询不会去重。如果业务需要去重，可在select后添加distinct关键字。

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