Is there a clean way to determine number of rows that WOULD have been returned if setMaxResults was not specified? Along the lines of MySQL's "SELECT SQL_CALC_FOUND_ROWS ..." and "SELECT FOUND_ROWS()". Obviously, I need this to allow for pretty pagination through a large list of items. Regards!

i do ScrollableResult and do it like this :
ScrollableResults scr = query.scroll();
src.last();
int size = scr.getRowNumber();


see faq example for another way

Wouldn't scroll() load all of the data and be just as slow as loading it all explicitly anyways?

Ans what "other" way in the FAQ are you referring to? Using "select count(*) ..."? I figured I could do that, but I was also wondering if hibernate had some sort of a framework for paging through collections of items.

no, scroll make cursor only - i play with about 200000 rows and it work fine
scroll.last() is longer and it populate last row -
"select count(*) can be better (maybe), but you have to make to queries (one for count and
other for true query)
i prefer scroll/last combination

snpesnpe, thank you for the helpful response. I will definitely play with the scroll() idea!

I use MySQL, and the scroll solution is a very bad idea on MySQL, I found that it was very slow. There is a much cleaner and more elegant approach, use a projection:



Notice the setProjection(null) and setResultTransformer which set the criteria query back to how it was.

(I do this before I set the max results).

This of course only works if you use the Criteria API, but I find that this API is quite useful for the typical cases where I need pagination anyway.[/code]

When I tested this solution against the scrolling solution (on mysql, which doesn't have cursors), I found that the scroll solution used the same amount of time as when I simply called list(), i.e. queried for all matching projects. The rowCount solution given here simply executes a COUNT(*) so it's much faster.

Projections.rowCount() sure did the trick. Thanks. What a beautiful solution!

scroll is slow on non-cursor oriented databases like mysql
this databases load complete query (for every query) in memory and it work very bad
for large databases - you can't do 'select * from table_name' for big table - you get
'out of memory' - postgresql is non-cursor, too, but it have hack for cursors in newer versions
i know that oracle work perfect for big tables - you call 'select * from table_name' and
database load X rows (fetchSize in java, but it is same for every client) - you can see
first Y rows (walk cursor) and see what you interest (all rows are never in memory in one moment)

non-cursor database do hack for this cases with limit clause, but it isn't sql keyword and it is unnecessary for true cursor's database

Are there a way to read the current database name through hibernate API?
It would be usefull for this use case :
if (mysql)
count;
else scroll;

or to read a property telling if the database support scrolling through cursor?

this is one possibility :

configuration.getProperty(Environment.DIALECT).equals("org.hibernate.dialect.MySQLDialect")

you remember configuration in HibernateUtil

unfortunately this does not work, if the criteria uses order by statements (at least not on my postgres-database), because hibernate will create a query like this:

which will cause an sql-exception.

to work around this I use a nasty hack. Where I must cast my criteria to CriteriaImpl fro iterateOrderings.
Then I remove all of them, execute the count and reapply the ordering before executing the list().

I really wished Hibernate would provide a paging functionality - then the HibernateDialect could use the most appropriate way for each database.

Another solution is the following:
1. run your criteria.list() without setting any alias => the referenced sets/list of the root entity will be filled with proxies => here you set correctly the max results and such
2. run the alias criteria on its own in the same hibernate session => the above proxies will be initialized

something like this:



Hope this can help,
Stijn