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

in the PLACED_ORDER table whenever RESTAURANT.NAME was updated. Here is the
simplified query:
SELECT o.order_id, o.restaurant_name, …
FROM PLACED_ORDER o
WHERE o.DELIVERY_TIME > (SYSDATE - 30)
ORDER BY o.ORDER_EXT_ID DESC
The query now just references the PLACED_ORDER table and gets the restaurant's
name from the RESTAURANT_NAME column. Because this is a simple query, the bene-
fits of eliminating the join are relatively small. However, for more complex queries
the performance gains can be large.
Writing queries that access denormalized columns is straightforward if you are
using JDBC or i BATIS , but in section 11.3.4 you will see that mapping denormal-
ized columns to the fields or properties of a domain model can affect it in
unpleasant ways.
Using the ROWNUM pseudo column
Normally, you want a query to return all of the matching rows in the database, but
search screens are different. They display one page of items at a time, and so the
application only wants a particular range of the rows from the database. One way
an application that uses JDBC can get a subset of rows is by skipping over the rows
in the JDBC ResultSet that it does not want and loading the rows that it does want.
Here is an example of JDBC code that does this:
PreparedStatement ps = …
ResultSet rs = ps.executeQuery();
rs.absolute(11);
int count = 10;
while (count-- > 0 && rs.next() {
…
}
This code uses ResultSet.absolute() to position the cursor on the 11 th row and
then iterates through the ResultSet , getting the next 10 rows. One potential per-
formance problem with this approach is that the database might find all of the
rows when it executes the query even though the application only wants a few of
them. Another drawback is that some JDBC drivers will read the rows that are
skipped over from the database, which is inefficient because they are transferred
from the database and then discarded.
An alternative approach, which avoids this problem, is to use a database-
specific SQL feature that restricts the range of rows returned by the query. Only
some databases have this feature, and it is implemented differently by each one.