How to get distinct results in hibernate with joins and row-based limiting (paging)?

Question

I'm trying to implement paging using row-based limiting (for example: setFirstResult(5) and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables.

Understandably, data is getting cut off randomly; and the reason for that is explained here.

As a solution, the page suggests using a "second sql select" instead of a join.

How can I convert my existing criteria query (which has joins using createAlias()) to use a nested select instead?

Answer 1

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

Simply add this to your criteria:

criteria.setProjection(Projections.distinct(Projections.property("id")));

Now you'll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check as part of the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness after the sql query has been performed.

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

Answer 2

Great tip, have you ever tried that? Cause that doesn't work

Answer 3

I have found another solution for this

Answer 4

I am using this one with my codes.

Simply add this to your criteria:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

that code will be like the select distinct * from table of the native sql. Hope this one helps.

Answer 5

The solution:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

works very well.

• JJ

Answer 6

NullPointerException in some cases! Without criteria.setProjection(Projections.distinct(Projections.property("id"))) all query goes well! This solution is bad!

Another way is use SQLQuery. In my case following code works fine:

List result = getSession().createSQLQuery(
"SELECT distinct u.id as usrId, b.currentBillingAccountType as oldUser_type,"
+ " r.accountTypeWhenRegister as newUser_type, count(r.accountTypeWhenRegister) as numOfRegUsers"
+ " FROM recommendations r, users u, billing_accounts b WHERE "
+ " r.user_fk = u.id and"
+ " b.user_fk = u.id and"
+ " r.activated = true and"
+ " r.audit_CD > :monthAgo and"
+ " r.bonusExceeded is null and"
+ " group by u.id, r.accountTypeWhenRegister")
.addScalar("usrId", Hibernate.LONG)
.addScalar("oldUser_type", Hibernate.INTEGER)
.addScalar("newUser_type", Hibernate.INTEGER)
.addScalar("numOfRegUsers", Hibernate.BIG_INTEGER)
.setParameter("monthAgo", monthAgo)
.setMaxResults(20)
.list();

Distinction is done in data base! In opposite to:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
where distinction is done in memory, after load entities!