Pagination with Apache iBatis SqlMaps

Doing pagination with Apache iBatis SqlMaps is really simple and straightforward. Pagination is when a database connection is queried for a sublist of objects instead for the full list of objects. This is often the case for web application when it's not desirable to display the full contents of a database table to the users all at once. Hence the name pagination: the user can "scroll" through the results using pages. Each page contains a collection of objects and the pages usually are ordered.
With iBatis pagination directly uses the database cursor provided by the JDBC driver. It tries to use the ResultSet#absolute(seekPosition) to jump to the current page. If this does not work, e.g. the cursor is in forward-only mode, then a manual approach by iterating through the result set until the seek position is reached is enforced.

When iBatis SqlMapCLient is used directly in your data access object implementation, you probably use the query methods like

com.ibatis.sqlmap.client.SqlMapExecutor.queryForList(String, Object)

Other methods have additional parameters with which pagination is made possible:

com.ibatis.sqlmap.client.SqlMapExecutor.queryForList(String, Object, int, int)

The two integer parameters are used for the seek position ("skipResults") and the number of results to be retrieved before the page is finished ("maxResults").

So, let's go and write a demo test case. We begin with the setup of the SqlMapClient, which is the facade for working with iBatis SqlMap:

private SqlMapClient createSqlMapClient() {
		final InputStream config = getClass().getResourceAsStream(
				"/SqlMapConfig.xml");
		Assert.assertNotNull(config);
		final SqlMapClient sqlMapClient = SqlMapClientBuilder
				.buildSqlMapClient(config);
		return sqlMapClient;
}

After we've got the SqlMapClient configured (which includes the DataSource configuration), we're going to create some demo data in the database. The in-memory database HSQLDB is used in this example.

private void createDemoData(final SqlMapClient sqlMapClient)
			throws SQLException {
		// Create table and sample data
		try {
			sqlMapClient.startTransaction();
			sqlMapClient.update("CREATE");
			for (long pid = 1; pid <= 50; pid++) {
				final Product parameter = new Product();
				parameter.setId(pid);
				parameter.setDescription("Product #" + pid);
				sqlMapClient.insert("INSERT", parameter);
			}
			sqlMapClient.commitTransaction();
		} finally {
			sqlMapClient.endTransaction();
		}
}

Now, you probably want to see the SqlMapConfig.xml:

The SqlMapConfig.properties file:

driver=org.hsqldb.jdbcDriver
url=jdbc:hsqldb:mem:db_pagingtest
username=sa
password=

And the Product.xml mapping file, so iBatis knows which Java Property has to be mapped to which Database Column:

The SqlMap File contains additional statements which would normally not be in there: CREATE, which is only for the ease of testing, INSERT for setting up demo data and COUNT which is needed for the actual pagination. Of course, the SELECT statement is your main statement we're going to paginate. Please note that the SELECT statement has set an ordering. However, it does NOT have any database vendor specific functionality for pagination, such as MySQLs LIMIT keyword or Oracles ROWS keyword.

Let's go on with the example and continue the PaginationDemoTest class. The class should retrieve "Products" from the database and print it out page by page:

import org.junit.Test;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class PaginationDemoTest {
	@Test
	public void testPagination() throws Exception {
		final SqlMapClient sqlMapClient = createSqlMapClient();
		createDemoData(sqlMapClient);
		final Product queryParameter = new Product();
		// use example object for query parameters
		List result = sqlMapClient.queryForList("SELECT",parameter);
		System.out.println(result);
	}
	// See above for createSqlMapClient();
	// See above for createDemoData();
}

(Yes, i know i'm not supposed to use System.outs in test cases. But since this is an interactive example, we have to finish the whole test case before we can remove that System.out)

Another note about the queryParameter object: usually, you will have some query parameter object to parameterize your SQL statements. As this is not used in this example, i still added it to the code so you see where it would go in real implementations.

So, if we run this code, it will print out all 50 demo products created by createDemoData().
No pagination yet. The easiest way to add manual pagination is to add the skipResults and maxResults parameters:

List result = sqlMapClient.queryForList("SELECT",parameter,0,10);

Running the changed code will reveal that now ony the first 10 Product objects are printed.
Works fine, so let's clean up the code a bit and encapsulate the data access into a ProductDAO class:

package de.mhaller.ibatis.pagination;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

public class ProductDAO {

	private SqlMapClient sqlMapClient;

	public void setSqlMapClient(SqlMapClient sqlMapClient) {
		this.sqlMapClient = sqlMapClient;
	}

	public List queryProducts(Product parameter,
			PaginationContext paginationContext) {

		assert paginationContext.getSkipResults() >= 0;
		assert paginationContext.getMaxResults() >= 0;

		try {
			final List result = new ArrayList();
			try {
				sqlMapClient.startTransaction();
				performProductQuery(parameter, paginationContext, result);
				sqlMapClient.commitTransaction();
			} finally {
				sqlMapClient.endTransaction();
			}
			return Collections.unmodifiableList(result);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	@SuppressWarnings("unchecked")
	private void performProductQuery(Product parameter,
			PaginationContext paginationContext, final List result)
			throws SQLException {

		// Gather total number of results using a separate query
		if (!paginationContext.hasTotalCount()) {
			updateTotalCount(parameter, paginationContext);
		}

		// iBatis does physical pagination using database cursor if
		// available using ResultSet.absolute(position);
		final List queryForList = sqlMapClient.queryForList("SELECT",
				parameter, paginationContext.getSkipResults(),
				paginationContext.getMaxResults());
		result.addAll(queryForList);
	}

	private void updateTotalCount(Product parameter,
			PaginationContext paginationContext) throws SQLException {
		paginationContext.updateTotalCount((Integer) sqlMapClient
				.queryForObject("COUNT", parameter));
	}

}

A PaginationContext object has been added, so the PaginationDemoTest class can make use of a stateful object to traverse all pages in a loop:

		final ProductDAO productDAO = new ProductDAO();
		productDAO.setSqlMapClient(sqlMapClient);
		final PaginationContext paginationContext = new PaginationContext();
		while (paginationContext.hasMorePages()) {

			// Fast, eager and simple access to pagination
			List products = productDAO.queryProducts(queryParameter,
					paginationContext);

			// For demo: print results and switch to next page
			String msg = String.format("Results %s of total %d records",
					products, paginationContext.getTotalCount());
			System.out.println(msg);
			paginationContext.nextPage();
		}

The total count of records is gathered by the DAO class using the COUNT statement on first usage of the PaginationContext. If the PaginationContext is not yet initialized, the COUNT statement is executed and the total number of objects in that table is set into the context for later reuse.

The PaginationContext class simply holds the parameters (how large is the page and on which page are we currently?) and the total number of records:

package de.mhaller.ibatis.pagination;

public class PaginationContext {

	// Special value for initial state
	private static final int NO_TOTAL_COUNT = -1;

	// Start on first page
	private int skipResults = 0;

	// Display 10 results per page
	private int maxResults = 10;

	// The total number of records is calculated on first page
	private int totalCount = NO_TOTAL_COUNT;

	public int getSkipResults() {
		return skipResults;
	}

	public void setSkipResults(int skipResults) {
		assert skipResults >= 0;
		this.skipResults = skipResults;
	}

	public int getMaxResults() {
		return maxResults;
	}

	public void setMaxResults(int maxResults) {
		assert maxResults >= 0;
		this.maxResults = maxResults;
	}

	public void updateTotalCount(int totalCount) {
		assert totalCount >= 0;
		this.totalCount = totalCount;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public boolean hasMorePages() {
		if (!hasTotalCount())
			// No totalCount yet, so display first page
			return true;
		if (skipResults + maxResults > totalCount)
			return false;
		return true;
	}

	public void nextPage() {
		skipResults += maxResults;
	}

	public boolean hasTotalCount() {
		return totalCount != NO_TOTAL_COUNT;
	}

}

Running this code will result into the following output:

Results [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] of total 50 records
Results [11, 12, 13, 14, 15, 16, 17, 18, 19, 20] of total 50 records
Results [21, 22, 23, 24, 25, 26, 27, 28, 29, 30] of total 50 records
Results [31, 32, 33, 34, 35, 36, 37, 38, 39, 40] of total 50 records
Results [41, 42, 43, 44, 45, 46, 47, 48, 49, 50] of total 50 records

And to make it a unit test, replace the System.out with the following line in the PaginationDemoTest

Assert.assertEquals(10, products.size());

• Comment (1)
• Trackbacks (0)