[jpa] JPA 2.0, Criteria API, Subqueries, In Expressions

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

Examples related to jpa

No converter found capable of converting from type to type How does spring.jpa.hibernate.ddl-auto property exactly work in Spring? Deserialize Java 8 LocalDateTime with JacksonMapper Error creating bean with name 'entityManagerFactory' defined in class path resource : Invocation of init method failed How to beautifully update a JPA entity in Spring Data? JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory How to return a custom object from a Spring Data JPA GROUP BY query How to find distinct rows with field in list using JPA and Spring? What is this spring.jpa.open-in-view=true property in Spring Boot? Spring Data JPA and Exists query

Examples related to subquery

What is the difference between LATERAL and a subquery in PostgreSQL? Postgres Error: More than one row returned by a subquery used as an expression How does Subquery in select statement work in oracle Difference between Subquery and Correlated Subquery How to do this in Laravel, subquery where in SQL LEFT JOIN Subquery Alias Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression subquery in FROM must have an alias Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable? How can I insert values into a table, using a subquery with more than one result?

Examples related to jpa-2.0

JPA Query selecting only specific columns without using Criteria Query? JPA With Hibernate Error: [PersistenceUnit: JPA] Unable to build EntityManagerFactory How to define unidirectional OneToMany relationship in JPA JPA CriteriaBuilder - How to use "IN" comparison operator JPA: unidirectional many-to-one and cascading delete How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery? JPA 2.0, Criteria API, Subqueries, In Expressions JPA Criteria API - How to add JOIN clause (as general sentence as possible) In JPA 2, using a CriteriaQuery, how to count results JPA CascadeType.ALL does not delete orphans

Examples related to criteria-api

Using Java generics for JPA findAll() query with WHERE clause JPA & Criteria API - Select only specific columns JPA CriteriaBuilder - How to use "IN" comparison operator JPA 2.0, Criteria API, Subqueries, In Expressions JPA Criteria API - How to add JOIN clause (as general sentence as possible)

Examples related to in-subquery

JPA 2.0, Criteria API, Subqueries, In Expressions MySQL DELETE FROM with subquery as condition