Hibernate version: 3.1
My current problem is that I have a query builder that uses the Criteria API to construct a query based on user input. My Criteria query joins to several other tables and specifies a max results. An example of a typical query in HQL that is impossible with the Criteria API is:
Code:
Query query = session.createQuery( "select distinct entity from MyEntity entity join entity.children child where child.name is not null" );
query.setMaxResults( 10 );
query.list();
In HQL, the previous query will select 10 distinct entities that have children with a non-null name. With the Criteria API, there are two problems:
Problem #1 Currenty with the Criteria API, distinct is very difficult to use. The only way I know of to return distinct results is to use a projection:
Code:
Criteria criteria = session.createCriteria( MyEntity.class );
criteria.setProjection( Projections.distinct( Projections.property( "id" ) ) );
Unfortunately, the Criteria API works in one of two modes: with a projection and without a projection. When a projection is not specified, Hibernate will hydrate the root entities. If a projection is specified, Hibernate will return the results of the projection to you - which will not include the root entity.
For example, in the previous example, Hibernate will return a list of "id"s - there is no way to specify a projection that will actually return instances of MyEntity. In fact, there is no way to specify any projection that I know of that will return a list of MyEntity objects.
So, the only way to make the Criteria API use a distinct clause is to add a projection - but adding a projection causes Hibernate to not hydrate the root entity, defeating the purpose of the distinct clause. The Criteria API needs to either be modified to support a distinct flag outside of the Projection API or support projections that return the root entity.
Problem #2I have done some digging in the Criteria source and see that unlike HQL, the Criteria API selects and hydrates all entities that are joined to by the query (any aliases or associations) This is inefficient and problematic. It is inefficient because typically a Criteria call only returns the root entity - so why hydrate all associated entities, especially if the associated entities are not eagerly fetched. It is problematic because, as in the example above, a distinct clause assumes that only the root entity columns are selected.
To clarify, the HQL above will result in:
Code:
select my_entities.col1, my_entities.col2 from .....
whereas the same in the Criteria API would result in:
Code:
select my_entities.col1, my_entities.col2, my_children.col1, my_children.col2 from .....
even though there is no need to include the children columns in the select clause. This appears to be due to the fact that the CriteriaLoader shares the same common superclass (OuterJoinLoader) I think it would be more efficient and support things like distinct if the CriteriaLoader was smarter about which objects it selects and hydrates. The QueryLoader used by HQL queries only selects columns that are either specified in the select clause or need to be eagerly fetched. I think the CriteriaLoader should only select columns that are part of the root entity or need to be eagerly fetched - rather than including all columns in all associations.
SolutionI would imagine the example should look like this with the Criteria API:
Code:
Criteria criteria = session.createCriteria( MyEntity.class );
criteria.add( ... ); // Add where-clause restrictions
criteria.setDistinctEntities( true );
criteria.setMaxResults( 10 );
criteria.list();
Alternatively, it could look like this:
Code:
Criteria criteria = session.createCriteria( MyEntity.class );
criteria.add( ... ); // Add where-clause restrictions
criteria.setProjection( Projections.distinct( Projections.rootEntity() ) );
criteria.setMaxResults( 10 );
criteria.list();
I am willing to spend some time making a patch, but when I dug into Problem #2 above I wasn't sure what all the implications of changing the select behavior would be. If a Hibernate developer (Gavin, Christian, ? =D ) could point me in the right direction I'd appreciate it.
Is this behavior a problem for many other users? I've seen several posts in the past regarding distinct selection using the Criteria API.
Login
FAQ
Search
