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

问题描述

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

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

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

没有太多的 JDBC 经验，我的理论(我无法通过 JavaDocs for ResultSet 确认)是

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

我看到了这个错误报告但我不确定如果有关系.

我的问题是:

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

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

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

谢谢！

解决方案

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

resultSet = statement.executeQuery(sql);string = resultSet.getString(1);//史诗失败.光标尚未设置.

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

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

public boolean existing(String username, String password) throws SQLException {布尔存在 = 假；尝试 (连接连接 = database.getConnection();PreparedStatement 语句 = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");){statement.setString(1, 用户名);statement.setString(2, 密码);尝试 (ResultSet resultSet = statement.executeQuery()) {存在 = resultSet.next();}}返回存在；}

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

public User find(String username, String password) throws SQLException {用户 user = null;尝试 (连接连接 = database.getConnection();PreparedStatement statement = connection.prepareStatement("SELECT id, username, email,birthdate FROM user WHERE username = ? AND password = MD5(?)");){statement.setString(1, 用户名);statement.setString(2, 密码);尝试 (resultSet = statement.executeQuery()) {如果(resultSet.next()){用户 = 新用户(resultSet.getLong("id"),resultSet.getString("用户名"),resultSet.getString("email"),resultSet.getDate("生日"));}}}返回用户；}

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

User user = userDAO.find(username, password);如果(用户！= null){//登录?}别的 {//显示错误?}

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

public Listlist() 抛出 SQLException {列表<用户>用户 = 新的 ArrayList<用户>();尝试 (连接连接 = database.getConnection();PreparedStatement statement = connection.prepareStatement("SELECT id, username, email,birthdate FROM user");结果集 resultSet = statement.executeQuery();){而(resultSet.next()){用户.添加(新用户(resultSet.getLong("id"),resultSet.getString("用户名"),resultSet.getString("email"),resultSet.getDate("生日")));}}回访用户；}

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

List用户 = userDAO.list();如果 (!users.isEmpty()) {int count = users.size();//...}别的 {//求助，没有用户?}

I have a problem in JDBC driver for SQLite.

I am executing a query with SELECT statement.

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

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

• 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.

My qeustions are:

1. Is the theory above correct?
2. Is it a bug? Feature? (and if so, can someone point to documentation please?)
3. Is it specific to SQLIte's JDBC or to generic ResultSet in all JDBC drivers?
4. What is the correct way of doing stuff like that??

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?

(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)

Thanks!

解决方案

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.

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.

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屋！