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.

Author:

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

See Also:

Project on GitHub

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	ResultSetIterator.CloseStrategy
protected static class	ResultSetIterator.State

Field Summary

Fields

Modifier and Type	Field and Description
protected ResultSetIterator.CloseStrategy	closeStrategy
protected T	nextObject
protected ResultSet	resultSet
protected String	sqlForLog
protected ResultSetIterator.State	state
protected PreparedStatement	statement

Constructor Summary

Constructors

Constructor and Description
ResultSetIterator(String sqlForLog, PreparedStatement statement, ResultSetIterator.CloseStrategy closeStrategy)

Method Summary

Modifier and Type	Method and Description
protected void	afterQueryExecuted() Can be overridden to do things just after the ResultSet has been executed but before any rows have been read.
boolean	hasNext()
protected abstract T	newObjectForRow(ResultSet r) Looks in the current data in the resultSet and returns a new object to be returned from the Iterator.
T	next()
void	remove()
protected void	stateTransitionToFinished()
protected void	stateTransitionToReturnResults()

Methods inherited from class java.lang.Object

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

Methods inherited from interface java.util.Iterator

forEachRemaining

Field Detail

state

protected ResultSetIterator.State state

sqlForLog

protected final String sqlForLog

statement

protected final PreparedStatement statement

closeStrategy

protected final ResultSetIterator.CloseStrategy closeStrategy

resultSet

protected ResultSet resultSet

nextObject

protected T nextObject

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

stateTransitionToReturnResults

protected void stateTransitionToReturnResults()

afterQueryExecuted

protected void afterQueryExecuted()
                           throws SQLException

Can be overridden to do things just after the ResultSet has been executed but before any rows have been read. For example process metadata.

Throws:

SQLException

stateTransitionToFinished

protected void stateTransitionToFinished()

newObjectForRow

protected abstract T newObjectForRow(ResultSet r)
                              throws SQLException

Looks in the current data in the resultSet and returns a new object to be returned from the Iterator.

Parameters:

r - cursor is already at the row containing the data.

Returns:

new object representing the data in the ResultSet.

Throws:

SQLException - if there is some problem reading the ResultSet. (This is converted into a RuntimeException by next(), so there is no point implementing the same code in the subclass' implementation of this method.)

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>