I was faced with a task, where I had to remove duplicate records from the result of a hibernate query, such that pagination parameters had to be applied only after the duplicate records were removed.
The following code snippet, used a single criteria object to do that.
https://stackoverflow.com/questions/11038234/pagination-with-hibernate-criteria-and-distinct-root-entity/23618190#23618190
The following code snippet, used a single criteria object to do that.
https://stackoverflow.com/questions/11038234/pagination-with-hibernate-criteria-and-distinct-root-entity/23618190#23618190
What you want for each pagination page is 2 things, the total count of all results and your single page of results, but to do that you need to take 3 steps. 1) get the total count, 2) get the unique ids for your page, and 3) get the full data for the ids found in step 2. And you can do all that with a single criteria object:
1) get total count, using distinct ids (uniqueField = the name of your id in the entity class)
Criteria criteria = session.createCriteria(YourEntity.class);
Projection idCountProjection = Projections.countDistinct(uniqueField);
criteria.setProjection(idCountProjection);
//setup criteria, joins etc here
int totalResultCount = ((Long)criteria.uniqueResult()).intValue();
2) reset projection and set start and length (you want the distinct ids)
criteria.setProjection(Projections.distinct(Projections.property(uniqueField)));
criteria.setFirstResult(start);
criteria.setMaxResults(length);
List uniqueSubList = criteria.list();
3) reset projection and get distinct results that match ids
criteria.setProjection(null);
criteria.setFirstResult(0); criteria.setMaxResults(Integer.MAX_VALUE);
criteria.add(Restrictions.in(uniqueField, uniqueSubList));
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
List searchResults = criteria.list();
//and now, however you want to return your results
Map<String, Object> searchResultsMap = new HashMap<String, Object>();
searchResultsMap.put("searchResults", searchResults);
searchResultsMap.put("totalResultCount", totalResultCount);
