在Hibernate中实现递归查询遇问题，请求技术协助

我想要在Hibernate中实现以下递归CTE查询，但始终无法得到预期结果，能否有人协助解决？

原SQL查询 #

with recursive FolderHierarchy as (
select
    folder_id
from
    iam.ice_folder
where
    workspace_id = 'wksc-5cc9a319-86bf-5569-beb8-76f1e9dfc886'
    and is_deleted = 0
    and folder_id = 'fldr-4935a498-322d-5efa-bf2c-790b15b0c5f4'
union
select
    f.folder_id
from
    iam.ice_folder f
join FolderHierarchy fh on
    f.parent_folder = fh.folder_id
)
select
    folder_id
from
    FolderHierarchy;

我尝试的代码 #

public List<String> getAllNestedFolderIds(String folderId, String workspaceId) {
    String sqlQuery =
        "with recursive FolderHierarchy as (select folder_id from iam.ice_folder where workspace_id = :workspaceId and is_deleted = 0 and folder_id = :folderId union select f.folder_id from iam.ice_folder f join FolderHierarchy fh on f.parent_folder = fh.folder_id) select folder_id from FolderHierarchy";
    Query query =
        entityManager
            .createNativeQuery(sqlQuery)
            .setParameter(WORKSPACE_ID, workspaceId)
            .setParameter(QueryAttributes.FOLDER_ID, folderId);
    return query.getResultList();
}

排查与修复建议 #

• 核对参数常量名：确认WORKSPACE_ID和QueryAttributes.FOLDER_ID的字符串值是否与SQL中的:workspaceId、:folderId完全一致（包括大小写），Hibernate参数匹配是严格区分大小写的，不匹配会导致参数无法正确绑定。
• 补全递归部分的过滤条件：原SQL的递归分支没有限制workspace_id和is_deleted，会导致查询到不属于目标工作区或已删除的文件夹。修改SQL，在union后的查询中添加过滤条件：

  with recursive FolderHierarchy as (
  select
      folder_id
  from
      iam.ice_folder
  where
      workspace_id = :workspaceId
      and is_deleted = 0
      and folder_id = :folderId
  union
  select
      f.folder_id
  from
      iam.ice_folder f
  join FolderHierarchy fh on
      f.parent_folder = fh.folder_id
  where f.workspace_id = :workspaceId and f.is_deleted = 0 -- 新增过滤条件
  )
  select
      folder_id
  from
      FolderHierarchy;
  

• 指定结果类型：createNativeQuery默认返回Object列表，直接转换为List<String>可能出现类型异常，建议指定结果类型：

  Query query = entityManager.createNativeQuery(sqlQuery, String.class);
  

• 开启SQL日志验证：在Hibernate配置中开启hibernate.show_sql=true，查看实际执行的SQL语句和参数绑定情况，对比原SQL确认是否存在差异。

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