JPQL select query for ElementCollection

Question

I have the following entity with enum collection. I would like to search the user with an enum parameter.
The user might have multiple permission. When I search the users with the parameter like Permission.APPROVE, there may be one or more user who have that permission. How can I write JPQL query?

User.java

@Entity
....
public class User implements Serializable {

    @ElementCollection(targetClass = Permission.class)
    @Enumerated(EnumType.STRING)
    @CollectionTable(name = "USER_PERMISSION", joinColumns = @JoinColumn(name = "PERMISSION", referencedColumnName = "ID")) 
    private List<Permission> permisssionList;
}

Permission.java

public enum Permission {
    REGISTER, APPROVE, REJECT, CONFIRM;
}

How to write ?

public List<User> findUserList(Permission permission) { 
    Query q = em.createQuery(.....);
    result = q.getResultList();
}

score 29 · Answer 1 · answered Feb 21 '14 at 04:53

@Embeddable and @CollectionTable are just a simple way to map One To Many relationships, they can not be queried directly nor persisted, but certainly can be joined, do it like you would with any One To Many relationship:

"select user from User user join user.permissionList p where p = :permission"

and pass the enum as a regular parameter

score 16 · Accepted Answer · edited Mar 29 '16 at 15:13

From spec

Enum literals support the use of Java enum literal syntax. The fully qualified enum class name must be specified.

select u from User u where u.Permission = XX.XX.Permission.APPROVE

Try this one.

String jpql = 
    "select u from User u join u.permission p"
    + " where p = :enumeration";
Query query = em.createQuery(jpql);
query.setParameter("enumeration", XX.XX.Permission.APPROVE);

JPQL select query for ElementCollection

2 Answers2