Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 437

Implementing paging in Hibernate
Hibernate provides two ways of selecting a range of rows from a result set when
executing criteria queries. The easier approach is to tell Hibernate the desired
range of rows using the Criteria.setFirstResult() and Criteria.setMax-
Results() methods and then execute the query using Criteria.list() , which
uses a ROWNUM SELECT statement and returns a list containing the specified rows.
The other option is to execute the query using Criteria.scroll() , which returns
a ScrollableResults that wraps the JDBC ResultSet . The application can then
navigate the ScrollableResults , selecting the rows that it needs.
Here is an example of a query that uses Criteria.list() to retrieve orders 100-
199 that are for delivery in San Francisco:
List orders = session
.createCriteria(Order.class)
.add(Restrictions.eq("deliveryAddress.city", "San Francisco"))
.setFetchMode("restaurant",FetchMode.JOIN)
.setFirstResult(100)
.setMaxResults(100)
.list()
This example calls setFirstResults() to specify the first order to return and set-
MaxResults() to specify how many orders to return. Hibernate retrieves the speci-
fied rows in two ways. If supported by the database, Hibernate generates a SQL
SELECT statement that uses a ROWNUM -like feature to return only the specified rows
using a database-specific SQL feature. If the database does not support this fea-
ture, then Hibernate executes a SELECT statement that retrieves all rows and then
navigates the JDBC ResultSet and picks out the specified rows.
Instead of using list() , the application can use scroll() and navigate Scrol-
lableResults and select the required rows. Here is an example code fragment
that uses scroll() to retrieve orders 100-199:
ScrollableResults results = session
bb .createCriteria(Order.class)
bb .add(Restrictions.eq("deliveryAddress.city", "San Francisco"))
bb .setFetchMode("restaurant", FetchMode.JOIN).scroll();
List orders = new ArrayList();
int pageSize = 100;
if (results.first() && results.scroll(100)) {
bb for (int i = 0; i < pageSize; i++) {
bbbbb bb orders.add(results.get(0));
bbbbb bb if (!results.next())break;
bb }
}