JoinTable上的JPA条件子查询

Question

如何创建高效的 JPA 条件查询，以便仅当实体存在于联接表中时才选择实体列表？例如，采用以下三个表：

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 = ?);

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

    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_WORGGroup用作根。

以下是实体类:

@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 c = session.createCriteria(User.class, "u");
c.createAlias("u.workgroupList", "wg");
c.add(Restrictions.eq("wg.name", groupName));
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

不必担心中间的联接表。