Spring Data JPA 动态拼接条件的通用设计模式

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.data.jpa.domain.Specification;
import com.xxx.controller.logManage.LogSearchParamDTO;
import com.xxx.controller.trade.TradeParams;
/**
* 改进方向 1：能不能通过反射，只要---
* 相关知识请自行查阅JPA Criteria查询
// 过滤条件
// 1：过滤条件会被应用到SQL语句的FROM子句中。在criteria
// 查询中，查询条件通过Predicate或Expression实例应用到CriteriaQuery对象上。
// 2：这些条件使用 CriteriaQuery .where 方法应用到CriteriaQuery 对象上
// 3：CriteriaBuilder也作为Predicate实例的工厂，通过调用CriteriaBuilder 的条件方法（
// equal，notEqual， gt， ge，lt， le，between，like等）创建Predicate对象。
// 4：复合的Predicate 语句可以使用CriteriaBuilder的and, or andnot 方法构建。
* @author 小言
* @date 2017年11月27日
* @time 上午10:44:53
* @version ╮(╯▽╰)╭
*/
public class SpecificationBuilderForOperateLog {

    public static <T> Specification buildSpecification(Class<T> clazz,
            final LogSearchParamDTO logSearchParamDTO) {
        return new Specification<T>() {
            @Override
            public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicate = new ArrayList<Predicate>();
                Timestamp startTime = logSearchParamDTO.getStartTime();
                Timestamp endTime = logSearchParamDTO.getEndTime();
                // 时间段
                if (startTime != null && endTime != null) {
                    predicate.add(cb.between(root.<Timestamp> get("logTime"), startTime, endTime));
                }
                // 操作日志查询栏
                String searchCondition = logSearchParamDTO.getSearchCondition();
                if (searchCondition != null && !searchCondition.equals("")) {
                    predicate.add(cb.or(cb.equal(root.<String> get("operatorName"), searchCondition),
                            cb.equal(root.<String> get("operatorId"), searchCondition)));
                }
                // 操作日志用户类型
                String operatorType = logSearchParamDTO.getOperatorType();
                System.out.println("operatorType=="+operatorType);
                if (operatorType != null ){
                    predicate.add(cb.equal(root.<String> get("operatorType"), operatorType));
                }
                Predicate[] pre = new Predicate[predicate.size()];
//              System.out.println("pre=="+predicate.toArray(pre));
                query.where(predicate.toArray(pre));
                return query.getRestriction();
            }
        };
    }
}

下面是实际开发例子：

controller层

 1 @Controller
 2 @RequestMapping(value = "/operateLog")
 3 public class BgOperateLogController {
 4 
 5     @Autowired
 6     private BgOperateLogService bgOperateLogService;
 7 
 8     @ResponseBody
 9     @PostMapping("/findOperateLogByCondition")
10     public Result findOperateLogByCondition(@RequestBody LogSearchParamDTO logSearchParamDTO) {
11         System.out.println("logSearchParamDTO="+logSearchParamDTO);
12         Map<String, Object> result = new HashMap<>();
13         String start = logSearchParamDTO.getStart();
14         String end = logSearchParamDTO.getEnd();
15         if (start != null && end == null) {
16             return new Result(1001, "操作日志查询错误,时间参数缺少结束时间", result);
17         }
18         if (end != null && start == null) {
19             return new Result(1001, "操作日志查询错误,时间参数缺少开始时间", result);
20         }
21         //时间
22         long startTimeTimestamp  = 0L;
23         long endTimeTimestamp = System.currentTimeMillis();
24         if(start != null && end != null){
25             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
26             Date startTime;
27             Date endTime;
28             try {
29                 startTime = sdf.parse(start);
30                 endTime = sdf.parse(end);
31                 startTimeTimestamp = startTime.getTime();
32                 endTimeTimestamp = endTime.getTime();
33             } catch (ParseException e) {
34                 e.printStackTrace();
35                 return new Result(1001, "操作日志查询错误，转换日期出错", result);
36             }
37         }
38         String condition = logSearchParamDTO.getSearchCondition();
39         Integer pageNumber = logSearchParamDTO.getPageNumber()-1;
40         Integer pageSize = logSearchParamDTO.getPageSize() ;
41         String operatorType =logSearchParamDTO.getOperatorType(); 
42         Page<BgOperateLog> findByCondition = bgOperateLogService.findByCondition(new Timestamp(startTimeTimestamp), 
43                 new Timestamp(endTimeTimestamp),
44                 condition,operatorType, pageNumber, pageSize);
45         // 这些字段必须有，暂时没有做校验
46         List<BgOperateLog> list = findByCondition.getContent();
47         result.put("totalPages", findByCondition.getTotalPages());
48         result.put("pageNumber", pageNumber+1);
49         result.put("list", list);
50         return new Result(1002, "操作日志查询成功", result);
51     }
52 
53 }

BgOperateLogController

DTO

 1 @Data
 2 public class LogSearchParamDTO {
 3     //前端传来的时间参数
 4     private String start;
 5     private String end;
 6     private Timestamp startTime;
 7     private Timestamp endTime;
 8     private String    searchCondition;
 9     //操作日志查询参数   
10     //操作用户类型(0，消费者，1商家，2后台人员)
11     private String operatorType;
12     private Integer pageNumber;
13     private Integer pageSize;
14     //登陆日志查询条件
15     public LogSearchParamDTO(Timestamp startTime, Timestamp endTime, String searchCondition) {
16         this.startTime = startTime;
17         this.endTime = endTime;
18         this.searchCondition = searchCondition;
19     }
20     public LogSearchParamDTO() {}
21     //操作日志查询条件
22     public LogSearchParamDTO(Timestamp startTime, Timestamp endTime, String searchCondition, String operatorType) {
23         this.startTime = startTime;
24         this.endTime = endTime;
25         this.searchCondition = searchCondition;
26         this.operatorType = operatorType;
27     }
28 }

LogSearchParamDTO

service 层

 1 @Override
 2     public Page<BgOperateLog> findByCondition(Timestamp start, 
 3             Timestamp end, String condition ,String operatorType,
 4             int pageNumber, int pageSize) {
 5         Sort sort = new Sort(Sort.Direction.DESC, "logTime");
 6         Pageable pageable = new PageRequest(pageNumber, pageSize, sort);
 7         LogSearchParamDTO operateLog = new LogSearchParamDTO(start, end, condition,operatorType);
 8         Page<BgOperateLog> page = bgOperateLogDao
 9                 .findAll(SpecificationBuilderForOperateLog.buildSpecification(BgOperateLog.class,operateLog), pageable);
10         return page;
11     }

View Code

dao层

1 import java.io.Serializable;
2 import org.springframework.data.jpa.repository.JpaRepository;
3 import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
4 import org.springframework.stereotype.Repository;
5 import com.xxx.entity.BgOperateLog;
6 @Repository
7 public interface BgOperateLogDao extends JpaRepository<BgOperateLog, Serializable>,JpaSpecificationExecutor<BgOperateLog>{}

View Code

entity层

 1 @Data
 2 @Entity
 3 public class BgOperateLog implements java.io.Serializable {
 4     @Id
 5     @GeneratedValue(strategy = GenerationType.AUTO)
 6     private Integer id;
 7     private String logText;
 8     private String operatorId;
 9     private String operatorName;
10     private String operatorType;
11     private String ip;
12     @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
13     private Timestamp logTime;
14 }

View Code

转自：

https://blog.csdn.net/dgutliangxuan/article/details/78644464

https://blog.csdn.net/u011726984/article/details/72627706

参考：

https://www.cnblogs.com/vcmq/p/9484398.html

https://www.cnblogs.com/g-smile/p/9177841.html

Spring Data JPA 动态拼接条件的通用设计模式

推荐阅读