JPA多条件查询这种业务场景是很常见的,比如说这种:
像这种同一个查询条件可以多选的用OR语句来查询,比如"材质"之间选了"PU"和"橡胶"就用OR;不同查询条件之间则用AND语句查询,比如"品牌"和"材质"之间就用AND拼接。我现在要根据不同的条件查询某个学校的学生:
实体类:
@Data@Entity@Table(name = "test_module")public class TestModule implements Serializable { private static final long serialVersionUID = 3584841637829830997L; /** * */ @Id @Column(name = "ID") @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; /** * 姓名 */ @Column(name = "NMAE") private String name; /** * 爱好 */ @Column(name = "HOBBIE") private String hobbie; /** * 年龄 */ @Column(name = "AGE") private Integer age; /** * 出生地 */ @Column(name = "BIRTH") private String brirth;}
Repository接口:
public interface TestModuleRepository extends JpaRepository, JpaSpecificationExecutor {}
多条件查询实现:
@Override public Page findVenueList(Pageable pageable,String name,String [] hobbies,Integer age, String brirth) { Specification specification=new Specification() { @Override public Predicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder criteriaBuilder) { List listAnd=new ArrayList<>(); //组装and语句 if(!StringUtils.isBlank(name)) { listAnd.add(criteriaBuilder.like(root.get("name"), "%" + name)); //姓名 模糊查询 } if(age!=null) { listAnd.add(criteriaBuilder.equal(root.get("age"), age)); //年龄 } if(!StringUtils.isBlank(brirth)) { listAnd.add(criteriaBuilder.like(root.get("brirth"), "%" + brirth + "%")); //出生地 模糊查询 } Predicate predicateAnd = criteriaBuilder.and(listAnd.toArray(new Predicate[listAnd.size()])); //AND查询加入查询条件 List listOr = new ArrayList<>();///组装or语句 if(hobbies!=null && hobbies.length>0) { for (String hoobbie : hobbies) { //爱好多选 用OR链接 listOr.add(criteriaBuilder.equal(root.get("hobbie"), hoobbie)); } } Predicate predicateOR = criteriaBuilder.or(listOr.toArray(new Predicate[listOr.size()])); //OR查询加入查询条件 return criteriaQuery.where(predicateAnd,predicateOR).getRestriction(); } }; Page page=testModuleRepository.findAll(specification,pageable); return page; }