原book对象
1 package com.shaying.domain;
2
3 import javax.persistence.Column;
4 import javax.persistence.Entity;
5 import javax.persistence.GeneratedValue;
6 import javax.persistence.GenerationType;
7 import javax.persistence.Id;
8 import javax.persistence.Table;
9
10 import lombok.Data;
11
12 @Data//可省略get、set方法，后续可直接使用get、set方法
13 @Entity
14 @Table(name="books")
15 public class Book {
16 @Id
17 @GeneratedValue(strategy=GenerationType.IDENTITY)
18 private Integer id;
19 @Column()
20 private String title;
21 @Column()
22 private Integer type;
23 @Column()
24 private double price;
25 public Book(){}
26 public Book(String title, double price) {
27 this.title = title;
28 this.price = price;
29 }
30
31 public String toString() {
32 return "Book [id=" + id + ", title=" + title + ", type=" + type + ", price=" + price + "]";
33 }
34 }
BookInfo对象
1 package com.shaying.domain;
2
3 import lombok.Data;
4
5 @Data
6 public class BookInfo {
7 private Integer type;
8 private double maxPrice;
9 private double sumPrice;
10
11 public BookInfo(){}
12 public BookInfo(Integer type, double maxPrice, double sumPrice) {
13 this.type = type;
14 this.maxPrice = maxPrice;
15 this.sumPrice = sumPrice;
16 }
17
18 public String toString() {
19 return "BookInfo [type=" + type + ", maxPrice=" + maxPrice + ", sumPrice=" + sumPrice + "]";
20 }
21 }
组建条件分组查询语句，返回分页查询结果
1 package com.shaying.service;
2
3 import java.util.List;
4
5 import javax.persistence.EntityManager;
6 import javax.persistence.TypedQuery;
7 import javax.persistence.criteria.CriteriaBuilder;
8 import javax.persistence.criteria.CriteriaQuery;
9 import javax.persistence.criteria.Root;
10
11 import org.springframework.beans.factory.annotation.Autowired;
12 import org.springframework.data.domain.Page;
13 import org.springframework.data.domain.PageImpl;
14 import org.springframework.data.domain.PageRequest;
15 import org.springframework.data.domain.Pageable;
16 import org.springframework.stereotype.Service;
17
18 import com.shaying.domain.Book;
19 import com.shaying.domain.BookInfo;
20
21 @Service
22 public class BookQueryService {
23
24 @Autowired
25 private EntityManager entityManager;
26
27 /**
28 * select type,max(price) maxPrice,sum(price) sumPrice from books group by type
29 */
30 public Page<BookInfo> groupBy(int index, int pageSize){
31 //新建一个页面，存放页面信息
32 Pageable page = new PageRequest(index, pageSize);
33 //criteriaBuilder用于构建CriteriaQuery的构建器对象
34 CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
35 //criteriaQuery包含查询语句的各个部分，如where、max、sum、groupBy、orderBy等
36 CriteriaQuery<BookInfo> criteriaQuery = criteriaBuilder.createQuery(BookInfo.class);
37 //获取查询实例的属性，select * from books
38 Root<Book> root = criteriaQuery.from(Book.class);
39 //相当于select type,max(price) maxPrice,sum(price) sumPrice from books中select 与 from之间的部分
40 criteriaQuery.multiselect(root.get("type"), criteriaBuilder.max(root.get("price")), criteriaBuilder.sum(root.get("price")));
41 //where type = 1
42 criteriaQuery.where(criteriaBuilder.equal(root.get("type"), 1));
43 //group by type
44 criteriaQuery.groupBy(root.get("type"));
45 //criteriaQuery拼成的sql是select type,max(price) maxPrice,sum(price) sumPrice from books group by type；查询出的列与对象BookInfo的属性对应
46 //记录当前sql查询结果总条数
47 List<BookInfo> counts = entityManager.createQuery(criteriaQuery).getResultList();
48 //sql查询对象
49 TypedQuery<BookInfo> createQuery = entityManager.createQuery(criteriaQuery);
50 //设置分页参数
51 createQuery.setFirstResult(index*pageSize);
52 createQuery.setMaxResults(pageSize);
53 //返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
54 return new PageImpl<BookInfo>(createQuery.getResultList(), page, counts.size());
55 }
56 }