Where to close statements in JDBC

Question

I am using JDBC to retrieve data from a database. I understand that a Statement needs to be closed after it is used. However, I keep run into the same error telling me that no operation is allowed after the statement is closed. The structure of my code is like

public void foo() {

Statement;
try {
} catch{
}
Statement.close();
}

In my code, I need to call this function repeatedly. I was wondering where to close the Statement.

Thanks

Answer 1

According to the Javadocs Statement.close() Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources. Which means you should close it after you done and not planning to use it again. I would, actually pay more attention to closing your Connection.

In your method which you say you call repeatedly you are calling Statement.close() which means that you can only use it once since after the first call you Statement is closed and cannot be used anymore.

It would be nice to see some of your code if you want a better answer

Answer 2

Note that JDBC resources should always be closed, so, its better to put that to the 'finally' block.

You can also consider Spring DAO usage - it is a thin wrapper on top of JDBC and covers most of JDBC boilerplate stuff.

Answer 3

1. ResultSets also need to be closed.

2. It sounds like you might be doing something like accessing a Blob, the Blob object often goes back through the connection to read the byte data from the database. So read all of the byte[] data before you close the connection. If that's not possible because there's too much data and you are trying to stream the bytes then you're just going to have to stick the connection somewhere safe and call close on it later.

3. Close statements should go into finally blocks, and be null protected - but it's such common and ugly code so stick in a static method somewhere.

---

public List someMethod() {

 Statement stmt;
 ResultSet rset;
 try {
  stmt = con.createStatement();
  rset = stmt.executeQuery(....);

  List resultList = ...create a list
  // get the data from the rset

  return resultList;
 }
 catch(SQLException ex) {
  throw new MyDatabaseException(ex);
 }
 finally {
 }

}

public class DatabaseUtils {

 public void close(Statement stmt, ResultSet rset) {
  try {
    if(rset != null) {
      rset.close();
    }
  }
  catch(SQLException ex) {
    throw new MyDatabaseException(ex);
  }
  finally {
    if(stmt != null) {
      throw new MyDatabaseException(ex);
    }
  }
 }

}

Answer 4

Isn't this quite the normal way of doing this:

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try {

// do your database stuff here

} catch(SQLException e) {

// do your proper error handling here

} finally {

   try {
      if ( resultSet!=null ) resultSet.close();
   } catch (Exception e ) {
      // empty catch block, there's isn't much you can do if closing fails
   }

   try {
       if ( statement!=null ) statement.close();
   } catch (Exception e ) {
      // empty catch block, there's isn't much you can do if closing fails
   }

   try {
       if ( connection!=null )  connection.close();
   } catch (Exception e ) {
      // empty catch block, there's isn't much you can do if closing fails
   }
}