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 }
