how do you get "real" sql distinct with hibernate criteria queries?

Question

I have a Hibernate criteria query that is incorrectly pulling out max results. In many cases, when I specify 20 max results, the query actually only returns 1 or 5 results, because the restrictions return many duplicates.

Criteria c = session.createCriteria(DomainObject.class);
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.createAlias("tags.list", "tag");
c.createAlias("names", "name");
c.createAlias("site", "site");
c.createAlias("site.tags.list", "siteTag");

// loads of or/and eq/like restrictions.

c.setFirstResult(0);
c.setMaxResults(20);

return c.list();

Is there any way to fix this query so that if I say 20 max results, it really does return 20 district results? It seems pretty crazy that hibernate limits the query to 20 results, and does the distinct filtering AFTER instead of at the database level.

Help?

Answer 1

Have you tried to use a Projection on the ID and create a subquery based on those results, as described on this page?

EDIT: (Note that there seems to be a bug in Oracle 11.2.0.1.0 which may prevent you from getting the results you want if that's what you're using.)

Answer 2

I think you are close, you just need a list of distinct Ids instead if distinct objects.

Try adding this to your criteria:

criteria.setProjection(Projections.distinct(Projections.property("Id")));

The reason this works is because the projection will perform the distinctness check as part of the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness after the sql query has been performed.

the above is a quote from the answer at 300491