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

原book对象

package com.shaying.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Data//可省略get、set方法，后续可直接使用get、set方法
@Entity
@Table(name="books")
public class Book {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    @Column()
    private String title;
    @Column()
    private Integer type;
    @Column()
    private double price;
    public Book(){}
    public Book(String title, double price) {
        this.title = title;
        this.price = price;
    }
    
    public String toString() {
        return "Book [id=" + id + ", title=" + title + ", type=" + type + ", price=" + price + "]";
    }
}

BookInfo对象

package com.shaying.domain;

import lombok.Data;

@Data
public class BookInfo {
    private Integer type;
    private double maxPrice;
    private double sumPrice;
    
    public BookInfo(){}
    public BookInfo(Integer type, double maxPrice, double sumPrice) {
        this.type = type;
        this.maxPrice = maxPrice;
        this.sumPrice = sumPrice;
    }
    
    public String toString() {
        return "BookInfo [type=" + type + ", maxPrice=" + maxPrice + ", sumPrice=" + sumPrice + "]";
    }
}

组建条件分组查询语句，返回分页查询结果

package com.shaying.service;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.shaying.domain.Book;
import com.shaying.domain.BookInfo;

@Service
public class BookQueryService {

    @Autowired
    private EntityManager entityManager;
    
    /**
     * select type,max(price) maxPrice,sum(price) sumPrice from books group by type
     */
    public Page<BookInfo> groupBy(int index, int pageSize){
        //新建一个页面，存放页面信息
        Pageable page = new PageRequest(index, pageSize);
        //criteriaBuilder用于构建CriteriaQuery的构建器对象
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        //criteriaQuery包含查询语句的各个部分，如where、max、sum、groupBy、orderBy等
        CriteriaQuery<BookInfo> criteriaQuery = criteriaBuilder.createQuery(BookInfo.class);
        //获取查询实例的属性，select * from books
        Root<Book> root = criteriaQuery.from(Book.class);
        //相当于select type,max(price) maxPrice,sum(price) sumPrice from books中select 与 from之间的部分
        criteriaQuery.multiselect(root.get("type"), criteriaBuilder.max(root.get("price")), criteriaBuilder.sum(root.get("price")));
        //where type = 1
        criteriaQuery.where(criteriaBuilder.equal(root.get("type"), 1));
        //group by type
        criteriaQuery.groupBy(root.get("type"));
        //criteriaQuery拼成的sql是select type,max(price) maxPrice,sum(price) sumPrice from books group by type；查询出的列与对象BookInfo的属性对应
        //记录当前sql查询结果总条数
        List<BookInfo> counts = entityManager.createQuery(criteriaQuery).getResultList();
        //sql查询对象
        TypedQuery<BookInfo> createQuery = entityManager.createQuery(criteriaQuery);
        //设置分页参数
        createQuery.setFirstResult(index*pageSize);
        createQuery.setMaxResults(pageSize);
        //返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
        return new PageImpl<BookInfo>(createQuery.getResultList(), page, counts.size());
    }
}