-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: How to reuse Criteria or DetachedCriteria objects?
PostPosted: Tue Jun 06, 2006 5:19 pm 
Beginner
Beginner

Joined: Tue May 23, 2006 10:53 am
Posts: 24
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1.3

Hi,

My query generally could return large number of rows, to control that, I set the setMaxResults() on the Criteria. But in the same time, I was requested to provide the actual number of rows that qualified for the query. Since there are a lot of unknown Restrictions will be added to my Criteria at run-time in the program, I'm trying to reuse the Criteria to get the returned rows and the actual count by setting the projection for count. However, I found whatever projection or restrictions set on the Criteria cannot be reset. Even I tried to use DetachedCriteria, and use getExecutableCriteria(session) separately, the underlying query still changed. How could I avoid construct the Criteria/Query from beginning for the count query? I cannot directly use the first query because some ordering constraint I put cannot be put on the select count(*) query.

Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 07, 2006 7:24 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
You should be able to "turn off" projections on a Criteria. Set up your full Criteria, including the count projection. When you run it now, you'll get the count. Then set its result transformer to anything other than PROJECTION (DISTINCT_ROOT_ENTITY is the usual choice) to "turn off" the projection(s).

Once you have it set up like this, you can toggle between the projection and non-projection versions of the Criteria by changing the result transformer.

_________________
Code tags are your friend. Know them and use them.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 08, 2006 9:21 am 
Beginner
Beginner

Joined: Tue May 23, 2006 10:53 am
Posts: 24
Unfortunately, it doesnot work. :( The projection is not turned off. Below is my code and Hibernate generated SQL:

Criteria crit = QueryHelper.getCriteria(session, Security.class,
sObj, loadID);

// Retrieve the actual number of rows that qualified for the query.
crit.setProjection(Projections.rowCount());
Integer count = (Integer)crit.uniqueResult();
qr.setNumOfActualRows(count);

crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
if (maxReturnRows > 0)
crit.setMaxResults(maxReturnRows);
crit.addOrder(Order.asc("id.externalSecurityID"));

List securities = crit.list();

Hibernate: select count(*) as y0_ from T_PUB_SECURITY this_ where this_.IhubLoadId=? and ((this_.ExternalSecurityId in (?)))

Hibernate: select top 100 count(*) as y0_ from T_PUB_SECURITY this_ where this_.IhubLoadId=? and ((this_.ExternalSecurityId in (?))) order by this_.ExternalSecurityId asc

Here's the error:

09:15:12,856 WARN JDBCExceptionReporter:71 - SQL Error: 8126, SQLState: HY000
09:15:12,856 ERROR JDBCExceptionReporter:72 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column name 'this_.ExternalSecurityID' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
09:15:12,856 WARN JDBCExceptionReporter:71 - SQL Error: 16945, SQLState: HY000
09:15:12,856 ERROR JDBCExceptionReporter:72 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The cursor was not declared.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 08, 2006 9:24 am 
Beginner
Beginner

Joined: Tue May 23, 2006 10:53 am
Posts: 24
Unfortunately, it doesnot work. :( The projection is not turned off. Below is my code and Hibernate generated SQL:

Criteria crit = QueryHelper.getCriteria(session, Security.class,
sObj, loadID);

// Retrieve the actual number of rows that qualified for the query.
crit.setProjection(Projections.rowCount());
Integer count = (Integer)crit.uniqueResult();
qr.setNumOfActualRows(count);

crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
if (maxReturnRows > 0)
crit.setMaxResults(maxReturnRows);
crit.addOrder(Order.asc("id.externalSecurityID"));

List securities = crit.list();

Hibernate: select count(*) as y0_ from T_PUB_SECURITY this_ where this_.IhubLoadId=? and ((this_.ExternalSecurityId in (?)))

Hibernate: select top 100 count(*) as y0_ from T_PUB_SECURITY this_ where this_.IhubLoadId=? and ((this_.ExternalSecurityId in (?))) order by this_.ExternalSecurityId asc

Here's the error:

09:15:12,856 WARN JDBCExceptionReporter:71 - SQL Error: 8126, SQLState: HY000
09:15:12,856 ERROR JDBCExceptionReporter:72 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column name 'this_.ExternalSecurityID' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
09:15:12,856 WARN JDBCExceptionReporter:71 - SQL Error: 16945, SQLState: HY000
09:15:12,856 ERROR JDBCExceptionReporter:72 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The cursor was not declared.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 08, 2006 5:19 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Hmm. I can't see any reason in the code for it not to work. Try this, perhaps the projection in the CriteriaImpl has to be null:
Code:
// Retrieve the actual number of rows that qualified for the query.
crit.setProjection(Projections.rowCount());
Integer count = (Integer)crit.uniqueResult();
qr.setNumOfActualRows(count);

crit.setProjection(null);
crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
if (maxReturnRows > 0)
crit.setMaxResults(maxReturnRows);
crit.addOrder(Order.asc("id.externalSecurityID"));
Note that the setProjection(null) is before the call to setResultTransformer: setProjection unconditionally sets the result transformer to PROJECTION.

_________________
Code tags are your friend. Know them and use them.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 08, 2006 5:26 pm 
Beginner
Beginner

Joined: Tue May 23, 2006 10:53 am
Posts: 24
tenwit:

Thanks a lot! It works!


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 12, 2006 10:48 am 
Newbie

Joined: Mon Jun 12, 2006 10:32 am
Posts: 3
crit.setProjection(null); works fine ... but once i set projection to null the next time i use crit.setProjection(Projections.rowCount()); the result is always null. I dont want to create another criteria cause i lose all my restrictions. Any idea?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 12, 2006 6:10 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Nope. Turn on sql output and post the resulting SQL. Also, try replacing the rowCount() with another projection.. even Projections.count(Property.forName("SomeProperty")), which should produce the result you need, if you pick the right property.

_________________
Code tags are your friend. Know them and use them.


Top
 Profile  
 
 Post subject: Re: How to reuse Criteria or DetachedCriteria objects?
PostPosted: Tue Aug 10, 2010 11:28 am 
Newbie

Joined: Mon May 11, 2009 3:15 pm
Posts: 4
This not work for below versions of hibernate tools.
I tired something like this:
Code:
       //dc is a DetachedCriteria with my criteria.       
       Criteria crit = dc.getExecutableCriteria(getSession())
                .setProjection(Projections.rowCount());
       p.setTotalResults((Integer)crit.uniqueResult());
       log.info("new total result:" +p.getTotalResults());

       crit.setProjection(null);
       crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
       crit
       .setFirstResult(p.getFirstResult())
       .setMaxResults(pageSize)
       .addOrder(Property.forName("startDate").desc());
       p.setResults(crit.list());

I got right TotalResult but result list is lower then pageSize.
How can I solve this in below version of hibernate?

Code:
     <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>3.3.0.GA</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-tools</artifactId>
            <version>3.2.3.GA</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>3.4.0.GA</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-annotations</artifactId>
            <version>3.4.0.GA</version>
        </dependency>


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 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.