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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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  gt   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

User · Answer

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  lt  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

User · Answer

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

User · Answer

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

User · Answer

The solution   criteria setResultTransformer Criteria DISTINCT ROOT ENTITY     works very well

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt String gt  resolveCollectionProperties Class lt   gt  type      List lt String gt  ret   new ArrayList lt String gt       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

User · Answer

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

User · Answer

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 lt String gt  resolveCollectionProperties Class lt   gt  type      List lt String gt  ret   new ArrayList lt String gt       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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

The solution   criteria setResultTransformer Criteria DISTINCT ROOT ENTITY     works very well

User · Answer

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  gt   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

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

Examples related to java

Examples related to hibernate

Examples related to paging

Examples related to criteria

Examples related to distinct