• SpringDataJpa多条件查询代码封装


     1 package com.pantech.cloud.mlogistics.util;
     2 
     3 import com.mysql.jdbc.StringUtils;
     4 import org.springframework.data.domain.PageRequest;
     5 import org.springframework.data.domain.Pageable;
     6 import org.springframework.data.domain.Sort;
     7 
     8 import java.util.Map;
     9 
    10 /**
    11  * @author xzy
    12  * @date 2019-11-25 10:15
    13  * 说明:用于生成sort、pageable
    14  */
    15 public class PageableUtils {
    16     /**
    17      * Meta = {"sortColumn":"","sortDirection":"","pageNumber","","pageSize":""}
    18      * 排序字段、排序方向、页码、分页大小
    19      */
    20     private Map<String, Object> meta = null;
    21     private String sortColumn = null;
    22     private String sortDirection = null;
    23     private Integer pageNumber = null;
    24     private Integer pageSize = null;
    25 
    26     public Map<String, Object> getMeta() {
    27         return meta;
    28     }
    29 
    30     public void setMeta(Map<String, Object> meta) {
    31         this.meta = meta;
    32         //排序字段和排序方向必须同时指定,且值不能为null
    33         if (meta.containsKey("sortColumn") && meta.get("sortColumn") != null
    34                 && meta.containsKey("sortDirection") && meta.get("sortDirection") != null) {
    35             sortColumn = String.valueOf(meta.get("sortColumn"));
    36             sortDirection = String.valueOf(meta.get("sortDirection"));
    37         }
    38         if (meta.containsKey("pageNumber") && !"".equals(meta.get("pageNumber"))
    39                 && meta.containsKey("pageSize") && !"".equals(meta.get("pageSize"))) {
    40             pageNumber = Integer.parseInt(meta.get("pageNumber").toString());
    41             pageSize = Integer.parseInt(meta.get("pageSize").toString());
    42         }
    43     }
    44 
    45     public PageableUtils(Map<String, Object> meta) {
    46         setMeta(meta);
    47     }
    48 
    49     /**
    50      * 获取排序条件
    51      */
    52     public Sort getSort() {
    53         Sort sort = null;
    54         //确保排序字段有效,确保排序方向有效
    55         if (StringUtils.isNullOrEmpty(sortColumn)) {
    56             return null;
    57         } else if ("ASC".equals(sortDirection) || "asc".equals(sortDirection)) {
    58             sort = new Sort(Sort.Direction.ASC, sortColumn);
    59         } else if ("DESC".equals(sortDirection) || "desc".equals(sortDirection)) {
    60             sort = new Sort(Sort.Direction.DESC, sortColumn);
    61         }
    62         return sort;
    63     }
    64 
    65     /**
    66      * 获取分页条件
    67      *
    68      * @param sort - 排序条件
    69      */
    70     public Pageable getPageable(Sort sort) {
    71         Pageable pageable;
    72         if (pageNumber == null || pageSize == null) {
    73             pageable = null;
    74         } else if (sort != null) {
    75             pageable = PageRequest.of(pageNumber, pageSize, sort);
    76         } else {
    77             pageable = PageRequest.of(pageNumber, pageSize);
    78         }
    79         return pageable;
    80     }
    81 
    82     public Pageable getPageable() {
    83         Sort sort = getSort();
    84         return getPageable(sort);
    85     }
    86 }

    PageableUtils.java工具类负责从参数中提取排序、分页条件

     1 package com.pantech.cloud.mlogistics.util;
     2 
     3 import com.pantech.cloud.common.msg.Message;
     4 import com.pantech.cloud.common.msg.MessageBox;
     5 import org.springframework.data.domain.Pageable;
     6 import org.springframework.data.domain.Sort;
     7 import org.springframework.data.jpa.domain.Specification;
     8 import org.springframework.data.jpa.repository.JpaRepository;
     9 import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    10 import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
    11 import org.springframework.data.repository.Repository;
    12 
    13 import java.util.Map;
    14 
    15 /**
    16  * @author xzy
    17  * @date 2019-11-25 10:26
    18  * 说明:条件查询
    19  */
    20 public class ConditionalQueryUtils<T extends JpaRepository & JpaSpecificationExecutor> {
    21 
    22     private T repository = null;
    23 
    24     public T getRepository() {
    25         return repository;
    26     }
    27 
    28     public void setRepository(T repository) {
    29         this.repository = repository;
    30     }
    31 
    32     public ConditionalQueryUtils(T repository) {
    33         this.repository = repository;
    34     }
    35 
    36     /**
    37      * 条件查询:多条件查询||排序查询||分页查询
    38      *
    39      * @param specification - 多条件查询条件
    40      * @param meta          - 包含条件查询、分页查询条件
    41      */
    42     public Message findByConditions(Specification specification, Map<String, Object> meta) {
    43         PageableUtils pageableUtils = new PageableUtils(meta);
    44         //排序条件
    45         Sort sort = pageableUtils.getSort();
    46         //分页条件
    47         Pageable pageable = pageableUtils.getPageable();
    48         /**
    49          * 查询
    50          */
    51         if (specification != null) {
    52             //1、多条件查询
    53             if (pageable != null) {
    54                 //1.1多条件查询+排序查询+分页查询
    55                 return new MessageBox<>(repository.findAll(specification, pageable));
    56             } else {
    57                 if (sort != null) {
    58                     //1.2.1多条件查询+排序查询
    59                     return new MessageBox<>(repository.findAll(specification, sort));
    60                 } else {
    61                     //1.2.2多条件查询
    62                     return new MessageBox<>(repository.findAll(specification));
    63                 }
    64             }
    65         } else {
    66             //2、查询所有数据
    67             if (pageable != null) {
    68                 //2.1查询所有数据+排序查询+分页查询
    69                 return new MessageBox<>(repository.findAll(pageable));
    70             } else {
    71                 if (sort != null) {
    72                     //2.2.1查询所有数据+排序查询
    73                     return new MessageBox<>(sort);
    74                 } else {
    75                     //2.2.2查询所有数据
    76                     return new MessageBox<>(repository.findAll());
    77                 }
    78             }
    79         }
    80     }
    81 }

    使用说明:

    1、Entity中包含参数

    1     /**
    2      * 附加参数
    3      */
    4     @Transient
    5     @JsonInclude(JsonInclude.Include.NON_DEFAULT)
    6     private Map<String, Object> meta = new HashMap<>();

    2、Repository实现接口

     1 package com.pantech.cloud.mlogistics.repository.ina;
     2 
     3 import com.pantech.cloud.mlogistics.entity.ina.WaybillReturnViewEntity;
     4 import org.springframework.data.jpa.repository.JpaRepository;
     5 import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
     6 
     7 /**
     8  * @author xzy
     9  * @date 2019-11-25 09:43
    10  * 说明:
    11  */
    12 public interface WaybillReturnViewRepository extends JpaRepository<WaybillReturnViewEntity, String>, JpaSpecificationExecutor<WaybillReturnViewEntity> {
    13 }

    使用例子:

      1 package com.pantech.cloud.mlogistics.service.impl.ina;
      2 
      3 import com.mysql.jdbc.StringUtils;
      4 import com.pantech.cloud.common.msg.Message;
      5 import com.pantech.cloud.mlogistics.entity.ina.WaybillReturnViewEntity;
      6 import com.pantech.cloud.mlogistics.repository.ina.WaybillReturnViewRepository;
      7 import com.pantech.cloud.mlogistics.service.ina.WaybillReturnViewService;
      8 import com.pantech.cloud.mlogistics.util.ConditionalQueryUtils;
      9 import com.pantech.cloud.mlogistics.util.SpecificationFactory;
     10 import org.springframework.beans.factory.annotation.Autowired;
     11 import org.springframework.data.jpa.domain.Specification;
     12 import org.springframework.stereotype.Service;
     13 
     14 import java.text.ParseException;
     15 import java.text.SimpleDateFormat;
     16 import java.util.ArrayList;
     17 import java.util.Date;
     18 import java.util.List;
     19 
     20 /**
     21  * @author xzy
     22  * @date 2019-11-25 09:46
     23  * 说明:
     24  */
     25 @Service
     26 public class WaybillReturnViewServiceImpl implements WaybillReturnViewService {
     27 
     28     private WaybillReturnViewRepository repository;
     29 
     30     @Autowired
     31     public void dependenceInject(WaybillReturnViewRepository repository) {
     32         this.repository = repository;
     33     }
     34 
     35     /**
     36      * 获取所有数据
     37      */
     38     @Override
     39     public List<WaybillReturnViewEntity> findAll() {
     40         return repository.findAll();
     41     }
     42 
     43     /**
     44      * 多条件查询
     45      *
     46      * @param conditions - 查询条件
     47      * @return - 返回符合条件的数据
     48      */
     49     @Override
     50     public Message findByConditions(WaybillReturnViewEntity conditions) {
     51         Specification specification = getConditions(conditions);
     52         ConditionalQueryUtils<WaybillReturnViewRepository> conditionUtils = new ConditionalQueryUtils<>(repository);
     53         return conditionUtils.findByConditions(specification, conditions.getMeta());
     54     }
     55 
     56     /**
     57      * 确认查询条件
     58      *
     59      * @param conditions - 包含查询条件的实体
     60      * @return - 查询条件
     61      */
     62     private Specification getConditions(WaybillReturnViewEntity conditions) {
     63         List<Specification> specificationList = new ArrayList<>();
     64         Specification specification = null;
     65 
     66         //主单号
     67         if (!StringUtils.isNullOrEmpty(conditions.getMainBill())) {
     68             specificationList.add(SpecificationFactory.equal("mainBill", conditions.getMainBill()));
     69         }
     70         //分单号
     71         if (!StringUtils.isNullOrEmpty(conditions.getHawbBill())) {
     72             specificationList.add(SpecificationFactory.equal("hawbBill", conditions.getHawbBill()));
     73         }
     74         //退货客户
     75         if (!StringUtils.isNullOrEmpty(conditions.getCustomerName())) {
     76             specificationList.add(SpecificationFactory.equal("customerName", conditions.getCustomerName()));
     77         }
     78         if (conditions.getMeta().containsKey("returnDateLeft") && !"".equals(conditions.getMeta().get("returnDateLeft").toString())) {
     79             Date returnDateLeft = null;
     80             try {
     81                 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     82                 returnDateLeft = dateFormat.parse(conditions.getMeta().get("returnDateLeft").toString());
     83             } catch (ParseException e) {
     84                 e.printStackTrace();
     85             }
     86             specificationList.add(SpecificationFactory.greaterOrEqual("returnDate", (Date) returnDateLeft));
     87         }
     88         if (conditions.getMeta().containsKey("returnDateRight") && !"".equals(conditions.getMeta().get("returnDateRight").toString())) {
     89             Date returnDateRight = null;
     90             try {
     91                 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     92                 returnDateRight = dateFormat.parse(conditions.getMeta().get("returnDateRight").toString());
     93             } catch (ParseException e) {
     94                 e.printStackTrace();
     95             }
     96             specificationList.add(SpecificationFactory.smallerOrEqual("returnDate", returnDateRight));
     97         }
     98 
     99         for (Specification spec : specificationList) {
    100             if (specification == null) {
    101                 specification = spec;
    102             } else {
    103                 specification = specification.and(spec);
    104             }
    105         }
    106         return specification;
    107     }
    108 }

    请求参数:

  • 相关阅读:
    delegate和event的区别 (zz)
    delegate和event的区别 (zz)
    delegate和event的区别 (zz)
    delegate和event的区别 (zz)
    以太坊网络服务分析
    以太坊:P2P网络数据处理流程
    以太坊:P2P网络数据交互
    以太坊虚拟机的基本介绍
    Solidity概述及基本代码展示
    Solidity编译器和简单调试
  • 原文地址:https://www.cnblogs.com/XiaoZhengYu/p/11927618.html
一二三 - 开发者的网上家园