基于springdata的组合分页查询
用了springData这么久了,也分享自己在项目中遇到的问题以及解决办法。
需求:查询订单
/**
* 方法用来查询订单的详细信息,管理员可根据时间(总查询条件)
* 需做分页
* 查询订单,查询订单的条件有:
* 订单支付状态:已支付,未支付,废弃(对应状态2,3,4)
* 消费金额:0 - 50 ,50 -100 ,100
* 元以上
*/
根据需求,首先我们将查询条件封装成一个对象orederCondition
public class OrderCondition {
private String orderNo; //用户订单号
private String mobileNo;//用户手机号
private String imeiIdOrDeviceId; //车辆imeiId或者deviceId
private String orderStatus; //2,3,4
private String actualConsume; //消费金额
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public String getMobileNo() {
return mobileNo;
}
public void setMobileNo(String mobileNo) {
this.mobileNo = mobileNo;
}
public String getImeiIdOrDeviceId() {
return imeiIdOrDeviceId;
}
public void setImeiIdOrDeviceId(String imeiIdOrDeviceId) {
this.imeiIdOrDeviceId = imeiIdOrDeviceId;
}
public String getOrderStatus() {
return orderStatus;
}
public void setOrderStatus(String orderStatus) {
this.orderStatus = orderStatus;
}
public String getActualConsume() {
return actualConsume;
}
public void setActualConsume(String actualConsume) {
this.actualConsume = actualConsume;
}
}
1.编写dao类或接口,也就是orderDAO,springdata中我们习惯取名为orderRepository,正如mybatis中我们取名为orderMapper一样。
public interface OrderRepository extends JpaRepository<Order, Long>,JpaSpecificationExecutor<Order> {
List<Order> findByOrderStatus(Integer orderStatus);
}
使用springdata动态查询,你需要继承JpaSpecificationExecutor接口,你可以直接调用接口提供的方法,或者自定义实现方法。
2.重点来了,我写了个动态查询方法
public Page selectDetailByCondition(
String pageNum, String pageSize, long timestamp
, final OrderCondition orderCondition) throws Exception{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String time = sdf.format(timestamp);
String startTime = time + " 00:00:00";
String endTime = time + " 23:59:59";
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date startDate = dateFormat.parse(startTime);
Date endDate = dateFormat.parse(endTime);
pageNum = TextUtils.isBlank(pageNum) ? "1":pageNum;
pageSize = TextUtils.isBlank(pageSize) ? "10":pageSize;
1. 这里的PageUtil我写了分页工具类,比较简单,就不再介绍了,有问题可以咨询我
Pageable pageAble =PageUtil.buildPageRequest(pageNum,pageSize);
Page page =orderRepository.findAll((root, query, cb) -> {
List<Predicate> predicateList = new ArrayList<>();
Predicate[] predicates;
//判断orderStatus的状态,状态放到查询条件中
2. orderStatus有三种状态,对应三种value,查询待支付,已支付,废弃中的任意一种或者几种,我们只需要将选中的参数传给后台,并用“,”隔开.例如:orderStatus="2,3,4",后台解析将参数封装成条件,用or连接即可
if(StringUtils.isNotBlank(orderCondition.getOrderStatus())){
String [] array = orderCondition.getOrderStatus().trim().split(",");
Predicate[] p=new Predicate[array.length];
for(int i=0;i<array.length;i++){
p[i] = cb.equal(root.get("orderStatus"), array[i]);
}
predicateList.add(cb.or(p));
predicates = new Predicate[predicateList.size()];
}else{
predicates = new Predicate[0];
}
//前台传入的actualConsume的值为1(0-50),2(50-100),3(100以上)
//判断actualConsume的值
if(StringUtils.isNotBlank(orderCondition.getActualConsume())){
String [] array = orderCondition.getActualConsume().trim().split(",");
Predicate[] p = new Predicate[array.length];
for(int i=0;i<array.length;i++){
if(Integer.parseInt(array[i])==3){
//消费金额100到100以上
p[i] = cb.greaterThanOrEqualTo(root.get("actualConsume"),100);
}else if(Integer.parseInt(array[i])==2){
p[i] = cb.between(root.get("actualConsume"),50,100);
}else{
p[i] = cb.between(root.get("actualConsume"),0,50);
}
}
predicateList.add(cb.or(p));
predicates = new Predicate[predicateList.size()];
}else{
predicates = new Predicate[0];
}
//筛选出符合时间条件的订单
predicateList.add(cb.between(root.get("startTime"), startDate, endDate));
return cb.and(predicateList.toArray(predicates));
},pageAble);
return page;
}
这是springdata的单表分页组合查询,多表查询会后续更新,优秀程序员之路任重道远,慢慢积累,总会有所顿悟.