An EXISTS expression is a predicate which is used in WHERE clause. It is true only if the result of the subquery returns one or more rows.

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;
    .............
}

Executing queries involving EXISTS expressions

The first query uses 'EXISTS' expression in WHERE clause. This query returns the employees whose job details exists in JobInfo table.
The second query 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();
          executeQuery();
          executeQuery2();
      } finally {
          entityManagerFactory.close();
      }
  }

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

  private static void executeQuery() {
      System.out.println("-- executing query EXISTS --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e"
              + " WHERE EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void executeQuery2() {
      System.out.println("-- executing query NOT EXISTS --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT e FROM Employee e"
              + " WHERE NOT EXISTS (SELECT j from JobInfo j WHERE j.jobName = e.job)");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  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;
  }

  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;
  }
}

-- persisting entities --
-- executing query EXISTS --
Employee{id=1, name='Diana', job='Developer'}
Employee{id=2, name='Mike', job='Manager'}
Employee{id=4, name='Jack', job='Architect'}
-- executing query NOT EXISTS --
Employee{id=3, name='Tim', job='Salesman'}

Example Project

Dependencies and Technologies Used:

• h2 1.4.197: H2 Database Engine.
• hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9