com.databasesandlife.util.jdbc
Class ResultSetIterator<T>

java.lang.Object
  com.databasesandlife.util.jdbc.ResultSetIterator<T>

All Implemented Interfaces:

Iterator<T>

public abstract class ResultSetIterator<T>
extends Object
implements Iterator<T>

Takes a PreparedStatement and creates a ResultSet and implements the Iterator interface that returns the results.

Subclasses may be created which convert a particular row into a particular type of object to be returned from the iterator.

It is intended for those ResultSets which can only iterate forward (i.e. streaming results). On these ResultSets the aResultSet.hasNext method cannot be called. Therefore the ResultSet only has a next method, but the Iterator must provide a hasNext method which can be called an arbitrary number of times to see if there would be a next row. The algorithm employed is to always fetch one row ahead store it in the object, so hasNext can return if there is a next row or not. The next method then returns that previously fetched one, and fetches the next one.

Any SQLException which are thrown from the ResultSet are assumed to be "should never happen" type, and thus are transformed into RuntimeExceptions.

The ResultSet is opened on the first next or hasNext method, and closed at the end of the iteration. This means that an object of this type can be created then used hours later; the ResultSet will not have timed out, as the statement is executed only briefly before the results are needed.

The PreparedStatement, and the associated Connection, can also be closed at the end of the iteration if desired (if this is the only code which will be using that statement) or can be left open (if other code will use it).

Usage:

 public class MyDocResultSetIterator extends ResultSetIterator<MyDoc> {
     protected MyDoc objectForRow(ResultSet r) throws SQLException {
         MyDoc result = new MyDoc();
         result.setVal(r.getString("val"));
         return result;
     }
 }
 public Iterator<MyDoc> foo() {
     PreparedStatement stat = aConnection.prepareStatement(
         "SELECT val FROM big_table",
         ResultSet.TYPE_FORWARD_ONLY,
         ResultSet.CONCUR_READ_ONLY);
     stat.setFetchSize(Integer.MIN_VALUE);
     boolean shouldClosePreparedStatement = truel
     return new MyDocResultSetIterator("...", stat, shouldClosePreparedStatement);
 }
 

See Reading row-by-row into Java from MySQL.

This code has beem tested using Java 6 with MySQL 5.0 and the JDBC driver "MySQL Connector" 5.1.15.

Version:

$Revision: 2248 $

Author:

This source is copyright Adrian Smith and licensed under the LGPL 3.

Nested Class Summary

static class

ResultSetIterator.CloseStrategy

Constructor Summary

ResultSetIterator(String sqlForLog, PreparedStatement statement, ResultSetIterator.CloseStrategy closeStrategy)

Method Summary

boolean	hasNext()
T	next()
void	remove()

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

ResultSetIterator

public ResultSetIterator(String sqlForLog,
                         PreparedStatement statement,
                         ResultSetIterator.CloseStrategy closeStrategy)

Parameters:

sqlForLog - What SQL was executed? This is only used to populate the text of Exceptions

Method Detail

hasNext

public boolean hasNext()

Specified by:

hasNext in interface Iterator<T>

next

public T next()

Specified by:

next in interface Iterator<T>

remove

public void remove()

Specified by:

remove in interface Iterator<T>