HQL / JPQL - Nested select on FROM

6
votes

I try to convert my SQL query into HQL or JPQL (I want to benefit of the object mapping).

My SQL Request is : 

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;

I try (unsuccessfully) to convert it in JPQL with :

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

But I got raised : 

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]

I read the nested select can only be in select or where clause.

Do you have workarounds to keep the request and benefit of object-mapping ?

3
sqlnhibernatehqljpql
Have you tried using INNER JOIN instead of joining in the WHERE clause? – Adriano Carneiro
I have search how to but I can't find any example with subquery and innerjoin. All the tests I tried failed... Thanks for the hint, I ll dig in that direction – kheraud
It is not possible to have subqueries in an inner join in jpql. It is only possible in where or having statements.... – kheraud

3 Answers

5
votes

Not possible with JPQL or HQL in a single request.

To do this in a single request I propose this :

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();
3
votes

this should achive similar results

select pc
from PushCampaign pc 
where pc.creationDate =
(select max(creationDate) from PushCampaign inner where inner.bde = pc.bde)
2
votes

A simple solution could be:

servlet
{
    Query q = entityManager.createNativeQuery("SQL");
    List<> li =  q.getResultList();

    @PersistenceContext(unitName="UNIT")
    private EntityManager entityManager;
}