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?

Owenism

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.

Sheugh

I get an error when i add this to my DetachedCriteria "Unable to perform find[SQL: SQL not available]" Do you have any idea – Cleek

Works fine for me - maybe check you actually have an id called "id" – Owenism

FishBoy is actually me. Back in '08 you were not allowed to answer your own questions. – Owenism

How do you hydrate the objets later? – Adust

but it returns to you only the property chosen, for example-> id.exampleA, it will return only the list with thevalues of exampleA, not with the class, do u underestand what im trying to say? how can u make that it returns to u the class? thanks – Tubate

We can use HQL to retrieve distinct rows based on the distinct id. – Tarboosh

Answer 2

I am using this one with my code.

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.

Emergency

This won't work in this case - see FishBoy's answer which explains why. – Owenism

Also, according to the link provided by Daniel Alexiuc in his question, this won't always translate in a distinct clause in native sql. But it does work if you don't need to paginate. – Zootomy

downvoted, as this answer is simply wrong, both in the context of this question and regarding its content, like explained here [#25537368 this "distinct" via ResultSetTransformer is done after the query is executed – Autoradiograph

SImply wrong answer, does not work with result limit, people who upvoted didn't need limit – Blood

Answer 3

A slight improvement building on FishBoy's suggestion.

It is possible to do this kind of query in one hit, rather than in two separate stages. i.e. the single query below will page distinct results correctly, and also return entities instead of just IDs.

Simply use a DetachedCriteria with an id projection as a subquery, and then add paging values on the main Criteria object.

It will look something like this:

DetachedCriteria idsOnlyCriteria = DetachedCriteria.forClass(MyClass.class);
//add other joins and query params here
idsOnlyCriteria.setProjection(Projections.distinct(Projections.id()));

Criteria criteria = getSession().createCriteria(myClass);
criteria.add(Subqueries.propertyIn("id", idsOnlyCriteria));
criteria.setFirstResult(0).setMaxResults(50);
return criteria.list();

Owenism

I think this answer is much more complete and really fills in an answer about how to hydrate a distinct list of an associated Object. This is exactly what I was looking for. Thank you. Really, I think this is the best answer. – Rhpositive

Tried this. Doesn't work. The subquery works, but the main query still isn't constrained by "distinct". – Boyle

I have saved a lot of time because of this answer, Thanks a lot. – Dympha

This works great, but a follow up question: How do I get the total result size for the idsOnlyCriteria? Often in paging you want to know how many total pages/iterms there are. – Confuse

I can verify this does not work after testing, we will still pull duplicates in the criteria query which will mess up the pagination/limit. – Blood

Answer 4

A small improvement to @FishBoy's suggestion is to use the id projection, so you don't have to hard-code the identifier property name.

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

Normalcy

Answer 5

The solution:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

works very well.

Angelangela

That works fine for normal queries. But this question specifically asks about Hibernate queries that use "row-based limiting" or "paging". – Owenism

...and that has joins to other tables. – Owenism

Answer 6

session = (Session) getEntityManager().getDelegate();
Criteria criteria = session.createCriteria(ComputedProdDaily.class);
ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("user.id"), "userid");
projList.add(Projections.property("loanState"), "state");
criteria.setProjection(Projections.distinct(projList));
criteria.add(Restrictions.isNotNull("this.loanState"));
criteria.setResultTransformer(Transformers.aliasToBean(UserStateTransformer.class));

This helped me :D

Mohave

Answer 7

if you want to use ORDER BY, just add:

criteria.setProjection(
    Projections.distinct(
        Projections.projectionList()
        .add(Projections.id())
        .add(Projections.property("the property that you want to ordered by"))
    )
);

Sunny

could you please elaborate on why this would work. and how would i set order by on multiple columns and add ascending or descending? – Thompkins

Answer 8

I will now explain a different solution, where you can use the normal query and pagination method without having the problem of possibly duplicates or suppressed items.

This Solution has the advance that it is:

• faster than the PK id solution mentioned in this article
• preserves the Ordering and don’t use the 'in clause' on a possibly large Dataset of PK’s

The complete Article can be found on my blog

Hibernate gives the possibility to define the association fetching method not only at design time but also at runtime by a query execution. So we use this aproach in conjunction with a simple relfection stuff and can also automate the process of changing the query property fetching algorithm only for collection properties.

First we create a method which resolves all collection properties from the Entity Class:

public static List<String> resolveCollectionProperties(Class<?> type) {
  List<String> ret = new ArrayList<String>();
  try {
   BeanInfo beanInfo = Introspector.getBeanInfo(type);
   for (PropertyDescriptor pd : beanInfo.getPropertyDescriptors()) {
     if (Collection.class.isAssignableFrom(pd.getPropertyType()))
     ret.add(pd.getName());
   }
  } catch (IntrospectionException e) {
    e.printStackTrace();
  }
  return ret;
}

After doing that you can use this little helper method do advise your criteria object to change the FetchMode to SELECT on that query.

Criteria criteria = …

//    … add your expression here  …

// set fetchmode for every Collection Property to SELECT
for (String property : ReflectUtil.resolveCollectionProperties(YourEntity.class)) {
  criteria.setFetchMode(property, org.hibernate.FetchMode.SELECT);
}
criteria.setFirstResult(firstResult);
criteria.setMaxResults(maxResults);
criteria.list();

Doing that is different from define the FetchMode of your entities at design time. So you can use the normal join association fetching on paging algorithms in you UI, because this is most of the time not the critical part and it is more important to have your results as quick as possible.

Insurgence

This way, you won't get the collections populated after you close the session o detach the criteria result. – Singlephase

Answer 9

Below is the way we can do Multiple projection to perform Distinct

    package org.hibernate.criterion;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.type.Type;

/**
* A count for style :  count (distinct (a || b || c))
*/
public class MultipleCountProjection extends AggregateProjection {

   private boolean distinct;

   protected MultipleCountProjection(String prop) {
      super("count", prop);
   }

   public String toString() {
      if(distinct) {
         return "distinct " + super.toString();
      } else {
         return super.toString();
      }
   }

   public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      return new Type[] { Hibernate.INTEGER };
   }

   public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      StringBuffer buf = new StringBuffer();
      buf.append("count(");
      if (distinct) buf.append("distinct ");
        String[] properties = propertyName.split(";");
        for (int i = 0; i < properties.length; i++) {
           buf.append( criteriaQuery.getColumn(criteria, properties[i]) );
             if(i != properties.length - 1) 
                buf.append(" || ");
        }
        buf.append(") as y");
        buf.append(position);
        buf.append('_');
        return buf.toString();
   }

   public MultipleCountProjection setDistinct() {
      distinct = true;
      return this;
   }

}

ExtraProjections.java

package org.hibernate.criterion; 

public final class ExtraProjections
{ 
    public static MultipleCountProjection countMultipleDistinct(String propertyNames) {
        return new MultipleCountProjection(propertyNames).setDistinct();
    }
}

Sample Usage:

String propertyNames = "titleName;titleDescr;titleVersion"

criteria countCriteria = ....

countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames);

Referenced from https://forum.hibernate.org/viewtopic.php?t=964506

Dishwater

Answer 10

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!

Commons