SQLException:结果集已关闭 [英] SQLException: the result set is closed

问题描述

我是使用NetBeans的Java初学者，我试图创建类似于教师注册系统的东西.我使用SQL Server 2005创建数据库. 在实施过程中，我试图创建一个使学生能够注册其学科的功能，因此该功能基本上是在搜索学生已完成其先决条件的学科.所以我写了下面的代码:

I am a beginner in Java using NetBeans and I was trying to create something similar to a faculty registration system. I use SQL Server 2005 to create the DB. During the implementation I was trying to create a function to make students able to register their subjects so the function basically searches for the subjects that the student has finished their prerequisites. So I wrote the following code:

package GUIs;
import java.sql.*;
import javax.swing.*;


public class AddSubToStd extends javax.swing.JFrame {

final void FillList1(){
    try{
    String url = "jdbc:sqlserver://localhost:1433;databaseName=BIS";
    String username = "sa";
    String password = "*****";
    Connection conn  = DriverManager.getConnection(url,username,password);
    Statement stmt = conn.createStatement();
    DefaultListModel DLM = new DefaultListModel();
    ResultSet res = stmt.executeQuery("SELECT * FROM Students");
    while(res.next()){
        DLM.addElement(res.getString("ID"));
    }
    List1.setModel(DLM);
}
    catch(SQLException e){
        JOptionPane.showMessageDialog(null, e.toString());
    }
}

final void FillList2(){
    try{
    String url = "jdbc:sqlserver://localhost:1433;databaseName=BIS";
    String username = "sa";
    String password = "*****";
    Connection conn  = DriverManager.getConnection(url,username,password);
    Statement stmt = conn.createStatement();
    DefaultListModel DLM = new DefaultListModel();
    String Query = "SELECT * FROM FinishedCourses WHERE ID = '"+List1.getSelectedValue()+"'";
    ResultSet res = stmt.executeQuery(Query);

    ResultSet res1;
    String S_Code;
    String Query1;

    while(res.next()){

         S_Code = res.getString("S_Code");
         Query1 = "SELECT * From Subjects WHERE Prerequisite = '"+S_Code+"'";
         res1 = stmt.executeQuery(Query1);

        while(res1.next()){

            DLM.addElement(res.getString("S_Code"));
        }

    }
    conn.close();
    stmt.close();
    List2.setModel(DLM);
}
    catch(SQLException e){
        JOptionPane.showMessageDialog(null, e.toString());
    }
}

public AddSubToStd() {
    initComponents();
    FillList1();

}

但是我得到SQLException，它说我尝试调用FillList2()

but I get the SQLException that says the result set is closed when I try to invoke FillList2()

 private void UpdateAllowedActionPerformed(java.awt.event.ActionEvent evt) {                                              
    try{
    String url = "jdbc:sqlserver://localhost:1433;databaseName=BIS";
    String username = "sa";
    String password = "*****";
    Connection conn  = DriverManager.getConnection(url,username,password);
    FillList2();    
    }
    catch(SQLException e){
        JOptionPane.showMessageDialog(null, e.toString());
    }

}                                 

请有人帮忙.

推荐答案

您正在重新使用相同的Statement对象来执行两个查询.当stmt用于执行第二个查询时，前一条语句返回的ResultSet对象将关闭.为每个查询创建两个对象.

You're re-using the same Statement object to execute two queries. When the stmt is used to execute the second query, the ResultSet object returned by the previous statement is closed. Create two objects for each query.

示例:

Statement stmt = conn.createStatement();
Statement stmt1 = conn.createStatement();
...
ResultSet res = stmt.executeQuery(Query);

...

while(res.next()){

     S_Code = res.getString("S_Code");
     Query1 = "SELECT * From Subjects WHERE Prerequisite = '"+S_Code+"'";
     res1 = stmt1.executeQuery(Query1); // use a separate statement

    while(res1.next()){

        DLM.addElement(res.getString("S_Code"));
    }

}

以下 Statement API文档:

This is explained in the following quote from the Statement API docs:

默认情况下，每个Statement对象只能同时打开一个ResultSet对象.因此，如果一个ResultSet对象的读取与另一个对象的读取交错，则每个对象必须由不同的Statement对象生成. Statement界面中的所有执行方法(如果存在已打开的语句，则隐式关闭该语句的当前ResultSet对象).

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

此外，强烈建议按相反的顺序关闭JDBC资源，例如，应先关闭Statement s然后再关闭Connection，并且应该在finally块:

Also it is strongly recommended to close the JDBC resources in the reverse order of their allocation, i.e.g you should close Statements then close the Connection, and you should do this in a finally block:

catch(SQLException e){
    JOptionPane.showMessageDialog(null, e.toString());
} finally {
    if(res != null) {
        res.close();
    }
    if(res1 != null) {
        res1.close();
    }
    if(stmt != null) {
        stmt.close();
    }
    if(stmt1 != null) {
        stmt1.close();
    }
    if(conn != null) {
         conn.close();
    }
}

如果您使用的是Java 7，则可以使用try-with-resources语句自动关闭这些资源(而不必显式调用close()方法):

If you're using Java 7, you can use a try-with-resources statement to automatically close these resources (without having to explicitly call the close() method):

// try-with-resources statement declaring two resources
try(Connection conn  = DriverManager.getConnection(url,username,password);
    Statement stmt = conn.createStatement()) {
    ...
} catch(SQLException e){
    JOptionPane.showMessageDialog(null, e.toString());
}

try-with-resources将确保先关闭Statement对象，然后再使用Connection.

The try-with-resources will make sure to close the Statement object, then the Connection after they are used.

这篇关于SQLException:结果集已关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！