JPA Criteria Subquery on JoinTable

Question

How do I create an efficient JPA Criteria query to select a list of entities only if they exist in a join table? For example take the following three tables:

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

The query in question (what I want JPA to produce) is:

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

The following Criteria query will produce a similar result, but with two joins:

    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();

The fundamental problem seems to be that I'm using the @JoinTable annotation for the USER_WORKGROUP join table instead of a separate @Entity for the join table so it doesn't seem I can use USER_WORKGROUP as a Root in a criteria query.

Here are the entity classes:

@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

As far as I know, JPA essentially ignores the join table. The JPQL that you do would be

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

for the Criteria query, you should be able to do:

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

there's no need to worry about the middle join table.