How do I write a JPA query that checks if a collection has at least one item in another collection?

Question

I'm using JPA 2.0, Hibernate 4.1.0.Final, and Spring 3.1.1.RELEASE. I have this entity with this field ...

@Entity
@Table(name = "user")
public class User
{
    …
    @ManyToMany
    @JoinTable(name = "user_role", joinColumns = { @JoinColumn(name = "USER_ID") }, inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") })
    @LazyCollection(LazyCollectionOption.FALSE)
    private Set<Role> roles = new HashSet<Role>();

I want to write a method

List<User> findUsers(List<Role> roles);

that will return users in which a user has at least one of the roles in the parameter "roles." How do I do that in JPA? I know I can write

    final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
    final CriteriaQuery<User> criteria = builder.createQuery(User.class);
    final Root<User> user = criteria.from(User.class);
…
    criteria.where(user.get("role").in(roles)));
    final TypedQuery<User> query = m_entityManager.createQuery(criteria);

if the user has a single role, but in this case the user has a set, so I don't know how to adjust the above.

LazyCollection is a Hibernate annotation, not a JPA's annotation, therefore I would avoid to use it if the requirement is to write a JPA's CriteriaBuilder query — perissf, Jun 24 '13 at 19:37

score 3 · Accepted Answer · edited May 23 '17 at 12:05

According to this answer, you can use Expression#in() method in this way, provided that you use a basic property as argument to the in expression:

Join<User, Role> rolesJoin = user.join("roles");
List<Integer> rolesIds = new ArrayList();
for (Role role : roles) {
    rolesIds.add(role.getId());
}
criteria.where(rolesJoin.get("id").in(rolesIds));

Hopefully there are also other ways to make it work, but I haven't found any other, yet.

How do I write a JPA query that checks if a collection has at least one item in another collection?

1 Answers1