Close

JPQL FETCH JOIN

[Last Updated: May 16, 2018]

The 'FETCH' option can be used on a JOIN (either INNER JOIN or LEFT JOIN) to fetch the related entities in a single query instead of additional queries for each access of the object's lazy relationships.

Example

Entities

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

Note that the default fetch type of the @ManyToMany annotation is LAZY, but we still used that in the above class for clarity.

@Entity
public class Task {
  @Id
  @GeneratedValue
  private long id;
  private String description;
  private String supervisor;

  public Task() {
  }
    .............
}

INNER JOIN without FETCH option

To understand the advantage of 'FETCH' option, first let's see INNER JOIN without this option.

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          executeQuery();
      } 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 executeQuery() {
      System.out.println("-- executing query --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT DISTINCT e FROM Employee e INNER JOIN e.tasks t");
      List<Employee> resultList = query.getResultList();
      for (Employee employee : resultList) {
          System.out.println(employee.getName() + " - " + employee.getTasks());
      }
      em.close();
  }
}
-- 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=[]}
-- executing query --
Hibernate: select distinct employee0_.id as id1_0_, employee0_.name as name2_0_ from Employee employee0_ inner join Employee_Task tasks1_ on employee0_.id=tasks1_.Employee_id inner join Task task2_ on tasks1_.tasks_id=task2_.id
Hibernate: select tasks0_.Employee_id as Employee1_1_0_, tasks0_.tasks_id as tasks_id2_1_0_, task1_.id as id1_2_1_, task1_.description as descript2_2_1_, task1_.supervisor as supervis3_2_1_ from Employee_Task tasks0_ inner join Task task1_ on tasks0_.tasks_id=task1_.id where tasks0_.Employee_id=?
Tim - [Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]
Hibernate: select tasks0_.Employee_id as Employee1_1_0_, tasks0_.tasks_id as tasks_id2_1_0_, task1_.id as id1_2_1_, task1_.description as descript2_2_1_, task1_.supervisor as supervis3_2_1_ from Employee_Task tasks0_ inner join Task task1_ on tasks0_.tasks_id=task1_.id where tasks0_.Employee_id=?
Mike - [Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]
Hibernate: select tasks0_.Employee_id as Employee1_1_0_, tasks0_.tasks_id as tasks_id2_1_0_, task1_.id as id1_2_1_, task1_.description as descript2_2_1_, task1_.supervisor as supervis3_2_1_ from Employee_Task tasks0_ inner join Task task1_ on tasks0_.tasks_id=task1_.id where tasks0_.Employee_id=?
Diana - [Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]

As seen in the output, for each employee.getTasks() call, a separate 'select' query was executed.

INNER JOIN with FETCH option

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          executeQuery();
      } finally {
          entityManagerFactory.close();
      }
  }
    .............
  private static void executeQuery() {
      System.out.println("-- executing query --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT DISTINCT e FROM Employee e INNER JOIN FETCH e.tasks t");
      List<Employee> resultList = query.getResultList();
      for (Employee employee : resultList) {
          System.out.println(employee.getName() + " - " + employee.getTasks());
      }
      em.close();
  }
}
-- 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=[]}
-- executing query --
Hibernate: select distinct employee0_.id as id1_0_0_, task2_.id as id1_2_1_, employee0_.name as name2_0_0_, task2_.description as descript2_2_1_, task2_.supervisor as supervis3_2_1_, tasks1_.Employee_id as Employee1_1_0__, tasks1_.tasks_id as tasks_id2_1_0__ from Employee employee0_ inner join Employee_Task tasks1_ on employee0_.id=tasks1_.Employee_id inner join Task task2_ on tasks1_.tasks_id=task2_.id
Diana - [Task{id=3, description='Designing', supervisor='Denise'}, Task{id=2, description='Coding', supervisor='Denise'}]
Mike - [Task{id=6, description='Documentation', supervisor='Mike'}, Task{id=5, description='Refactoring', supervisor='Rose'}]
Tim - [Task{id=6, description='Documentation', supervisor='Mike'}, Task{id=3, description='Designing', supervisor='Denise'}]

Only one 'select' statement for the main query was executed, hence the lazy employee#tasks relations were loaded at the same time.

LEFT JOIN with FETCH option

The FETCH option can also be used with 'LEFT JOIN'. As we have seen in our last tutorial the only difference with LEFT JOIN is, it will also return those left entity instances (employees) which have no related tasks.

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          executeQuery();
      } finally {
          entityManagerFactory.close();
      }
  }
    .............
  private static void executeQuery() {
      System.out.println("-- executing query --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery("SELECT DISTINCT e FROM Employee e LEFT JOIN FETCH e.tasks t");
      List<Employee> resultList = query.getResultList();
      for (Employee employee : resultList) {
          System.out.println(employee.getName() + " - " + employee.getTasks());
      }
      em.close();
  }
}
-- 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=[]}
-- executing query --
Hibernate: select distinct employee0_.id as id1_0_0_, task2_.id as id1_2_1_, employee0_.name as name2_0_0_, task2_.description as descript2_2_1_, task2_.supervisor as supervis3_2_1_, tasks1_.Employee_id as Employee1_1_0__, tasks1_.tasks_id as tasks_id2_1_0__ from Employee employee0_ left outer join Employee_Task tasks1_ on employee0_.id=tasks1_.Employee_id left outer join Task task2_ on tasks1_.tasks_id=task2_.id
Diana - [Task{id=3, description='Designing', supervisor='Denise'}, Task{id=2, description='Coding', supervisor='Denise'}]
Mike - [Task{id=6, description='Documentation', supervisor='Mike'}, Task{id=5, description='Refactoring', supervisor='Rose'}]
Tim - [Task{id=6, description='Documentation', supervisor='Mike'}, Task{id=3, description='Designing', supervisor='Denise'}]
Jack - []

As seen above, even though Jack entity doesn't have any tasks, it is also returned as compared to our last INNER JOIN example. Also at the same time, only one 'select' statement was executed because of the 'FETCH' option.

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

JPQL FETCH JOIN Example Select All Download
  • jpa-fetch-join-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain2.java
          • resources
            • META-INF

    See Also