Eclipse Community Forums: EclipseLink » select records where elementcollection contains one of parameter list

Home » Eclipse Projects » EclipseLink » select records where elementcollection contains one of parameter list(jpql select elementcollection contain parameter list)

Show: Today's Messages :: Show Polls :: Message Navigator

select records where elementcollection contains one of parameter list [message #923864]

Wed, 26 September 2012 09:01

Rolf Gisler

Messages: 1
Registered: September 2012

Junior Member

A user may play many roles. I want to fetch all users which have at least one role out of a parameter list.

The data definition looks as follows:

@Entity
public class User implements Serializable {
    @Id
    @Column(name = "Id",
            nullable = false)
    private long id;

    @Column(name = "LoginName",
            length = 30,
            unique = true,
            nullable = false)
    private String loginName;

    @ElementCollection
    @Enumerated(EnumType.STRING)
    @CollectionTable(name = "Role",
                     joinColumns = @JoinColumn(name = "Id"),
                     uniqueConstraints = @UniqueConstraint(columnNames = {"Id", "Role"}))
    @Column(name = "Role")
    private Set<RoleType> roles = new HashSet<RoleType>();
}


public enum RoleType {
    GUEST,
    ACCOUNTING,
    ADMIN,
    OPERATOR;
}

Now I want all users which have at least one role out of a parameter list. The list may be dynamically. I tried the following code:

String sql = "SELECT DISTINCT u FROM User u JOIN u.roles AS r WHERE r IN :filteredRoles";
Query query = entityManager.createQuery(sql);
List<RoleType> filteredRoles = new ArrayList<RoleType>();
filteredRoles.add(RoleType.ADMIN);
filteredRoles.add(RoleType.ACCOUNTING);
query.setParameter("filteredRoles", filteredRoles);
List<User> result = (List<User>) query.getResultList();

and receive the following exception:

Exception [EclipseLink-6078] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: The class of the argument for the object comparison is incorrect. 
Expression: [null] 
Mapping: [org.eclipse.persistence.mappings.DirectCollectionMapping[roles]] 
Argument: [[ADMIN, ACCOUNTING]]
Query: ReadAllQuery(referenceClass=User sql="SELECT DISTINCT t0.Id FROM User t0, Role t1 WHERE ((t1.Role IN ?) AND (t1.Id = t0.Id))")

I tried several variations of the JPQL but didn't succeed.
Another question would be how this JQPL would look like with CriteriaBuilder?

Report message to a moderator