Close

JPA Criteria API - Inner Join

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

This tutorial shows how to create INNER JOIN queries in JPA Criteria API.

Generally speaking, INNER JOIN queries select the records common to the target tables.

The method CriteriaQuery#from() returns a Root object. The Root interface extends From interface which has various methods to create objects which are equivalent SQL JOINS. For example following method creates an inner join for the specified List-valued attribute.

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

Quick example (assuming Employee has list of Tasks with many-to-many relation):

CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
   Root<Employee> employee = query.from(Employee.class);
   ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks);
   query.select(employee)
        .where(criteriaBuilder.equal(tasks.get(Task_.supervisor), employee.get(Employee_.name)));
   TypedQuery<Employee> typedQuery = entityManager.createQuery(query);
   List<Employee> employees = typedQuery.getResultList();

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 inner Join

Following example creates a inner join without any where clause. The inner join will only return results where employee tasks is not null.

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 em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Employee> query = cb.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      employee.join(Employee_.tasks);
      query.select(employee).distinct(true);
      TypedQuery<Employee> typedQuery = em.createQuery(query);
      typedQuery.getResultList().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=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'}]}

Following examples apply inner join with where clauses:

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 em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Employee> query = cb.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks);
      query.select(employee)
           .where(cb.equal(tasks.get(Task_.supervisor), "Denise"))
           .distinct(true);
      TypedQuery<Employee> typedQuery = em.createQuery(query);
      typedQuery.getResultList().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 supervisor Denise --
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, 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 ExampleMain3 {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeSupervisor();
      } finally {
          entityManagerFactory.close();
      }
  }
    .............
  private static void findEmployeeSupervisor() {
      System.out.println("-- find employees who are also supervisor --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employee = query.from(Employee.class);
      ListJoin<Employee, Task> tasks = employee.join(Employee_.tasks);
      query.select(employee)
           .where(criteriaBuilder.equal(tasks.get(Task_.supervisor), employee.get(Employee_.name)));
      TypedQuery<Employee> typedQuery = entityManager.createQuery(query);
      typedQuery.getResultList().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 who are also supervisor --
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}

Also check out how to apply inner 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

Inner Join Example Select All Download
  • jpa-criteria-api-inner-join
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain2.java
          • resources
            • META-INF

    See Also