JPA and JPQL MEMBER OF queries always returning empty results

Question

I am having problems with JPA2/Hibernate 4.2/MySQL 5.1/Java 6

I have the following class, User, which has an attribute, Set<Role>, where Role is an enum. Here is the class:

@Entity
public class User
{
  private static final long serialVersionUID = 1L;

  @ElementCollection(fetch=FetchType.EAGER)
  @Enumerated(value=EnumType.STRING)
  protected Set<Role> roles;

  // .. other stuff
}

public enum Role { ADMIN, OPERATOR, ... }

I have tried creating both a CriteriaQuery and a JPQL query for this but for some reason both are returning an empty list each time, even if the role is present.

Here is my code for the criteria query:

CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
Root<User> root = query.from(User.class);

query.where(builder.isMember( Role.ADMIN, root.<Set<Role>>get( "roles" ) ) );

List<User> result = getEntityManager().createQuery(query).getResultList();

Here is the JPQL I am creating:

String jpql = "SELECT * FROM com.mypackage.user.User WHERE (:roles1 MEMBER OF roles))";
Query query = em.createQuery(jpql);
query.setParameter( "roles1", Role.ADMIN );
List result = em.getResultList();

The users table contains a single user with all roles, but neither of these are returning any results. I need to have both the JPA and JPQL versions for the work I am doing. Please help!

What's the generated SQL? What happens when you execute this generated SQL directly? — JB Nizet, Aug 21 '13 at 14:23
I have altered the jpql so it does a count for the moment - The hibernate native SQL gives: `select count(*) as col_0_0_ from User user0_ where ? in (select roles1_.roles from User_roles roles1_ where user0_.id=roles1_.User_id)` - If I change the ? to 'ADMIN' it works. — fancyplants, Aug 21 '13 at 14:42
The SQL looks correct. Try to see which value is actually bound to the statement, execute the query by yourself on the database, check your data, and check that you're connecting to the correct database. — JB Nizet, Aug 21 '13 at 14:46
Not sure whether thats the right way round though - I would have expected something like `select count(*) as col_0_0_ from User user0_ where user0_.id in (select roles1_.User_id from User_roles roles1_ where roles1_.roles = 'ADMIN')` — fancyplants, Aug 21 '13 at 14:50
Hi JB Nizet - if I alter the generated SQL so the ? is replaced by 'ADMIN' I get the expected result. The Role enum is just a bare enum (ie I'm not changing the toString or name methods) so I would guess hibernate is swapping in ADMIN as well, except it fails. — fancyplants, Aug 21 '13 at 14:53
I have managed to get the JPQL version working by doing: `query.setParameter( "roles1", Role.ADMIN.toString() );` which sounds a bit wrong. Surely the point is that it can accept the type of data that is in that property.. — fancyplants, Aug 21 '13 at 15:29
The same is true of the CriteriaQuery. If I do the following: `query.where(builder.isMember( Role.ADMIN.toString(), root.>get( "roles" ) ) );` that works too. :( — fancyplants, Aug 21 '13 at 15:45

score 1 · Answer 1 · answered Aug 22 '13 at 08:21

OK, for people out there having a similar problem, I have a solution, but it's not very good. Thanks to JB Nizet for the pointers as they helped diagnose the problem.

The problem is because of limitations in JPA and/or Hibernate when dealing with data that is not a String. Here, I have used an enum, and it is doing an equality test between a Role object and it's string representation in the database, rather than converting the Role I pass in to its db representation (as you would expect it to do) and then doing the compare.

To get the JPQL version going, I did:

String jpql = "SELECT * FROM com.mypackage.user.User WHERE (:roles1 MEMBER OF roles))";
Query query = em.createQuery(jpql);
query.setParameter( "roles1", Role.ADMIN.toString() );
List result = em.getResultList();

To fix the JPA version, I did:

CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
Root<User> root = query.from(User.class);

query.where(builder.isMember( Role.ADMIN.toString(), root.<Set<String>>get( "roles" ) ) );

List<User> result = getEntityManager().createQuery(query).getResultList();

If anyone can correct this code and make it so the toString is unnecessary (I haven't excluded the idea that I just havent set it up correctly) I'd be grateful if you can put me right. :)

JPA and JPQL MEMBER OF queries always returning empty results

1 Answers1