526互联

Spring Data JPA : 查-条件化查询Specification

发布时间 2023-10-23 15:05:59作者: dreamstar

条件化查询  用查询条件创建Specification对象 参考Spring Data JPA Specification查询

使用Criteria查询 Criteria查询是面向对象查询， root就是一个对象，root.get("name")就是name属性。可以级联获取属性

每一个查询条件创建一个Specification对象，如果有多个查询条件，就把多个Specification对象and或or成最后一个总的Specification对象。

有实体类Person如下

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;
}
View Code

它的form-PersonRequest如下， age 接收一个数组

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;
}
View Code

它的form-PersonResponse如下

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;
}
View Code

Repository层如下

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> {

}
View Code

如果对它进行组合条件查询，如果查询条件互相都是and关系，那么很好做。如果查询条件有and 和 or 的关系会相对复杂一些

所有查询条件都是and关系， age 是in 查询

 

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());
    }
}
View Code

 

查询条件存在and 和 or 的关系，比如 （A or B ）and C

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());
    }
}
View Code

它生成的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