孙卫琴的《精通JPA与Hibernate》的读书笔记:JPQL与QBC的分组查询

JPQL查询语句中的group by 子句用于分组查询，它和SQL中的用法很相似。下面举例说明它的用法。

（1）按照姓名分组，统计CUSTOMERS表中具有相同姓名的记录的数目：

//JPQL检索方式
Iterator<Object[]> it=entityManager
  .createQuery("select c.name,count(c) from Customer c  group by c.name",
                 Object[].class)
  .getResultList()
  .iterator();

while(it.hasNext()){
  Object[] pair=it.next();
  String name=(String)pair[0];
  Long count=(Long)pair[1];  
  System.out.println(name+":"+count);
}

//QBC检索方式
CriteriaQuery<Object[]> criteriaQuery =
      criteriaBuilder.createQuery(Object[].class );    
Root<Customer> root = criteriaQuery.from(Customer.class );
criteriaQuery.multiselect(root.get("name"),
                      criteriaBuilder.count(root));  
criteriaQuery.groupBy(root.get("name")); //按名字分组
List<Object[]> result=entityManager.createQuery(criteriaQuery)
                  .getResultList();

以上Query的getResultList()方法生成的SQL语句为：

select NAME,count(ID) from CUSTOMERS group by NAME;

Query的getResultList()方法返回的集合中包含四个对象数组类型的元素，每个对象数组对应查询结果中的一条记录。

（2）按照客户分组，统计每个客户的订单数目：

//JPQL检索方式
Iterator<Object[]> it=entityManager
  .createQuery("select c.id,c.name,count(o) from Customer c  "
  +" left join c.orders o group by c.id",Object[].class )
  .getResultList()
  .iterator();

while(it.hasNext()){
  Object[] pair=it.next();
  Long id=(Long)pair[0];
  String name=(String)pair[1];
  Long count=(Long)pair[2];  
  System.out.println(id+" "+name+" "+count);
}

//QBC检索方式
CriteriaQuery<Object[]> criteriaQuery =
      criteriaBuilder.createQuery(Object[].class );    
Root<Customer> root = criteriaQuery.from(Customer.class );
Join<Customer,Order> orderJoin=root.join("orders",JoinType.LEFT);

criteriaQuery.multiselect(root.get("id"),root.get("name"),
                      criteriaBuilder.count(orderJoin));  
criteriaQuery.groupBy(root.get("id")); //按客户ID分组
List<Object[]> result=entityManager.createQuery(criteriaQuery)
                  .getResultList();

以上JPQL查询语句对应的SQL语句为：

select c.ID, c.NAME, count(o.ID) from CUSTOMERS c 
left outer join ORDERS o
on c.ID=o.CUSTOMER_ID group by c.ID

（3）统计每个客户发出的所有订单的总价：

//JPQL检索方式
Iterator<Object[]> it=entityManager.createQuery(
   "select c.id,c.name,sum(o.price) from "
   +" Customer c left join c.orders o group by c.id",Object[].class)
  .getResultList()
  .iterator();

while(it.hasNext()){
  Object[] pair=it.next();
  Long id=(Long)pair[0];
  String name=(String)pair[1];
  Double price=(Double)pair[2];  
  System.out.println(id+" "+name+" "+price);
}

//QBC检索方式
CriteriaQuery<Object[]> criteriaQuery =
      criteriaBuilder.createQuery(Object[].class );    
Root<Customer> root = criteriaQuery.from(Customer.class );
Join<Customer,Order> orderJoin=root.join("orders",JoinType.LEFT);

criteriaQuery.multiselect(root.get("id"),root.get("name"),
                      criteriaBuilder.sum(orderJoin.get("price")));  
criteriaQuery.groupBy(root.get("id")); //按客户ID分组
List<Object[]> result=entityManager.createQuery(criteriaQuery)
                  .getResultList();

以上Query的getResultList()方法生成的SQL语句为：

select c.ID, c.NAME,sum(o.PRICE) from CUSTOMERS c 
left outer join ORDERS o 
on c.ID=o.CUSTOMER_ID group by c.ID;

having子句用于为分组查询加上约束，例如以下查询语句仅统计具有一条以上订单的客户的所有订单的总价：

//JPQL检索方式
Iterator<Object[]> it=entityManager.createQuery
  ("select c.id,c.name,sum(o.price) from Customer c "
  +"join c.orders o group by c.id having (count(o)>1)",Object[].class)
  .getResultList()
  .iterator();

while(it.hasNext()){
  Object[] pair=it.next();
  Long id=(Long)pair[0];
  String name=(String)pair[1];
  Double price=(Double)pair[2];  
  System.out.println(id+" "+name+" "+price);
}

//QBC检索方式
CriteriaQuery<Object[]> criteriaQuery =
      criteriaBuilder.createQuery(Object[].class );    
Root<Customer> root = criteriaQuery.from(Customer.class );
Join<Customer,Order> orderJoin=root.join("orders",JoinType.INNER);

criteriaQuery.multiselect(root.get("id"),root.get("name"),
                      criteriaBuilder.sum(orderJoin.get("price")));  
criteriaQuery.groupBy(root.get("id")); //按客户ID分组
//设置分组约束条件
criteriaQuery.having(criteriaBuilder.gt(
             criteriaBuilder.count(orderJoin),1));
List<Object[]> result=entityManager.createQuery(criteriaQuery)
                  .getResultList();

以上JPQL查询语句对应的SQL语句为：

select c.ID, c.NAME,sum(o.PRICE) from CUSTOMERS c 
inner join ORDERS o
on c.ID=o.CUSTOMER_ID group by c.ID having (count(o.ID)>1);