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!

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.

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.

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.

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:Note that the setProjection(null) is before the call to setResultTransformer: setProjection unconditionally sets the result transformer to PROJECTION.

tenwit:

Thanks a lot! It works!

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?

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.

This not work for below versions of hibernate tools.
I tired something like this:

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