Criteria: setMaxResults() + setResultTransformer() problem

helwick · **Posted:** Tue Mar 21, 2006 11:17 am

Hibernate version: 3.1.2

Name and version of the database you are using: Cloudscape 10.0

Some of our criteria queries involve collection joining. To avoid duplicates, i.e. get distinct root entities, we use:

Code:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

which does the job.

We also do some eager fetching (to avoid expensive roundtrips on our WAN), e.g.:

Code:

criteria.setFetchMode("actionHistory", FetchMode.JOIN);

Finally, we limit the result set:

Code:

criteria.setMaxResults(hits);

However, the result set may contain less elements as specified by hits, which is the problem. I suppose that setMaxResults() is applied to the list before the transformer gets a hand on it.

I've read the book and searched the forum for an answer to this problem but didn't find one (I don't want to do plain HQL or SQL).
Any help would be much appreciated.

Helmut

helwick · **Posted:** Tue Mar 21, 2006 12:34 pm

Appending the complete code of the query:

Code:

   /**
    * Searches for mcls. Sorted by dateLatestAction (desc.)
    * @param bean
    * @return
    * @throws HibernateException
    */
   @SuppressWarnings("unchecked")
   private List<Mcl> searchMclsByCriteria(MclSearchBean bean) throws HibernateException {
      
      Criteria criteria = HibernateUtil.getSession().createCriteria(Mcl.class, "mcl");
      if (StringUtils.isNotEmpty(bean.getDriver())) {
         Criteria criteriaFixes = criteria.createCriteria("fixes", "aliasFixes");
            criteriaFixes.add(Restrictions.eq("fbi.mcfInfo.driver", bean.getDriver()));
      }
      if (StringUtils.isNotEmpty(bean.getEcNumber())) {
         criteria.add(Restrictions.like("ecNumber", bean.getEcNumber()));
      }
      if (bean.getBuildDateFrom() != null && bean.getBuildDateTo() != null) {
         criteria.add(Restrictions.between("dateLatestAction", 
               bean.getBuildDateFrom(), bean.getBuildDateTo()));
      }
      if (bean.getState() != null) {
         criteria.add(Restrictions.eq("state", bean.getState()));
      }
      if (StringUtils.isNotEmpty(bean.getTicket())) {
         criteria.createCriteria("fixes")
            .createCriteria("fbi.problems").add(Restrictions.like("name", bean.getTicket()));
      }
      criteria.setFetchMode("actionHistory", FetchMode.JOIN);
      criteria.setFetchMode("state", FetchMode.JOIN);
      
      criteria.addOrder(Order.desc("dateLatestAction"));
      if (bean.getHits() != null) {
         criteria.setMaxResults(bean.getHits().intValue());
      }
      // remove duplicates
      // HW on Mar 20, 2006: BUG when no driver is specified, this query may return less mcls than specified!
      // I suspect setMaxResults() being applied prior to the transformer removing rows
      criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
      return criteria.list();
   }

Collection of "fixes" in class Mcl is mapped as follows:

Code:

        <set name="fixes" inverse="true" lazy="true" access="field"
           sort="com.ibm.emcf.FixInMclOrderComparator">
         <cache usage="nonstrict-read-write"></cache>
         <key column="MCL_ID"/>
         <one-to-many class="Fix"/>
      </set>

helwick · **Posted:** Wed Mar 22, 2006 4:39 am

Hi,
tried to set

Code:

criteria.setMaxResults(hits);

before

Code:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

which didn't help.

Is there any way to avoid limiting the rows before the transformer is applied?

helwick · **Posted:** Fri Mar 24, 2006 4:56 am

Hi,
maybe I'm not getting any reply since I did not describe clear enough what I want to do.

In the above query, I want to limit the number of root objects to be returned to a specific value (bean.getHits()).

criteria.setMaxResults() won't work since the query will join collections (to avoid lazy fetching).

Is there another way that does the job? (except for iterating criteria.list())

Regards,
Helmut

cmodic · **Posted:** Fri Apr 21, 2006 12:42 pm

I am running into the exact same issue. Were you ever able to figure this out using criteria or did you go about it some other way?

Any help would be appreciated.

pepelnm · **Posted:** Fri Apr 21, 2006 1:13 pm

What happens if you do

Code:

Criteria criteria = HibernateUtil.getSession().createCriteria(Mcl.class, "mcl");
if(bean.getHits() != null) {
   criteria.setMaxResults(bean.getHits().intValue());
}

and then the remaining code.

Also, what is the generated SQL code in both cases?

helwick · **Posted:** Mon Apr 24, 2006 5:57 am

cmodic wrote:

I am running into the exact same issue. Were you ever able to figure this out using criteria or did you go about it some other way?

Any help would be appreciated.

No, I wasn't able to resolve that issue. However, I think that there just is no way for Hibernate to resolve this, because the database doesn't provide for a solution to this (joining collections will yield an unpredictable number of rows dependening on the data).

Regards,
Hemut

bonfarj · **Joined:** Wed Mar 07, 2007 2:36 pm **Posts:** 5

cmodic wrote:

I am running into the exact same issue.

I'm too, exactly the same. :(

Does somebody know how to resolve?

Best regards,

raphabpc · **Joined:** Wed Jul 14, 2010 5:26 pm **Posts:** 2

i have the same problem.

take a look here: http://opensource.atlassian.com/project ... issue-tabs

seems that they will not solve this problem, then you should use sub-select fetching, so you can limit the results before the join

virchete · **Joined:** Fri Aug 13, 2010 4:43 am **Posts:** 3

I have the same issueee!!! ufff, someone can help me??? It would be very nice, because at the beginning I thought for making pagination was enough with use the setMaxResults. Apparently worked but with less results than expected...

lalitnandan · **Joined:** Wed Jul 04, 2012 3:12 am **Posts:** 1

Hope this can be help

Code:

public List<Employee> getData(int to, int from) {
      
                Criteria hCriteria = null;
      List<Employee> viewDataList = null;
                List<Employee> exactDataList = null;
      try {

         hSession = HibernateSessionFactory.getSession();
         hTransaction = hSession.beginTransaction();
         hCriteria = hSession.createCriteria(Employee.class);

         /*
                        hCriteria.setFirstResult(to);
         hCriteria.setFirstResult(from);
                        */
         hCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
         viewDataList = hCriteria.list();
         
                      // for limit
                     exactDataList=viewDataList.subList(from,to);

                    hTransaction.commit();
      } catch (Exception e) {
         hTransaction.rollback();
         
      } finally {
         try {
            hSession.flush();
            HibernateSessionFactory.closeSession();
         } catch (Exception hExp) {
         }

      }

      return exactDataList;

   }

raul.palma · **Joined:** Tue Apr 23, 2013 9:14 am **Posts:** 1

Hi,

I managed to make it by using :

Code:

criteria.setFetchMode("property", FetchMode.SELECT);

for every join made in the resulting SQL query. That seems to work, but the problem is that I generally get less rows than expected.