Spring Boot3/Hibernate6分页原生SQL生成错误问题求助

我把项目迁移到Spring Boot 3（对应Hibernate 6.1.7.Final）时，碰到了原生分页查询的异常问题。以下是简化后的复现代码：

@Query(
        value = """
                select s.id as id, s.name as name, gp.points
                from specialist s
                left join (select q.specialist_id, listagg(q.points, ';') as points from qualification q group by q.specialist_id) gp on gp.specialist_id = s.id
                where name like :name
                """
        , nativeQuery = true)
Page<SpecialistOverview> overview(@Param("name") String name, Pageable pageable);

Hibernate 6生成的SQL出现了明显错误：分页语句fetch first ? rows only被错误嵌入到listagg函数的分隔符字面量里，导致SQL执行时触发DataIntegrityViolationException，提示参数不匹配（第二个?属于被误改的字面量，并非真正的分页参数）：

select s.id as id, s.name as name, gp.points
from specialist s
         left join (select q.specialist_id, listagg(q.points, ' fetch first ? rows only;') as points
                    from qualification q
                    group by q.specialist_id) gp on gp.specialist_id = s.id
where name like ?
order by name asc

而在Spring Boot 2.7.9（Hibernate 5.6.15.Final）中生成的SQL是正常的：

select s.id as id, s.name as name, gp.points
from specialist s
         left join (select q.specialist_id, listagg(q.points, ';') as points
                    from qualification q
                    group by q.specialist_id) gp on gp.specialist_id = s.id
where name like ?
order by name asc
limit ?

下面是几个可行的解决办法：

• 指定单独的count查询
  在@Query中添加countQuery属性，手动编写统计总数的SQL，让Hibernate分别处理主查询和分页统计，避免干扰主查询的字符串字面量：

  @Query(
          value = """
                  select s.id as id, s.name as name, gp.points
                  from specialist s
                  left join (select q.specialist_id, listagg(q.points, ';') as points from qualification q group by q.specialist_id) gp on gp.specialist_id = s.id
                  where name like :name
                  """,
          countQuery = """
                  select count(*)
                  from specialist s
                  where name like :name
                  """,
          nativeQuery = true)
  Page<SpecialistOverview> overview(@Param("name") String name, Pageable pageable);
  

• 嵌套子查询隔离listagg语句
  将包含listagg的子查询再嵌套一层，规避Hibernate分页解析逻辑对内部字符串的误处理：

  @Query(
          value = """
                  select s.id as id, s.name as name, gp.points
                  from specialist s
                  left join (
                      select * from (
                          select q.specialist_id, listagg(q.points, ';') as points from qualification q group by q.specialist_id
                      ) as inner_gp
                  ) gp on gp.specialist_id = s.id
                  where name like :name
                  """,
          nativeQuery = true)
  Page<SpecialistOverview> overview(@Param("name") String name, Pageable pageable);
  

• 升级Hibernate版本
  这个问题属于Hibernate 6早期版本的SQL解析bug，升级到Hibernate 6.2及以上版本（对应Spring Boot 3.1+），官方大概率已经修复了该字符串字面量解析的问题。

• 改用HQL查询（若业务允许）
  如果场景适配，将原生SQL替换为HQL编写，让Hibernate更安全地处理分页和函数调用，从根源避免解析冲突。

内容的提问来源于stack exchange，提问作者F. Labusch