I have a criteria that returns all data the application requires, basically:
Criteria criteria = session.createCriteria(Client.class);
criteria.createAlias("address", "address");
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setFirstResult(init);
criteria.setMaxResults(max);
List<Client> clients = criteria.list();
The problem is that the relation client / address is bidirectional: on client has one address and one address may belong to more than one client.
I want to retrieve "single" client objects based on their pk of course, some number of clients as they are displayed in a table.
Because the setFirstResult/setMaxResults are executed first I am getting duplicated clients within the already applied limits. After (application level as not group by was used) hibernate gets rids of the duplicate clients so I end up with less clients that the maximum specified in the setMaxResults.
Cannot group by (projection group) as it won't return all columns required in client/addresses, only the group the query is grouping by.
(To sum up, My table has 100 results per page but after discarding duplicates I have 98 results instead of 100...) that is because the limit : LIMIT 0,100 is applied BEFORE hibernate groups when it should be performed AFTER)
As it is pointed out in the thread linked by "Ashish Thukral" next line solves this:
criteria.setFetchMode("address.clients", FetchMode.SELECT);
It prevents the join that causes the problem to be made.
Of course, it is possible to remove fetch="join" from the xml configuration file but this solution does not affect other places where the beans may be being retrieved.