A correlated subquery is a query nested inside another query which uses values from the outer query.

Criteria API, provides methods for the situation where a correlated subquery uses a field of the outer (parent) query in the 'from' clause.

Following methods of Subquery allows to correlate the subquery with the parent query:

package javax.persistence.criteria;
 ....
public interface Subquery<T> extends AbstractQuery<T>, Expression<T> {
 .... 
    <Y> Root<Y> correlate(Root<Y> parentRoot);
    <X, Y> Join<X, Y> correlate(Join<X, Y> parentJoin);
    <X, Y> CollectionJoin<X, Y> correlate(CollectionJoin<X, Y> parentCollection);
    <X, Y> SetJoin<X, Y> correlate(SetJoin<X, Y> parentSet);
    <X, Y> ListJoin<X, Y> correlate(ListJoin<X, Y> parentList);
    <X, K, V> MapJoin<X, K, V> correlate(MapJoin<X, K, V> parentMap);
 ....
}

Quick Example

Let's consider following JPQL:

  Query query = em.createQuery(
               "SELECT e FROM Employee e WHERE (SELECT COUNT(p) FROM e.phoneNumbers p) >= 2");
  List<Employee> resultList = query.getResultList();

Equivalent code using Criteria Query:

   //main query
   CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
   Root<Employee> employee = employeeQuery.from(Employee.class);

   //phone count subquery
   Subquery<Long> countSubQuery = employeeQuery.subquery(Long.class);

   // following two lines make 'from' clause of the subquery
   Root<Employee> employee2 = countSubQuery.correlate(employee);
   ListJoin<Employee, String> phoneNumbers = employee2.join(Employee_.phoneNumbers);
   //above two lines can be written as
   //ListJoin<Employee, String> phoneNumbers = countSubQuery.correlate(employee).join(Employee_.phoneNumbers); 

   //subquery selection
   countSubQuery.select(criteriaBuilder.count(phoneNumbers));

   //main query selection
   employeeQuery.select(employee)
                .where(criteriaBuilder.greaterThanOrEqualTo(countSubQuery, 2L));

   TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
   List<Employee> employees = typedQuery.getResultList();

Complete Example

Entity

@Entity
public class Employee {
    @Id
    @GeneratedValue
    private long id;
    private String name;
    private String dept;
    private long salary;
    @ElementCollection
    private List<String> phoneNumbers;
    .............
}

Using Subquery.correlate()

public class ExampleMain {
    private static EntityManagerFactory entityManagerFactory =
            Persistence.createEntityManagerFactory("example-unit");

    public static void main(String[] args) {
        try {
            persistEmployees();
            findEmployeeByPhoneCount();
        } finally {
            entityManagerFactory.close();
        }
    }

    private static void findEmployeeByPhoneCount() {
        System.out.println("-- Employees who have more than 2 phones inclusively --");
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        //main query
        CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
        Root<Employee> employee = employeeQuery.from(Employee.class);

        //phone count subquery
        Subquery<Long> countSubQuery = employeeQuery.subquery(Long.class);

        //create the subquery root correlated to the parent subquery root
        Root<Employee> employee2 = countSubQuery.correlate(employee);

        //employee-phones join
        ListJoin<Employee, String> phoneNumbers = employee2.join(Employee_.phoneNumbers);

        //subquery selection
        countSubQuery.select(criteriaBuilder.count(phoneNumbers));

        //main query selection
        employeeQuery.select(employee)
             .where(criteriaBuilder.greaterThanOrEqualTo(countSubQuery, 2L));

        TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
        List<Employee> employees = typedQuery.getResultList();
        employees.forEach(System.out::println);
        entityManager.close();
    }

    public static void persistEmployees() {
        Employee employee1 = Employee.create("Diana", "IT", 3000, "111-111-111", "222-111-111");
        Employee employee2 = Employee.create("Rose", "Admin", 2000, "333-111-111", "444-111-111");
        Employee employee3 = Employee.create("Denise", "Admin", 4000, "555-111-111");
        Employee employee4 = Employee.create("Mike", "IT", 3500, "777-111-111");
        Employee employee5 = Employee.create("Linda", "Sales", 2000);
        EntityManager em = entityManagerFactory.createEntityManager();
        em.getTransaction().begin();
        em.persist(employee1);
        em.persist(employee2);
        em.persist(employee3);
        em.persist(employee4);
        em.persist(employee5);
        em.getTransaction().commit();
        em.close();
        System.out.println("-- all employees --");
        System.out.println(employee1);
        System.out.println(employee2);
        System.out.println(employee3);
        System.out.println(employee4);
        System.out.println(employee5);
    }
}

-- all employees --
Employee{id=1, name='Diana', dept='IT', salary=3000, phoneNumbers=[111-111-111, 222-111-111]}
Employee{id=2, name='Rose', dept='Admin', salary=2000, phoneNumbers=[333-111-111, 444-111-111]}
Employee{id=3, name='Denise', dept='Admin', salary=4000, phoneNumbers=[555-111-111]}
Employee{id=4, name='Mike', dept='IT', salary=3500, phoneNumbers=[777-111-111]}
Employee{id=5, name='Linda', dept='Sales', salary=2000, phoneNumbers=[]}
-- Employees who have more than 2 phones inclusively --
Employee{id=1, name='Diana', dept='IT', salary=3000, phoneNumbers=[111-111-111, 222-111-111]}
Employee{id=2, name='Rose', dept='Admin', salary=2000, phoneNumbers=[333-111-111, 444-111-111]}

Example Project

Dependencies and Technologies Used:

• hibernate-core 5.3.7.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• hibernate-jpamodelgen 5.3.7.Final: Annotation Processor to generate JPA 2 static metamodel classes.
• h2 1.4.197: H2 Database Engine.
• JDK 1.8
• Maven 3.5.4