Criteria API not in @ElementCollection

Question

With Permission being an enumeration, is it possible to select all users without a certain permission using JPA Criteria API:

@Entity
public class User {

    @ElementCollection
    @CollectionTable(joinColumns = @JoinColumn(name = "USER_ID"))
    private Set<Permission> permissions = new HashSet<>();

}

My current approach (returns nothing):

// em => EntityManager
// cb => CrieriaBuilder
Root<User> root = query.from(User.class);
query.select(root);
SetJoin<User, Permission> join = root.joinSet("permissions");
query.where(cb.not(cb.equal(join, Permission.RULE_THEM_ALL)));
List<User> result = em.createQuery(query).getResultList();

JPQL equivalent is `SELECT u FROM User u WHERE :myPerm IN u.permissions` You cannot do "Set == elementValue" but you can use IN — , Sep 15 '17 at 14:10

score 0 · Accepted Answer · answered Sep 18 '17 at 10:55

I ended up using a subquery to select all users with the specific permission and exclude them from the result set.

CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> root = query.from(User.class);
query.select(root);

Subquery<Long> subquery = query.subquery(Long.class);
Root<User> subroot = subquery.from(User.class);
SetJoin<User, Permission> join = subroot.join("permissions");
subquery.select(subroot.get("id")).where(cb.equal(join, Permission.RULE_THEM_ALL));

query.where(root.get("id").in(subquery).not());

Criteria API not in @ElementCollection

1 Answers1

Linked