Problems with Hibernate Criteria and distinct entities
I've encountered a strange lack of ability in Hibernate Criteria API. Suppose you have an entity called User with many-to-many relationship to groups. If you need to find all users appearing in one or more of given groups, you can write an HQL like
u from User u join u.groups g where g.id in :groups
This query will do the job, but if a user appears in a few groups, it will appear few times in the result set. To fix that, we can use "distinct" keyword:
distinct u from User u join u.groups g where g.id in :groups
Unfortunately, I had to provide an ability to order the results by any property of User (first name, last name, etc), and support paging for showing only a few results out of thousands. HQL does not allow variables in "order by" clause, so I had to use Criteria for that.
Without the "distinct" the code is quite simple:
Collection<Long> groups = ... //will come as a parameter Order order = ... //will also come a parameter int maxResults = ... //you've got the idea Criteria c = getSession().createCriteria(User.class); c.createAlias("groups", "g"); c.add(Restrictions.in("g.id", groups); c.addOrder(o); c.setMaxResults(maxResults); return c.list();
So far so good. Let us add distinct now. Criteria API has support for that. First I tried to add
But the problem with this approach is it does not return a collection of User entities anymore, it returns a collection of Ids (integers). I tried to google a little bit for the solution, but found only complains on this missing feature.
So at the end I made up an inner query. I have to check yet what will be the performance degrade of this solution relative to the original HQL one, but at least it returns correct results:
Collection<Long> groups = ... //will come as a parameter Order order = ... //will also come a parameter int maxResults = ... //you've got the idea DetachedCriteria dc = DetachedCriteria.forClass(User.class); dc.createAlias("groups", "g"); dc.add(Restrictions.in("g.id", groups); Criteria c = getSession().createCriteria(User.class); c.add(Property.forName("id").in(dc)); c.addOrder(o); c.setMaxResults(maxResults); return c.list();
This criteria will create the following SQL (for brevity I shortened the aliases and replaced with the asterisk the long list of columns, generated by Hibernate:
select this_.* from Users this_ where this_.ID in ( /* criteria query */ select distinct this_.ID from Users this_ join User_to_Group u2g on this_.ID=u2g.USER_ID join Groups gr on u2g.GROUP_ID=gr.ID where gr.ID in ( ?,? ) ) order by this_.UserName
One can see that in addition to SELECT IN SELECT, caused by the limitation of Criteria API I explained above, this query makes also unnecessary second because of many-to-many relationship. Ideally, I would like to get SQL like:
select this_.* from Users this_ join User_to_Group u2g on this_.ID=u2g.USER_ID where u2g.GROUP_ID in ( ?,? ) order by this_.UserName
If you have suggestions how to improve my solution, please do not hesitate to comment. Thank you!