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

The application does not have a consistent view of the database because it
can change between queries.

■

This strategy is best used when the queries return only a modest number of rows.

Holding onto database connections
Instead of loading all of the data in one go, another approach is for the applica-
tion to hold onto a database connection and iterate through the JDBC ResultSet
as the user pages through the list of orders.
This approach offers these benefits:

It uses a single query, which is efficient and ensures a consistent view of the
database.

■

It does not store a large result set in the session state.

■

The database doesn't have to find all of the matching rows immediately and
so it can execute the query lazily.

■

as well as the following drawbacks:

It does not scale to support a large number of users since database connec-
tions are a precious resource in a J2EE application.

■

The long-running query could potentially lock a large number of rows.

■

Because of these limitations, this approach is rarely practical.

Using multiple queries
As we have just seen, in most applications the result set is too large to load into
memory in its entirety. Furthermore, it's usually impractical for the application to
hold onto database connections across user requests. Consequently, a more scal-
able approach is for the application to repeatedly query the database as the user
pages through the list of items. There are a couple of different ways to do this. One
option is for each query to return one page of data. Each time the user clicks on
the next or previous page button, the application retrieves another page of data
from the database. Another option, which reduces the number of database
accesses and improves the response time, is for each query to retrieve multiple
pages of data, which are then cached by the application as part of the session state.
Here are the benefits of this approach:

It is scalable because it only uses a database connection for the duration of
each HTTP request.

■