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

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 + "]";
}
}

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());
}
}