Is there a way within a construction of a single criteria object (and therefore a single sql statement) that not only gets a page of data with a firstResult, maxResults setting, but also a Projection.rowCount()?

I was on Matt Riable's wiki and saw his post:
http://raibledesigns.com/wiki/Wiki.jsp?page=HibernatePagination

He has code that looks like this:



However, I cannot get that to work on Criteria objects. The returning resultset is always empty. When I remove the last projection line, I always get results so I know the criteria is good. I thought perhaps that "numResults" was a reserved keyword since there is no context as to what it is. But I guess it is not.

(tried this)


I have posted a question on the Oracle Forums to see if this is even possible and I received very favorable opinions which lead me to believe that from a sql perspective it is possible.

http://forums.oracle.com/forums/thread.jspa?threadID=510229

So, does anyone know how to do this in a Hibernate criteria object? The reason why I want to know is that the cost of the query is rather expensive (triple join) and we want to minimize a performance hit with two sql statements for the same info - one to get the page of data and one to get the total available.

Anyone know how to do this?

Thanks in advance,
Mark

I have the same problem.

Did you find a solution to this issue?

Thanks in advance.

Unfortunately, no.

I ended up just splitting it into two separate sql statements like so:

This thread is a bit old, but I'm using this same technique for automatically paging Criteria queries and ran into a problem. If the passed criteria has already a projection, this technique overwrites the original criteria when it sets the the projection to null. To avoid this situation, what I did is to save the original Projection and ResultTransformer, seting the count projection and then restoring the original Projection and ResultTransformer like this:




The ResultTransformer has to be saved and then restored, because setting the count projection also changes the original ResultTransformer.

This is working for me, but I would like to know if there is a better/cleaner way of doing this (i.e. without modifying the original criteria).
I'm also not 100% sure that restoring the original Projection and ResultTransformer is all that is needed to return the criteria to its original state.

Any feedback would be greatly welcomed.

P.S. Using Hibernate 3.2.6

Hi.

For now I found two solutions:
1. save Projection and ResultTransformer before getting rows, and then restore it
2. use second criteria to get count of rows

About first solution: I'm not sure that restoring Projection and ResultTransformer only needed to get original criteria state
Is it some better solution to get count of rows from criteria and use criteria later?

This feature is very usable for pagination: get number of all results and then set restrictions for the current page number and page size.
In JIRA I've found feature request, but it still not implemented: https://hibernate.onjira.com/browse/HHH-1046