Java Iterator backed by a ResultSet

Question

I've got a class that implements Iterator with a ResultSet as a data member. Essentially the class looks like this:

public class A implements Iterator{
    private ResultSet entities;
    ...
    public Object next(){
        entities.next();
        return new Entity(entities.getString...etc....)
    }

    public boolean hasNext(){
        //what to do?
    }
    ...
}

How can I check if the ResultSet has another row so I can create a valid hasNext method since ResultSet has no hasNext defined itself? I was thinking doing SELECT COUNT(*) FROM... query to get the count and managing that number to see if there's another row but I'd like to avoid this.

Solution 1

This is a bad idea. This approach requires that the connection is open the whole time until the last row is read, and outside the DAO layer you never know when it will happen, and you also seem to leave the resultset open and risk resource leaks and application crashes in the case the connection times out. You don't want to have that.

The normal JDBC practice is that you acquire Connection, Statement and ResultSet in the shortest possible scope. The normal practice is also that you map multiple rows into a List or maybe a Map and guess what, they do have an Iterator.

public List<Data> list() throws SQLException {
    List<Data> list = new ArrayList<Data>();

    try (
        Connection connection = database.getConnection();
        Statement statement = connection.createStatement("SELECT id, name, value FROM data");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            list.add(map(resultSet));
        }
    }

    return list;
}

private Data map(ResultSet resultSet) throws SQLException {
    Data data = new Data(); 
    data.setId(resultSet.getLong("id"));
    data.setName(resultSet.getString("name"));
    data.setValue(resultSet.getInteger("value"));
    return data;
}

And use it as below:

List<Data> list = dataDAO.list(); 
int count = list.size(); // Easy as that.
Iterator<Data> iterator = list.iterator(); // There is your Iterator.

Do not pass expensive DB resources outside the DAO layer like you initially wanted to do. For more basic examples of normal JDBC practices and the DAO pattern you may find this article useful.

Solution 2

You can get out of this pickle by performing a look-ahead in the hasNext() and remembering that you did a lookup to prevent consuming too many records, something like:

public class A implements Iterator{
    private ResultSet entities;
    private boolean didNext = false;
    private boolean hasNext = false;
    ...
    public Object next(){
        if (!didNext) {
            entities.next();
        }
        didNext = false;
        return new Entity(entities.getString...etc....)
    }

    public boolean hasNext(){
        if (!didNext) {
            hasNext = entities.next();
            didNext = true;
        }
        return hasNext;
    }
    ...
}

Solution 3

ResultSet has an 'isLast()' method that might suit your needs. The JavaDoc says it is quite expensive though since it has to read ahead. There is a good chance it is caching the look-ahead value like the others suggest trying.

Solution 4

public class A implements Iterator<Entity>
{
    private final ResultSet entities;

    // Not required if ResultSet.isLast() is supported
    private boolean hasNextChecked, hasNext;

    . . .

    public boolean hasNext()
    {
        if (hasNextChecked)
           return hasNext;
        hasNext = entities.next();
        hasNextChecked = true;
        return hasNext;

        // You may also use !ResultSet.isLast()
        // but support for this method is optional 
    }

    public Entity next()
    {
        if (!hasNext())
           throw new NoSuchElementException();

        Entity entity = new Entity(entities.getString...etc....)

        // Not required if ResultSet.isLast() is supported
        hasNextChecked = false;

        return entity;
    }
}

Solution 5

Its not a really bad idea in the cases where you need it, it's just that you often do not need it.

If you do need to do something like, say, stream your entire database.... you could pre-fetch the next row - if the fetch fails your hasNext is false.

Here is what I used:

/**
 * @author Ian Pojman <[email protected]>
 */
public abstract class LookaheadIterator<T> implements Iterator<T> {
    /** The predetermined "next" object retrieved from the wrapped iterator, can be null. */
    protected T next;

    /**
     * Implement the hasNext policy of this iterator.
     * Returns true of the getNext() policy returns a new item.
     */
    public boolean hasNext()
    {
        if (next != null)
        {
            return true;
        }

        // we havent done it already, so go find the next thing...
        if (!doesHaveNext())
        {
            return false;
        }

        return getNext();
    }

    /** by default we can return true, since our logic does not rely on hasNext() - it prefetches the next */
    protected boolean doesHaveNext() {
        return true;
    }

    /**
     * Fetch the next item
     * @return false if the next item is null. 
     */
    protected boolean getNext()
    {
        next = loadNext();

        return next!=null;
    }

    /**
     * Subclasses implement the 'get next item' functionality by implementing this method. Implementations return null when they have no more.
     * @return Null if there is no next.
     */
    protected abstract T loadNext();

    /**
     * Return the next item from the wrapped iterator.
     */
    public T next()
    {
        if (!hasNext())
        {
            throw new NoSuchElementException();
        }

        T result = next;

        next = null;

        return result;
    }

    /**
     * Not implemented.
     * @throws UnsupportedOperationException
     */
    public void remove()
    {
        throw new UnsupportedOperationException();
    }
}

then:

    this.lookaheadIterator = new LookaheadIterator<T>() {
        @Override
        protected T loadNext() {
            try {
                if (!resultSet.next()) {
                    return null;
                }

                // process your result set - I use a Spring JDBC RowMapper
                return rowMapper.mapRow(resultSet, resultSet.getRow());
            } catch (SQLException e) {
                throw new IllegalStateException("Error reading from database", e);
            }
        }
    };
}

View more solutions