问题
We're using JPA with hibernate as the provider,
we have a query that contains a join with a subquery in the FROM clause, but we get the following error:
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 x.state=":state" group>
BY x.viewId, x.state) sd2 WHERE sd.viewId = sd2.viewId AND sd.state =
:state AND sd.changeDate = sd2.maxChangeDate]
This is the query:
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 x.state=":state</p">
GROUP BY x.viewId, x.state) sd2
WHERE sd.viewId = sd2.viewId
AND sd.state = :state
AND sd.changeDate = sd2.maxChangeDate
Thank you for helping
回答1:
I did not think HQL could do subqueries in the from clause
https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html#queryhql-subqueries
note the sentence:
Note that HQL subqueries can occur only in the select or where clauses.
I imagine you could change it to a native query and execute it that way.
回答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>
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 like
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)
Find inspired by example
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
My similar example
I had 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
Rewrite it for 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
It will be translated to
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
return same result as SQL.
Use Hebirnate 3.3.1 with MySQL 5.0.24
来源:https://stackoverflow.com/questions/7269010/jpa-hibernate-subquery-in-from-clause