I’ve signed an account at MyJavaServer.com. It provides HSQLDB database services. The version of HSQLDB is 1.61. My problem comes here. I want to break up the resultsets retrieved from database into multiple pages, each page only displays specified subset of rows. However, One limitation of HSQLDB 1.61 is that it does not support LIMIT clause in the SELECT statement like MySQL. On the other hand, I don’t want to use pagination taglibs. I’ve been searching the solution for a long time, but no luck. Finally, I implemented the pagination using BETWEEN and AND sql clause.
This paging solution assumes that the table must have a primary key. In my example, I will create a table called bookmarks with the primary key id as follows:
CREATE TABLE BOOKMARKS(
ID INTEGER PRIMARY KEY,
NAME VARCHAR,
URL VARCHAR,
DESCRIPTION VARCHAR
)
The pagination approach will be something like this:
// the first page
SELECT * FROM bookmarks WHERE id BETWEEN 1 AND 10
// the second page
SELECT * FROM bookmarks WHERE id BETWEEN 11 AND 20
To implement the pagination, I will use four variables:
- numofrec - the total number of records
- recPerPage - the number of rows per page
- pageno - the page number
- url - the page URL
The variables numofrec and pageno will be appended to request parameters of the page when generating the page index. It will be something like this:
pagination.jsp?pageno=5&numofrec=1000 (without other parameters in the page URL)
or
pagination.jsp?otherparam=value&pageno=5&numofrec=1000 (with other parameters in the page URL)
When the page is requested the first time, count the total number of records and save the value into a variable. As I mentioned above, this value will be appended to the request parameter called numofrec, When the page number is greater than 1, the total number of records will be retrieved directly from this request parameter instead of count it again.
if (iPageNo == 1) {
rs = stat.executeQuery(“SELECT COUNT(id) AS cnt FROM bookmarks”);
if (rs.next() && rs.getString(1) != null) {
numOfRec = Integer.parseInt(rs.getString(1));
}
}
The next work is to find out the lowest primary key and highest primary key according to the current page number. These two values will be assigned to the parameters of the following sql statement:
SELECT * FROM bookmarks WHERE id BETWEEN ? AND ?
To achieved this, first count the start index to retrieve the lowest primary key, then offset the cursor away from the start index by the recperpage amount, and retrieve the highest primary key:
int iFrom = (iPageNo - 1) * recPerPage + 1;
int iOffset = recPerPage - 1;
if ( (iFrom + iOffset) > numOfRec ) iOffset = numOfRec - iFrom;
rs = stat.executeQuery(“SELECT id FROM bookmarks ORDER BY id”);
rs.relative(iFrom);
String sLowestId = rs.getString(1);
rs.relative(iOffset);
String sHighestId = rs.getString(1);
In order to improve the performance, I limit the number of rows returned for the query used to retrieve the lowest primary key and highest primary key using Statement->setMaxRows(int max).
stat.setMaxRows(iPageNo * recPerPage);
For instance, if the rows per page is 10, when fetching the first page, the query will return 10 rows, when fetching the second page, it will return 20 rows. (I have no better ideas for this currently, any good suggestions will be appreciated).
Now fetch the specified rows of the requested page:
pst = conn.prepareStatement(“SELECT * FROM bookmarks WHERE id BETWEEN ? AND ? ORDER BY id”);
pst.setString(1, sLowestId);
pst.setString(2, sHighestId);
rs = pst.executeQuery();
Finally, generate the page index using the four parameters: numOfRec, recPerPage, PageNo and url. I’ve created a JavaBean to achieve this:
package org.esurfer.util;
public class PaginationUtil {
/*
function: Pagination
author: esurfer
date: 2005-10-01
parameter:
1. numOfRec — the total number of records
2. recPerPage — the number of rows per page
3. currPage –current page number
4. url — the requested url
return: pagination links with First, Prev, Next, Last, Go to
*/
public static String Pagination(int numOfRec, int recPerPage, int currPage, String url) {
StringBuffer ressurl = new StringBuffer();
String spChar = “?”;
if (url.indexOf(spChar) != -1) spChar = “&”;
if (numOfRec > recPerPage) {
// Get total number of pages
int pages = (int)java.lang.Math.ceil((double)numOfRec / (double)recPerPage);
ressurl.append(“<div nowrap>n”);
ressurl.append(“<font face=arial size=-1>”);
ressurl.append(“Results Page:”).append(currPage).append(” / “).append(pages);
// Display link to first page
ressurl.append(“ <b><a href=”").append(url).append(spChar).append(“pageno=1&numofrec=”);
ressurl.append(numOfRec).append(“”>First</a></b>n”);
// Link to previous page
if (currPage > 1) {
ressurl.append(“| <b><a href=”").append(url).append(spChar).append(“pageno=”);
ressurl.append(currPage - 1).append(“&numofrec=”).append(numOfRec).append(“”>Prev</a></b>n”);
}
// Display link to next page
if (currPage < pages) {
ressurl.append(“| <b><a href=”").append(url).append(spChar).append(“pageno=”);
ressurl.append(currPage + 1).append(“&numofrec=”).append(numOfRec).append(“”>Next</a></b>n”);
}
// Link to last page
ressurl.append(“| <b><a href=”").append(url).append(spChar).append(“pageno=”).append(pages);
ressurl.append(“&numofrec=”).append(numOfRec).append(“”>Last</a></b>n”);
// Drop down navigation
ressurl.append(“ Go to: <select id=”pageno” name=”pageno”");
ressurl.append(” onchange=”javascript:location.href=this.options[this.selectedIndex].value;”>n”);
// If number of pages > 200
// display only 20 pagination links in the drop down list
// else
// display all pagination links in the drop down list
int iBeginPageNo = 1, iEndPageNo = pages;
if (pages >= 200) {
// Calculate the beginning index
int temp = currPage - 20;
if (temp <= 0) {
iBeginPageNo = 1;
} else {
iBeginPageNo = temp;
}
// Calculate the ending index
temp = currPage + 20;
if (temp >= pages) {
iEndPageNo = pages;
} else {
iEndPageNo = temp;
}
}
for (int i = iBeginPageNo; i <= iEndPageNo; i++) {
if (i == currPage) {
ressurl.append(“<option value=”").append(url).append(spChar).append(“pageno=”).append(i);
ressurl.append(“&numofrec=”).append(numOfRec).append(“” selected>”).append(i).append(“</option>n”);
} else {
ressurl.append(“<option value=”").append(url).append(spChar).append(“pageno=”).append(i);
ressurl.append(“&numofrec=”).append(numOfRec).append(“”>”).append(i).append(“</option>n”);
}
}
ressurl.append(“</select></font>n”);
ressurl.append(“</div>n”);
}
return ressurl.toString();
}
}
The following is the full source code of the jsp page that implement the pagination. You can also click here for a working demo.
<%@page contentType=“text/html; charset=utf-8″
%><%@ page import=“java.sql.*, com.esurfer.util.PaginationUtil”
%><%!
Driver driver = null;
String dbURL = null;
Connection conn = null;
Statement stat = null;
PreparedStatement pst = null;
ResultSet rs = null;
%><%
String url = “pagination.jsp”;
int recPerPage = 15, numOfRec = 0;
String pageNo = request.getParameter(“pageno”);
String recNum = request.getParameter(“numofrec”);
if (pageNo == null) pageNo = “1″;
if (recNum != null) numOfRec = Integer.parseInt(recNum);
int iPageNo = Integer.parseInt(pageNo);
int iFrom = (iPageNo - 1) * recPerPage + 1;
int iOffset = recPerPage - 1;
try {
driver = (Driver) Class.forName(“org.hsqldb.jdbcDriver”).newInstance();
dbURL = “jdbc:hsqldb:” + getServletContext().getRealPath(“/~demo/test/demoDB”);
conn = DriverManager.getConnection(dbURL, “username”, “password”);
stat = conn.createStatement();
// when the page is requested the first time
// count the total number of rows
if (iPageNo == 1) {
rs = stat.executeQuery(“SELECT COUNT(id) AS cnt FROM bookmarks”);
if (rs.next() && rs.getString(1) != null) {
numOfRec = Integer.parseInt(rs.getString(1));
}
}
// Fetch the lowest primary key and highest key according to page number
if ( (iFrom + iOffset) > numOfRec ) iOffset = numOfRec - iFrom;
stat.setMaxRows(iPageNo * recPerPage);
rs = stat.executeQuery(“SELECT id FROM bookmarks ORDER BY id”);
rs.relative(iFrom);
String sLowestId = rs.getString(1);
rs.relative(iOffset);
String sHighestId = rs.getString(1);
stat.setMaxRows(0);
// Retrieve the rows of the specified page
pst = conn.prepareStatement(“SELECT * FROM bookmarks WHERE id BETWEEN ? AND ? ORDER BY id”);
pst.setString(1, sLowestId);
pst.setString(2, sHighestId);
rs = pst.executeQuery();
} catch (Exception e) {
out.print(“Unable do make connection to database”);
out.print(e);
}
%>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
<html>
<head>
<title>hsqldb 1.61 pagination</title>
<meta http-equiv=“Content-Type” content=“text/html; charset=utf-8″>
</head>
<body bgcolor=“#FFFFFF” text=“#000000″>
<body leftmargin=“0″ topmargin=“0″>
<table width=“100%” border=“0″ align=“center” cellpadding=“3″ cellspacing=“1″>
<tr align=“center” bgcolor=“CECFCE”>
<td><b>ID</b></td>
<td><b>NAME</b></td>
<td><b>URL</b></td>
<td><b>DESCRIPTION</b></td>
</tr>
<% while (rs.next()) { %>
<tr bgcolor=“#ffffff”>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
</tr>
<% } %>
</table><br />
<%=PaginationUtil.Pagination(numOfRec, recPerPage, iPageNo, url)%>
</body></html>
<%
rs.close();
pst.close();
stat.close();
conn.close();
%>
Although it works well, the performance of the query that retrieve the lowest primary key and the highest primary key will be more and more inefficient along with the increase of the page number. Any good ideas will be appreciated.
