javahibernatepaginationdistinctcriteria

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


I'm trying to implement paging using row-based limiting (for example: setFirstResult(5) and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables.

Understandably, data is getting cut off randomly; and the reason for that is explained here.

As a solution, the page suggests using a "second sql select" instead of a join.

How can I convert my existing criteria query (which has joins using createAlias()) to use a nested select instead?


Solution

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