By Daniel Alexiuc


2008-11-18 23:05:13 8 Comments

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?

10 comments

@rekinyz 2014-07-14 16:01:59

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

@Stoppal 2014-08-05 06:28:43

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

@JJ. 2010-04-16 11:07:31

The solution:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

works very well.

@Daniel Alexiuc 2010-06-03 04:41:50

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

@Daniel Alexiuc 2010-06-03 05:17:40

...and that has joins to other tables.

@che javara 2017-02-21 21:23:01

does not work for the question asked

@Krzysztof Barczyński 2010-05-19 09:46:14

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!

@Andrew 2013-01-24 13:12:50

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

@Yashpal Singla 2013-01-08 07:21:11

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

@Andreas Hartmann-schneevoigt 2012-07-06 14:44:45

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.

@antgar9 2016-11-23 12:15:20

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

@Daniel Alexiuc 2011-10-27 04:43:57

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

@JamesD 2013-08-29 16:13:01

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.

@Gary Kephart 2013-10-10 16:55:46

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

@Do Will 2014-04-23 16:30:06

Very good solution.

@Rodrigo Almeida 2015-06-10 22:14:35

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

@Casey 2016-07-16 11:30:19

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.

@che javara 2017-02-22 16:07:08

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.

@nikita 2011-06-08 15:04:32

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

@grayshop 2009-12-03 06:56:52

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.

@Daniel Alexiuc 2009-12-04 00:14:27

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

@Alberto de Paola 2012-07-25 17:33:29

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.

@user2039709 2015-09-30 15:54:39

downvoted, as this answer is simply wrong, both in the context of this question and regarding its content, like explained here [stackoverflow.com/questions/25536868/… this "distinct" via ResultSetTransformer is done after the query is executed

@che javara 2017-02-21 21:18:15

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

@FishBoy 2008-11-19 00:53:36

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.

@Barbaros Alp 2009-02-13 01:39:27

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

@Daniel Alexiuc 2009-02-19 04:29:43

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

@Michel 2011-07-04 13:46:08

This doesn't work with an orderby on the join

@Daniel Alexiuc 2012-03-14 05:59:07

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

@Gavin Haynes 2016-11-23 00:48:35

How do you hydrate the objets later?

@Alberto Acuña 2017-04-25 10:36:02

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

@MR AND 2018-12-28 20:43:08

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

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Hibernate pagination mechanism

2 Answered Questions

[SOLVED] Hibernate: Pagination with setFirstResult and setMaxResult

1 Answered Questions

17 Answered Questions

3 Answered Questions

[SOLVED] Hibernate Criteria: distinct entities and then limit

1 Answered Questions

Hibernate eager loading not fetching all child rows

  • 2015-08-07 19:06:33
  • user871199
  • 995 View
  • 2 Score
  • 1 Answer
  • Tags:   java hibernate

1 Answered Questions

[SOLVED] How to join 2 entities based on NOT condition

1 Answered Questions

1 Answered Questions

Sponsored Content