结果集未打开。验证自动提交已关闭。Apache Debry

Question

我正在为我的数据库使用apache
derby。我能够对数据库执行插入操作。以下是尝试显示我唯一的表’MAINTAB’的内容的代码摘录。java.sql.Connection的实例为’dbconn’。

    ResultSet word;

    Statement query;

    String getData="SELECT THEWORD FROM MAINTAB";
    try{
        System.out.println(dbconn.getAutoCommit());
        query = dbconn.createStatement();
        word = query.executeQuery(getData);
        query.close();

        dbconn.setAutoCommit(false);
        System.out.println(dbconn.getAutoCommit());

        for(;word.next();)
            System.out.println(word.getString(1));

    }catch(Throwable e){
        System.out.println("Table fetch failed or result data failed");}

以下是输出。

org.apache.derby.jdbc.EmbeddedDriver loaded.
Database testDB connected
true
false
Table fetch failed or result data failed

---SQLException Caught---

SQLState:   XCL16
Severity: 20000
Message:  ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
java.sql.SQLException: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.getString(Unknown Source)
Closed connection
    at test.ShowData.main(ShowData.java:30)
Caused by: java.sql.SQLException: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(



Unknown Source)
    ... 9 more
Database shut down normally

当它第一次要求验证AUTOCOMMIT是否为OFF时，我从Derby文档中发现默认情况下AUTOCOMMIT对任何连接都是打开的。因此，我已使用dbconn.setAutoCommit（false）将其关闭。仍然会引发错误。

错误之前的输出说明结果集已正确获取。另外，请注意，即使我未将AutoCommit设置为false，也会引发相同的错误。之间，我在日食上跑德比。

Answer 1

问题是您 在 读取结果集 之前 已关闭查询。关闭查询，关闭结果集，因此为什么会出现“ ResultSet not
open”错误。您应该在finally块的最后立即关闭查询：

ResultSet word;

Statement query=null;

String getData="SELECT THEWORD FROM MAINTAB";
try{
    System.out.println(dbconn.getAutoCommit());
    query = dbconn.createStatement();
    word = query.executeQuery(getData);


    dbconn.setAutoCommit(false);
    System.out.println(dbconn.getAutoCommit());

    for(;word.next();)
        System.out.println(word.getString(1));

}catch(Throwable e){
    System.out.println("Table fetch failed or result data failed");
} finally{
    if(query!=null) {
        try {
             query.close();
        }
        catch(SQLException ex) {
              System.out.println("Could not close query");
        }
   }
}