3 Replies Latest reply: Apr 3, 2013 11:06 PM by 1000389 RSS

    Returning a ResultSet and closing the connection etc

    1000389

      Hi all,

      I'm having difficulty in closing my recordset, connection and statement after populating and returning the recordset. I am trying the close these in the method SelectTeams that I'm calling from the SqlSelect class however when I do I get an error saying rs is closed, conn is closed etc. I want to close these once I'm finished with them to follow best practice. Is it possible to close these and return the ResultSet rs to my main class, or do I need to do what I need to do (in this case just printing out the contents of rs) within the SelectTeams method itself?

      My main class is here:

      public class SqlInterface {
      public static void main (String[] args) throws SQLException, ClassNotFoundException {
      SqlSelect select = new SqlSelect();
      ResultSet rs = select.SelectTeams();
      try {
      while (rs.next()) {
      System.out.print(rs.getString("team_name") + "\t");
      System.out.print(rs.getString("team_principal") + "\t");
      System.out.print(rs.getString("driver_one") + "\t");
      System.out.print(rs.getString("driver_two") + "\t");
      System.out.println(rs.getString("team_championship_position") + "\t");
      }
      } catch (SQLException e) {
      e.printStackTrace();
      }
      }
      }

      and my SqlSelect class is here:

      package SqlPackage;

      import java.sql.SQLException;
      import java.sql.ResultSet;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.Statement;
      /**
      *
      * @author lhardy
      */
      public class SqlSelect {
      public ResultSet SelectTeams() throws SQLException, ClassNotFoundException {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;

      try{
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      String connString = "jdbc:sqlserver://D4XFF2S\\SQLEXPRESS;databaseName=JavaDB;user=sa;password=pAss1_word";
      conn = DriverManager.getConnection(connString);

      String sql = "SELECT * FROM tblTeams";
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      } catch (SQLException e) {
      e.printStackTrace();
      } catch (ClassNotFoundException n) {
      n.printStackTrace();
      }
      rs.close();
      return rs;
      }
      }

      Thanks

      Lee

        • 1. Re: Returning a ResultSet and closing the connection etc
          rp0428
          >
          I'm having difficulty in closing my recordset, connection and statement after populating and returning the recordset. I am trying the close these in the method SelectTeams that I'm calling from the SqlSelect class however when I do I get an error saying rs is closed, conn is closed etc. I want to close these once I'm finished with them to follow best practice. Is it possible to close these and return the ResultSet rs to my main class, or do I need to do what I need to do (in this case just printing out the contents of rs) within the SelectTeams method itself?
          >
          Neither one. You close them when you are finished with them.

          You can't close the result set in 'SelectTeams' and then pass the result set back. Once you close it you can't use that result set anymore.
          • 2. Re: Returning a ResultSet and closing the connection etc
            EJP
            do I need to do what I need to do (in this case just printing out the contents of rs) within the SelectTeams method itself?
            Yes, and close it in a finally{} block. Also the Statement and the Connection.
            • 3. Re: Returning a ResultSet and closing the connection etc
              1000389
              Thankyou rp0428 and EJP for your answers and solution on this

              Lee