Paginating large SQL-Results

Question

It feels like pagination is one of the most discussed questions on the web, which always ends up with some quick and dirty hacks on how to paginate. I want to paginate a large result set from an SQL query on a website where I use Spring-JDBC for querying and Displaytag for display. I thought about it for a while and eventually came up with implementing a ResultSetExtractor like this:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.displaytag.pagination.PaginatedList;
import org.displaytag.properties.SortOrderEnum;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;

public class PaginatingResultMapExtractor implements
        ResultSetExtractor<PaginatedList>
{
    private final int page;
    private final int pageSize;
    private final String sortColumn;
    private final SortOrderEnum sortDirection;

    public PaginatingResultMapExtractor(int page, int pageSize,
            String sortColumn, SortOrderEnum sortDirection)
    {
        super();
        this.page = page;
        this.pageSize = pageSize;
        this.sortColumn = sortColumn;
        this.sortDirection = sortDirection;
    }

    @Override
    public PaginatedList extractData(ResultSet rs) throws SQLException,
            DataAccessException
    {
        final ColumnMapRowMapper mapper = new ColumnMapRowMapper();
        final List<Object> result = new ArrayList<Object>(
                pageSize);
        int i;
        for (i = 0; rs.next(); ++i)
        {
            if (i > (page - 1) * pageSize
                    && i < page * pageSize)
                result.add(mapper.mapRow(rs, i));
        }
        return new PaginatedListImpl<Object>(result, i,
                pageSize, page, sortColumn, sortDirection);
    }
}

PaginatedListImpl is simply stores the values that I pass in the constructor and makes them available with getter-methods, fulfilling the requirements of the PaginatedList interface by displaytag.

I can use this by simply calling:

jdbcTemplate.query("select ... from ...", new PaginatingResultMapExtractor(page, pageSize, sortColumn, sortDirection));

At least this solution is very easy to use. But after all I have to iterate the whole result set. Can this code be improved?

Answer 1

Pagination should be done by the database server where applicable. There is really no point pushing data down the wire if it is not being used, so what you should think about doing, is passing in your page number and count variables to the query or preferable a stored procedure, and use SQL to select the subset of data you want. There are SQL constructs in most SQL language variants to do this.

Here is an example using MySql:

SELECT `field1`, `field2` from `mycatalog` LIMIT start, (start + cnt)

And something similar with Sql Server:

WITH #set AS (SELECT [Field1], [Field2], ROW_NUMER() OVER (ORDER BY [Field1]) AS [Index] FROM [MyDatabase])
SELECT [Field1], [Field2] FROM #set WHERE [INDEX] BETWEEN @start AND (@start + @count)