Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » select records where elementcollection contains one of parameter list(jpql select elementcollection contain parameter list)
select records where elementcollection contains one of parameter list [message #923864] Wed, 26 September 2012 09:01 Go to next message
Rolf Gisler is currently offline Rolf GislerFriend
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?
Re: select records where elementcollection contains one of parameter list [message #930653 is a reply to message #923864] Tue, 02 October 2012 14:11 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I think this was fixed in 2.4, can you try the latest release?


James : Wiki : Book : Blog : Twitter
Previous Topic:Eclipselink mongoDB native query find() not working
Next Topic:JTA
Goto Forum:
  


Current Time: Sun Nov 23 09:02:41 GMT 2014

Powered by FUDForum. Page generated in 0.11347 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software