Spring JDBC Pagination Tutorial

由于工程的需要，只能用spring来进行分页显示，发现一个比较好的源码，收藏一下。

Overview

This short tutorial provides sample code for implementating pagination with the Spring Framework.

Goals

When users of web applications request data, such as a list of bank transactions, it is usually not a good idea to return all of that data in a single page as there could be hundreds or thousands of records. Typically, web applications return the data one page at a time and allow the user to navigate between those pages.

There are some different approaches to implementing pagination. One simple aproach is simply to run the query against the database and store all of the results in memory in the user's session and have the view render a subset of those results. This is easy to implement but does not scale well as it could potentially use a lot of memory on the server, limiting the number of users that can be supported at any time.

An alternative approach is to implement pagination in the data services layer so that only a single page of information is loaded from the database at any time. This means that a new query will be run each time the user moves between pages so there is a performance overhead with this approach. However, because of the lower memory usage, this solution is also more scalable and that is typically a more important consideration when it comes to web applications.

Example

This tutorial uses the use code of listing a number of companies. This might be a public service listing records of public companies.

Using Spring JDBC without Pagination

First of all, let's look at a standard technique using Spring JDBC to return a list of companies in a single result set.

public List<Company> getCompanies() throws SQLException {
    return jdbcTemplate.query(
            "SELECT id, name FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",
            new ParameterizedRowMapper<Company>() {
                public Company mapRow(ResultSet rs, int i) throws SQLException {
                    return new Company(
                            rs.getInt(1),
                            rs.getString(2)
                    );
                }
            },
            userId
    );
}

Using Spring JDBC with Pagination

Here's a new version of the method which uses a PaginationHelper class, which is shown further on in this tutorial. As you can see, the usage is very similar to standard Spring JDBC. The most obvious difference is that we now have two SQL statements instead of one. This is necessary if we want to be able to show the users how many pages of data there are but it is an extra performance hit so there is room for refinement in this approach by perhaps caching the number of pages. The code uses the standard ParameterizedRowMapper to minimize impact on existing code.

public Page<Company> getCompanies(final int pageNo, final int pageSize) throws SQLException {
        PaginationHelper<Company> ph = new PaginationHelper<Company>();
        return ph.fetchPage(
                jdbcTemplate,
                "SELECT count(*) FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",
                "SELECT id, name FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",
                new Object[]{userId},
                pageNo,
                pageSize,
                new ParameterizedRowMapper<Company>() {
                    public Company mapRow(ResultSet rs, int i) throws SQLException {
                        return new Company(
                            rs.getInt(1),
                            rs.getString(2)
                        );
                    }
                }
        );

    }

The Page class

The Page class is a very simple template class that contains a list of items, the page number, and the number of pages that are available.

public class Page<E> {

        private int pageNumber;
        private int pagesAvailable;
        private List<E> pageItems = new ArrayList<E>();

        public void setPageNumber(int pageNumber) {
            this.pageNumber = pageNumber;
        }

        public void setPagesAvailable(int pagesAvailable) {
            this.pagesAvailable = pagesAvailable;
        }

        public void setPageItems(List<E> pageItems) {
            this.pageItems = pageItems;
        }

        public int getPageNumber() {
            return pageNumber;
        }

        public int getPagesAvailable() {
            return pagesAvailable;
        }

        public List<E> getPageItems() {
            return pageItems;
        }
    }

Pagination Helper

Here's the source code for the PaginationHelper class. This is actually very simple. The first SQL query is executed to determine how many rows of data are available. This allows the number of pages to be calculated. The second query is then executed using the JdbcTemplate query method that accepts a Spring ResultSetExtractor. The implementation of this ResultSetExtractor processes the result set and delegates to the supplied ParameterizedRowMapper for those rows that should be returned as part of the current page of data.

public class PaginationHelper<E> {

    public Page<E> fetchPage(
            final JdbcTemplate jt,
            final String sqlCountRows,
            final String sqlFetchRows,
            final Object args[],
            final int pageNo,
            final int pageSize,
            final ParameterizedRowMapper<E> rowMapper) {

        // determine how many rows are available
        final int rowCount = jt.queryForInt(sqlCountRows, args);

        // calculate the number of pages
        int pageCount = rowCount / pageSize;
        if (rowCount > pageSize * pageCount) {
            pageCount++;
        }

        // create the page object
        final Page<E> page = new Page<E>();
        page.setPageNumber(pageNo);
        page.setPagesAvailable(pageCount);

        // fetch a single page of results
        final int startRow = (pageNo - 1) * pageSize;
        jt.query(
                sqlFetchRows,
                args,
                new ResultSetExtractor() {
                    public Object extractData(ResultSet rs) throws SQLException, DataAcces***ception {
                        final List pageItems = page.getPageItems();
                        int currentRow = 0;
                        while (rs.next() && currentRow < startRow + pageSize) {
                            if (currentRow >= startRow) {
                                pageItems.add(rowMapper.mapRow(rs, currentRow));
                            }
                            currentRow++;
                        }
                        return page;
                    }
                });
        return page;
    }

}

Summary

This tutorial demonstrates simple and effective database level pagination using Spring JDBC but this code could be improved in a number of ways:

• It could cache the number of pages available to remove the overhead of running the 'count' SQL each time
• It could make use of ScrollableResultSet support when fetching the rows
• It could make use of database specific features such as MySQL's support for the "LIMIT offset, count" syntax

It would be nice if the Spring Framework could be extended to include pagination support but the beauty of the Spring Framework though is that it makes it easy to extend with extra functionality.