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

A query can return a result set that is too large to display on one page or
load into memory. The application must implement a paging mechanism
that loads and displays part of the result set.

■

There are usually too many permutations of search criteria to use a set of
static queries. The application must generate queries dynamically, which
requires conditional logic that is messy to write and difficult to maintain.

■

Queries can require careful optimization in order to achieve acceptable
performance because if the database is large, even simple queries can take
many seconds to execute if they are not tuned correctly.

■

In this section we'll delve into the details of these design issues and describe some
solutions that you can use in an application that executes SQL statements directly
using either i BATIS or JDBC . In a later section we'll cover the additional problems
that must solved when implementing this kind of use case using JDO and Hibernate.

11.1.1 Implementing a paging mechanism

Many applications let the user page backward and forward through a list of results
that is too long to display on a single screen. There are several different ways an
application can implement a paging mechanism. Table 11.1 lists the options
along with their benefits and drawbacks.

Table 11.1 Paging options

Option Benefits Drawbacks When to use it

Retrieve all the rows in
one query

Consistent reads
Executes a single
bb expensive query

Cost of storing the data
Cost of executing the query

Small result sets

Retrieve primary keys Executes a single

bb expensive query and
bb multiple cheap ones

Inconsistent reads
Space overhead of storing
bb the primary keys
Cost of executing the query

Modest result sets

Lazily iterate through
the JDBC ResultSet

Executes a single query
Consistent reads
Avoids reading a lot of
bb data from the data-
bb base in one go

Severely limits the scalabil-
bb ity of the application
Long transaction could lock
bb a large number of rows
Long-running serializable
bb transaction could fail

Applications that
bb only have a small
bb number of users

Repeatedly query the
database

Stateless
Retrieves only the data
bb that will be displayed

Repeatedly querying the
bb database
Inconsistent reads

Large result sets
Large numbers of
bb users