hibernate Native Query转换为HQL

9gm1akwq 于 2023-04-06 发布在其他

关注(0)|答案(1)|浏览(164)

我一直在jpa中使用native query来处理下面的查询，因为我之前一直在急切地获取关系表，但是最近我的数据库中有很多数据增量，所以我对一些关系表实现了lazy fetch。现在我遇到了麻烦，无法将下面的查询转换为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运行下面的另一个查询来获取tableB和tableC关系：

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

Hibernate

来源：https://stackoverflow.com/questions/75926002/native-query-convert-to-hql

1条答案

按热度按时间

nle07wnf1#

我对distinct on持怀疑态度，这个替代方案“可能”会有所帮助：

SELECT a, b, c
FROM TableA a
INNER JOIN a.tableb b
INNER JOIN (
    SELECT auid, MAX(dateCreated) AS maxDateCreated
    FROM TableB
    WHERE status = 4 AND b.type <> 2 AND b.enable = true
    GROUP BY auid
    ) b2 ON b.auid = b2.auid AND b.dateCreated = b2.maxDateCreated
INNER JOIN b.tablec c
WHERE a.groupUid = :groupUid AND c.condition = 1 AND c.status = 0

然而，b2子查询“可能”不会将子查询限制为每个auid只有1行，尽管这只会发生在多行可以共享最大日期（希望真的是时间戳）的情况下。
如果这种方法不起作用，那么也许这会起作用：

SELECT a, b, c
FROM TableA a 
INNER JOIN (
    SELECT DISTINCT ON (b.auid) b 
    FROM TableB b 
    WHERE b.status = 4 AND b.type <> 2 AND b.enable = true
    ORDER BY b.auid, b.dateCreated DESC
    ) b ON a.uid = b.auid 
INNER JOIN TableC c ON c.uid = b.uid 
WHERE a.groupUid = :groupUid AND c.condition = 1 AND c.status = 0

赞(0）回复(0）举报 2023-04-06

我来回答

hibernate Native Query转换为HQL

1条答案

相关问题

热门标签

最新问答