I am using Criteria API for doing all the queries to the database and I need the pagination feature of Criteria API - Setting firstResult and maxResults.

All goes well until the response to the search request contains non distinct entries (I am not sure why it happens, but it does).

I filter the duplicates using Criteria.DISTINCT_ROOT_ENTITY ResultTransformer but then all the pagination logic does not apply anymore.

I searched the forum but did not find a solution to the problem (which seems trivial to me). There are workarounds such as :

http://forum.hibernate.org/viewtopic.php?t=941669&postdays=0&postorder=asc&highlight=distinctrootentity&start=0

but it does not work if I use abstract classes in my search criterias and for normal classes query cache fails with class cast exception.

Also there is an open feature request (unsolved) for several years :

http://opensource.atlassian.com/projects/hibernate/browse/HB-520

Using the projection is not handy since I need the whole object (or list of them) to be returned, and when using projection.distinct I get only the fields I specify and then the first solution fails to help due to abstract classes.

Does anyone have a solution on how can I apply distinctiveness not on the result but pass it on to SQL, to ensure proper pagination, via criteria API ? (Or implement pagination by some other means)

Thanks in advance.

Hibernate version: 3.2.1 GA