JDBC 驱动程序抛出“ResultSet Closed"；空结果集异常 [英] JDBC driver throws "ResultSet Closed" exception on empty ResultSet

问题描述

我在 SQLite 的 JDBC 驱动程序中遇到问题.

I have a problem in JDBC driver for SQLite.

我正在使用 SELECT 语句执行查询.

I am executing a query with SELECT statement.

如果我得到一个空的 ResultSet(0 行)，那么我会在调用 getString(1) 时看到Closed ResultSet"异常.

If I get an empty ResultSet (0 rows) then I see a "Closed ResultSet" exception thrown when calling getString(1).

在没有太多 JDBC 经验的情况下，我的理论(我无法通过 JavaDocs 确认 ResultSet)是这样的

Without much prior JDBC experience, my theory (which I could not confirm via JavaDocs for ResultSet) is that

• getString(1) 不适用于空(零行)结果集(根据设计或由于错误)
• ResultSet 的open"标志在零行上设置为 false(同样，由于设计或错误)

• getString(1) does NOT work on an empty (zero-row) resultset (by design or due to a bug)
• ResultSet's "open" flag is set to false on zero rows (again, by design or a bug)

我看到了这个 错误报告，但我不确定如果是相关的.

I saw this bug report but am not sure if it's related.

我的问题是:

1. 上述理论正确吗?
2. 这是一个错误吗?特征?(如果是这样，有人可以指出文档吗?)
3. 它是特定于 SQLIte 的 JDBC 还是所有 JDBC 驱动程序中的通用 ResultSet?
4. 这样做的正确方法是什么??

对于#4，我的解决方案是在 executeQuery() 之后使用 isFirst() 调用来检查结果集中是否存在任何行.这是最佳实践方法吗?

For #4, my solution was to use isFirst() call right after executeQuery() to check whether any rows are there in result set. Is this the best practice approach?

(我也可以简单地选择一个计数 insetad，因为我并不真正需要结果集，只需要零非零标志，但如果我确实关心 select 的结果，我想知道正确的做法)

(I could also have simply selected a count insetad since I didn't really need a result set, merely zero-nonzero flag, but I want to know the correct thingh to do if I did care about select's results)

谢谢！

推荐答案

不管是否为空，但执行以下操作总是错误:

Empty or not, but doing the following is always faulty:

resultSet = statement.executeQuery(sql);
string = resultSet.getString(1); // Epic fail. The cursor isn't set yet.

这不是错误.这是记录在案的行为.每个 体面的 JDBC 教程 提到了它.您需要使用 next() 设置 ResultSet 的光标，然后才能访问任何数据.

This is not a bug. This is documented behaviour. Every decent JDBC tutorial mentions it. You need to set the ResultSet's cursor using next() before being able to access any data.

如果您真的对所谓的唯一行 存在 是否真的感兴趣，那么只需检查 next() 的结果.例如在一个虚构的 UserDAO 类中:

If you're actually interested whether the supposedly unique row exist or not, then just check the outcome of next(). For example in a fictive UserDAO class:

public boolean exist(String username, String password) throws SQLException {
    boolean exist = false;

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (ResultSet resultSet = statement.executeQuery()) {
            exist = resultSet.next();
        }
    }

    return exist;
}

如果您实际上只期望 零 或 一个 行，那么只需执行以下操作:

If you actually expect only zero or one row, then just do something like:

public User find(String username, String password) throws SQLException {
    User user = null;

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user WHERE username = ? AND password = MD5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                user = new User(
                    resultSet.getLong("id"),
                    resultSet.getString("username"),
                    resultSet.getString("email"),
                    resultSet.getDate("birthdate")); 
            }
        }
    }

    return user;
}

然后在业务/域对象中相应地处理它，例如

and then just handle it accordingly in the business/domain object, e.g.

User user = userDAO.find(username, password);

if (user != null) {
    // Login?
}
else {
    // Show error?
}

如果您实际上只期望 零 或 许多 行，那么只需执行以下操作:

If you actually expect only zero or many rows, then just do something like:

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

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            users.add(new User(
                resultSet.getLong("id"),
                resultSet.getString("username"),
                resultSet.getString("email"),
                resultSet.getDate("birthdate")));
        }
    }

    return users;
}

然后在业务/域对象中相应地处理它，例如

and then just handle it accordingly in the business/domain object, e.g.

List<User> users = userDAO.list();

if (!users.isEmpty()) {
    int count = users.size();
    // ...
}
else {
    // Help, no users?
}

这篇关于JDBC 驱动程序抛出“ResultSet Closed"；空结果集异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！