# Paginating large SQL-Results

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)
}
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?

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)

• I agree 100% with having the DB do the paging as much as possible, but I would strongly avoid using stored procedures unless there is a clear, large (and necessary) performance win. Stored procedures are notoriously hard to maintain because they are separate from the codebase and are much harder to deal with that plain files. Also, in most cases, you don't get much perf. benefit from SProcs because query caches have gotten much better than in the past, as has query parsing time. – cdeszaq Jul 15 '11 at 14:19
• But you could / should use a prepared statement. – Arne Dec 3 '12 at 12:41
• The second approach may bear an unnecessary overhead when order by is used over a massive table. Unless, of course, the table is clustered along the lines of the same "order by" expression. – access_granted Aug 8 '19 at 18:03