本文為【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 sql = cb.createQuery(Object[].class);
Root emp = sql.from(Employee.class);
// make sql
sql.multiselect(emp.get(Employee_.dept), cb.count(emp));
sql.groupBy(emp.get(Employee_.dept));
// run sql
TypedQuery typedQuery = em.createQuery(sql);
List resultList = typedQuery.getResultList();
em.close();
List stringList = resultList.stream()
.map(o -> Arrays.toString(o))
.collect(Collectors.toList());
stringList.forEach(System.out::println);
assertThat(stringList, containsInAnyOrder("[IT, 2]", "[Sales, 1]", "[Admin, 2]"));
}
【說明】
使用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 sql = cb.createQuery(Object[].class);
Root emp = sql.from(Employee.class);
// make sql
sql.multiselect(emp.get(Employee_.dept), cb.avg(emp.get(Employee_.salary)));
sql.groupBy(emp.get(Employee_.dept));
// run sql
TypedQuery typedQuery = em.createQuery(sql);
List resultList = typedQuery.getResultList();
em.close();
List stringList = resultList.stream().map(o -> Arrays.toString(o))
.collect(Collectors.toList());
stringList.forEach(System.out::println);
assertThat(stringList, containsInAnyOrder("[IT, 3250.0]", "[Sales, 2000.0]",
"[Admin, 3000.0]"));
}
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 sql = cb.createQuery(Object[].class);
Root emp = sql.from(Employee.class);
// make sql
sql.multiselect(emp.get(Employee_.dept), cb.max(emp.get(Employee_.salary)));
sql.groupBy(emp.get(Employee_.dept));
sql.having( emp.get(Employee_.dept).in("IT", "Admin") );
// run sql
TypedQuery typedQuery = em.createQuery(sql);
List resultList = typedQuery.getResultList();
em.close();
List stringList = resultList.stream().map(o -> Arrays.toString(o))
.collect(Collectors.toList());
stringList.forEach(System.out::println);
assertThat(stringList, containsInAnyOrder("[IT, 3500.0]", "[Admin, 4000.0]"));
}
【說明】
行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)));
}
【說明】
群組欄位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))：輸出時排序。
