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?

Solution 1

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

Solution 2

I got this answer from another forum It appears to be the best solution. Basically you create a subquery first (the DetachedCriteria). This will fetch all the distinct ids. Then, you apply the results of that subquery to your main query.

The resulting SQL generated by this is quite clean, and hibernate returns the results to you as a list of objects.

Criteria criteria = session().createCriteria(Employee.class);
criteria.add(Property.forName("id").in(dc)); 
criteria.setMaxResults(maxLength);
criteria.setFirstResult((int)rowNum);


DetachedCriteria dc = DetachedCriteria.forClass(Employee.class);
dc.createAlias("location", "location");
dc.createAlias("location.dept", "department");
dc.add(
    Restrictions.or(
        Restrictions.eq("location.id", locationId),
        Restrictions.eq("department.name", departmentName)));
dc.setProjection(Projections.distinct(Property.forName("id")));

Solution 3

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.)