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

If an application stores data, then you can be certain that users need to access that
data in digestible chunks. All of the enterprise applications that I've developed
have featured search screens that allow the user to enter search criteria and page
through and sort long lists of items such as orders, mobile devices, and financial
transactions. Implementing these search screens was difficult. The applications
had to handle result sets consisting of hundreds of thousands of rows that were
too large to load into memory and to display on a single screen. They had to
dynamically generate database queries from the search criteria entered by the
user. These queries had to be carefully optimized in order to achieve acceptable
performance because the tables they accessed contained millions of rows.
The burden of implementing efficient paged queries falls on the persistence
layer. When entity beans and JDBC were the only available options for accessing
the database, I had always implemented them by using JDBC to execute SQL
directly. Even though the code was very low-level and pretty messy, using JDBC was
easier than using esoteric vendor-specific extensions to EJB 2.0 CMP . However, as
you will see in this chapter, you now have more options. You can sometimes imple-
ment dynamic paged queries using JDO and Hibernate queries. They allow you to
optimize queries and handle large result sets. What's more, if you must resort to
SQL you can execute it using Hibernate or JDO . Alternatively, you can use i BATIS ,
which lets you dynamically generate queries with very little Java code.
In this chapter you will learn how to implement dynamic paged queries using
i BATIS , JDO , and Hibernate. We describe a paging mechanism that avoids having
to load an entire result set into memory, and provide an overview of the various
ways to optimize the performance of SQL queries. In addition, we explain how to
configure JDO and Hibernate to use efficient queries. We show how to apply these
techniques by implementing the database access code for the View Orders use
case using the i BATIS , JDO , and Hibernate persistence mechanisms.
11.1 Key design issues
When you're implementing dynamic paged queries, you must solve a number of
design problems. For example, you have to choose from one of several ways of
implementing a paging mechanism, each with its own benefits and drawbacks.
You must also decide the best way to generate the queries, which can be a mainte-
nance headache. Finally, you should have a good understanding of how to opti-
mize SQL queries because that can dramatically impact the performance of the
application. This section provides an overview of these issues, but first let's look at
an example.