CODEWITHSUNDEEP
javajpajpql
Zaw Than oo
Zaw Than oo

Reputation: 9935

JPQL select query for ElementCollection

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();
}

Upvotes: 15

Views: 22685

Answers (2)

Koitoer
Koitoer

Reputation: 19533

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);

Upvotes: 17

Camilo
Camilo

Reputation: 1909

@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

Upvotes: 29

Related Questions