Close

JPA Criteria API - Left Outer Join On Condition

[Last Updated: Aug 28, 2018]

Just like ON condition of JPQL LEFT JOIN, we can also apply ON condition in Criteria API.

Following are the methods of Join<Z,X> interface which can be used to apply ON condition:

Join<Z, X> on(Expression<Boolean> restriction);
Join<Z, X> on(Predicate... restrictions);

Quick example:

  CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
  Root<Employee> employee = query.from(Employee.class);
  ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks, JoinType.LEFT);
  tasks.on(criteriaBuilder.equal(tasks.get(Task_.supervisor), "Denise"));
  ....

Example

Entities

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  @ManyToMany(cascade = CascadeType.ALL)
  private List<Task> tasks;
    .............
}
@Entity
public class Task {
  @Id
  @GeneratedValue
  private long id;
  private String description;
  private String supervisor;
    .............
}

Queries involving Join ON condition

First, let's not use 'ON' condition to understand the difference:

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

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

  public static void persistEmployees() {
      Task task1 = new Task("Coding", "Denise");
      Task task2 = new Task("Refactoring", "Rose");
      Task task3 = new Task("Designing", "Denise");
      Task task4 = new Task("Documentation", "Mike");

      Employee employee1 = Employee.create("Diana", task1, task3);
      Employee employee2 = Employee.create("Mike", task2, task4);
      Employee employee3 = Employee.create("Tim", task3, task4);
      Employee employee4 = Employee.create("Jack");

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
      System.out.println("-- Employee persisted --");
      System.out.println(employee1);
      System.out.println(employee2);
      System.out.println(employee3);
      System.out.println(employee4);
  }

  private static void findEmployeesBySupervisor() {
      System.out.println("-- find employees left joined with tasks --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
      Root<Employee> employee = query.from(Employee.class);
      ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks, JoinType.LEFT);
      query.select(criteriaBuilder.tuple(employee.get(Employee_.name).alias("employeeName"),
              tasks.get(Task_.supervisor).alias("supervisor")))
           .distinct(true);
      TypedQuery<Tuple> typedQuery = entityManager.createQuery(query);
      for (Tuple tuple : typedQuery.getResultList()) {
          System.out.printf("name: %s, supervisor: %s%n",
                  tuple.get("employeeName", String.class),
                  tuple.get("supervisor", String.class));
      }
  }
}
-- Employee persisted --
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=8, name='Jack', tasks=[]}
-- find employees left joined with tasks --
name: Diana, supervisor: Denise
name: Mike, supervisor: Mike
name: Mike, supervisor: Rose
name: Jack, supervisor: null
name: Tim, supervisor: Mike
name: Tim, supervisor: Denise

The query returned all employee names and supervisor names even there are no tasks (because of LEFT JOIN) and so the corresponding supervisor does not exit.

Now let's apply an 'ON' condition. This time we will filter the results using ON condition so that the results will only show supervisor = 'Denise'

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeesBySupervisor();
      } finally {
          entityManagerFactory.close();
      }
  }
    .............
  private static void findEmployeesBySupervisor() {
      System.out.println("-- find employees left joined with tasks with supervisor 'Denise' --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
      Root<Employee> employee = query.from(Employee.class);
      ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks, JoinType.LEFT);
      tasks.on(criteriaBuilder.equal(tasks.get(Task_.supervisor), "Denise"));
      query.select(criteriaBuilder.tuple(employee.get(Employee_.name).alias("employeeName"),
              tasks.get(Task_.supervisor).alias("supervisor")))
           .distinct(true);
      TypedQuery<Tuple> typedQuery = entityManager.createQuery(query);
      for (Tuple tuple : typedQuery.getResultList()) {
          System.out.printf("name: %s, supervisor: %s%n",
                  tuple.get("employeeName", String.class),
                  tuple.get("supervisor", String.class));
      }
  }
}
-- Employee persisted --
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=8, name='Jack', tasks=[]}
-- find employees left joined with tasks with supervisor 'Denise' --
name: Diana, supervisor: Denise
name: Jack, supervisor: null
name: Mike, supervisor: null
name: Tim, supervisor: Denise

Example Project

Dependencies and Technologies Used:

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

Criteria API Left Outer Join On Condition Select All Download
  • jpa-criteria-api-left-join-on-condition
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain2.java
          • resources
            • META-INF

    See Also