INNER JOIN

使用INNER JOIN且未限定條件

當JPQL使用以下語句時：

SELECT DISTINCT e FROM Employee e 
                  INNER JOIN e.tasks t      

對應的Criteria API用法如下：

public void Criteria_InnnerJoin() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  emp.join(Employee_.tasks);
  sql.select(emp)
      .distinct(true);
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee1, employee2, employee3));
  assertThat(resultList, hasSize(3));
}    

說明

• 行6：

emp.join(Employee_.tasks); 等價於Employee e INNER JOIN e.tasks t

使用INNER JOIN並限定條件

承前例，但JPQL增加Task欄位值的限定條件：

SELECT DISTINCT e FROM Employee e 
                  INNER JOIN e.tasks t      
                  WHERE t.name='Denise'  

對應的Criteria API用法如下：

@Test
public void Criteria_InnerJoinWhere1() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  Join task = emp.join(Employee_.tasks);
  sql.select(emp)
      .where(cb.equal(task.get(Task_.name), "Denise"))
      .distinct(true);
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee1, employee3));
  assertThat(resultList, hasSize(2));
}

說明：

• 行7：

因WHERE子句需要INNER JOIN的表格的欄位加以限制條件，因此需要使用變數Join<Employee, Task> task承接Root<Employee>呼叫join()方法後回傳的物件。相似於Root<Employee>，但用Join<Employee, Task>；變數名稱慣例上使用第2個泛型型態Task來命名，本例為「task」。
若需要連續JOIN數個表格，如Entity類別A需要JOIN類別B與C，則依此類推：
Root<A> a = sql.from(A.class);
Join<A, C> c = a.join(A_.b).join(B_.c);

• 行9：

where(cb.equal(task.get(Task_.name), "Denise"))等價於「WHERE t.name='Denise'」

使用INNER JOIN並指定2表格結合的欄位

承前例，但JPQL條件限定為跨2個表格的欄位必須相等：

SELECT DISTINCT e FROM Employee e 
                  INNER JOIN e.tasks t      
                  WHERE t.name = e.name

對應的Criteria API用法如下：

@Test
public void Criteria_InnerJoinWhere2() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  ListJoin task = emp.join(Employee_.tasks);
  sql.select(emp)
      .where( cb.equal( task.get(Task_.name), emp.get(Employee_.name) ) );
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee2));
  assertThat(resultList, hasSize(1));
}

說明：

• 行9：

where( cb.equal( task.get(Task_.name), emp.get(Employee_.name) ) );等價於「WHERE t.name = e.name」