Spring Data JPA : 查-条件化查询Specification
条件化查询 用查询条件创建Specification对象 参考Spring Data JPA Specification查询
使用Criteria查询 Criteria查询是面向对象查询, root就是一个对象,root.get("name")就是name属性。可以级联获取属性
每一个查询条件创建一个Specification对象,如果有多个查询条件,就把多个Specification对象and或or成最后一个总的Specification对象。
有实体类Person如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import lombok.Data; @Data @Entity @Table(name = "tb_person_info") public class Person { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; @Column private String firstName; @Column private String lastName; @Column private Integer age; @Column private String phone; @Column private String address; }
它的form-PersonRequest如下, age 接收一个数组
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.util.List; import lombok.Data; @Data public class PersonRequest { private Long id; private String firstName; private String lastName; private List<Integer> age; private String phone; private String address; }
它的form-PersonResponse如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import lombok.Data; @Data public class PersonResponse { private Long id; private String firstName; private String lastName; private Integer age; private String phone; private String address; }
Repository层如下
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; import org.springframework.stereotype.Repository; @Repository public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> { }
如果对它进行组合条件查询,如果查询条件互相都是and关系,那么很好做。如果查询条件有and 和 or 的关系会相对复杂一些
所有查询条件都是and关系, age 是in 查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.util.ArrayList; import java.util.List; import javax.persistence.criteria.CriteriaBuilder; import javax.persistence.criteria.CriteriaQuery; import javax.persistence.criteria.Predicate; import javax.persistence.criteria.Root; import javax.persistence.criteria.CriteriaBuilder.In; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.jpa.domain.Specification; import org.springframework.stereotype.Service; import org.springframework.util.StringUtils; @Service public class PersonServiceImpl implements PersonService { @Autowired private PersonRepository personRepository; @Override public List<PersonResponse> findListAllAnd2(PersonRequest request) { Specification<Person> specification = new Specification<Person>() { @Override public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { //1.调用criteriaBuilder.conjunction()方法 返回一个默认true 的 and predicate List<Predicate> predicates = new ArrayList<>(); //把所有查询条件add到这个predicate的expression if (StringUtils.hasText(request.getFirstName())) { predicates.add(criteriaBuilder.like(root.get("firstName"), "%" + request.getFirstName() + "%")); } if (StringUtils.hasText(request.getLastName())) { predicates.add(criteriaBuilder.like(root.get("lastName"), "%" + request.getLastName() + "%")); } if (request.getAge() != null && request.getAge().size() > 0) { In<Object> in = criteriaBuilder.in(root.get("age")); for (Integer age : request.getAge()) { in.value(age); } predicates.add(criteriaBuilder.and(in)); } //返回predicate return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }; List<Person> persons = personRepository.findAll(specification); List<PersonResponse> responses = new ArrayList<PersonResponse>(); for (Person person : persons) { PersonResponse response = new PersonResponse(); modelToResponse(person, response); responses.add(response); } return responses; } private void modelToResponse(Person person, PersonResponse response) { response.setId(person.getId()); response.setFirstName(person.getFirstName()); response.setLastName(person.getLastName()); response.setAddress(person.getAddress()); response.setAge(person.getAge()); response.setPhone(person.getPhone()); } }
查询条件存在and 和 or 的关系,比如 (A or B )and C
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import java.util.ArrayList; import java.util.List; import javax.persistence.criteria.CriteriaBuilder; import javax.persistence.criteria.CriteriaQuery; import javax.persistence.criteria.Predicate; import javax.persistence.criteria.Root; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.jpa.domain.Specification; import org.springframework.stereotype.Service; import org.springframework.util.StringUtils; @Service public class PersonServiceImpl implements PersonService { @Autowired private PersonRepository personRepository; @Override public List<PersonResponse> findListAndOr(PersonRequest request) { Specification<Person> specification = new Specification<Person>() { @Override public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { // A conjunction with zero conjuncts is true. Predicate predicate = criteriaBuilder.conjunction(); // or 条件 List<Predicate> orPredicates = new ArrayList<Predicate>(); if (StringUtils.hasText(request.getFirstName())) { orPredicates.add(criteriaBuilder.like(root.get("firstName"), "%" + request.getFirstName() + "%")); } if (StringUtils.hasText(request.getLastName())) { orPredicates.add(criteriaBuilder.like(root.get("lastName"), "%" + request.getLastName() + "%")); } if (orPredicates.size() > 0) { predicate.getExpressions().add(criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]))); } // and 条件 if (null != request.getPhone()) { predicate.getExpressions().add(criteriaBuilder.equal(root.get("phone"), request.getPhone())); } return predicate; } }; List<Person> persons = personRepository.findAll(specification); List<PersonResponse> responses = new ArrayList<PersonResponse>(); for (Person person : persons) { PersonResponse response = new PersonResponse(); modelToResponse(person, response); responses.add(response); } return responses; } private void modelToResponse(Person person, PersonResponse response) { response.setId(person.getId()); response.setFirstName(person.getFirstName()); response.setLastName(person.getLastName()); response.setAddress(person.getAddress()); response.setAge(person.getAge()); response.setPhone(person.getPhone()); } }
它生成的sql如下 where (person0_.first_name like ? or person0_.last_name like ?) and person0_.phone= 12 实现了 ( A or B) and C
Hibernate: select person0_.id as id1_7_, person0_.address as address2_7_, person0_.age as age3_7_, person0_.first_name as first_name4_7_, person0_.last_name as last_name5_7_, person0_.phone as phone6_7_ from tb_person_info person0_ where (person0_.first_name like ? or person0_.last_name like ?) and person0_.phone=12