ResultSet to Pagination

Question

How do I convert Resultset object to a paginated view on a JSP?

For example, this is my query and result set:

pst = con.prepareStatement("select userName, job, place from contact");
rs = pst.executeQuery();

Answer 1

Here's a couple things you can do:

• Marshall the result set to some list of objects/records
• Based on your required page size, figure out how many pages you will have based on the result set.
• Check request parameter for the required page and offsets based on the number of items to display on the page. So if you're on page 4 with 12 to display, your offset is 48.

• Determine the total number of pages based on the count of the items.

• Display your items based on the offset that you determined (only display starting at item 48)

• Generate your pagination with the amount of pages based on the total number of pages that you determined.

=======

That's your basic approach. You can tweak this with:

• Determining a way to limit the query to the page (but this wont help you with determining page sizes)
• Fancy ways of pagination
• etc..

Answer 2

Look up the Value List Pattern, and apply that. That's typically the best way to handle these kinds of things.

Answer 3

To start, you need to add one or two extra request parameters to the JSP: firstrow and (optionally) rowcount. The rowcount can also be left away and definied entirely in the server side.

Then add a bunch of paging buttons to the JSP: the next button should instruct the Servlet to increment the value of firstrow with the value of rowcount. The previous button should obviously decrement the value of firstrow with the value of rowcount. Don't forget to handle negative values and overflows correctly! You can do it with help of SELECT count(id).

Then fire a specific SQL query to retrieve a sublist of the results. The exact SQL syntax however depends on the DB used. In MySQL and PostgreSQL it is easy with LIMIT and OFFSET clauses:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " contact ORDER BY id LIMIT %d OFFSET %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, rowcount);

    // Implement JDBC.
    return contacts;
}

In Oracle you need a subquery with rownum clause which should look like:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " (SELECT id, username, job, place FROM contact ORDER BY id)"
    + " WHERE ROWNUM BETWEEN %d AND %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount);

    // Implement JDBC.
    return contacts;
}

In DB2 you need the OLAP function row_number() for this:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " (SELECT row_number() OVER (ORDER BY id) AS row, id, username, job, place"
    + " FROM contact) AS temp WHERE row BETWEEN %d AND %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount);

    // Implement JDBC.
    return contacts;
}

I don't do MSSQL, but it's syntactically similar to DB2. Also see this topic.

Finally just present the sublist in the JSP page the usual way with JSTL c:forEach.

<table>
    <c:forEach items="${contacts}" var="contact">
        <tr>
            <td>${contact.username}</td>
            <td>${contact.job}</td>
            <td>${contact.place}</td>
        </tr>
    </c:forEach>
</table>
<form action="yourservlet" method="post">
    <input type="hidden" name="firstrow" value="${firstrow}">
    <input type="hidden" name="rowcount" value="${rowcount}">
    <input type="submit" name="page" value="next">
    <input type="submit" name="page" value="previous">
</form>

Note that some may suggest that you need to SELECT the entire table and save the List<Contact> in the session scope and make use of List#subList() to paginate. But this is far from memory-efficient with thousands rows and multiple concurrent users.

For ones who are interested in similar answer in JSF/MySQL context using h:dataTable component, you may find this article useful. It also contains some useful language-agnostic maths to get the "Google-like" pagination nicely to work.