java - 如何通过两个字段加入(CriteriaBuilder)

Question

我有一个实体

@Entity
@Table(name = "`petition`")
@Getter
@Setter
public class Petition extends Auditable {

    private static final long serialVersionUID = -3234225397035713824L;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "petition_selected_school", joinColumns = {
            @JoinColumn(name = "petition_id", nullable = false, updatable = false)},
            inverseJoinColumns = {@JoinColumn(name = "school_id",
                    nullable = false, updatable = false)})
    private List<School> selectedSchools;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "petition_basic_school", joinColumns = {
            @JoinColumn(name = "petition_id", nullable = false, updatable = false)},
            inverseJoinColumns = {@JoinColumn(name = "school_id",
                    nullable = false, updatable = false)})
    private List<School> fullBasicSchoolList;

}

我想通过 criteria buildeer 像这样构建 SQL 选择

SELECT DISTINCT
  p.id
FROM petition p JOIN petition_selected_school s ON p.id = s.petition_id
  LEFT JOIN petition_basic_school b ON p.id = b.petition_id AND s.school_id = b.school_id
WHERE b IS NULL
ORDER BY p."id" ASC

我试过这个:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Petition> criteriaQuery = builder.createQuery(Petition.class);
Root root = criteriaQuery.from(Petition.class);
criteriaQuery.distinct(true);
Join join = root.join("selectedSchools");
Join join2 = root.join("fullBasicSchoolList", JoinType.LEFT,);
Predicate p = builder.equal(join2.get("school"),join.get("school"));
finalPredicateList.add(p);
criteriaQuery = criteriaQuery.where(builder.and(finalPredicateList.toArray(new Predicate[finalPredicateList.size()])));

但是生成的sql是这样的:

SELECT DISTINCT 
  petition0_.id AS col_0_0_
FROM "petition" petition0_ INNER JOIN petition_selected_school selectedsc1_ ON petition0_.id = selectedsc1_.petition_id
  INNER JOIN "school" school2_ ON selectedsc1_.school_id = school2_.id
  LEFT OUTER JOIN petition_basic_school fullbasics3_ ON petition0_.id = fullbasics3_.petition_id
  LEFT OUTER JOIN "school" school4_ ON fullbasics3_.school_id = school4_.id
WHERE petition0_.id = 245 AND school4_.school = school2_.school

主要问题是“如何不仅仅通过一个参数加入”？

p.id = b.petition_id AND s.school_id = b.school_id

Answer 1

您使用的是什么版本的 JPA？

在 JPA 2.1 中，您可以通过两个字段进行连接，只要其中一个字段按以下方式确定关系即可:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Petition> criteriaQuery = builder.createQuery(Petition.class);
Root root = criteriaQuery.from(Petition.class);
criteriaQuery.distinct(true);
Join join = root.join("selectedSchools");
//FIRST CONDITION (BY ID)
Join join2 = root.join("fullBasicSchoolList", JoinType.LEFT,);
//NEW
join2.on([SECOND_CONDITION])
[...]