[hibernate-issues] [Hibernate-JIRA] Created: (HHH-7211) Restriction on ElementCollection not working using criteria API
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