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 ?
With JPQL, you can use subqueries only in the WHERE and HAVING clauses but not in the SELECT and FROM clause. SQL, of course, allows you to use subqueries also in the SELECT and FROM clause.
The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.
A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. Note that HQL subqueries can occur only in the select or where clauses.
Query createQuery(String name) - The createQuery() method of EntityManager interface is used to create an instance of Query interface for executing JPQL statement.
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();
this should achive similar results
select pc
from PushCampaign pc
where pc.creationDate =
(select max(creationDate) from PushCampaign inner where inner.bde = pc.bde)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With