Hi

I have written an application with search screens where I use Hibernate's 2.1 Criteria API to dynamically generate select queries. I use setFirstResult() and setMaxResult to implement paging of result set, because there can be a lot of records in database.

Now I need to get total number of records that satisfy given criteria. So I can display something like "x-y from total z records". Which would be the best way to get total number of records ? I have some ideas, for example:

1) If it's possible to get SQL statement generated by Hibernate (?) I could modify by replacing fields with "count (*)"

2) It's also possible to read all records from db and display just a subset, but it would have very bad performance.

Any ideas ?

or use criteria with projections

http://www.hibernate.org/hib_docs/v3/reference/en/html/querycriteria.html#querycriteria-projection

How can you add a projection, or otherwise get the count, but leave the original Criteria unchanged?

My page results object takes a DetachedCriteria in the constructor, but the getCount() query needs to add the count Projection. I can't seem to undo that addition.

this is a workaround to leave the criteria unchanged for later query. This needs Scrollable result sets.

What you need to do is.



rowNumber + 1 should be what you are looking for.

Thanks, that worked great!

how about rating the post then

I'm not the OP, you'll have to ask MarisO about that.