记录一下复杂的 spring data jpa 包括外连接 不区分大小写模糊查询

Pageable pageable = PageRequest.of(page, size, sort);
		Page<CXCustomStudies> result;
		if(searchJson!=null){
			result=allTrailsRepository.findAll((root,query,cb)->{
				List<Predicate> predicates = new ArrayList<>();
				boolean ageGroupFlag = searchJson.isAdult()||searchJson.isChild()||searchJson.isOlder_adult();
				if(!StringUtils.isEmpty(searchJson.getStatus())){
					List<String> status = Arrays.asList(searchJson.getStatus().split("\\^\\^"));
					Predicate p1 = root.get("status").in(status);
					predicates.add(p1);
				}
				if(!StringUtils.isEmpty(searchJson.getSearch_value())){
					query.distinct(true);
					Join<CXCustomStudies, Conditions> conditionss = root.join("conditionss", JoinType.LEFT);
					conditionss.on(cb.equal(root.get("nctId"),conditionss.get("nctId")));
					Join<CXCustomStudies, CXCustomeInterventions> cxCustomeInterventionss = root.join("cxCustomeInterventionss", JoinType.LEFT);
					cxCustomeInterventionss.on(cb.equal(root.get("nctId"),cxCustomeInterventionss.get("nctId")));
					Predicate p1=cb.like(cb.lower(root.get("nctId")),"%"+searchJson.getSearch_value().toLowerCase()+"%");
					Predicate p2=cb.like(conditionss.get("name"),"%"+searchJson.getSearch_value().toLowerCase()+"%");
					Predicate p3=cb.like(cxCustomeInterventionss.get("intervention"),"%"+searchJson.getSearch_value().toLowerCase()+"%");
					Predicate p4=cb.or(p1,p2,p3);
					predicates.add(p4);
				}
				if(!StringUtils.isEmpty(searchJson.getGender())&&!"All".equalsIgnoreCase(searchJson.getGender())){
					Predicate p1=cb.equal(root.get("gender"),searchJson.getGender());
					predicates.add(p1);
				}
				if(searchJson.getAge()!=null){
					Predicate p1=cb.ge(root.get("transMaxage").as(Double.class),searchJson.getAge());
					Predicate p2=cb.le(root.get("transMinage").as(Double.class),searchJson.getAge());
					Predicate p3=cb.and(p1,p2);
					predicates.add(p3);
				}
				if(ageGroupFlag){
					Predicate p1 = cb.equal(root.get("childGroup"),searchJson.isAdult());
					Predicate p2 = cb.equal(root.get("aultGroup"),searchJson.isChild());
					Predicate p3 = cb.equal(root.get("olderGroup"),searchJson.isOlder_adult());
					if(searchJson.isOlder_adult()&&searchJson.isChild()&&searchJson.isAdult()){
						Predicate p4 = cb.or(p1,p2,p3);
						predicates.add(p4);
					}else if(searchJson.isOlder_adult()&&searchJson.isChild()&&!searchJson.isAdult()){
						Predicate p4 = cb.or(p3,p2);
						predicates.add(p4);
					}else if(searchJson.isOlder_adult()&&!searchJson.isChild()&&!searchJson.isAdult()){
						predicates.add(p3);
					}else if(!searchJson.isOlder_adult()&&searchJson.isChild()&&searchJson.isAdult()){
						Predicate p4 = cb.or(p2,p1);
						predicates.add(p4);
					}else if(!searchJson.isOlder_adult()&&searchJson.isChild()&&!searchJson.isAdult()){
						predicates.add(p2);
					}else if(!searchJson.isOlder_adult()&&!searchJson.isChild()&&searchJson.isAdult()){
						predicates.add(p1);
					}else if(searchJson.isOlder_adult()&&!searchJson.isChild()&&searchJson.isAdult()){
						Predicate p4 = cb.or(p3,p1);
						predicates.add(p4);
					}
				}
				return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
			},pageable);
		}else{
			result = allTrailsRepository.findAll(pageable);
		}