jpa 复杂查询 join/exists/动态条件拼接

JpaSpecificationExecutor是Spring Data JPA提供的一个接口，用于实现复杂查询。
它提供了一个方法：findAll(Specification<T> spec)，可以根据指定的条件查询实体对象。

使用JpaSpecificationExecutor进行复杂查询的步骤如下：

1. 定义查询条件
   可以使用Specification接口来定义查询条件，它是一个函数式接口，需要实现其中的toPredicate()方法。该方法接收两个参数：Root和CriteriaQuery，用于构建查询条件。
2. 调用JpaSpecificationExecutor的findAll()方法
   在需要进行查询的地方，注入JpaSpecificationExecutor接口，并调用其findAll(Specification<T> spec)方法，传入查询条件即可。

实体类

@Entity
@Table
public class Person {
    @Column
    private Long id;
    
    @Column
    private String name;

    @Column
    private Integer age;

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name="person_id", updatable=false, insertable=false)
    private List<Hobby> listHobby;

}

@Entity
@Table
public class Hobby {
    @Column
    private Long id;
    @Column
    private Long personId;
    @Column
    private String type;
    @Column
    private String name;
    @Column
    private Integer years;

}

DAO类继承JpaSpecificationExecutor接口

import com.popo.boot.person.domain.PersonDO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface PersonDAO extends JpaRepository<Person, Long>,JpaSpecificationExecutor<Person>{}

构造Specification对象, 将它传递给JpaSpecificationExecutor的findAll()方法

List<Person> listPerson = personDAO.findAll( (root, query, criteriaBuilder) -> {
    List<Predicate> predicates = new ArrayList<>();

    // 动态拼接条件
    if(ObjectUtil.isNotNull(age)){
        predicates.add(criteriaBuilder.equal(root.get("age"), age));
    }
    if(ObjectUtil.isNotNull(name)){
        predicates.add(criteriaBuilder.equal(root.get("name"), "%"+name+"%"));
    }
   
    // join查询
    Join<Object, Object> joinHobby = root.join("listHobby");
    Predicate hobbyYearsGt = criteriaBuilder.gt(joinHobby.get("years"), 5);
    query.where(hobbyYearsGt);
    query.distinct(true);
    predicates.add(hobbyYearsGt);

    // exists查询
    Subquery<Hobby> subQuery = query.subquery(Hobby.class);
    Root<Hobby> subRoot = subQuery.from(Hobby.class);
    Predicate relative = criteriaBuilder.and(criteriaBuilder.equal(root.get("id"), subRoot.get("personId")));
    Predicate hobbyTypeIn = criteriaBuilder.in(subRoot.get("type")).value(List.of("music", "art"));
    subQuery.select(subRoot.get("id")).where(relative);
    Predicate existsHobby = criteriaBuilder.exists(subQuery);
    predicates.add(existsHobby);
    
    return cb.and(predicates.toArray(new Predicate[0]));
});

思考: 构造join对象的查询时, 如果使用root.get("listHobbly").get("type")能行吗?
例如,

@Override
public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
    List<Predicate> predicates = new ArrayList<>();

    if (StringUtils.isNotBlank(type)) {
        predicates.add(cb.equal(root.get("listHobbly").get("type"), type));
    }
    return cb.and(predicates.toArray(new Predicate[0]));
}

思考之后, 往下滚动查看答案~

答案是不能, 如果使用root.get("listHobbly")会出现如下报错

java.lang.IllegalStateException: Illegal attempt to dereference path source [null.listHobbly] of basic type
    at org.hibernate.jpa.criteria.path.AbstractPathImpl.illegalDereference(AbstractPathImpl.java:98)
    at org.hibernate.jpa.criteria.path.AbstractPathImpl.get(AbstractPathImpl.java:191)