请求协助将JPA原生查询转换为HQL以解决LazyInitializeException

之前用立即关联表查询，数据库数据量上来后给部分关联表开了懒加载，现在没法把下面的原生查询转成HQL，触发了LazyInitializeException，求帮忙。

原生查询代码 #

@Query("SELECT * from tableA a inner join (select DISTINCT ON (auid) * from tableB where " +
"status = 4 order by auid,date_created desc ) b on a.uid = b.auid inner join tableC c on " +
"c.uid = b.uid where a.group_uid = :groupUid and c.condition = 1 and c.status = 0 and " +
"b.type <> 2 and b.enable = true", nativeQuery = true) 
List<TableA> findListA(@Param("groupUid") String groupUid)

临时解决方案 #

先通过原生查询选出tableA的uid，再用以下HQL查询关联数据：

@Query("SELECT a FROM TableA JOIN FETCH a.tableB JOIN a.tableB.tableC WHERE a.uid = :uid")
TableA getOne(@Param("uid") String uid)

转HQL的两种实现方式 #

HQL不支持PostgreSQL的DISTINCT ON语法，得换用子查询或窗口函数实现“每个auid取最新tableB记录”的逻辑，同时通过JOIN FETCH预加载关联数据，避免懒加载异常。

方式一：子查询匹配最新记录

适合JPA版本较低、不支持窗口函数的场景，直接匹配每个TableA对应的最新TableB记录：

@Query("""
    SELECT a 
    FROM TableA a
    JOIN FETCH a.tableB b
    JOIN FETCH b.tableC c
    WHERE a.groupUid = :groupUid
      AND b.status = 4
      AND b.type <> 2
      AND b.enable = true
      AND c.condition = 1
      AND c.status = 0
      AND b = (
          SELECT bSub 
          FROM TableB bSub
          WHERE bSub.auid = a.uid
          ORDER BY bSub.dateCreated DESC
          LIMIT 1
      )
""")
List<TableA> findListA(@Param("groupUid") String groupUid);

方式二：窗口函数筛选（推荐）

如果用的是Hibernate 5.2+或支持窗口函数的JPA实现，用ROW_NUMBER()窗口函数效率更高——先给每个auid的TableB记录按创建时间排序打序号，再取序号为1的最新记录：

@Query("""
    SELECT a
    FROM TableA a
    JOIN (
        SELECT bSub, ROW_NUMBER() OVER (PARTITION BY bSub.auid ORDER BY bSub.dateCreated DESC) as rn
        FROM TableB bSub
        WHERE bSub.status = 4 AND bSub.type <> 2 AND bSub.enable = true
    ) b ON b.bSub.auid = a.uid AND b.rn = 1
    JOIN FETCH b.bSub.tableC c
    JOIN FETCH a.tableB b
    WHERE a.groupUid = :groupUid
      AND c.condition = 1
      AND c.status = 0
""")
List<TableA> findListA(@Param("groupUid") String groupUid);

注意事项 #

1. 确保实体类关联关系配置正确：比如TableA和TableB的关联字段（对应数据库a.uid和b.auid）要用@ManyToOne等注解正确映射，实体属性名要和数据库列名对应（比如数据库date_created对应实体dateCreated）。
2. 必须用JOIN FETCH替代普通JOIN，否则关联的TableB和TableC还是懒加载状态，依然会触发LazyInitializeException。

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