Java ResultSet hasNext()

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.

Answer 1

It sounds like you are stuck between either providing an inefficient implementation of hasNext or throwing an exception stating that you do not support the operation.

Unfortunately there are times when you implement an interface and you don't need all of the members. In that case I would suggest that you throw an exception in that member that you will not or cannot support and document that member on your type as an unsupported operation.

Answer 2

entities.next returns false if there are no more rows, so you could just get that return value and set a member variable to keep track of the status for hasNext().

But to make that work you would also have to have some sort of init method that reads the first entity and caches it in the class. Then when calling next you would need to return the previously cached value and cache the next value, etc...

Answer 3

Do you expect most of the data in your result set to actually be used? If so, pre-cache it. It's quite trivial using eg Spring

  List<Map<String,Object>> rows = jdbcTemplate.queryForList(sql);
  return rows.iterator();

Adjust to suit your taste.

Answer 4

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;
    }
    ...
}

Answer 5

You could try the following:

public class A implements Iterator {
    private ResultSet entities;
    private Entity nextEntity;
    ...
    public Object next() {
        Entity tempEntity;
        if ( !nextEntity ) {
            entities.next();
            tempEntity = new Entity( entities.getString...etc....)
        } else {
            tempEntity = nextEntity;
        }

        entities.next();
        nextEntity = new Entity( entities.getString...ext....)

        return tempEntity;
    }

    public boolean hasNext() {
        return nextEntity ? true : false;
    }
}

This code caches the next entity, and hasNext() returns true, if the cached entity is valid, otherwise it returns false.

Answer 6

There are a couple of things you could do depending on what you want your class A. If the major use case is to go through every single result then perhaps its best to preload all the Entity objects and throw away the ResultSet.

If however you don't want to do that you could use the next() and previous() method of ResultSet

public boolean hasNext(){
       boolean next = entities.next();

       if(next) {

           //reset the cursor back to its previous position
           entities.previous();
       }
}

You do have to be careful to make sure that you arent currently reading from the ResultSet, but, if your Entity class is a proper POJO (or at least properly disconnected from ResultSet then this should be a fine approach.

Answer 7

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.

Answer 8

This is really 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 {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<Data> list = new ArrayList<Data>();

    try {
        connection = database.getConnection();
        statement = connection.createStatement("SELECT id, name, value FROM data");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            Data data = new Data(); 
            data.setId(resultSet.getLong("id"));
            data.setName(resultSet.getString("name"));
            data.setValue(resultSet.getInteger("value"));
            list.add(data);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return list;
}

And use it as follows:

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.