Hibernate Criteria: distinct entities and then limit

java hibernate group-by criteria
kandan picture kandan · Aug 16, 2013 · Viewed 33.4k times · Source

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

kandan picture kandan · Sep 18, 2013

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.

Related questions

JPA Criteria query group by uses only the id

This is a sample entity: public class Account{ @Id Long id Double remaining; @ManyToOne AccountType type } public class AccountType{ @Id Long id; String name; } Now i create a criteria query with Join as follwing : CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder(); CriteriaQuery criteriaQuery = …

Read More
select "all columns" with "group by" in hibernate criteria queries

I want to write a criteria query using "group by" and want to return all the columns. Plane sql is like this: select * from Tab group by client_name order by creation_time; I understand that it will have count(…

Read More
What are the possible values of the Hibernate hbm2ddl.auto configuration and what do they do

I really want to know more about the update, export and the values that could be given to hibernate.hbm2ddl.auto I need to know when to use the update and when not? And what is the alternative? These …

Read More