背景
查询应该考虑程序的扩展性,使用mybatis可以实现查询条件的动态sql的拼接,springboot jpa如何实现这一功能呢?
eg:
动态查询出任务名称,任务状态,任务创建方式,查询方式的用户任务。
示例:
public PageBean findEmailGradeByUserId(Integer userId, Integer page, Date beginDate, Date endDate,
Integer filterType,Integer dateType, String name, Integer status) {
PageBean pageBean=new PageBean();
// createTime表示按数据库中哪个字段排序,DESC倒序
Pageable pageable = PageRequest.of(page - 1,pageBean.getPageSize(), Sort.by(Sort.Direction.DESC, "createTime"));
Specification<EmailGradeTask> specification = new Specification<EmailGradeTask>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<EmailGradeTask> root, CriteriaQuery<?> query,
CriteriaBuilder criteriaBuilder) {
List<Predicate> lst = new ArrayList<Predicate>();
lst.add(criteriaBuilder.equal(root.get("userId"), userId));
if (name != null) {
lst.add(criteriaBuilder.like(root.get("name"), "%"+name+"%"));
}
if (status != null && status!=-2) {
if (status == -3) {
lst.add(criteriaBuilder.equal(root.get("billingStatus"), 0));
} else {
lst.add(criteriaBuilder.equal(root.get("status"), status));
}
}
if(filterType!=null) {
if (filterType==EmailGradeTask.FILTER_CREATE_TIME) {
lst.add(criteriaBuilder.greaterThan(root.get("createTime"), beginDate));
lst.add(criteriaBuilder.lessThan(root.get("createTime"), endDate));
} else if (filterType == EmailGradeTask.FILTER_FINISHED_TIME) {
lst.add(criteriaBuilder.greaterThan(root.get("finishTime"), beginDate));
lst.add(criteriaBuilder.lessThan(root.get("finishTime"), endDate));
}
}
return query.where(lst.toArray(new Predicate[] {})).getRestriction();
}
};
System.out.println(specification.toString());
// 封装分页查询条件
Page<EmailGradeTask> emailGradeTaskList = emailGradeTaskRepository.findAll(specification, pageable);
long count = emailGradeTaskRepository.count(specification);
PageBean pageBean2 = new PageBean(pageBean.getPageSize(), count);
pageBean2.setList(emailGradeTaskList.getContent());
for(EmailGradeTask emailGradeTask:emailGradeTaskList.getContent()){
System.out.println("查询结果是:"+emailGradeTask);
}
return pageBean2;
}