Paginated query / iterator recipe

Question

I see this template a lot.

On server:

// Get a bounded number of results, along with a resume token to use // for the next call. Successive calls yield a "weakly consistent" view of // the underlying set that may or may not reflect concurrent updates. public<T> String getObjects( int maxObjects, String resumeToken, List<T> objectsToReturn); 

On the client:

 // An iterator wrapping repeated calls to getObjects(bufferSize, ...) public<T> Iterator<T> getIterator(int bufferSize); 

Most places collapse their versions of these two methods, and implementations are surprisingly hard to get right. There are many errors in the edge.

Is there a canonical recipe or library for these requests?

(you can make some simplifying assumptions for server storage, for example, T has a natural order).

Answer 1

This uses AbstractIterator from google-guava and spring -jdbc library to actually query the database:

 public Iterable<T> queryInBatches( final String query, final Map<String, Integer> paramMap, final int pageSize, final Class<T> elementType) { return new Iterable<T>() { @Override public Iterator<T> iterator() { final Iterator<List<T>> resultIter = queryResultIterator(query, paramMap, pageSize, elementType); return new AbstractIterator<T>() { private Iterator<T> rowSet; @Override protected T computeNext() { if (rowSet == null) { if (resultIter.hasNext()) { rowSet = resultIter.next().iterator(); } else { return endOfData(); } } if (rowSet.hasNext()) { return rowSet.next(); } else { rowSet = null; return computeNext(); } }}; }}; } private AbstractIterator<List<T>> queryResultIterator( final String query, final Map<String, Integer> paramMap, final int pageSize, final Class<T> elementType) { return new AbstractIterator<List<T>>() { private int page = 0; @Override protected List<T> computeNext() { String sql = String.format( "%s limit %s offset %s", query, pageSize, page++ * pageSize); List<T> results = jdbc().queryForList(sql, paramMap, elementType); if (!results.isEmpty()) { return results; } else { return endOfData(); } }}; } 

AbstractIterator hides most of the complications associated with writing its own implementation of Iterator . You only need to implement the computeNext method, which either returns the next value in the iterator or calls endOfData to indicate that there are more values ​​in the iterator.

Answer 2

Here is something that works for me. It also uses AbstractIterator from google-guava library, but uses Java8 Stream to simplify implementation. It returns an Iterator of elements of type T.

 Iterator<List<T>> pagingIterator = new AbstractIterator<List<T>>() { private String resumeToken; private boolean endOfData; @Override protected List<T> computeNext() { if (endOfData) { return endOfData(); } List<T> rows = executeQuery(resumeToken, PAGE_SIZE); if (rows.isEmpty()) { return endOfData(); } else if (rows.size() < PAGE_SIZE) { endOfData = true; } else { resumeToken = getResumeToken(rows.get(PAGE_SIZE - 1)); } return rows; } }; // flatten Iterator of lists to a stream of single elements Stream<T> stream = StreamSupport.stream(Spliterators.spliteratorUnknownSize(pagingIterator, 0), false) .flatMap(List::stream); // convert stream to Iterator<T> return stream.iterator(); 

You can also return Iterable using the method reference as follows:

 // convert stream to Iterable<T> return stream::iterator;