ResultSet to Pagination

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.


This Oracle example is wrong.

Yes, in the outer select whe have good ROWNUM values, but it is still pseudo column so we can not use BETWEEN on it. We need one more select.

The right sql code is:

SELECT c.*
  FROM (SELECT c.*, ROWNUM as rnum
          FROM (SELECT id, username, job, place FROM contact ORDER BY id) c) c
 WHERE c.rnum BETWEEN 5 AND 10

Comrades, using solid sql string and Statement class is SLOOOW. Oracle have to parse your SQL every time your execute it.

//Slooow example
Satement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from my_table where id = 11");

Use PreparedStatement and binding parameters.

   //Faster example
   PreparedStatement ps = conn.getPrepareStatement("select * from my_table where id = ?");
   ps.setInt(1, 11);

And fastest solution is put your sql in oracle stored procedure and use CallableStatement to call it.

//Fastest example
CallableStatement cs = conn.prepareCall("{? = call my_plsql_function(?)}");
cs.setInt(1, 11);

Tags:

Java

Pagination

Jsp

Jdbc

Recent Posts