以下列方法为例,结合公司实际业务需求,演示jpa动态拼接sql,满足业务需求
/*
FlightPlanDetailRequest 是请求体,可以把它当作一个对象
PageRequest 是分页对象,可以用JPA自带的,这里我使用的是自己封装的
flightPlanSerialList 是用来存储你要查询的flightPlanSerial集合
*/
@Autowired
private IFlightPlanDetailRepository detailRepository;
@Override
public GetFlightDetailResponse searchFlightPlanDetail(FlightPlanDetailRequest requset, PageRequest page){
if(StringUtils.isEmpty(request.getFlightPlanDetail())){
return "传递参数有误";//这里简写,实际应该用你们公司封装的错误返回对象,这里只表述jpa多条件查询相关,不多涉及业务
}
List<Integer> flightPlanSerialList = new ArrayList<>();//此处的值可以查询得出
List<FlightPlanDetail> listDetail = detailRepository.findByFlightPlanSerial(request.getFlightPlanDetail());
for(int i = 0; i < listDetail.size(); i++){
flightPlanSerialList .add(listDetail.get(i).getFlightPlanDetail());
}
Sepcification<FlightPlanDetail> spec = new Specification<FlightPlanDetail>(){
@Override
public Predicate toPredicate(Root<FlightPlanDetail> root, CriteriaQuery<?> cq, CriteriaBuilder cb){
List<Predicate> listOr = new ArrayList<>();
List<Predicate> listAnd = new ArrayList<>();
Expression<Integer> flightPlanSerial = root.<Integer>get("flightPlanSerial");
Path contactName = root.get("contactName");
Path deleteState= root.get("deleteState");
if(!StringUtils.isEmpty(request.getContactName())){
listOr.add(cb.like(contactName,"%"+request.getContactName()+"%"));
}
//这里要对集合判空,当然判断条件也可以用flightPlanSerialList.size() > 0,防止in()括号中没有值
if(!flightPlanSerialList.isEmpty()){
listAnd.add(flightPlanSerial.in(flightPlanSerialList ))
}
listAnd.add(cb.equal(deleteState,1));//此步是过滤逻辑删除
Predicate preOr = cb.or(listOr.toArray(new Predicate[listOr.size()]));//or条件
Predicate preAnd = cb.and(listAnd.toArray(new Predicate[listAnd.size()]));//and条件
return cq.where(preOr,preAnd).getRestriction();
}
};
//Page<FlightPlanDetail> pageResult = detailRepository.findAll(spec, page);//也可以不用分页
//这样含有and or以及in的动态sql就生成了,当然也可以在Repository接口中定义动态sql,不过我个人倾向于业务动态拼接,这样扩展性较好
List<FlightPlanDetail> list = detailRepository.findAll(spec);
//.....此处省略封装返回值对象,简写为list返回
return list;
}