Where to close result set

Question

I am getting result set is already closed where i am passing resultset into other method.where should i closed result set.

public void mainTest(){

ResultSet rs= pstmt.executeQuery(query);

List list = populateRS(rs);

if(rs!=null)rs.close();
}

public List populateRS(ResultSet rs){

//work with result set
if(rs!=null)rs.close();
}

score 5 · Accepted Answer · answered Aug 13 '13 at 18:53

5

You should probably use a try-finally block to close the ResultSet even if populateRS (or something else) throws an exception:

ResultSet rs;
try {
    rs = pstmt.executeQuery(query);
    List list = populateRS(rs);
} finally {
    if (rs != null) {
        rs.close();
    }
}

answered Aug 13 '13 at 18:53

andersschuller

13,509
2
42
33

So i should close the result set in mainTest not in populateRS.is it? public void mainTest(){ ResultSet rs= pstmt.executeQuery(query); List list = populateRS(rs); if(rs!=null)rs.close(); } public List populateRS(ResultSet rs){ //work with result set } – Jaque Aug 13 '13 at 18:58
Yes, I think it is always clearer (and less error-prone) to create and close a `ResultSet` in one method, and let any other method you call just deal with handling the results themselves (without having to worry about opening, closing, checking if it's already open, ...). – andersschuller Aug 13 '13 at 19:00
@Jaque I think that is probably a different problem with your `populateRS` method. See similar questions [here](http://stackoverflow.com/questions/3502005/java-sql-sqlexception-exhausted-resultset) and [here](http://stackoverflow.com/questions/8495835/sqlexception-exhausted-resultset). – andersschuller Aug 13 '13 at 19:05

Mike Samuel · Answer 2 · 2017-11-23T05:03:50.677

5

Close in the same method you open in if at all possible. Consistently doing this makes it easy for code-reviewers and maintainers to easily triage resources into (obviously freed, obviously problematic, and needs more attention).

A few other notes:

Use try (...) or do the closing in finally so the resource is closed even when the code using it fails with an exception.
Use the @WillClose and @WillNotClose annotations as appropriate so that IDEs and tools like findbugs can point out problems.

public void mainTest(){
  List<?> list;
  try (ResultSet rs = pstmt.executeQuery(query)) {
    list = populateRS(rs);
  }
  // work with list
}

public List<?> populateRS(@WillNotClose ResultSet rs){
  //work with result set
}

or if you're stuck with older Java:

public void mainTest(){
  List<?> list;
  ResultSet rs = pstmt.executeQuery(query);
  try {
    list = populateRS(rs);
  } finally {
    if(rs!=null)rs.close();
  }
  // work with list
}

edited Nov 23 '17 at 05:03

answered Aug 13 '13 at 18:53

Mike Samuel

118,113
30
216
245

1

Where did the `@WillNotClose` annotation come from? Is that part of the JDK or some other library? – andersschuller Aug 13 '13 at 18:55
@andersschuller JSR-305 – Dave Newton Aug 13 '13 at 18:56
@andersschuller, Please see the link I added. – Mike Samuel Aug 13 '13 at 19:00
@Jaque, check that there are more rows before getting the next row. – Mike Samuel Aug 14 '13 at 14:38
1

And from Java 7 on, use try with autoclose – Christophe Roussy Nov 22 '17 at 10:44

score 3 · Answer 3 · answered Aug 13 '13 at 19:06

Use the new try-with-resources statement which would automatically close the ResultSet whether an exception occurs or not because it implements AutoCloseable.

The try-with-resources statement is a try statement that declares one or more resources. A resource is an object that must be closed after the program is finished with it. The try-with-resources statement ensures that each resource is closed at the end of the statement.

public void mainTest()
{
  try (ResultSet rs = pstmt.executeQuery(query)) { 
     List list = populateRS(rs);
  } catch (SQLException ex) {

  }
}

public List populateRS(ResultSet rs){
    // work with result set
}

Only from Java 7 on. – Christophe Roussy Nov 22 '17 at 10:45 — Christophe Roussy, Nov 22 '17 at 10:45

Prabhaker A · Answer 4 · 2013-08-13T18:59:50.523

It is good to close where you are opening .
It is good programming practise to close all resouces in finally block

       public void mainTest()
       {
         ResultSet rs = null;
         try{ 
              rs= pstmt.executeQuery(query);
             List list = populateRS(rs);
          }finally{
               try {
                 rs.close();
               } catch (SQLException ex) {

               }
          }
      }

     public List populateRS(ResultSet rs){

        //work with result set

    }

according to java docs

Putting cleanup code in a finally block is always a good practice, even when no exceptions are anticipated.

score 1 · Answer 5 · answered Aug 13 '13 at 18:50

1

Close things near where you open them. In this case that would be in the mainTest method after you call populateRS. If a method doesn't open something, it shouldn't close it.

answered Aug 13 '13 at 18:50

Nathan Hughes

94,330
19
181
276

So i should close the result set in mainTest not in populateRS.is it? public void mainTest(){ ResultSet rs= pstmt.executeQuery(query); List list = populateRS(rs); if(rs!=null)rs.close(); } public List populateRS(ResultSet rs){ //work with result set } – Jaque Aug 13 '13 at 18:57
I think it is often appropriate for methods to close things that other pass to it, as long as the contract is clear. It is often done with streams. – Colin D Aug 13 '13 at 18:58
@Jaque: yes. you can put the rs.close in a finally block or use try-with-resources. – Nathan Hughes Aug 13 '13 at 19:06
@Colin: there can be exceptions, but this is an easy case. like Dave Newton said, this is basic Separation of concerns. – Nathan Hughes Aug 13 '13 at 19:10

JHS · Answer 6 · 2013-08-13T19:00:39.397

0

You should close the ResultSet in the mainTest method to segregate populateRS method's participation in the lifecycle of the ResultSet.

You should have a finally block in which you should close the ResultSet. This practice gives you a guarantee that the ResultSet is closed even if an exception is thrown.

edited Aug 13 '13 at 19:00

answered Aug 13 '13 at 18:52

JHS

7,761
2
29
53

1

I disagree; the responsibility of populateRS should be to work w/ the result set, not to participate in its lifecycle. Basic separation of concerns. – Dave Newton Aug 13 '13 at 18:54

Where to close result set

6 Answers6