Resultset to Pagination

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.

JDBC Pagination

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

See this JDBC Pagination Tutorial:
http://java.avdiel.com/Tutorials/JDBCPaging.html

How do you implement pagination with a result set from a stored procedure?

You can convert your List to a Page by using the PageImpl constructor(s). If you want an unpaged Page all you have to do is the following:

Page<SearchResult> searchResultsPage = new PageImpl<>(searchResults);

If you want an actual paged result you'll also need a pageable and total records count. The conversion would then look like this:

Page<SearchResult> searchResultsPage = new PageImpl<>(searchResults, pageable, count);

A pageable can be created by using PageRequest:

var pageable = PageRequest.of(pageNumber, pageSize, sort); 

JDBC ResultSet total rows

Another option is to add the count aggregation as a sub queried column in your query. If your database is just a little bit smart, it will only execute that once. You should be able to check this easily using the query analyzer in your favorite database.

SELECT id,username,(SELECT COUNT(id) FROM users) FROM users;

Paging a ArrayList or Result Set

You can use sql.eachRow or sql.rows which takes offset and maxRows as parameters. Here is an example of its usage.

How to apply pagination to the result of a SQL query with Joins?

It's funny how writing a question makes you think, and actually helps a lot in imagining a solution for your own problem.

I was able to solve this problem by simply adding the pagination part of the query to a sub-query of my main query, rather than to the main query itself.

For example, instead of doing:

SELECT client.id, client.name ...
FROM clients AS client
LEFT JOIN client_account_types AS cat ON client.id = cat.client_id
FULL JOIN account_types AS at ON cat.account_type_id = at.id
ORDER BY client.name ASC
LIMIT 10 OFFSET 30;

I am doing this:

SELECT client.id, client.name ...
FROM (
    SELECT * FROM clients
    ORDER BY name ASC
    LIMIT 10 OFFSET 0
) AS client
LEFT JOIN client_account_types AS cat ON client.id = cat.client_id
FULL JOIN account_types AS at ON cat.account_type_id = at.id;

Hope this helps other people too.

---

Related Topics