JPA中继承BaseRepo之后,可以使用最基本的增删改查,如果想实现复杂查询,则需要借助Specification来完成这个功能:下面就简单介绍一下Specification的使用
public void findAll(ConstructPlanPageReqEntity constructPlanPageReqEntity) {
Integer pageNum = page.getPageNum();
Integer pageSize = page.getPageSize();
String costType = constructPlanPageReqEntity.getCostType();
String name = constructPlanPageReqEntity.getName();
String planMoneyStart = constructPlanPageReqEntity.getPlanMoneyStart();
String planMoneyEnd = constructPlanPageReqEntity.getPlanMoneyEnd();
String singMoneyEnd = constructPlanPageReqEntity.getSingMoneyEnd();
String signMoneyStart = constructPlanPageReqEntity.getSignMoneyStart();
long projectId = Long.parseLong(constructPlanPageReqEntity.getProjectId());
String status = constructPlanPageReqEntity.getStatus();
//分页
pageNum=pageNum-1;
Pageable pageable = PageRequest.of(pageNum, pageSize);
//多条件匹配查询
Specification specification= new Specification<ContractPlanBean>() {
@Override
public Predicate toPredicate(Root<ContractPlanBean> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
ArrayList<Predicate> list = new ArrayList<>();
Path<String> costType1 = root.get("costType");
Path<String> name1 = root.get("name");
Path<Long> projectId1 = root.get("projectId");
Path<Object> status1 = root.get("status");
if (projectId>0){
list.add(criteriaBuilder.equal(projectId1,projectId));
}
if (StringUtil.isNotEmpty(status)){
list.add(criteriaBuilder.equal(status1,status));
}
//条件查询
if (StringUtil.isNotEmpty(costType)){
list.add(criteriaBuilder.equal(costType1,costType));
}
//模糊查询
if (StringUtil.isNotEmpty(name)){
list.add(criteriaBuilder.like(name1,"%"+name+"%"));
}
//范围查询
if (StringUtil.isNotEmpty(planMoneyStart)&&StringUtil.isNotEmpty(planMoneyEnd)){
try {
list.add(criteriaBuilder.between(root.get("planMoney"),NumberUtil.strToDouble(planMoneyStart),NumberUtil.strToDouble(planMoneyEnd)));
} catch (Exception e) {
throw new ApiException("规划金额查询失败");
}
}
//排序
criteriaQuery.orderBy(criteriaBuilder.asc(root.get("name")));
Predicate[] array = new Predicate[list.size()];
return criteriaBuilder.and(list.toArray(array));
}
};
}
以上代码实现了多条件查询,其中需要重写toPredicate方法,具体参数:
用root.get()获取bean中的数据库对应字段
用criteriaBuilder来组建条件查询语句
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/28370cdfe75dae97657fea5115044404.png)
上图是criteriaBuilder各种sql符号的方法名,根据需求组建不同的sql语句
criteriaBuilder.and(list.toArray(array))这句是最后定义各个sql查询条件的关系,这里用的and
至此,复杂sql语句就拼接完成,本人对Specification的使用未进行深入研究,个人觉得相对filter Strem的复杂查询来说Specification更繁琐,因此更倾向于通过Strem的复杂查询,下一篇就介绍如何使用Stream进行复杂查询