Hibernate Criteria: distinct entities and then limit
criteria, group-by, hibernate, java
Solution
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.
Problem
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)