I want to have the pagination after the multiple table join in criteria.
The issue is:
The duplicate records generated when I join the tables. The pagination applied to the record set(With duplication).
I use this criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); to remove the duplicated records.
Normally I got 10 records after applying the removing duplication.
When I set my start index as 1 and end index as 5 , I am supposed to get 5 records but it returns 2 or 3(approx). It depends on the joining.
Please help me out.
The problem here is in the two different techniques beeing applied.
So if there are in fact 2 rows doubled plus 1 other, the transformation will result in 3 objects.
The correct (and maybe the best) way, how to avoid that, is to not use fetching of the collections. If we need collection to be displayed, we should load it lazily (e.g. using batch-size to reduce number of selects)
If we need collection to be used as a filter, we should convert it into
subquery, and again do the pagination on the root entity, with the IN (Subquery) clause in place
Imagine this, Parent table:
ParentId, Code 1 , 'P1' 2 , 'P2' 3 , 'P3'
The child table:
ChildId , Code , ParentId 1 , 'C1' , 1 2 , 'C2' , 1 3 , 'C3' , 2 4 , 'C4' , 2 5 , 'C5' , 3
If we will ask for a
Parent and also join the
Child collection, we will
I answered this here: Pagination with Hibernate Criteria and DISTINCT_ROOT_ENTITY
You need to do 3 things, 1) get the total count, 2) get the ids of the rows you want, and then 3) get your data for the ids found in step 2. It is really not all that bad once you get the order correct, and you can even create a generic method and send it a detached criteria object to make it more abstract.