RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Paging in J2EE: Manage Large Result Sets Efficiently : Page 3

Learn how to manage paging for unbounded queries over large result sets—without blowing up your server. Strike the right balance between resource utilization and response-time requirements to satisfy your user demands.

Put Query-based Paging into Practice
Once you have established your basic query-based paging algorithm, put it into practice by creating a generic, reusable paging component that can be used for any arbitrary simple or complex query. Your paging framework should provide a clear interface, a simple API, and it should encapsulate the low-level internals of the paging implementation and algorithm. It should handle data fetching, transport, row IDs and parameter passing, query manipulation, as well as simple search criteria parameter substitution.

One way to set up this framework is to adopt a configuration-driven or declarative approach in which you pre-define the search as a logical paginated view in a properties file and supply the actual search parameters at runtime with the request. In your configuration file, you can make the view explicitly identify the row IDs, ORDER BY columns, and any other information deemed necessary for your paging implementation. To issue a search, the caller doesn't have to do any fancy SQL footwork. It just identifies and loads the desired view using the view's unique identifier and then issues the search/fetch request along with any user-supplied criteria.

A view is just a logical window across one or more physical DBMS tables and views. A paginated view is a view that contains additional discriminating information (such as row IDs) that enables it to be paged. Configure your paginated view in a properties file and define the forward paging direction (ORDER BY's) and row ids.

The following steps walk you through the creation of a paging component. For a complete package of all the required Java classes, download the source code.

Step 1. Create/configure your paginated view

# Example view
example.rowids=col2 ASC,col3 DESC

Step 2. Create a class to represent your view, PageDefn
Create a Java class (PageDefn) to represent your paginated view. You can either load it from your properties file or create it on the fly in your code. Configuration is the preferred approach, as the caller doesn't have to know about the view internals or table meta-data. Your PageDefn contains all the information relevant to your paginated view and is responsible for handling the SQL construction details:

public class PageDefn extends ViewDefn {

	public interface PageAction {
		public static final int FIRST = 0;
		public static final int NEXT = 1;
		public static final int PREVIOUS = 2;
		public static final int CURRENT = 3;

	protected int pageSize;
	protected ColumnDesc[] rowIds;

	public PageDefn() {
		rowIds = null;
		pageSize = 50;


	public PageDefn(String viewname) {
		rowIds = null;
		pageSize = 50;



Step 3: Create a paging data access object (DAO)
Create a special-purpose PagingDAO (see Listing 1: PageDefn.java) that supports parameter substitution and uses Prepared statements. Prepared statements are faster than normal queries as they can be pre-compiled by the DBMS. Your PagingDAO executes the paging query and handles the results. The actual SQL construction is delegated to the PageDefn. This separation of concerns allows you to evolve the PageDefn and its subclasses to support more sophisticated query configuration independently of the DAO.

Ensure that your DAO closes all database resources (ResultSets, Statements, and Connections) once the query has been executed. Put your close code inside a FINALLY clause so that your resources will close even when an exception is thrown.

Step 4. Create a PageContext and Page command
Create a PageCommand class to encapsulate your page request and hold the results. Client servlets and actions will use the PageCommand to interact with your paging framework. Your command should provide methods to do the following:

  • Specify the target view (PageDefn)
  • Supply optional search criteria (query parameters)
  • Specify the page action (FIRST, CURRENT, NEXT, or BACK)
  • Access the page results

In addition, your PageCommand should encapsulate any context information required by the paging implementation to support your paging algorithm. Create a context object that holds your paging request action and results, and encapsulates your paging internals:

public class PageContext implements Serializable {

	protected Object page; // results
	protected Object firstEntry; //  first row ID 
	protected Object lastEntry; // last row id
	protected int action; // paging action
	protected PageContext prevContext; // previous context state

	public PageContext() {
		this.page = new Object[0];
		this.firstEntry = null;
		this.lastEntry = null;
		this.action = PageDefn.PageAction.FIRST;

	public Object[] getPage() {
		return ((Collection) page).toArray();

	public void setPage(Object page) {
		this.page = page;

	public int getAction() {
		return action;

	public void setAction(int action) {
		this.action = action;

Ensure that your PageContext object is Serializable, as it will be passed on the wire back and forth between the Servlet and middle tiers.

Create your PageCommand class to encapsulate your paging requests, and include the PageContext as a member variable:

public class PageCommand extends ViewCommand {

	protected PageContext context;



The PageCommand class is just a data transfer object (DTO) that conveys request parameters and holds page results. Page data and row-IDs are held inside the PageContext object. The caller doesn't have to know about the PageContext attribute directly except to remember to re-use it across page requests for a given read request.

Step 5. Create a PagingService
Create a specialized paging service (see The COR Pattern Puts Your J2EE Development on the Fast Track for more information on COR services and configuration) for handling PageCommand requests. It will process PageCommands and pass them to the PagingDAO for processing:

public class PageService implements Service {

	public Command process(Command command) throws ServiceException {
		if (!command.getClass().equals(PageCommand.class)) {
			return null;

		PageCommand pcmd = (PageCommand) command;

		PageContext context = pcmd.getContext();
		PageDefn desc = (PageDefn) pcmd.getDefn();

		// Get A DAO    
		PagingDAO dao = PagingDAO.get();
		// Issue Query, results are set into the context by the DAO
		try {

			dao.executeQuery(desc, context, true);
			return pcmd;
		} catch (DAOException e) {
			throw new ServiceException(e);



Step 6. Exercise your Page command!
To fetch the first page, create an instance of PageCommand, load your target view, set any user-supplied parameters and page action, and issue the command to the CORManager. The CORManager will transparently route the command to the PageService to handle:

// load view and set user supplied criteria
PageDefn d = PageDefnFactory.getPDF().createPageDefn(bundle,view);
d.setParams(new Object[] { criteria });
PageCommand cmd = new PageCommand(d);

// fetch the first page
cmd = (PageCommand) CORManager.get().process(cmd);

// process results
PageContext context = cmd.getContext();

// Process results
Object[] rows = cmd.getContext().getPage();
if (rows == null) return;
for (int i = 0; i < rows.length; ++i) {
	System.out.println("ROW(" + i + ") " + rows[i].toString());

// cache context to be reused for subsequent pages..

To fetch the next page, insure that you reuse the PageContext from the previous request. The PageContext contains all the information the paging mechanism needs for qualifying your queries. If you're managing the paging from the servlet tier, cache the PageCommand/PageContext object on the HttpSession so that you can reuse it when the user pages forward or back:

// Create PageDefintion
PageDefn d = PageDefnFactory.getPDF().createPageDefn(bundle, view);

// Retrieve context from ServletContext
PageContext c = (PageContext) getServletContext().getAttribute(