What is difference between setMaxResults and setFetchSize in org.hibernate.Query?

Solution 1:

setMaxResults is the same as LIMIT in SQL- you are setting the maximum number of rows you want returned. This is a very common use case of course.

setFetchSize is about optimization, which can change how Hibernate goes about sending the results to the caller (example: buffered, in different size chunks). setFetchSize is NOT implemented by all database drivers.

Solution 2:

setMaxResults limits the number of results the query will ever get.

setFetchSize tells the jdbc driver how many rows to return in one chunk, for large queries. Say you want 1000 rows. If you set the fetch size to 100, the db will return 100, then another 100 when you want more, and so on. setFetchSize will do nothing if your driver does not support it.

Solution 3:

For example, if the table has 100 records, then

criteria.setMaxResults(25);

will only fetch 25 records out of 100 records, and

criteria.setFetchSize(20);

will fetch 20 records at each time until it reads all 100 records from the table.