[Hibernate-JIRA] Created: (HHH-7211) Restriction on ElementCollection not working using criteria API
by 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
12 years, 9 months
[Hibernate-JIRA] Commented: (HHH-1599) ConstraintViolationException returns null with PostgresDialect
by Mark (JIRA)
[ https://hibernate.onjira.com/browse/HHH-1599?page=com.atlassian.jira.plug... ]
Mark commented on HHH-1599:
---------------------------
Any update on a fix for this?
It is messy to handle constraint errors with codes and it seems the only alternative is to report a general constraint error
> ConstraintViolationException returns null with PostgresDialect
> --------------------------------------------------------------
>
> Key: HHH-1599
> URL: https://hibernate.onjira.com/browse/HHH-1599
> Project: Hibernate ORM
> Issue Type: Bug
> Components: core
> Affects Versions: 3.1.3
> Environment: Hibernate 3.1.3 , Postgres 8.1.2 (i686), postgresql-8.2dev-501.jdbc.jar
> Reporter: Niclas Lindberg
> Assignee: Diego Plentz
> Priority: Minor
> Attachments: HHH-1599.patch, HHH-1599-postgres-only.patch, PostgreSQLDialect.java
>
> Original Estimate: 0.05h
> Remaining Estimate: 0.05h
>
> ConstraintViolationException.getConstraintName() returns null. The problem is that the message being parsed comes from the SqlException that is the inputparameter to extractConstraintName(SqlException) in ViolatedConstraintNameExtracter.
> Solution. The message provided by sqlException.getMessage() is "batch bla bla something" but the correct Exception message to parse is sqlException.getNextException().getMessage()
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 9 months
[Hibernate-JIRA] Issue Comment Edited: (HHH-1599) ConstraintViolationException returns null with PostgresDialect
by Mark (JIRA)
[ https://hibernate.onjira.com/browse/HHH-1599?page=com.atlassian.jira.plug... ]
Mark edited comment on HHH-1599 at 4/1/12 1:53 PM:
---------------------------------------------------
Any update on a fix for this?
It is messy to handle constraint errors with code and it seems the only alternative is to report a general constraint error
was (Author: mcchafist):
Any update on a fix for this?
It is messy to handle constraint errors with codes and it seems the only alternative is to report a general constraint error
> ConstraintViolationException returns null with PostgresDialect
> --------------------------------------------------------------
>
> Key: HHH-1599
> URL: https://hibernate.onjira.com/browse/HHH-1599
> Project: Hibernate ORM
> Issue Type: Bug
> Components: core
> Affects Versions: 3.1.3
> Environment: Hibernate 3.1.3 , Postgres 8.1.2 (i686), postgresql-8.2dev-501.jdbc.jar
> Reporter: Niclas Lindberg
> Assignee: Diego Plentz
> Priority: Minor
> Attachments: HHH-1599.patch, HHH-1599-postgres-only.patch, PostgreSQLDialect.java
>
> Original Estimate: 0.05h
> Remaining Estimate: 0.05h
>
> ConstraintViolationException.getConstraintName() returns null. The problem is that the message being parsed comes from the SqlException that is the inputparameter to extractConstraintName(SqlException) in ViolatedConstraintNameExtracter.
> Solution. The message provided by sqlException.getMessage() is "batch bla bla something" but the correct Exception message to parse is sqlException.getNextException().getMessage()
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
12 years, 9 months