JPA 2.0, Criteria API, Subqueries, In Expressions

Question

I have tried to write a query statement with a subquery and an IN expression for many times. But I have never succeeded.

I always get the exception, " Syntax error near keyword 'IN' ", the query statement was build like this,

SELECT t0.ID, t0.NAME
FROM EMPLOYEE t0
WHERE IN (SELECT ?
          FROM PROJECT t2, EMPLOYEE t1
          WHERE ((t2.NAME = ?) AND (t1.ID = t2.project)))

I know the word before 'IN' lose.

Have you ever written such a query? Any suggestion?

A JPQL and Criteria API examples can be found here: [http://stackoverflow.com/questions/10854334/jpa2-criteria-api-select-in-select-from-where/10858479#10858479][1] [1]: http://stackoverflow.com/questions/10854334/jpa2-criteria-api-select-in-select-from-where/10858479#10858479 — Techky, Jun 04 '12 at 12:45
you are missing the "left side" of the where expression, just before IN. WHERE IN ... — Matthias B, May 21 '13 at 12:48

Nayan Wadekar · Answer 1 · 2016-03-31T10:38:28.177

Below is the pseudo-code for using sub-query using Criteria API.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
Root<EMPLOYEE> from = criteriaQuery.from(EMPLOYEE.class);
Path<Object> path = from.get("compare_field"); // field to map with sub-query
from.fetch("name");
from.fetch("id");
CriteriaQuery<Object> select = criteriaQuery.select(from);

Subquery<PROJECT> subquery = criteriaQuery.subquery(PROJECT.class);
Root fromProject = subquery.from(PROJECT.class);
subquery.select(fromProject.get("requiredColumnName")); // field to map with main-query
subquery.where(criteriaBuilder.and(criteriaBuilder.equal("name",name_value),criteriaBuilder.equal("id",id_value)));

select.where(criteriaBuilder.in(path).value(subquery));

TypedQuery<Object> typedQuery = entityManager.createQuery(select);
List<Object> resultList = typedQuery.getResultList();

Also it definitely needs some modification as I have tried to map it according to your query. Here is a link http://www.ibm.com/developerworks/java/library/j-typesafejpa/ which explains concept nicely.

+1 in general, but multiple `subquery.where(…)` statements didn’t work for me. I had to use `subquery.where(criteriaBuilder.and(…, …))` instead. It seems that the last `where` statement overwrites the previous. — Chriki, Jun 18 '15 at 09:29
@Chriki Thanks for your input, will edit based on you comment, it was like pseudo-code hand written, so issues like this were expected. — Nayan Wadekar, Jun 18 '15 at 11:40
for me this creates a cross join in the sub-query - is that normal? — Sepultura, Jan 15 '21 at 13:23

score 73 · Answer 2 · edited Sep 18 '17 at 15:14

73

Late resurrection.

Your query seems very similar to the one at page 259 of the book Pro JPA 2: Mastering the Java Persistence API, which in JPQL reads:

SELECT e 
FROM Employee e 
WHERE e IN (SELECT emp
              FROM Project p JOIN p.employees emp 
             WHERE p.name = :project)

Using EclipseLink + H2 database, I couldn't get neither the book's JPQL nor the respective criteria working. For this particular problem I have found that if you reference the id directly instead of letting the persistence provider figure it out everything works as expected:

SELECT e 
FROM Employee e 
WHERE e.id IN (SELECT emp.id
                 FROM Project p JOIN p.employees emp 
                WHERE p.name = :project)

Finally, in order to address your question, here is an equivalent strongly typed criteria query that works:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);

Subquery<Integer> sq = c.subquery(Integer.class);
Root<Project> project = sq.from(Project.class);
Join<Project, Employee> sqEmp = project.join(Project_.employees);

sq.select(sqEmp.get(Employee_.id)).where(
        cb.equal(project.get(Project_.name), 
        cb.parameter(String.class, "project")));

c.select(emp).where(
        cb.in(emp.get(Employee_.id)).value(sq));

TypedQuery<Employee> q = em.createQuery(c);
q.setParameter("project", projectName); // projectName is a String
List<Employee> employees = q.getResultList();

edited Sep 18 '17 at 15:14

naXa stands with Ukraine

35,493
19
190
259

answered Apr 02 '12 at 20:09

Anthony Accioly

21,918
9
70
118

I have been a Rails programmer now, thanks for your answer Anthony, my query was from the book <>. I like JPQL, and will learn from the answers sometimes. – Keating Apr 05 '12 at 13:40
How can I create a subquery on a JoinTable? Ie, I don't have an entity for a root subquery – Zlatko Dec 10 '13 at 23:50
@Zlatko, open a question for it describing your entities and structure. – Anthony Accioly Dec 11 '13 at 00:27
Thanks, found something in the mean time. – Zlatko Dec 11 '13 at 01:08
@AnthonyAccioly http://stackoverflow.com/questions/20557444/ CAre to take a look? – Zlatko Dec 13 '13 at 04:11
@Zlatko, you should have an entity for your root, that is what you are joining the subquery to. The nice thing is you don't need an entity for the subquery, it just is used to filter your results. It does not make any new data available for retrieval. – Wallace Howery Oct 21 '21 at 01:38

score 6 · Answer 3 · answered Sep 05 '18 at 19:20

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class);
Root<Employee> empleoyeeRoot = criteriaQuery.from(Employee.class);

Subquery<Project> projectSubquery = criteriaQuery.subquery(Project.class);
Root<Project> projectRoot = projectSubquery.from(Project.class);
projectSubquery.select(projectRoot);

Expression<String> stringExpression = empleoyeeRoot.get(Employee_.ID);
Predicate predicateIn = stringExpression.in(projectSubquery);

criteriaQuery.select(criteriaBuilder.count(empleoyeeRoot)).where(predicateIn);

score 0 · Answer 4 · answered Feb 29 '20 at 11:04

You can use double join, if table A B are connected only by table AB.

public static Specification<A> findB(String input) {
    return (Specification<A>) (root, cq, cb) -> {
        Join<A,AB> AjoinAB = root.joinList(A_.AB_LIST,JoinType.LEFT);
        Join<AB,B> ABjoinB = AjoinAB.join(AB_.B,JoinType.LEFT);
        return cb.equal(ABjoinB.get(B_.NAME),input);
    };
}

That's just an another option
Sorry for that timing but I have came across this question and I also wanted to make SELECT IN but I didn't even thought about double join. I hope it will help someone.

JPA 2.0, Criteria API, Subqueries, In Expressions

4 Answers4

Linked