在WHERE敘述使用比較(Comparision)關鍵字：BETWEEN

本文為【Spring Boot情境式網站開發指南：使用Spring Data JPA、Spring Security、Spring Web Flow】一書的【 第4章 Criteria API入門】延續，完整範例程式碼可至出版社下載

BETWEEN數字區間

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.salary BETWEEN 2000 AND 4000  

對應的Criteria API用法如下：

@Test 
public void Criteria_BetweenLiteralNumbers() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  sql.select(emp)
      .where(cb.between(emp.get(Employee_.salary), 2000.0, 4000.0));
  List resultList = em.createQuery(sql).getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee1, employee2, employee4));
  assertThat(resultList, hasSize(3));
}

BETWEEN數字欄位與數字

當JPQL使用以下查詢時：

SELECT e FROM Employee e 
        INNER JOIN e.supervisor s 
            WHERE e.salary BETWEEN s.salary AND 4000
            ORDER BY e.salary

對應的Criteria API用法如下：

@Test 
public void Criteria_BetweenLiteralNumberAndJoinedColumn() {
   EntityManager em = emf.createEntityManager();
   CriteriaBuilder cb = em.getCriteriaBuilder();
   CriteriaQuery sql = cb.createQuery(Employee.class);
   Root emp = sql.from(Employee.class);
   Join empSupervisor= emp.join(Employee_.supervisor);
   sql.select(emp)
       .where( cb.between(emp.get(Employee_.salary), 
                         empSupervisor.get(Employee_.salary),   // start
                         cb.literal(4000.0)) );                   // end
   List resultList = em.createQuery(sql).getResultList();
   resultList.forEach(System.out::println);
   em.close();
   assertThat(resultList, contains(employee1, employee2));
   assertThat(resultList, hasSize(2));
}

【說明】
行7：

因為要between()起始邊界為self Join 類別Employee，因此必須宣告Join<Employee, Employee> empSupervisor指向join()方法呼叫後的回傳物件。 

行9-11：

使用CriteriaBuilder的between()方法時，第1個參數指定要查詢的欄位，第2個參數為區間起始邊界，第3個參數為區間結束邊界。

BETWEEN日期區間命名變數 

當JPQL使用以下查詢時：

SELECT e FROM Employee e 
        WHERE e.joinDate BETWEEN :startDate AND :endDate 
        ORDER BY e.joinDate

對應的Criteria API用法如下：

@Test 
public void Criteria_BetweenNamedParams() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  // prepare parameters
  ParameterExpression startDate = 
                    cb.parameter(Timestamp.class, "myStart");
  ParameterExpression endDate = 
                    cb.parameter(Timestamp.class, "myEnd");
  // create query                   
  sql.select(emp)
      .where(cb.between(emp.get(Employee_.joinDate), startDate, endDate))
      .orderBy(cb.desc(emp.get(Employee_.joinDate)));
  // run query and set parameter values
  TypedQuery typedQuery = em.createQuery(sql);
  typedQuery.setParameter("myStart", 
                            localToTimeStamp(LocalDate.of(1990, 1, 1)));
  typedQuery.setParameter("myEnd", 
                            localToTimeStamp(LocalDate.of(2011, 1, 1)));
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, contains(employee3, employee1));
  assertThat(resultList, hasSize(2));
}

【說明】
行8-10：

以ParameterExpression<Timestamp>建立欄位joinDate的命名變數：myStart。

行10-11：

以ParameterExpression<Timestamp>建立欄位joinDate的命名變數：myEnd。

行18-19：

以TypedQuery的setParameter()方法指定命名變數myStart並傳入參數值。

行20-21：

以TypedQuery的setParameter()方法指定命名變數myEnd並傳入參數值。