如何使用Hibernate中的查询结果执行实体的连接

Question

我想在hibernate中运行以下SQL：

SELECT c.id, c.name, ipc.total 
FROM Category c,
(
    SELECT comm.category.id as id, count(*) as total 
    FROM Commerce as comm 
    GROUP BY comm.category.id 
) ipc 
WHERE c.id = ipc.id

这是我试图用JQL编写的，但是我得到了一个错误。它似乎不喜欢我针对查询结果的连接。

请求处理失败；嵌套的异常是java.lang.IllegalArgumentException：org.hibernate.hql.internal.ast.QuerySynahException：意外令牌：（第1行附近，第86列[SELECTc.id，c.name，ipc.total来自com.brujulasolutions.directorio.model.类别c，（SELECTcomm.category.idid， count（*）as总计fromcom.brujulasolutions.directorio.model.Commerce as comm GROUP BYcomm.category.id）ipc WHEREc.id=ipc.id]

SELECT c.id, c.name, ipc.total 
FROM Category c,
(
    SELECT comm.category.id as id, count(*) as total 
    FROM Commerce as comm 
    GROUP BY comm.category.id 
) ipc 
WHERE c.id = ipc.id

Answer 1

Hibernate查询语言不直接支持这种类型的更新连接。相反，我们可以在select子句中使用相关子查询：

SELECT c.id, c.name,
       (SELECT COUNT(*)
        FROM Commerce AS comm
        WHERE comm.category.id = c.id) AS total
FROM Category c