JoinTable 上的 JPA 标准子查询

Question

如何创建高效的 JPA Criteria 查询以仅在连接表中存在实体时选择实体列表？以下面三个表为例:

create table user (user_id int, lastname varchar(64));
create table workgroup (workgroup_id int, name varchar(64));
create table user_workgroup (user_id int, workgroup_id int); -- Join Table

有问题的查询(我希望 JPA 生成什么)是:

select * from user where user_id in (select user_id from user_workgroup where workgroup_id = ?);

以下 Criteria 查询将产生类似的结果，但有两个连接:

    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> root = cq.from(User.class);
    cq.select(root);

    Subquery<Long> subquery = cq.subquery(Long.class);
    Root<User> subroot = subquery.from(User.class);
    subquery.select(subroot.<Long>get("userId"));
    Join<User, Workgroup> workgroupList = subroot.join("workgroupList");
    subquery.where(cb.equal(workgroupList.get("workgroupId"), ?));
    cq.where(cb.in(root.get("userId")).value(subquery));

    getEntityManager().createQuery(cq).getResultList();

基本问题似乎是我对 USER_WORKGROUP 连接表使用了 @JoinTable 注释，而不是对连接表使用单独的 @Entity，所以我似乎不能在条件查询中使用 USER_WORKGROUP 作为根。

以下是实体类:

@Entity
public class User {
  @Id
  @Column(name = "USER_ID")
  private Long userId;
  @Column(name = "LASTNAME")
  private String lastname;
  @ManyToMany(mappedBy = "userList")
  private List<Workgroup> workgroupList;
}

@Entity
public class Workgroup {
  @Id
  @Column(name = "WORKGROUP_ID")
  private Long workgroupId;
  @Column(name = "NAME")
  private String name;
  @JoinTable(name = "USER_WORKGROUP", joinColumns = {
        @JoinColumn(name = "WORKGROUP_ID", referencedColumnName = "WORKGROUP_ID", nullable = false)}, inverseJoinColumns = {
        @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID", nullable = false)})
  @ManyToMany
  private List<User> userList;
}

Answer 1

据我所知，JPA 本质上忽略了连接表。您所做的 JPQL 将是

select distinct u from user u join u.workgroupList wg where wg.name = :wgName

对于 Criteria 查询，您应该能够执行以下操作:

Criteria c = session.createCriteria(User.class, "u");
c.createAlias("u.workgroupList", "wg");
c.add(Restrictions.eq("wg.name", groupName));
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

无需担心中间连接表。