由 ResultSet 支持的 Java 迭代器

Java Iterator backed by a ResultSet

提问by Jordan Messina

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

我有一个类，它使用 ResultSet 作为数据成员实现 Iterator。基本上这个类看起来像这样：

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.

如何检查 ResultSet 是否有另一行，以便我可以创建有效的 hasNext 方法，因为 ResultSet 本身没有定义 hasNext ？我正在考虑进行SELECT COUNT(*) FROM...查询以获取计数并管理该数字以查看是否还有另一行，但我想避免这种情况。

采纳答案by BalusC

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.

这是一个坏主意。这种方法要求连接一直打开直到最后一行被读取，在 DAO 层之外你永远不知道它什么时候会发生，而且你似乎也让结果集保持打开状态，在这种情况下有资源泄漏和应用程序崩溃的风险连接超时。你不想拥有那个。

The normal JDBC practice is that you acquire Connection, Statementand ResultSetin the shortestpossible scope. The normal practice is also that you map multiple rows into a Listor maybe a Mapand guess what, they dohave an Iterator.

正常JDBC的做法是，你购买Connection，Statement并ResultSet在最短的可能范围。通常的做法也是将多行映射到 aList或 aMap并猜测是什么，它们确实有一个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 articleuseful.

不要像最初想做的那样在 DAO 层之外传递昂贵的数据库资源。有关常规 JDBC 实践和 DAO 模式的更多基本示例，您可能会发现本文很有用。

回答by Andrew Hare

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

听起来您在提供低效实现hasNext或抛出异常表明您不支持该操作之间陷入困境。

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.

不幸的是，有时您实现一个接口并且不需要所有成员。在这种情况下，我建议您在该成员中抛出一个您不会或不能支持的异常，并将该成员作为不受支持的操作记录在您的类型上。

回答by Justin Ethier

entities.nextreturns 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().

如果没有更多行，则entities.next返回false，因此您可以获取该返回值并设置一个成员变量来跟踪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...

但是要完成这项工作，您还必须有某种 init 方法来读取第一个实体并将其缓存在类中。然后在调用 next 时，您需要返回先前缓存的值并缓存下一个值，等等...

回答by Dan

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

您是否希望实际使用结果集中的大部分数据？如果是这样，请预先缓存它。使用例如 Spring 非常简单

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

Adjust to suit your taste.

调整以适合您的口味。

回答by rsp

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:

您可以通过在 中执行前瞻hasNext()并记住您进行了查找以防止消耗太多记录来摆脱这种泡菜，例如：

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

回答by ComSubVie

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.

这段代码缓存下一个实体，如果缓存的实体有效，hasNext() 返回true，否则返回false。

回答by Dunderklumpen

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.

根据您想要 A 类的内容，您可以做几件事。如果主要用例是检查每个结果，那么最好预加载所有 Entity 对象并丢弃 ResultSet。

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

但是，如果您不想这样做，则可以使用 ResultSet 的 next() 和 previous() 方法

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.

您必须小心确保您当前没有从 ResultSet 中读取数据，但是，如果您的实体类是一个正确的 POJO（或者至少与 ResultSet 正确断开连接，那么这应该是一个很好的方法。

回答by Charlie Brown

ResultSet has an 'isLast()' method that might suit your needs. The JavaDocsays 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.

ResultSet 有一个“isLast()”方法，可能适合您的需要。该JavaDoc的说，这是相当昂贵的，虽然，因为它要读。它很有可能像其他人建议的那样缓存前瞻值。

回答by Ian Pojman

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.

如果您确实需要执行诸如流式传输整个数据库之类的操作....您可以预取下一行 - 如果提取失败，您的 hasNext 为 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);
            }
        }
    };
}

回答by B. Broto

You can use ResultSetIterator, just put your ResultSet in the constructor.

您可以使用ResultSetIterator，只需将您的 ResultSet 放在构造函数中。

ResultSet rs = ...    
ResultSetIterator = new ResultSetIterator(rs);