原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 @Entity14 @Table(name="books")15 public class Book {16     @Id17     @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 @Service22 public class BookQueryService {23 24     @Autowired25     private EntityManager entityManager;26     27     /**28      * select type,max(price) maxPrice,sum(price) sumPrice from books group by type29      */30     public Page
     
       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
      
        criteriaQuery = criteriaBuilder.createQuery(BookInfo.class);37         //获取查询实例的属性，select * from books38         Root
       
         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 = 142         criteriaQuery.where(criteriaBuilder.equal(root.get("type"), 1));43         //group by type44         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
        
          counts = entityManager.createQuery(criteriaQuery).getResultList();48         //sql查询对象49         TypedQuery
         
           createQuery = entityManager.createQuery(criteriaQuery);50         //设置分页参数51         createQuery.setFirstResult(index*pageSize);52         createQuery.setMaxResults(pageSize);53         //返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数54         return new PageImpl
          
           (createQuery.getResultList(), page, counts.size());55 }56 }