How to get distinct results in hibernate with joins and row-based limiting (paging)?

Question

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

Simply add this to your criteria:

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

Now you'll get the correct number of results according to your row-based limiting. 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.

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

I am using this one with my codes.

Simply add this to your criteria:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

that code will be like the select distinct * from table of the native sql. Hope this one helps.

A slight improvement building on FishBoy's suggestion.

It is possible to do this kind of query in one hit, rather than in two separate stages. i.e. the single query below will page distinct results correctly, and also return entities instead of just IDs.

Simply use a DetachedCriteria with an id projection as a subquery, and then add paging values on the main Criteria object.

It will look something like this:

DetachedCriteria idsOnlyCriteria = DetachedCriteria.forClass(MyClass.class);
//add other joins and query params here
idsOnlyCriteria.setProjection(Projections.distinct(Projections.id()));

Criteria criteria = getSession().createCriteria(myClass);
criteria.add(Subqueries.propertyIn("id", idsOnlyCriteria));
criteria.setFirstResult(0).setMaxResults(50);
return criteria.list();

A small improvement to @FishBoy's suggestion is to use the id projection, so you don't have to hard-code the identifier property name.

criteria.setProjection(Projections.distinct(Projections.id()));