This discussion is archived
3 Replies Latest reply: Apr 3, 2013 9:06 PM by 1000389 RSS

Returning a ResultSet and closing the connection etc

1000389 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thankyou rp0428 and EJP for your answers and solution on this

    Lee

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points