from 子句中的 JPA/hibernate 子查询

Question

我们使用 JPA 和 hibernate 作为提供者， 我们有一个查询，其中包含与 FROM 子句中的子查询的联接，但出现以下错误：

org.hibernate.hql.ast.QuerySyntaxException：意外的标记：（附近 第 1 行，第 75 列 [SELECT sd FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates sd，（选择 max(x.changeDate) maxChangeDate, x.viewId, x.state FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates x WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state GROUP BY x.viewId, x.state) sd2 其中 sd.viewId = sd2.viewId AND sd.state = :state AND sd.changeDate = sd2.maxChangeDate]

这是查询：

SELECT sd 
FROM SnapshotDates sd, 
     (SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state 
      FROM SnapshotDates x
     WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND x.state=:state
GROUP BY x.viewId, x.state) sd2
WHERE sd.viewId = sd2.viewId 
      AND sd.state = :state 
      AND sd.changeDate = sd2.maxChangeDate

谢谢您的帮助

Answer 1

Hibernate 更新 >= 6.1

从 Hibernate 6.1（2022 年 6 月 14 日发布）：

长期请求支持 HQL 和 Criteria 查询的 from 子句中的子查询（包括横向子查询）

请参阅 Hibernate 存储库中的单元测试文件中的示例代码 HQLTest.java：

    @Test
    public void test_hql_derived_root_example() {
        doInJPA(this::entityManagerFactory, entityManager -> {
            //tag::hql-derived-root-example[]
            List<Tuple> calls = entityManager.createQuery(
                "select d.owner, d.payed " +
                "from (" +
                "  select p.person as owner, c.payment is not null as payed " +
                "  from Call c " +
                "  join c.phone p " +
                "  where p.number = :phoneNumber) d",
                Tuple.class)
            .setParameter("phoneNumber", "123-456-7890")
            .getResultList();
            //end::hql-derived-root-example[]
        });
    }

旧版本的 Hibernate：

我不认为 HQL 可以在 from 子句中执行子查询

https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-subqueries

注意这句话：

请注意，HQL 子查询只能出现在 select 或 where 子句中。

我想您可以将其更改为本机查询并以这种方式执行。

Answer 2

你的SQL是：

从 SnapshotDates sd 中选择 sd，
(SELECT max(x.changeDate) maxChangeDate, x.viewId, x.state
来自快照日期 x
WHERE x.changeDate<:date AND x.viewId in (:viewIds) AND
x.state=:state GROUP BY x.viewId, x.state) sd2 WHERE sd.viewId =
sd2.viewId
AND sd.state = :state
AND sd.changeDate = sd2.maxChangeDate

您可以重写您的sql，如

SELECT sd 
FROM SnapshotDates sd, 
WHERE sd.viewId in (:viewIds)
    AND sd.state = :state
    sd.changeDate = (SELECT max(x.changeDate) FROM SnapshotDates x WHERE x.viewId = ds.viewId AND x.state = ds.state)

查找示例

SELECT m FROM Professor m WHERE (SELECT COUNT(e) FROM Professor e WHERE e.manager = m) > 0

http ://www.java2s.com/Code/Java/JPA/EJBQLWhereClauseWithSubQuery.htm

我的类似示例
我

select k.* from kredits k, 
  (select client_id, max(r_date) r_date from kredits k group by client_id) k2 
where k.client_id = k2.client_id 
    AND k.r_date = k2.r_date 
order by k.id

为 PQL 重写了

select k From Kredit k
where k.rDate = (select MAX(k2.rDate) from Kredit k2 where k2.clientId = k.clientId)
order by k.id

SQL它将被转换为

select kredit0_.id as id28_, kredit0_.client_id as client59_28_ from kredits kredit0_ 
where kredit0_.r_date=(select MAX(kredit1_.r_date) from kredits kredit1_ where kredit1_.client_id=kredit0_.client_id) 
order by kredit0_.id

返回与 SQL 相同的结果。

将 Hebirnate 3.3.1 与 MySQL 5.0.24 结合使用

Answer 3

Hibernate 6.1 Final 支持 HQL 和 Criteria 查询的 from 子句中的子查询（包括横向子查询）

https://in.relation.to/2022/06/14/orm-61-final/