Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards

Question

It is said to be a good habit to close all JDBC resources after usage  But if I have the following code  is it necessary to close the Resultset and the Statement   Connection conn   null  PreparedStatement stmt   null  ResultSet rs   null  try       conn      Retrieve connection     stmt   conn prepareStatement    Some SQL       rs   stmt executeQuery      catch Exception e           Error Handling   finally       try   if  rs    null  rs close      catch  Exception e          try   if  stmt    null  stmt close      catch  Exception e          try   if  conn    null  conn close      catch  Exception e          The question is if the closing of the connection does the job or if it leaves some resources in use

User · Answer

Doesn t matter if Connection is poolable or not  Even poolable connection has to clean before returning to the pool     Clean  usually means closing resultsets  amp  rolling back any pending transactions but not closing the connection  Otherwise pooling looses its sense

User · Answer

What you have done is perfect and very good practice   The reason I say its good practice    For example  if for some reason you are using a  primitive  type of database pooling and you call connection close    the connection will be returned to the pool and the ResultSet Statement will never be closed and then you will run into many different new problems   So you can t always count on connection close   to clean up   I hope this helps

User · Answer

Some convenience functions   public static void silentCloseResultSets Statement st        try           while     st getMoreResults    amp  amp   st getUpdateCount      -1             catch  SQLException ignore       public static void silentCloseResultSets Statement    statements        for  Statement st  statements  silentCloseResultSets st

User · Answer

With Java 6 form I think is better to check it is closed or not before close  for example if some connection pooler evict the connection in other thread  - for example some network problem - the statement and resultset state can be come closed   it is not often happens  but I had this problem with Oracle and DBCP   My pattern is for that  in older Java syntax  is   try                    return resp    finally       if  rs    null  amp  amp   rs isClosed              try               rs close              catch  Exception e2                 log warn  Cannot close resultset      e2 getMessage                         if  stmt    null  amp  amp   stmt isClosed              try               stmt close              catch  Exception e2                log warn  Cannot close statement     e2 getMessage                          if  con    null  amp  amp   conn isClosed              try               con close              catch  Exception e2                log warn  Cannot close connection      e2 getMessage                         In theory it is not 100  perfect because between the the checking the close state and the close itself there is a little room for the change for state  In the worst case you will get a warning in long  - but it is lesser than the possibility of state change in long run queries  We are using this pattern in production with an  avarage  load  150 simultanous user  and we had no problem with it - so never see that warning message

User · Answer

Java 1 7 makes our lives much easier thanks to the try-with-resources statement   try  Connection connection   dataSource getConnection        Statement statement   connection createStatement          try  ResultSet resultSet   statement executeQuery  some query                 Do stuff with the result set            try  ResultSet resultSet   statement executeQuery  some query                 Do more stuff with the second result set            This syntax is quite brief and elegant  And connection will indeed be closed even when the statement couldn t be created

User · Answer

The correct and safe method for close the resources associated with JDBC this  taken from How to Close JDBC Resources Properly     Every Time    Connection connection   dataSource getConnection    try       Statement statement   connection createStatement         try           ResultSet resultSet   statement executeQuery  some query             try                  Do stuff with the result set            finally               resultSet close                    finally           statement close            finally       connection close

User · Answer

If you want more compact code  I suggest using Apache Commons DbUtils  In this case   Connection conn   null  PreparedStatement stmt   null  ResultSet rs   null  try       conn      Retrieve connection     stmt   conn prepareStatement    Some SQL       rs   stmt executeQuery      catch Exception e           Error Handling   finally       DbUtils closeQuietly rs       DbUtils closeQuietly stmt       DbUtils closeQuietly conn

User · Answer

I m now using Oracle with Java  Here my point of view    You should close ResultSet and Statement explicitly because Oracle has problems previously with keeping the cursors open even after closing the connection  If you don t close the ResultSet  cursor  it will throw  an error like Maximum open cursors exceeded    I think you may encounter with the same problem with other databases you use   Here is tutorial Close ResultSet when finished      Close ResultSet when finished       Close ResultSet object as soon as you finish   working with ResultSet object even   though Statement object closes the   ResultSet object implicitly when it   closes  closing ResultSet explicitly   gives chance to garbage collector to   recollect memory as early as possible   because ResultSet object may occupy   lot of memory depending on query       ResultSet close

User · Answer

As far as I remember  in the current JDBC  Resultsets and statements implement the AutoCloseable interface  That means they are closed automatically upon being destroyed or going out of scope

User · Answer

No you are not required to close anything BUT the connection  Per JDBC specs closing any higher object will automatically close lower objects  Closing Connection will close any Statements that connection has created  Closing any Statement will close all ResultSets that were created by that Statement  Doesn t matter if Connection is poolable or not  Even poolable connection has to clean before returning to the pool    Of course you might have long nested loops on the Connection creating lots of statements  then closing them is appropriate  I almost never close ResultSet though  seems excessive when closing Statement or Connection WILL close them

User · Answer

I created the following Method to create reusable One Liner   public void oneMethodToCloseThemAll ResultSet resultSet  Statement statement  Connection connection        if  resultSet    null            try               if   resultSet isClosed                      resultSet close                            catch  SQLException e                e printStackTrace                        if  statement    null            try               if   statement isClosed                      statement close                            catch  SQLException e                e printStackTrace                         if  connection    null            try               if   connection isClosed                      connection close                            catch  SQLException e                e printStackTrace                        I use this Code in a parent Class thats inherited to all my classes that send DB Queries  I can use the Oneliner on all Queries  even if i do not have a resultSet The Method takes care of closing the ResultSet  Statement  Connection in the correct order  This is what my finally block looks like   finally       oneMethodToCloseThemAll resultSet  preStatement  sqlConnection

User · Answer

From the javadocs      When a Statement object is closed  its   current ResultSet object  if one   exists  is also closed    However  the javadocs are not very clear on whether the Statement and ResultSet are closed when you close the underlying Connection  They simply state that closing a Connection      Releases this Connection object s   database and JDBC resources   immediately instead of waiting for   them to be automatically released    In my opinion  always explicitly close ResultSets  Statements and Connections when you are finished with them as the implementation of close could vary between database drivers   You can save yourself a lot of boiler-plate code by using methods such as closeQuietly in DBUtils from Apache

[java] Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards?

Examples related to java

Examples related to jdbc