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)

Airliah

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.

Airliah

FetchMode.SELECT fixed it for me - the other modes does not work in this case. – Guitarist

FetchMode.SELECT is great... except that it the forces an eager fetch, for some reason. See docs.jboss.org/hibernate/core/3.3/api/org/hibernate/… Is there no way to tell hibernate simply to do a "distinct" on the pk of the entity being fetched?? This seems like the most obvious an trivial thing to want, and, as per usual, hibernate makes life extremely complicated for us. – Brewhouse

By the way, doing this with HQL is just as trivial as it sounds. You just add the word "distinct" in your query. Why this is so painfully onerous using the Criteria API is beyond me. Can we all start using OO databases yet? – Brewhouse

Answer 2

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();

Teodoro

thanks a lot for your answer, but I was looking for all clients, returning them as a response in pages. The problem is than a page that should return 100 results, returns less after grouping them (DISTINCT_ROOT_ENTITY). That grouping should happend before the limit... – Airliah

the 2nd block might result in lesser no of rows as posted by @Airliah in his question – Shirk

Answer 3

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.

Shirk

Thanks. I am creating an alias to force hibernate get other data in the query as i may be filtering clients by for example their postcode (is a paginated table with filters). So i get all data I need: clients with address. the problem is that with the address also comes a set of clients that have that address and i need a resulttransformer to unify the clients by their id. Probably it is possible, without modifying any annotation, tell hibernate not to retrieve/join an specific column for a class/table, but do that to the other fields, whatever the default defined (lazy or not) is specified. – Airliah

i think you are looking for this #5568254 – Shirk