Advanced Queries Using Criteria - Beginning Hibernate: From Novice to Professional - page 217

The other two sqlRestriction() methods permit you to pass JDBC parameters and val-
ues into the SQL statement. Use the standard JDBC parameter placeholder ( ? ) in your SQL
fragment.
Paging Through the Result Set
One common application pattern that criteria can address is pagination through the result
set of a database query. When we say pagination, we mean an interface in which the user
sees part of the result set at a time, with navigation to go forward and backward through the
results. A naive pagination implementation might load the entire result set into memory for
each navigation action, and would usually lead to atrocious performance. Both of us have
worked on improving performance for separate projects suffering from exactly this prob-
lem. The problem appeared late in testing because the sample dataset that developers were
working with was trivial, and they did not notice any performance problems until the first
test data load.
If you are programming directly to the database, you will typically use proprietary database
SQL or database cursors to support paging. Hibernate abstracts this away for you—behind the
scenes, Hibernate uses the appropriate method for your database.
There are two methods on the Criteria interface for paging: setFirstResult() and
setMaxResults() . The setFirstResult() method takes an integer that represents the first
row in your result set, starting with row 0. You can tell Hibernate to retrieve a fixed number
of objects with the setMaxResults() method. Using both of these together, we can construct
a paging component in our web or Swing application. We have a very small dataset in our
sample application, so here is an admittedly trivial example:
Criteria crit = session.createCriteria(Product.class);
crit.setFirstResult(1);
crit.setMaxResults(2);
List results = crit.list();
As you can see, this makes paging through the result set easy. You can increase the first
result you return (for example, from 1 , to 21 , to 41 , etc.) to page through the result set. If you
only have one result in your result set, Hibernate has a shortcut method for obtaining just
that object.
Obtaining a Unique Result
Sometimes you know you are only going to return zero or one objects from a given query.
This could be because you are calculating an aggregate (like COUNT , which we discuss later),
or because your restrictions naturally lead to a unique result—when selecting upon a prop-
erty under a unique constraint, for example. You may also limit the results of any result set
to just the first result, using the setMaxResults() method discussed earlier. In any of these
circumstances, if you want obtain a single Object reference instead of a List , the
uniqueResult() method on the Criteria object returns an object or null . If there is more
than one result, the uniqueResult() method throws a HibernateException .
The following short example demonstrates having a result set that would have included
more than one result, except that it was limited with the setMaxResults() method: