Close

JPA Criteria API - Subqueries

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

In Criteria API, following method in CommonAbstractCriteria interface allows to create subqueries:

package javax.persistence.criteria;
 ....
public interface CommonAbstractCriteria {
 ....
    <U> Subquery<U> subquery(Class<U> type);
 ....
}

CommonAbstractCriteria is implemented by all kind of query objects including CriteriaQuery and Subquery itself.

Following is Subquery interface snippet:

package javax.persistence.criteria;
 ....
public interface Subquery<T> extends AbstractQuery<T>, Expression<T> {
    Subquery<T> select(Expression<T> expression);
    Subquery<T> where(Expression<Boolean> restriction);
    Subquery<T> where(Predicate... restrictions);
    Subquery<T> groupBy(Expression<?>... grouping);
    Subquery<T> groupBy(List<Expression<?>> grouping);
    Subquery<T> having(Expression<Boolean> restriction);
    Subquery<T> having(Predicate... restrictions);
    Subquery<T> distinct(boolean distinct);
  ....  
}

As seen above, just like CriteriaQuery, Subquery also has select, restriction, join etc methods.

Subquery also implements Expression, so that means it can be used in all those places of Criteria API where expression is used.

Quick Example

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

   //create subquery
   Subquery<Double> averageSalarySubQuery = employeeQuery.subquery(Double.class);
   //subquery from
   Root<Employee> subQueryEmployee = averageSalarySubQuery.from(Employee.class);
   //subquery selection
   averageSalarySubQuery.select(criteriaBuilder.avg(subQueryEmployee.get(Employee_.salary)));

   //main query selection
   employeeQuery.select(employee)
                .where(criteriaBuilder.lessThan(employee.get(Employee_.salary).as(Double.class), 
                                                                           averageSalarySubQuery));
   TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
   List<Employee> employees = typedQuery.getResultList();

Above query is equivalent to following JPQL:

   Query query = entityManager.createQuery(
              "SELECT e FROM Employee e where e.salary < (SELECT AVG(e2.salary) FROM Employee e2)");
   List<Employee> resultList = query.getResultList();

Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private String dept;
  private long salary;
    .............
}

Using subquery()

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          //show some pre info
          showNetAvgSalary();
          findEmployeeWithLessThanAverageSalary();
          showAvgSalaryByDept();
          findDeptHavingAboveNetAverageSalary();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void showNetAvgSalary() {
      System.out.println("-- net average salary --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Double> netAvgSalaryQuery = criteriaBuilder.createQuery(Double.class);
      Root<Employee> employee = netAvgSalaryQuery.from(Employee.class);
      netAvgSalaryQuery.select(criteriaBuilder.avg(employee.get(Employee_.salary)));
      Double netAvgSalary = entityManager.createQuery(netAvgSalaryQuery).getSingleResult();
      System.out.println(netAvgSalary);
  }

  private static void showAvgSalaryByDept() {
      System.out.println("-- Dept by average salaries --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Tuple> netAvgSalaryQueryByDept = criteriaBuilder.createTupleQuery();
      Root<Employee> employee = netAvgSalaryQueryByDept.from(Employee.class);
      netAvgSalaryQueryByDept.groupBy(employee.get(Employee_.dept));
      netAvgSalaryQueryByDept.multiselect(employee.get(Employee_.dept),
              criteriaBuilder.avg(employee.get(Employee_.salary)));
      TypedQuery<Tuple> typedQuery = entityManager.createQuery(netAvgSalaryQueryByDept);
      List<Tuple> resultList = typedQuery.getResultList();
      resultList.forEach(tuple ->
              System.out.printf("Dept: %s, Avg Salary: %s%n", tuple.get(0), tuple.get(1)));
  }

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

      //create subquery
      Subquery<Double> averageSalarySubQuery = employeeQuery.subquery(Double.class);
      //subquery from
      Root<Employee> subQueryEmployee = averageSalarySubQuery.from(Employee.class);
      //subquery selection
      averageSalarySubQuery.select(criteriaBuilder.avg(subQueryEmployee.get(Employee_.salary)));

      //main query selection
      employeeQuery.select(employee)
                   .where(criteriaBuilder
                           .lessThan(employee.get(Employee_.salary).as(Double.class), averageSalarySubQuery));
      TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
      List<Employee> employees = typedQuery.getResultList();
      employees.forEach(System.out::println);
      entityManager.close();
  }

  private static void findDeptHavingAboveNetAverageSalary() {
      System.out.println("-- Dept having AVG salaries greater than net AVG salary --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      //main query
      CriteriaQuery<Tuple> netAvgSalaryQueryByDept = criteriaBuilder.createTupleQuery();
      Root<Employee> employee = netAvgSalaryQueryByDept.from(Employee.class);
      netAvgSalaryQueryByDept.groupBy(employee.get(Employee_.dept));
      netAvgSalaryQueryByDept.multiselect(employee.get(Employee_.dept),
              criteriaBuilder.avg(employee.get(Employee_.salary)));
      //subquery
      Subquery<Double> netAvgSalarySubquery = netAvgSalaryQueryByDept.subquery(Double.class);
      Root<Employee> subQueryEmployee = netAvgSalarySubquery.from(Employee.class);
      netAvgSalarySubquery.select(criteriaBuilder.avg(subQueryEmployee.get(Employee_.salary)));
      //main query with having restriction
      netAvgSalaryQueryByDept.having(
              criteriaBuilder.greaterThan(criteriaBuilder.avg(employee.get(Employee_.salary)), netAvgSalarySubquery)
      );
      TypedQuery<Tuple> typedQuery = entityManager.createQuery(netAvgSalaryQueryByDept);
      List<Tuple> resultList = typedQuery.getResultList();
      resultList.forEach(tuple ->
              System.out.printf("Dept: %s, Avg Salary: %s%n", tuple.get(0), tuple.get(1)));

  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", "IT", 3000);
      Employee employee2 = Employee.create("Rose", "Admin", 2000);
      Employee employee3 = Employee.create("Denise", "Admin", 4000);
      Employee employee4 = Employee.create("Mike", "IT", 3500);
      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}
Employee{id=2, name='Rose', dept='Admin', salary=2000}
Employee{id=3, name='Denise', dept='Admin', salary=4000}
Employee{id=4, name='Mike', dept='IT', salary=3500}
Employee{id=5, name='Linda', dept='Sales', salary=2000}
-- net average salary --
2900.0
-- Employees who have less than average salary --
Employee{id=2, name='Rose', dept='Admin', salary=2000}
Employee{id=5, name='Linda', dept='Sales', salary=2000}
-- Dept by average salaries --
Dept: IT, Avg Salary: 3250.0
Dept: Sales, Avg Salary: 2000.0
Dept: Admin, Avg Salary: 3000.0
-- Dept having AVG salaries greater than net AVG salary --
Dept: IT, Avg Salary: 3250.0
Dept: Admin, Avg Salary: 3000.0

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

CriteriaQuery.subquery() examples. Select All Download
  • jpa-criteria-api-subquery-method
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also