sql - HQL/JPQL - FROM 上的嵌套选择

Question

我尝试将 SQL 查询转换为 HQL 或 JPQL(我想受益于对象映射)。

我的 SQL 请求是:

SELECT * 
FROM (SELECT bde, MAX(creation_date) 
      FROM push_campaign GROUP BY bde) temp, 
push_campaign pc where pc.bde = temp.bde and pc.creation_date = temp.creation_date;

我尝试(未成功)将其转换为 JPQL:

select pc 
from (select bde, max(creationDate) 
      from PushCampaign group by bde) temp, 
PushCampaign pc 
where pc.bde = temp.bde and pc.creationDate = temp.creationDate

但我长大了:

IllegalArgumentException occured :

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 16 [select pc from (select id, max(creationDate) from models.PushCampaign group by bde) temp, models.PushCampaign pc where pc.id = temp.id]

我读到嵌套选择只能在 select 或 where 子句中。

您是否有解决方法来保持对象映射的要求和好处？

Answer 1

不可能在单个请求中使用 JPQL 或 HQL。

要在单个请求中执行此操作，我建议这样做:

String campaignToLaunch = "select pc.* from PushCampaign pc ..."
//SQL request which return a resultset compatible with the models.PushCampaign class
Class className = Class.forName("models.PushCampaign");
List<PushCampaign> result = JPA.em()
                           .createNativeQuery(campaignToLaunch,className)
                           .getResultList();