Close

JPA Criteria API - Using CriteriaBuilder.exists() Method

[Updated: Jan 8, 2019, Created: Jan 8, 2019]

Following method of CriteriaBuilder can be used to apply EXISTS predicate in WHERE clause:

package javax.persistence.criteria;
 ........
public interface CriteriaBuilder {
 ........
   //Creates a predicate testing the existence of a subquery result.
   Predicate exists(Subquery<?> subquery);
 ........
}

To apply 'NOT EXISTS' predicate we can use criteriaBuilder.not(criteriaBuilder.exists(.....)).

Quick Example

Consider following JPQL:

"SELECT e FROM Employee e WHERE EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)"

The equivalent code using Criteria API:

   //main query
   CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
   Root<Employee> employee = employeeQuery.from(Employee.class);
   //subquery
   Subquery<JobInfo> jobInfoSubquery = employeeQuery.subquery(JobInfo.class);
   Root<JobInfo> jobInfo = jobInfoSubquery.from(JobInfo.class);
   jobInfoSubquery.select(jobInfo)//subquery selection
                  .where(criteriaBuilder.equal(jobInfo.get(JobInfo_.jobName),
                          employee.get(Employee_.job)));//subquery restriction
   //main query selection
   employeeQuery.select(employee)
                .where(criteriaBuilder.exists(jobInfoSubquery));

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

Complete Example

Entities

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private String job;
    .............
}
@Entity
public class JobInfo {
  @Id
  @GeneratedValue
  private long id;
  private String jobName;
  private String jobType;
    .............
}

Using CriteriaBuilder.exists() method:

The first method findEmployeeIfJobInfoExists uses 'EXISTS' predicate in WHERE clause. The result should be the employees whose job details exists in JobInfo table.

The second method findEmployeeIfJobInfoNotExists uses 'NOT EXISTS' expression in WHERE clause which returns the employees whose job details does not exist in JobInfoTable.

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

  public static void main(String[] args) {
      try {
          persistEntities();
          findEmployeeIfJobInfoExists();
          findEmployeesIfJobInfoNotExists();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findEmployeeIfJobInfoExists() {
      System.out.println("-- executing EXISTS query --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

      //main query
      CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = employeeQuery.from(Employee.class);
      //subquery
      Subquery<JobInfo> jobInfoSubquery = employeeQuery.subquery(JobInfo.class);
      Root<JobInfo> jobInfo = jobInfoSubquery.from(JobInfo.class);
      jobInfoSubquery.select(jobInfo)//subquery selection
                     .where(criteriaBuilder.equal(jobInfo.get(JobInfo_.jobName),
                             employee.get(Employee_.job)));//subquery restriction
      //main query selection
      employeeQuery.select(employee)
                   .where(criteriaBuilder.exists(jobInfoSubquery));

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

      entityManager.close();
      // equivalent JPQL
      //"SELECT e FROM Employee e WHERE EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)"
  }

  private static void findEmployeesIfJobInfoNotExists() {
      System.out.println("-- executing  NOT EXISTS query --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      //main query
      CriteriaQuery<Employee> employeeQuery = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = employeeQuery.from(Employee.class);
      //subquery
      Subquery<JobInfo> jobInfoSubquery = employeeQuery.subquery(JobInfo.class);
      Root<JobInfo> jobInfo = jobInfoSubquery.from(JobInfo.class);
      jobInfoSubquery.select(jobInfo)
                     .where(criteriaBuilder.equal(jobInfo.get(JobInfo_.jobName),
                             employee.get(Employee_.job)));
      //main query selection
      employeeQuery.select(employee)
                   .where(criteriaBuilder.not(criteriaBuilder.exists(jobInfoSubquery)));

      TypedQuery<Employee> typedQuery = entityManager.createQuery(employeeQuery);
      List<Employee> resultList = typedQuery.getResultList();
      resultList.forEach(System.out::println);
      entityManager.close();
      ///equivalent JPQL
      // "SELECT e FROM Employee e WHERE NOT EXISTS(SELECT j from JobInfo j WHERE j.jobName = e.job)"
  }

  public static void persistEntities() {
      System.out.println("-- persisting entities --");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      List<Employee> employees = getEmployees();
      employees.forEach(em::persist);
      List<JobInfo> jobInfoList = getJobInfoList();
      jobInfoList.forEach(em::persist);
      em.getTransaction().commit();
      em.close();
      employees.forEach(System.out::println);
      jobInfoList.forEach(System.out::println);
  }

  private static List<Employee> getEmployees() {
      List<Employee> employees = new ArrayList<>();
      employees.add(Employee.create("Diana", "Developer"));
      employees.add(Employee.create("Mike", "Manager"));
      employees.add(Employee.create("Tim", "Salesman"));
      employees.add(Employee.create("Jack", "Architect"));
      return employees;
  }

  private static List<JobInfo> getJobInfoList() {
      List<JobInfo> list = new ArrayList<>();
      list.add(JobInfo.create("Developer", "Consultant"));
      list.add(JobInfo.create("Manager", "FullTime"));
      list.add(JobInfo.create("Architect", "Consultant"));
      return list;
  }
}
-- persisting entities --
Employee{id=1, name='Diana', job='Developer'}
Employee{id=2, name='Mike', job='Manager'}
Employee{id=3, name='Tim', job='Salesman'}
Employee{id=4, name='Jack', job='Architect'}
JobInfo{id=5, jobName='Developer', jobType='Consultant'}
JobInfo{id=6, jobName='Manager', jobType='FullTime'}
JobInfo{id=7, jobName='Architect', jobType='Consultant'}
-- executing EXISTS query --
Employee{id=1, name='Diana', job='Developer'}
Employee{id=2, name='Mike', job='Manager'}
Employee{id=4, name='Jack', job='Architect'}
-- executing NOT EXISTS query --
Employee{id=3, name='Tim', job='Salesman'}

Example Project

Dependencies and Technologies Used:

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

Criteria API - CriteriaBuilder.exists() Example Select All Download
  • jpa-criteria-api-exists-method
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also