jpa 动态sql拼接_Spring Data JPA 动态拼接条件的通用设计模式

最新推荐文章于 2023-04-18 22:29:38 发布

吴冲若

最新推荐文章于 2023-04-18 22:29:38 发布

阅读量1k

点赞数

文章标签： jpa 动态sql拼接

本文链接：https://blog.youkuaiyun.com/weixin_32018379/article/details/113391136

版权

本文介绍了一种使用Spring Data JPA的Specification接口动态构建SQL查询条件的方法，详细展示了如何根据LogSearchParamDTO对象的参数，如时间范围、搜索条件和操作者类型，来动态拼接查询条件。通过CriteriaBuilder和Predicate实现复杂的WHERE子句，并在实际的Controller、Service和DAO层中应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 Specification buildSpecification(Class clazz,

final LogSearchParamDTO logSearchParamDTO) {

return new Specification() {

@Override

public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) {

List predicate = new ArrayList();

Timestamp startTime = logSearchParamDTO.getStartTime();

Timestamp endTime = logSearchParamDTO.getEndTime();

// 时间段

if (startTime != null && endTime != null) {

predicate.add(cb.between(root. get("logTime"), startTime, endTime));

}

// 操作日志查询栏

String searchCondition = logSearchParamDTO.getSearchCondition();

if (searchCondition != null && !searchCondition.equals("")) {

predicate.add(cb.or(cb.equal(root. get("operatorName"), searchCondition),

cb.equal(root. get("operatorId"), searchCondition)));

}

// 操作日志用户类型

String operatorType = logSearchParamDTO.getOperatorType();

System.out.println("operatorType=="+operatorType);

if (operatorType != null ){

predicate.add(cb.equal(root. 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 @Controller2 @RequestMapping(value = "/operateLog")3 public classBgOperateLogController {4

5 @Autowired6 privateBgOperateLogService bgOperateLogService;7

8 @ResponseBody9 @PostMapping("/findOperateLogByCondition")10 publicResult findOperateLogByCondition(@RequestBody LogSearchParamDTO logSearchParamDTO) {11 System.out.println("logSearchParamDTO="+logSearchParamDTO);12 Map 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 findByCondition = bgOperateLogService.findByCondition(newTimestamp(startTimeTimestamp),43 newTimestamp(endTimeTimestamp),44 condition,operatorType, pageNumber, pageSize);45 //这些字段必须有，暂时没有做校验

46 List 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 @Data2 public classLogSearchParamDTO {3 //前端传来的时间参数

4 privateString start;5 privateString end;6 privateTimestamp startTime;7 privateTimestamp endTime;8 privateString searchCondition;9 //操作日志查询参数10 //操作用户类型(0，消费者，1商家，2后台人员)

11 privateString operatorType;12 privateInteger pageNumber;13 privateInteger pageSize;14 //登陆日志查询条件

15 publicLogSearchParamDTO(Timestamp startTime, Timestamp endTime, String searchCondition) {16 this.startTime =startTime;17 this.endTime =endTime;18 this.searchCondition =searchCondition;19 }20 publicLogSearchParamDTO() {}21 //操作日志查询条件

22 publicLogSearchParamDTO(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 @Override2 public PagefindByCondition(Timestamp start,3 Timestamp end, String condition ,String operatorType,4 int pageNumber, intpageSize) {5 Sort sort = new Sort(Sort.Direction.DESC, "logTime");6 Pageable pageable = newPageRequest(pageNumber, pageSize, sort);7 LogSearchParamDTO operateLog = newLogSearchParamDTO(start, end, condition,operatorType);8 Page page =bgOperateLogDao9 .findAll(SpecificationBuilderForOperateLog.buildSpecification(BgOperateLog.class,operateLog), pageable);10 returnpage;11 }

View Code

dao层

1 importjava.io.Serializable;2 importorg.springframework.data.jpa.repository.JpaRepository;3 importorg.springframework.data.jpa.repository.JpaSpecificationExecutor;4 importorg.springframework.stereotype.Repository;5 importcom.xxx.entity.BgOperateLog;6 @Repository7 public interface BgOperateLogDao extends JpaRepository,JpaSpecificationExecutor{}

View Code

entity层

1 @Data2 @Entity3 public class BgOperateLog implementsjava.io.Serializable {4 @Id5 @GeneratedValue(strategy =GenerationType.AUTO)6 privateInteger id;7 privateString logText;8 privateString operatorId;9 privateString operatorName;10 privateString operatorType;11 privateString ip;12 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")13 privateTimestamp logTime;14 }

View Code

转自：

https://blog.youkuaiyun.com/dgutliangxuan/article/details/78644464

https://blog.youkuaiyun.com/u011726984/article/details/72627706

参考：

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

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