[hibernate-issues] [Hibernate-JIRA] Created: (HHH-7211) Restriction on ElementCollection not working using criteria API

[Lars Heller (JIRA)]

Restriction on ElementCollection not working using criteria API
---------------------------------------------------------------

                 Key: HHH-7211
                 URL: https://hibernate.onjira.com/browse/HHH-7211
             Project: Hibernate ORM
          Issue Type: Bug
          Components: query-criteria
    Affects Versions: 4.1.1
            Reporter: Lars Heller
         Attachments: criteria-elementcollection.zip

I have a User object containing an ElementCollection of roles. The following is the roles' mapping.

{code:java}
@ElementCollection( fetch = FetchType.EAGER )
@CollectionTable( name = "user_roles", joinColumns = @JoinColumn( name = "user_id" ) )
@Column( name = "role", length = 20 )
@Fetch( FetchMode.SELECT )
public Set<String> getRoles()
{
    return this.roles;
}
{code}

Now I want to count all users having one of a given collection of roles. Using HQL I execute 
{code:sql}select count(distinct u) from User u join u.roles r where r in (:roles){code}
binding an array of roles to {{roles}}.

This works fine. But actually I want to use the criteria API, since I have a bunch of filter options to apply dynamically. So I tried
{code}
Criteria criteria = currentSession().createCriteria( User.class, "u" );
criteria.createAlias( "roles", "r", JoinType.INNER_JOIN );
criteria.add( Restrictions.in( "roles", roles ) );

criteria.setProjection( Projections.countDistinct( "id" ) );

return ( (Number) criteria.uniqueResult() ).intValue();
{code}
which does not work. It results in 
{noformat}
nested exception is org.hibernate.exception.GenericJDBCException: 
Parameter "#1" is not set; SQL statement:
select count(distinct this_.id) as y0_ from acme.users this_ inner join acme.user_roles r1_ on this_.id=r1_.user_id where this_.id in (?) [90012-163]
{noformat}
The problem here in my opinions is, that Hibernate creates the SQL
{code:sql}
select count(distinct this_.id) as y0_ from acme.users this_ inner join acme.user_roles r1_ on this_.id=r1_.user_id where this_.id in (?)
{code}
which is absolutely not what I expect, since the where references the user-id instead of the role.

It didn't work leaving out the join or using {{r}} instead of {{roles}} or using {{r.role}}.

My workaround was to create the sql myself and add a sql restriction:
{code}
Criteria roleCriteria = criteria.createCriteria( "roles", JoinType.INNER_JOIN );

String questionMarks = StringUtils.repeat( "?", ", ", roles.size() );
Type[] types = new Type[roles.size()];

Arrays.fill( types, currentSession().getTypeHelper().custom( RoleUserType.class ) );
roleCriteria.add( Restrictions.sqlRestriction( "{alias}.role in (" + questionMarks + ")", roles.toArray(),
                                                           types ) );
{code}
Not a nice solution. :-(

I added a Maven project for testing. It uses Spring and stuff, but I have reduced the code down to the problem (UserDaoHibernate and a simple test class).

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira