spring data jpa条件分组查询及分页

原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 }
posted @ 2017-08-11 10:13  莎影  阅读(12521)  评论(0编辑  收藏  举报