In this example, we will see how to use INNER JOIN queries in JPQL.

INNER JOIN queries select the records common to the target tables.

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;

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

Above example has @ManyToMany relationship; one employee can have many tasks and one task can be assigned to many employees (tasks done by teams rather than individuals).

Persisting entities

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

-- 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=[]}

INNER JOIN Query

   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();
       resultList.forEach(System.out::println);
       em.close();
   }

-- executing query --
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'}]}

Above query returns the rows where employee task is not null, or simply matching records in both Employee and Task tables exist. Only 'Jack' excluded because he is not assigned any task.

Alternatively, we can write above query as:

SELECT DISTINCT e FROM Employee e WHERE e.tasks IS NOT EMPTY

Note that 'INNER' keyword is optional, so our original query can be written as:

SELECT DISTINCT e FROM Employee e JOIN e.tasks t

Also we used two identifiers, 'e' for Employee entity and 't' for task entity.

INNER JOIN with WHERE Clause

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 e.tasks t where t.supervisor='Denise'");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      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 --
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'}]}

Above query returns only those employees who have the tasks supervised by 'Denise'.

Another JOIN query with WHERE clause:

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 INNER JOIN e.tasks t where t.supervisor = e.name");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      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 --
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}

Above query selects only 'Mike' because he has a task ('Documentation') whose supervisor is himself. The task 'Documentation' is assigned to two employees 'Mike' and 'Tim' and 'Mike' is the one who is also supervising the 'Documentation' team.

Note that a JOIN query can also be made for single valued relationship as well (other than collection based relationship). Check out an example here.

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