Menu
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'
)
581
A subselect is a query embedded into another query. It's a powerful feature you probably know from SQL. Unfortunately, JPQL supports it only in the WHERE clause and not in the SELECT or FROM clause. Subqueries can return one or multiple records and can use the aliases defined in the outer query.
Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.
where(criteriaBuilder.in(path). value(subquery)); TypedQuery<Object> typedQuery = entityManager. createQuery(select); List<Object> resultList = typedQuery. getResultList();
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.
73
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
