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;
}
As far as I know, JPA essentially ignores the join table. The JPQL that you do would be
for the Criteria query, you should be able to do:
there's no need to worry about the middle join table.