Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Criteria: setMaxResults() + setResultTransformer() problem
PostPosted: Tue Mar 21, 2006 11:17 am 
Beginner
Beginner

Joined: Mon Jan 24, 2005 11:33 am
Posts: 24
Location: Stuttgart, Germany
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


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 21, 2006 12:34 pm 
Beginner
Beginner

Joined: Mon Jan 24, 2005 11:33 am
Posts: 24
Location: Stuttgart, Germany
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>


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 22, 2006 4:39 am 
Beginner
Beginner

Joined: Mon Jan 24, 2005 11:33 am
Posts: 24
Location: Stuttgart, Germany
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?


Top
 Profile  
 
 Post subject: Criteria: limit the result set when joining collections
PostPosted: Fri Mar 24, 2006 4:56 am 
Beginner
Beginner

Joined: Mon Jan 24, 2005 11:33 am
Posts: 24
Location: Stuttgart, Germany
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


Top
 Profile  
 
 Post subject: setMaxResults w/ setResultTransformer
PostPosted: Fri Apr 21, 2006 12:42 pm 
Newbie

Joined: Tue Apr 18, 2006 9:36 pm
Posts: 6
Location: Los Angeles, CA
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Apr 21, 2006 1:13 pm 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
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?


Top
 Profile  
 
 Post subject: Re: setMaxResults w/ setResultTransformer
PostPosted: Mon Apr 24, 2006 5:57 am 
Beginner
Beginner

Joined: Mon Jan 24, 2005 11:33 am
Posts: 24
Location: Stuttgart, Germany
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


Top
 Profile  
 
 Post subject: Re: setMaxResults w/ setResultTransformer
PostPosted: Wed Mar 28, 2007 9:01 am 
Newbie

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,

_________________
IGOR BRITO ALVES
bonfarj@gmail.com
Cultura Nerd


Top
 Profile  
 
 Post subject: Re: Criteria: setMaxResults() + setResultTransformer() problem
PostPosted: Wed Jul 14, 2010 5:36 pm 
Newbie

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


Top
 Profile  
 
 Post subject: Re: Criteria: setMaxResults() + setResultTransformer() problem
PostPosted: Fri Feb 25, 2011 10:08 am 
Newbie

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


Top
 Profile  
 
 Post subject: Re: Criteria: setMaxResults() + setResultTransformer() problem
PostPosted: Wed Jul 04, 2012 3:14 am 
Newbie

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;

   }


Top
 Profile  
 
 Post subject: Re: Criteria: setMaxResults() + setResultTransformer() problem
PostPosted: Tue Apr 23, 2013 9:21 am 
Newbie

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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.