Close

JPA Criteria API - Inner Join

[Updated: Dec 11, 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 to SQL JOINS.

package javax.persistence.criteria;
 ......
public interface From<Z, X> extends Path<X>, FetchParent<Z, X> {
 ......
    <Y> Join<X, Y> join(SingularAttribute<? super X, Y> attribute);
    <Y> Join<X, Y> join(SingularAttribute<? super X, Y> attribute, JoinType jt);
    <Y> CollectionJoin<X, Y> join(CollectionAttribute<? super X, Y> collection);
    <Y> SetJoin<X, Y> join(SetAttribute<? super X, Y> set);
    <Y> ListJoin<X, Y> join(ListAttribute<? super X, Y> list);
    <K, V> MapJoin<X, K, V> join(MapAttribute<? super X, K, V> map);
    <Y> CollectionJoin<X, Y> join(CollectionAttribute<? super X, Y> collection, JoinType jt);
    <Y> SetJoin<X, Y> join(SetAttribute<? super X, Y> set, JoinType jt);
    <Y> ListJoin<X, Y> join(ListAttribute<? super X, Y> list, JoinType jt);
    <K, V> MapJoin<X, K, V> join(MapAttribute<? super X, K, V> map, JoinType jt);
    <X, Y> Join<X, Y> join(String attributeName);	
    <X, Y> CollectionJoin<X, Y> joinCollection(String attributeName);	
    <X, Y> SetJoin<X, Y> joinSet(String attributeName);	
    <X, Y> ListJoin<X, Y> joinList(String attributeName);		
    <X, K, V> MapJoin<X, K, V> joinMap(String attributeName);	
    <X, Y> Join<X, Y> join(String attributeName, JoinType jt);	
    <X, Y> CollectionJoin<X, Y> joinCollection(String attributeName, JoinType jt);	
    <X, Y> SetJoin<X, Y> joinSet(String attributeName, JoinType jt);	
    <X, Y> ListJoin<X, Y> joinList(String attributeName, JoinType jt);	
    <X, K, V> MapJoin<X, K, V> joinMap(String attributeName, JoinType jt);	
}

As seen above, the first parameter is either a subclass of javax.persistence.metamodel.Attribute or a string based entity's attribute name. If we are going to pass Attribute, then we can use fields provided by generated metamodel class. In metamodel class, all entity's field representations are of Attribute type.

All above methods return an instance of javax.persistence.criteria.Join interface or a subinterface. Join extends From interface, which means we can obtain Paths of attributes belonging to the join (just like we do for Root object).


In this tutorial, we will use following method:

<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