Hibernate Criteria: distinct entities and then limit

Question

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)

Answer 1

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.

Answer 2

If I understand your relations correctly, you will have a list of client in Address and One address in each Client Entity class. So if you just want a list of client, what's the big deal, can't you just get them by

Criteria criteria = session.createCriteria(Client.class);
criteria.setFirstResult(init);
criteria.setMaxResults(max);
List<Client> clients = criteria.list();

Why are you creating an alias and using distinct_root_entity ? If you need to get that address, when you access it in your DAO or ServiceImpl, Hibernate will anyways fetch it lazily for you.

Correct me if I am wrong.

Answer 3

If you looking for Client based on the id as follows. Based on the your criteria there is no need for max and init size because it always return one client.

Criteria criteria = getSession().createCriteria(Client.class);
criteria .add(Restrictions.eq("id", yourClientId);
criteria.createAlias("address", "address");
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setFirstResult(init);
criteria.setMaxResults(max);
List<Client> clients = criteria.list();

If you looking for Address based on the id as follows.

Criteria criteria = getSession().createCriteria(Client.class);
criteria.createAlias("address", "address");
criteria .add(Restrictions.eq("address.id", yourAddressId);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setFirstResult(init);
criteria.setMaxResults(max);
List<Client> clients = criteria.list();