由 ResultSet 支持的 Java 迭代器

2022-01-10 00:00:00 sql iterator resultset jdbc java

我有一个使用 ResultSet 作为数据成员来实现 Iterator 的类.基本上这个类看起来像这样:

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

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

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.

推荐答案

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

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.

正常的 JDBC 实践是在 最短 可能的范围内获取 Connection、Statement 和 ResultSet.通常的做法是将多行映射到一个 List 或者可能是一个 Map 并猜猜是什么，他们 确实 有一个 Iterator.

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

如下使用:

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

不要像最初想要的那样将昂贵的数据库资源传递到 DAO 层之外.有关常规 JDBC 实践和 DAO 模式的更多基本示例，您可以找到 这篇文章很有用.

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.

相关文章