题 from子句中的JPA / hibernate子查询


我们使用JPA和hibernate作为提供者, 我们有一个查询,其中包含FROM子句中带子查询的连接,但是我们收到以下错误:

org.hibernate.hql.ast.QuerySyntaxException:意外令牌:(近   第1行,第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和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

感谢您的帮助


17
2017-09-01 10:25


起源


jpa不支持'FROM'和“SELECT”子句中的子查询,您必须更改查询并将子查询放在'WHERE'子句中。 - Omid Rostami


答案:


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

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

注意这句话:

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

我想你可以把它改成原生查询并以那种方式执行。


21
2017-09-03 07:48





你的SQL是:

SELECT sd FROM SnapshotDates sd,        (SELECT max(x.changeDate)maxChangeDate,x.viewId,x.state         从SnapshotDates x        WHERE x.changeDate <:与(:viewIds)AND中的日期和x.viewId   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

我的类似例子 我有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相同的结果。

将Hebirnate 3.3.1与MySQL 5.0.24一起使用


3
2017-09-09 11:28