Close

JPA Criteria API - Using Subquery.correlate() method

[Updated: Dec 16, 2018, Created: Dec 14, 2018]

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

Subquery.correlate() Example Select All Download
  • jpa-criteria-api-correlate-method
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also