java - SQLException: the result set is closed

Question

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

}

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

}                                 

someone please help.

Answer 1

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.

Example:

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

}

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

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.

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

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

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