How do I do a JPQL SubQuery?

java orm jpa jpql
ErVeY picture ErVeY · Sep 2, 2010 · Viewed 71.4k times · Source

It is possible to do the equivalent of this sql query in JPQL?

SELECT * 
 FROM COUNTRIES c WHERE COUNTRY_ID IN (
  SELECT DISTINCT COUNTRY_ID 
   FROM PORTS p 
   WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
)

Answer

topchef picture topchef · Sep 2, 2010

You need to test it with IN and subquery since both do work in JPQL (according to syntax reference they do work together). You may also look at MEMBER OF expressions.

But there is a better approach in my opinion. Such queries are called correlated sub-queries and one can always re-write them using EXISTS:

SELECT * FROM COUNTRIES c WHERE 
EXISTS (
        SELECT 'found' FROM PORTS p 
        WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
)

JPQL supports EXISTS with subqueries.

Related questions

How To Define a JPA Repository Query with a Join

I would like to make a Join query using Jpa repository with annotation @Query. I have two tables: table user with iduser,user_name and: table area with idarea, area_name and iduser The native query is: SELECT u.user_…

Read More
Doing an "IN" query with Hibernate

I have a list of IDs in a String, and want to use Hibernate to get the rows with these IDs. TrackedItem is a Hibernate/JPA entity (sorry if I'm getting the naming mixed up here). My code is: String …

Read More
JPQL Create new Object In Select Statement - avoid or embrace?

I've learnt recently that it is possible to create new Objects in JPQL statements as follows: select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr Is this something …

Read More