from子句中的JPA / hibernate子查询 [英] JPA/hibernate subquery in from clause

问题描述

我们使用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 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]

这是查询： p>

  SELECT sd 
 FROM SnapshotDates sd，
（SELECT max（x.changeDate）maxChangeDate，x.viewId， x.state 
 FROM SnapshotDates x 
 WHERE x.changeDate<：date AND x.vi （：viewIds）AND x.state =：state 
 GROUP BY x.viewId，x.state）sd2 
 WHERE sd.viewId = sd2.viewId 
 AND sd.state =：州
 AND sd.changeDate = sd2.maxChangeDate 
  

感谢您的帮助

解决方案

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

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

记下这句话：

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

我想你可以将它更改为原生查询并以此方式执行。

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 (: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]

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 (: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

Thank you for helping

解决方案

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.

这篇关于from子句中的JPA / hibernate子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！