java - 基于主查询 JPA Criteria API 创建子查询

Question

我需要在 JPA 中编写与此类似的查询:

SELECT
  a.id,
  b.status
FROM
  atable a
  JOIN btable b ON a.btable_id = b.id
where
  (
    (
      b.status = 'INITIAL'
      and a.main_atable_id is null
    )
    or exists (
      SELECT
        1
      FROM
        atable a2
        JOIN btable b2 ON a2.btable_id = b2.id
      WHERE
        b2.status = 'INITIAL'
        and b2.main_atable_id = a.id
    )
  );

如您所见，atable 有一个名为 main_atable_id 的列，它创建了父子关系，其想法是存在一个主版本，其子版本是重复的。

我需要构建一个与父查询几乎相同的子查询。我会手写它，只是复制代码，但如果可能的话，我想通过重用主查询的规范来保持简单。

我的主要查询现在如下所示:

public Page<AtableDTO> findAtables(AtableSearchDTO filter, Pageable pageable) {
    Specifications<Atable> where = Specifications.where(alwaysTrue());

    if(filter.getStatus() != null) {
        where = where.and(statusEquals(filter.getStatus()));
    }

    Page<AtableDTO> resultPage = atableRepository.findAll(where, new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), Sort.Direction.DESC, "id")).map(atableMapper::toDto);
}

public Specification<Atable> alwaysTrue() {
    return (root, query, cb) -> cb.and();
}

public Specification<Atable> statusEquals(AtableStatus value) {
    return (root, query, cb) -> cb.equal(root.get("status"), value);
}

我只需要知道:

1) 是否可以重复使用相同的规范
2)如果是，你能用这个或任何其他简单的例子来演示

谢谢

Answer 1

为什么使用EXISTS子句？您的查询相当于:

SELECT
  a.id,
  b.status
FROM
  atable a
  JOIN btable b ON a.btable_id = b.id
  JOIN btable b2 ON a.btable_id = b2.id
WHERE (b.status = 'INITIAL' AND a.main_atable_id IS NULL)
OR (b2.status = 'INITIAL' AND b2.main_atable_id = a.id);

(参见 fiddle )

假设您的实体具有以下结构:

@Entity
public class ATable {

    @Id
    private Long id;

    @OneToOne
    @JoinColumn(name = "btable_id")
    private BTable b;

    @ManyToOne
    @JoinColumn(name = "atable_main_id")
    private ATable main;
}

@Entity
public class BTable {

    @Id
    private Long id;

    private Status status;

    @ManyToOne
    @JoinColumn(name = "atable_main_id")
    private ATable main;
}

您需要以下查询:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteria = cb.createQuery(Object[].class);
Root<ATable> a = criteria.from(ATable.class);
Join<ATable, BTable> b = a.join("b");
Join<ATable, BTable> b2 = a.join("b");

criteria.where(cb.or(
        cb.and(
                cb.equal(b.get("status"), cb.literal(Status.INITIAL)),
                a.get("main").isNull()),
        cb.and(
                cb.equal(b2.get("status"), cb.literal(Status.INITIAL)),
                cb.equal(b2.get("main"), a)
                )));

criteria.multiselect(a.get("id"), b.get("status"));