JPA / Hibernate子查询中的“FROM”子句

Question

我们正在使用JPA和Hibernate作为提供者，我们有一个包含子查询的连接查询（在FROM子句中），但是我们得到了以下错误：

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 75 [SELECT sd FROM com.hp.amber.datamodel.entities.analysis.SnapshotDates sd, (SELECT 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 WHERE 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

谢谢你的帮助

- gads

---

jpa 不支持在 'FROM' 和 'SELECT' 子句中使用子查询，您需要更改查询，并将子查询放在 'WHERE' 子句中。 - Omid Rostami

Answer 1

我曾经认为HQL不能在from子句中使用子查询。

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

注意这句话：

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

我认为你可以将其更改为本地查询并以那种方式执行。

- dispake

Answer 2

Your SQL is:

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

You can rewrite your SQL as follows:

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

以下是类似的例子 我有SQL：

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

我将会把它翻译成

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相同的结果。

使用Hibernate 3.3.1和MySQL 5.0.24。

- Joter

---

请帮我翻译以下查询语句：select t.order_state,t.claim_number,t.id from (select * from t_repair_order where claim_number='SERCON201465') t where t.order_state = 'Cancel' and not exists ( select 1 from (select * from t_repair_order where claim_number='SERCON201465') tt where tt.id > t.id and tt.order_state <> t.order_state ); - ankit

Answer 3

从Hibernate 6.1正式版开始，HQL和Criteria查询中的子查询（包括侧向子查询）在from子句中得到支持。

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

- SANN3