6 SQL query result pagination

When one SQL query, result to too many data, we need to do pagination for the result data. Only a few data can be seen one time. Because too many data is useless for human eyes.

But, this is a un-solved issue in ANSI SQL. Mysql/PostgreSQL have their own SQL to solve this among the populate databases. The is somehow, one of the reason that Mysql is widely used in web applications. For it's a big issue of reducing page size in web applications.

SQL standard 2003 gives us new Window function: ROW_NUMBER() OVER () , databases like SQL server 2005/DB2/Oracle support it. Oracle has a little difference with standard. And Mysql/PostgreSQL don't support ROW_NUMBER().

This is enough. VelocityWeb brings us a uniform interface to support pagination of many database. It also supports special SQL such select distinct/ group by. And codes in internet normally don't support these.

Pagination contains two parts: get current page data, get total record count. And we should known that it's necessary to sort data for getting current page data which means 'order by' in SQL. It's meaningless without sort.

There are some class, all implement the interface of PageDialect: StandardPageDialect(support SQL Server 2005,DB2), SQLServer2000PageDialect, OraclePageDialect, MySQLPageDialect, PostgreSQLPageDialect.

Here is an example:

sql = new StringBuffer();
sql.append("SELECT i.itemid, qty");
sql.append(" FROM inventory v,item i");
sql.append(" where i.itemid = v.itemid");
sql.append(" and i.itemid > ?");
sql.append(" and v.itemid > ?");

orderBy = "i.itemid,v.qty desc";

paraList = new LinkedList();
paraList.add("aa");
paraList.add("bb");

countSql = dialect.getCountSql(sql.toString());
count = (Number)this.queryScalar(countSql, paraList.toArray(), 1);

pageSql = dialect.getPageSqlWithoutPageInfo(sql.toString(), orderBy);
values = dialect.getPageParemeterValuesWithPageInfo(pageIndex, pageSize);
paraList.add(new Long(values[0]));
paraList.add(new Long(values[1]));

list = this.queryBeanList(pageSql, paraList.toArray(), Inventory.class);

It quite good till now.

VelocityWeb make it more simple. If we use JDBC as DAO, we can write like this:

List resultDataList = new LinkedList();
int recordCount = JdbcPageUtils.getPageData(sql.toString(), paraList, orderBy,
pageIndex, pageSize, con, dialect, resultDataList);
Each item of current page data in resultDataList is Object[].

If we use JdbcDao of VelocityWeb, we can write like this:

List resultDataList = new LinkedList();
int recordCount = VelocitywebPageUtils.getPageData(sql.toString(), paraList, orderBy,
pageIndex, pageSize, dao, dialect, Inventory.class, resultDataList);
Each item of current page data in resultDataList is Inventory object.