Jim's 教學/工作分享雜記: 7月 2020

2020年7月19日星期日

LEFT OUTER JOIN

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

使用LEFT OUTER JOIN並擷取Employee所有欄位

當JPQL使用以下語句時：

SELECT DISTINCT e FROM Employee e 
       LEFT OUTER JOIN e.tasks t

對應的Criteria API用法如下：

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

說明：

行7：

emp.join(Employee_.tasks, JoinType.LEFT);等價於Employee e LEFT OUTER JOIN e.tasks t。方法join()未指定JoinType時預設為JoinType.INNER。本例為LEFT JOIN，需指定JoinType.LEFT。

使用LEFT OUTER JOIN擷取Employee的name與Task的description欄位

承前例，但查詢的欄位改為「e.name, t.name」。當JPQL使用以下語句時：

SELECT DISTINCT e.name, t.name 
    FROM Employee e 
     LEFT OUTER JOIN e.tasks t

對應的Criteria API用法如下：

@Test 
public void Criteria_LeftJoin2() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createTupleQuery();
    Root emp = sql.from(Employee.class);
    Join task = emp.join(Employee_.tasks, JoinType.LEFT);
    // task.on(cb.equal(task.get(Task_.name), "Denise"));
    sql.select(cb.tuple(emp.get(Employee_.name).alias("employeeName"),
                         task.get(Task_.name).alias("supervisor")))
        .distinct(true);
    TypedQuery typedQuery = em.createQuery(sql);
    List resultList = typedQuery.getResultList();
    List rows = resultList.stream()
            .map(t -> t.get("employeeName", String.class) + ", " + 
                        t.get("supervisor", String.class))
            .collect(Collectors.toList());
    rows.forEach(System.out::println);
    em.close();
    assertThat(rows, containsInAnyOrder(
            "Tim, Mike", 
            "Tim, Denise",
            "Mike, Rose",
            "Mike, Mike",
            "Jim, Denise", 
            "Jack, null"));
    assertThat(rows, hasSize(6));
    /*      
    assertThat(rows, containsInAnyOrder(
            "Jim, Denise", 
            "Jack, null", 
            "Mike, null",
            "Tim, Denise"));
    assertThat(rows, hasSize(4));
    */  
}

說明：

行7：

因為必須查詢聯結表格Task的欄位，因此建立Join<Employee, Task> task的物件參考指向join()方法執行結果。

行8：

可以藉由Join<Employee, Task> task的on()方法限定對聯結表格Task的查詢條件，對應的JPQL子句為「LEFT OUTER JOIN e.tasks t ON t.name='Denise'」。本行若取消comment，將改得到行29-34的結果；或改執行單元測試方方法Criteria_LeftJoinOn()驗證結果。

行10：

指定要查詢Task的name欄位。

Jim

1. Java：SCJP (SE4)、SCWCD、SCBCD (EE5)、SCJD (SE5)、OCEJWSD (EE6)、OCPWCD (EE7)、OCAJP (SE7)、OCPJP (SE7)、OCAJP (SE8, 滿分通過)、OCPJP (SE8, 滿分通過)、OCP, Java SE 11 Developer
2. 資訊安全：CEH (EC-Council Certified Ethical Hacker)、ECSP Java (EC-Council Certified Secure Programmer)、ECSA (EC-Council Certified Security Analyst)
3. Oracle DBA：9i OCP 、9i PLSQL 、10g OCP
4. SQL Server 2005 MCTS

在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並傳入參數值。

Jim

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

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

【小標】BETWEEN數字區間
當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.salary IN (2000.0, 3000.0, 4000.0)

對應的Criteria API用法如下：

@Test 
public void Criteria_InExpression() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee.class);
    Root emp = sql.from(Employee.class);
    Expression salary = emp.get(Employee_.salary);
    Expression inExp = salary.in(2000.0, 3000.0, 4000.0);
    sql.select(emp).where(inExp);
    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));
}

行7：

建立欄位表示式物件Expression<Double> salary = emp.get(Employee_.salary);

行8：

呼叫Expression<Double>物件的in()方法，允許傳入個數變動的Double物件，本例為in(2000.0, 3000.0, 4000.0)，並回傳Expression<Boolean>物件，將作為where()方法的輸入參數。

使用NOT IN於字面字串常量群組

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.name NOT IN ('Jim', 'Rose')

對應的Criteria API用法如下：

public void Criteria_NotInExpression() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee.class);
    Root emp = sql.from(Employee.class);
    sql.select(emp) .where( emp.get(Employee_.name).in("Jim", "Rose").not() );
    TypedQuery typedQuery = em.createQuery(sql);
    List resultList = typedQuery.getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, containsInAnyOrder(employee3, employee4));
    assertThat(resultList, hasSize(2));
}

行6：

承前單元測試方法Criteria_InExpression()，本例不在刻意建立欄位表示式物件Expression<String>，與呼叫in()方法後回傳的Expression<Boolean>物件，直接將兩者合併傳入方法where()中：where( emp.get(Employee_.name).in("Jim", "Rose").not() );並在in()方法後再呼叫not()方法，表示否定：NOT IN。

使用IN於命名變數

當JPQL使用以下查詢時：

    Query query = em.createQuery(
        "SELECT e FROM Employee e WHERE e.dept IN :deptNames");
    query.setParameter("deptNames", Arrays.asList("IT", "Sales", "HR"));

對應的Criteria API用法如下：

@Test
public void Criteria_ParameterExpression() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  ParameterExpression deptParam = 
                      cb.parameter(Collection.class, "deptNames");
  sql.select(emp) .where(emp.get(Employee_.dept).in(deptParam));
  TypedQuery typedQuery = em.createQuery(sql);
  typedQuery.setParameter("deptNames", Arrays.asList("IT", "Sales", "HR"));
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee1, employee2, employee4));
  assertThat(resultList, hasSize(3));
}

【說明】
行7-8：

以ParameterExpression<Collection>建立欄位dept的命名變數物件參考「deptParam」，其名稱為「deptNames」，注意參數型態為Collection。

行9：

欄位emp.get(Employee_.dept)的限定條件為in(deptParam);

行11：

以TypedQuery的setParameter()方法指定命名變數deptNames並傳入Collection物件Arrays.asList("IT", "Sales", "HR")作為參數值。

Jim

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

使用LIKE搭配萬用字元「_」查詢
當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.salary LIKE '_500.0'

對應的Criteria API用法如下：

@Test 
public void Criteria_Like1() {
    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.like( emp.get(Employee_.salary).as(String.class), "_500.0" ) );
    List resultList = em.createQuery(sql).getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, containsInAnyOrder(employee4));
    assertThat(resultList, hasSize(1));
}

行8：

使用CriteriaBuilder的like()方法，第2個欄位指定like的樣式，第1個參數指定欄位。本例因為salary欄位是數字，要樣式比對必須是字串欄位：Predicate like(Expression<String> x, String pattern);因此使用as(String.class)方法將salary數字欄位值轉換為字串：emp.get(Employee_.salary).as(String.class)

使用LIKE搭配萬用字元「%」查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.name LIKE 'J%'

對應的Criteria API用法如下：

@Test 
public void Criteria_Like2() {
    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.like( emp.get(Employee_.name), "J%" ) );
    List resultList = em.createQuery(sql).getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, containsInAnyOrder(employee1));
    assertThat(resultList, hasSize(1));
}

使用NOT LIKE搭配命名變數查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.name NOT LIKE :nameStartsWith

對應的Criteria API用法如下：

@Test
public void Criteria_NotLikeNamedParams() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  ParameterExpression nameParam = 
          cb.parameter(String.class, "nameStartsWith");
  sql.select(emp)
      .where( cb.notLike(emp.get(Employee_.name), nameParam) );
  //  .where( cb.like(emp.get(Employee_.name), nameParam).not() );
  TypedQuery typedQuery = em.createQuery(sql);
  typedQuery.setParameter("nameStartsWith", "J%");
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee2, employee3, employee4));
  assertThat(resultList, hasSize(3));
}

行7-8：

以ParameterExpression< String >建立欄位name的命名變數物件參考「nameParam」，其名稱為「nameStartsWith」。

行10：

使用CriteriaBuilder的notLike()方法。欄位emp.get(Employee_.name)的限定條件為notLike (nameParam);

行11：

也可以使用CriteriaBuilder的like()方法，並在結尾加上not()。

行13：

以TypedQuery的setParameter()方法指定命名變數nameStartsWith並傳入字串樣式"J%"作為參數值。

使用LIKE並以ESCAPE跳脫萬用字元「_」查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.dept LIKE '%@_%' ESCAPE '@'

對應的Criteria API用法如下：

@Test 
public void Criteria_LikeEscape() {
    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.like(emp.get(Employee_.dept), "%@_%", '@'));
    List resultList = em.createQuery(sql).getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, containsInAnyOrder(employee4));
    assertThat(resultList, hasSize(1));
}

行8：

使用CriteriaBuilder的like()方法，因第2個參數要比對的是like樣式內的特殊字元「_」，因此由最後參數定義使用的跳脫字元：Predicate like(Expression<String> x, String pattern, char escapeChar);本例為「@」：cb.like( emp.get(Employee_.dept), "%@_%", '@' );

Jim

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

使用「isNull()」或「isNotNull()」可以查詢一般欄位物件值是否為null。

使用 IS NULL 查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.dept IS NULL

對應的Criteria API用法如下：

@Test
public void Criteria_IsNull() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  sql.select(emp)
      .where(emp.get(Employee_.dept).isNull());
  //  .where(cb.equal(emp.get(Employee_.dept), cb.nullLiteral(String.class)));
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee4));
  assertThat(resultList, hasSize(1));
}

行8：

使用欄位表示式物件emp.get(Employee_.dept)的isNull()方法，等價於「e.dept IS NULL」

行9：

也可以使用CriteriaBuilder的equal()方法，第1個參數指定欄位dept，第2個參數則為CriteriaBuilder的nullLiteral(String.class)方法，等價於「e.dept = NULL」。

使用 IS NOT NULL 查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE e.dept IS NOT NULL

對應的Criteria API用法如以下範例行8的isNotNull()：

@Test
public void Criteria_IsNotNull() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  sql.select(emp)
      .where(emp.get(Employee_.dept).isNotNull());
  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));
}

Jim

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

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

使用CriteriaBuilder的isEmpty()或isNotEmpty()方法可以查詢集合型態的物件欄位值是否為空(empty)。

使用 IS EMPTY 查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee1 e WHERE e.tasks IS EMPTY

對應的Criteria API用法如下：

@Test
public void Criteria_JoinedEntityIsEmpty() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee1.class);
    Root emp1 = sql.from(Employee1.class);
    sql.select(emp1)
        .where( cb.isEmpty(emp1.get(Employee1_.tasks)) );
    List resultList = em.createQuery(sql).getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, containsInAnyOrder(employee4, employee2));
    assertThat(resultList, hasSize(2));
}

行8：

使用CriteriaBuilder的isEmpty()方法查詢集合型態的欄位Employee1_.tasks是否為空(empty)。

使用 IS NOT EMPTY 查詢

當JPQL使用以下查詢時：

SELECT e FROM Employee1 e WHERE e.tasks IS NOT EMPTY

對應的Criteria API用法如下：

@Test
public void Criteria_JoinedEntityIsNotEmpty() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee1.class);
    Root emp1 = sql.from(Employee1.class);
    sql.select(emp1)
        .where( cb.isNotEmpty(emp1.get(Employee1_.tasks)) );
    List resultList = em.createQuery(sql).getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, contains(employee1, employee3));
    assertThat(resultList, hasSize(2));
}

行8：

使用CriteriaBuilder的isNotEmpty()方法查詢集合型態的欄位Employee1_.tasks是否不為空(empty)。

對一般欄位使用 IS NULL 敘述

對非集合物件型態的一般欄位應該使用使用isNull()方法判斷是否為空(null)。當JPQL使用以下查詢時：

SELECT DISTINCT e FROM Employee1 e INNER JOIN e.tasks t WHERE t.name IS NULL

對應的Criteria API用法如下：

@Test
public void Criteria_JoinedEntityFieldIsEmpty() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee1.class);
    Root emp1 = sql.from(Employee1.class);
    ListJoin tasks = emp1.join(Employee1_.tasks);
    sql.select(emp1)
        .distinct(true)
        .where(cb.isNull(tasks.get(Task_.name)));
    List resultList = em.createQuery(sql).getResultList();
    resultList.forEach(System.out::println);
    em.close();
    assertThat(resultList, contains(employee3));
    assertThat(resultList, hasSize(1));
}

行10：

使用CriteriaBuilder的isNull()方法查詢非集合型態的欄位Task_.name是否為空(null)。

Jim

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

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

使用CriteriaBuilder的isMember()方法和isNotMember()方法決定關聯的「集合物件」內是否有特定成員。以isMember()為例：
【語法】
<E, C extends Collection<E>>
Predicate isMember(Expression<E> elem, Expression<C> collection);
泛型E：代表集合物件成員型態。
泛型C：代表集合物件型態。
回傳型態Predicate。
方法參數：
1. Expression<E> elem：型態為E的Expression物件參考，輸入成員物件。
2. Expression<C> collection：型態為C的Expression物件參考，輸入集合物件欄位物件。

使用 MEMBER OF

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE '777' MEMBER OF e.phoneNumbers

對應的Criteria API用法如下：

@Test
public void Criteria_MemberOf() {
  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.isMember(cb.literal("777"), emp.get(Employee_.phoneNumbers)));
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  //assertThat(resultList, IsEmptyCollection.empty());
  assertThat(resultList, containsInAnyOrder(employee1, employee3));
  assertThat(resultList, hasSize(2));
}

行8：

查詢欄位Employee_.phoneNumbers內含字串777者。藉由CriteriaBuilder 的literal()方法將String轉換為Expression<String>。

使用 NOT MEMBER OF

當JPQL使用以下查詢時：

SELECT e FROM Employee e WHERE '777' NOT MEMBER OF e.phoneNumbers

對應的Criteria API用法如下：

@Test 
public void Criteria_NotMemberOf() {
  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.isNotMember("777", emp.get(Employee_.phoneNumbers)));
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee2, employee4));
  assertThat(resultList, hasSize(2));
}

行8：

使用CriteriaBuilder的isNotMember()方法。第1個參數直接使用String型態，此為Expression<String>的overloading版本。

方法isMember()或isNotMember()的第1個參數也可以是另一個「一般型態」的欄位。當JPQL使用以下查詢時：

SELECT e FROM Employee e 
    WHERE e.primaryPhoneNumber 
            NOT MEMBER OF e.phoneNumbers

對應的Criteria API用法如下，注意行8程式碼：

@Test
public void Criteria_NotMemberOf2() {
  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.isNotMember(emp.get(Employee_.primaryPhoneNumber),
                            emp.get(Employee_.phoneNumbers)));
  TypedQuery typedQuery = em.createQuery(sql);
  List resultList = typedQuery.getResultList();
  resultList.forEach(System.out::println);
  em.close();
  assertThat(resultList, containsInAnyOrder(employee4, employee2));
  assertThat(resultList, hasSize(2));
}

Jim

使用GROUP BY與聚合(Aggregation)函式

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

聚合函式不一定套用在全部資料，也可以使用CriteriaBuilder的groupBy()方法予以分組再執行聚合函式，並可搭配CriteriaBuilder的having()方法過濾分組條件。

groupBy() & count()

當JPQL使用以下查詢時：

SELECT e.dept, COUNT(e) FROM Employee e GROUP BY e.dept

對應的Criteria API用法如下：

@Test 
public void Criteria_GroupBy4Count() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery

【說明】
行8-9：

使用CriteriaBuilder的groupBy()指定分組的依據欄位；該欄位也要出現在multiselect()方法中。

分組的目的在使用count()方法計算各組數量。

行5：

查詢分組欄位與各分組數量共2個欄位，使用Object[]型態輸出。

groupBy() & avg()

當JPQL使用以下查詢時：

SELECT e.dept, AVG(e.salary) FROM Employee e GROUP BY e.dept

對應的Criteria API用法如下範例程式碼行8-10。同前例，只是將count()改為avg()，且avg()只接受數字欄位：

@Test
public void Criteria_GroupBy4Average() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery

MAX()、GROUP BY、HAVING

當JPQL使用以下查詢時：

SELECT e.dept, MAX(e.salary) FROM Employee e 
GROUP BY e.dept 
HAVING e.dept IN ('IT', 'Admin')

對應的Criteria API用法如下：

@Test
public void Criteria_GroupByAndHaving() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery

【說明】
行10：

使用CriteriaBuilder的having()方法限定分組欄位dept只能是IT與Admin兩種值：emp.get(Employee_.dept)in("IT", "Admin"));

相似的情況，也可以再加上orderBy()方法排序輸出結果。當JPQL使用以下查詢時：

SELECT NEW entity.DeptGroup(e.dept, COUNT(e.dept)) 
  FROM Employee e 
 GROUP BY e.dept 
 HAVING COUNT(e.dept) > 1 
 ORDER BY COUNT(e.dept) DESC, e.dept ASC

對應的Criteria API用法如下，可以由JPQL_GroupByWithConstructor()驗證：

@Test
public void Criteria_GroupByWithConstructor() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(DeptGroup.class);
    Root emp = sql.from(Employee.class);
    // reusable column expressions
    Expression deptExp = emp.get(Employee_.dept);
    Expression countExp = cb.count(deptExp);
    // make sql
    sql.multiselect(deptExp, countExp);
    sql.groupBy(deptExp);
    sql.having(cb.gt(countExp, 1));
    sql.orderBy(cb.desc(countExp), cb.asc(deptExp));
    // run sql
    TypedQuery query = em.createQuery(sql);
    List resultList = query.getResultList();
    resultList.forEach(System.out::println);
    assertThat(resultList, 
            contains(new DeptGroup("Admin", 2), new DeptGroup("IT", 2)));
}

【說明】
行8-9：

群組欄位emp.get(Employee_.dept)與套用聚合函式的欄位在建構SQL時會反覆使用，因此宣告變數以利重複使用：
Expression<String> deptExp = emp.get(Employee_.dept);
Expression<Long> countExp = cb.count(deptExp);

行10-14：

建構SQL： sql.having(cb.gt(countExp, 1))：要求分組後的數量統計必須great than(gt) 1。
sql.orderBy(cb.desc(countExp), cb.asc(deptExp))：輸出時排序。

Jim

使用聚合(Aggregation)函式

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

JPQL支援在SELECT敘述內使用AVG、COUNT、MAX、MIN、SUM等聚合函式。

COUNT()

當JPQL使用以下查詢時：

SELECT COUNT(e) FROM Employee e

【語法】

Expression<Long> count(Expression<?> x);
1. 回傳型態為Expression<Long>，即Long型態欄位。
2. 方法參數：
Expression<?> x：不限定欄位型態。

如以下範例行7：

@Test
public void Criteria_Count() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Long.class);
    Root emp = sql.from(Employee.class);
    sql.select(cb.count(emp));
    TypedQuery typedQuery = em.createQuery(sql);
    Long count = typedQuery.getSingleResult();
    System.out.println(count);
    em.close();
    assertEquals(4, count.intValue());
}

AVG()

當JPQL使用以下查詢時：

SELECT AVG(e.salary) FROM Employee e

對應Criteria API可以使用CriteriaBuilder的avg()方法：
【語法】

<N extends Number> Expression<Double> avg(Expression<N> x);
1. 泛型N必須是Number的子類別，即數字型態。
2. 回傳型態為Expression<Double>，因此數字型態取平均後得到Double型態。
3. 方法參數：
Expression<N> x：需為數字型態的欄位。

如以下範例行7：

@Test
public void Criteria_Average() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Double.class);
    Root emp = sql.from(Employee.class);
    sql.select(cb.avg(emp.get(Employee_.salary)));
    TypedQuery typedQuery = em.createQuery(sql);
    Double average = typedQuery.getSingleResult();
    System.out.println(average);
    em.close();
    assertEquals(2625.0, average, 0.0);
}

max() & greatest()

當JPQL使用以下查詢取得數字欄位的最大數值時：

SELECT MAX(e.salary) FROM Employee e

對應Criteria API可以使用CriteriaBuilder的max()方法：
【語法】

<N extends Number> Expression<N> max(Expression<N> x);
1. 泛型N必須是Number的子類別，即數字型態。
2. 回傳型態為Expression<N>，即數字型態欄位。
3. 方法參數：
Expression<N> x：需為數字型態的欄位。

如以下範例行7：

@Test
public void Criteria_Max() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Double.class);
    Root emp = sql.from(Employee.class);
    sql.select( cb.max( emp.get(Employee_.salary) ) );
    TypedQuery typedQuery = em.createQuery(sql);
    Double max = typedQuery.getSingleResult();
    System.out.println(max);
    em.close();
    assertEquals(4000.0, max, 0.0);
}

相似的情況，但若欄位型態非數字但求最大值，如String和Date，則使用CriteriaBuilder的greatest()方法：
【語法】

<X extends Comparable<? super X>> Expression<X> greatest(Expression<X> x);
1. 泛型X必須有實作Comparable介面，因此可比較。
2. 回傳型態為Expression<X>：型態為X的Expression物件參考，代表實作Comparable介面的物件型態的欄位。
3. 方法參數：
Expression<X> x：型態為X的Expression物件參考，代表實作Comparable介面的物件型態的欄位。

如以下範例行7：

@Test 
public void Criteria_Greatest() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(String.class);
    Root emp = sql.from(Employee.class);
    sql.select( cb.greatest( emp.get(Employee_.name) ) );
    TypedQuery typedQuery = em.createQuery(sql);
    String greatest = typedQuery.getSingleResult();
    System.out.println(greatest);
    em.close();
    assertEquals("Rose", greatest);
}

min() & least()

當JPQL使用以下查詢取得數字欄位的最小數值時：

SELECT MIN(e.salary) FROM Employee e

對應Criteria API可以使用CriteriaBuilder的min()方法：
【語法】

<N extends Number> Expression<N> min(Expression<N> x);
1. 泛型N必須是Number的子類別，即數字型態。
2. 回傳型態為Expression<N>，即數字型態的欄位。
3. 方法參數：
Expression<N> x：即數字型態的欄位。

如以下範例行7：

@Test
public void Criteria_Min() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Double.class);
    Root emp = sql.from(Employee.class);
    sql.select( cb.min( emp.get(Employee_.salary) ) );
    TypedQuery typedQuery = em.createQuery(sql);
    Double min = typedQuery.getSingleResult();
    System.out.println(min);
    em.close();
    assertEquals(1500.0, min, 0.0);
}

相似的情況，但若欄位型態非數字但求最小值，如String和Date，則使用CriteriaBuilder的least()方法：
【語法】

<X extends Comparable<? super X>> Expression<X> least(Expression<X> x);
1. 泛型X必須有實作Comparable介面，因此可比較。
2. 回傳型態為Expression<X>：型態為X的Expression物件參考，代表實作Comparable介面的物件型態的欄位。
3. 方法參數：
Expression<X> x：型態為X的Expression物件參考，代表實作Comparable介面的物件型態的欄位。

如以下範例行7：

@Test
public void Criteria_Least() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(String.class);
    Root emp = sql.from(Employee.class);
    sql.select( cb.least( emp.get(Employee_.name) ) );
    TypedQuery typedQuery = em.createQuery(sql);
    String least = typedQuery.getSingleResult();
    System.out.println(least);
    em.close();
    assertEquals("Denise", least);
}

SUM()

當JPQL使用以下查詢時：

SELECT SUM(e.salary) FROM Employee e

對應Criteria API可以使用CriteriaBuilder的sum()方法：
【語法】

<N extends Number> Expression<N> sum(Expression<N> x);
1. 泛型N必須是Number的子類別，即數字型態。
2. 回傳型態為Expression<N>，即為數字型態的欄位。
3. 方法參數：
Expression<N> x：需為數字型態的欄位。

如以下範例行7：

@Test
public void Criteria_Sum() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Double.class);
    Root emp = sql.from(Employee.class);
    sql.select(cb.sum(emp.get(Employee_.salary)));
    TypedQuery typedQuery = em.createQuery(sql);
    Double sum = typedQuery.getSingleResult();
    System.out.println(sum);
    em.close();
    assertEquals(10500.0, sum.doubleValue(), 0.0);
}

Jim

訂閱：文章 (Atom)

2020年7月19日 星期日

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

使用LEFT OUTER JOIN並擷取Employee所有欄位

使用LEFT OUTER JOIN擷取Employee的name與Task的description欄位

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

BETWEEN數字區間

BETWEEN數字欄位與數字

BETWEEN日期區間命名變數

使用NOT IN於字面字串常量群組

使用IN於命名變數

使用LIKE搭配萬用字元「%」查詢

使用NOT LIKE搭配命名變數查詢

使用LIKE並以ESCAPE跳脫萬用字元「_」查詢

使用 IS NULL 查詢

使用 IS NOT NULL 查詢

使用 IS EMPTY 查詢

使用 IS NOT EMPTY 查詢

對一般欄位使用 IS NULL 敘述

使用 MEMBER OF

使用 NOT MEMBER OF

groupBy() & count()

groupBy() & avg()

MAX()、GROUP BY、HAVING

COUNT()

AVG()

max() & greatest()

min() & least()

SUM()

2020年7月19日星期日

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

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