JPA条件API和多个子查询的问题

Question

我正在努力使用JPA标准API来为我的数据结构制定一个查询。好的，我的实体如下。我有用户和组（两者共享一个公共的基类OrgEntity）。从逻辑上讲，用户当然可以是多个组中的成员。最后，我有一个表示任务的实体，它有一个潜在所有者的列表（可以是单个用户，也可以是整个组）。域模型总结在下面，是给出的，所以我不能改变它。

@Entity
@Inheritance(strategy=InheritanceType.JOINED)
abstract public class OrgEntity {
   @Id
   public String name;
   ...
}

@Entity
public class User extends OrgEntity {
   public String displayName;

   @ManyToMany(mappedBy="members")
   public List<Group> groups;   
   ...
}

@Entity
public class Group extends OrgEntity {
   @ManyToMany
   public List<User> members;
   ...
}

@Entity
public class Task {
   @Id
   public String uuid;

   @ManyToMany
   public List<OrgEntity> potentialOwners;

   ...
}

查询的起点是User的单个实例。我想知道用户是潜在所有者的所有任务（无论用户是直接包含在potentialOwners集合中还是包含在potentialOwners中的组的成员）。

SELECT DISTINCT t FROM Task AS t JOIN t.potentialOwners po 
WHERE (po IN (SELECT g FROM User u JOIN u.groups g WHERE u = :user) 
       OR po IN (SELECT u FROM User u WHERE u = :user))

谢谢

Answer 1

好了，我终于想出了怎么做。如果你对我的解决方案感兴趣，就在这里。u是用户对象，基本上是查询参数，em是EntityManager实例。

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

// specifies the result value of the query
CriteriaQuery<Task> cq = criteriaBuilder.createQuery(Task.class);        
// start with the navigation at the task entity
Root<Task> from = cq.from(Task.class);
// join the potential owner organizational entities
Join<Task,OrgEntity> potentialOwners = from.join("potentialOwners");        
// select the tasks but remove duplicates
CriteriaQuery<Task> select = cq.select(from).distinct(true);

// definition for subquery1: fetch the user instance
Subquery<User> subquery1 = cq.subquery(User.class);
// start at the User entities
Root<User> from1 = subquery1.from(User.class);
// select the whole user
subquery1.select(from1);
// based on the specified user
subquery1.where(criteriaBuilder.equal(from1, u)); 

// definition for subquery2: fetch all groups for given user
Subquery<Group> subquery2 = cq.subquery(Group.class);
// we start at the User entity
Root<User> from2 = subquery2.from(User.class);
// join to Group entities via the groups collection
Join<User, Group> groups = from2.join("groups");
// select the group entities only
subquery2.select(groups).distinct(true);
// and finally restrict to all groups of the specified user
subquery2.where(criteriaBuilder.equal(from2, u));

// order in descending order based on the unique task id
select.orderBy(criteriaBuilder.desc(from.get("uuid")));

// here we restrict to those tasks that have the potential
// owners either in the result set of subquery2 or subquery1
// additionally I've tried to filter for another restriction
// in the task (based on a like statement of the uuid)
select.where(criteriaBuilder.and(
        criteriaBuilder.or(
            criteriaBuilder.in(potentialOwners).value(subquery2), 
            criteriaBuilder.in(potentialOwners).value(subquery1)),
        criteriaBuilder.like(from.<String>get("uuid"), "1%")));


TypedQuery<Task> typedQuery = em.createQuery(select);
List<Task> resultList = typedQuery.getResultList();