Close

JPA Criteria API - Left Outer Join

[Updated: Aug 28, 2018, Created: Aug 27, 2018]

A LEFT OUTER JOIN (or simply LEFT JOIN) selects all records from the left side table even if there are no matching records in right side table.

In JPA Criteria API, Left Outer Join can be applied by specifying JoinType.LEFT in the one of various methods of From interface. For example:

ListJoin<X, Y> join(ListAttribute<? super X, Y> list, JoinType jt);

Quick example:

  CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
  Root<Employee> employee = query.from(Employee.class);
  employee.join(Employee_.tasks, JoinType.LEFT);
  query.select(employee).distinct(true);
  TypedQuery<Employee> typedQuery = entityManager.createQuery(query);
  List<Employee> list = typedQuery.getResultList();

JoinType enum

package javax.persistence.criteria;
 ...
public enum JoinType {
  INNER, 
  LEFT, 
  RIGHT
}

By default, JoinType.INNER is used (last example). According to JPA specifications, applications that make use of right outer joins or right outer fetch joins will not be portable.

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

Applying Left Outer Join

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeesWithTasks();
      } 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 findEmployeesWithTasks() {
      System.out.println("-- find employees with tasks --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      employee.join(Employee_.tasks, JoinType.LEFT);
      query.select(employee).distinct(true);
      TypedQuery<Employee> typedQuery = entityManager.createQuery(query);
      List<Employee> resultList = typedQuery.getResultList();
      resultList.forEach(System.out::println);
  }
}
-- 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 with tasks --
Employee{id=8, name='Jack', tasks=[]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', 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 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);
      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 with supervisor Denise --
name: Diana, supervisor: Denise
name: Mike, supervisor: Mike
name: Mike, supervisor: Rose
name: Jack, supervisor: null
name: Tim, supervisor: Mike
name: Tim, supervisor: Denise

Also check out how to apply LEFT OUTER JOIN in JPQL.

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

Left Outer Join Example Select All Download
  • jpa-criteria-api-left-outer-join
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain2.java
          • resources
            • META-INF

    See Also