Closing database connections in Java

Question

I am getting a little confused  I was reading the below from Java Database Connectivity  Connection conn   DriverManager getConnection        quot jdbc somejdbcvendor other data needed by some jdbc vendor quot         quot myLogin quot         quot myPassword quot      Statement stmt   conn createStatement    try       stmt executeUpdate   quot INSERT INTO MyTable  name   VALUES    my name     quot       finally          It s important to close the statement when you are done with it     stmt close       Do you not need to close the conn connection  What is really happening if the conn close   doesn t occur  I have a private web application I m maintaining that doesn t currently close either form  but is the important one really the stmt one  the conn one  or both  The site keeps going down intermittently  but the server keeps saying it s a database connection issue  My suspicion is that it s not being closed  but I don t know which  if any  to close

User · Answer

Yes  you need to close Connection  Otherwise  the database client will typically keep the socket connection and other resources open

User · Answer

Actually  it is best if you use a try-with-resources block and Java will close all of the connections for you when you exit the try block  You should do this with any object that implements AutoClosable  try  Connection connection   getDatabaseConnection    Statement statement   connection createStatement          String sqlToExecute    quot SELECT   FROM persons quot       try  ResultSet resultSet   statement execute sqlToExecute             if  resultSet next                  System out println resultSet getString  quot name quot                      catch  SQLException e        System out println  quot Failed to select persons  quot       The call to getDatabaseConnection is just made up   Replace it with a call that gets you a JDBC SQL connection or a connection from a pool

User · Answer

It is always better to close the database resource objects after usage  Better close the connection  resultset and statement objects in the finally block  Until Java 7  all these resources need to be closed using a finally block  If you are using Java 7  then for closing the resources  you can do as follows  try Connection con   getConnection url  username  password   quot org postgresql Driver quot        Statement stmt   con createStatement        ResultSet rs   stmt executeQuery sql               Statements   catch          Now  the con  stmt and rs objects become part of try block and Java automatically closes these resources after use

User · Answer

When you are done with using your Connection  you need to explicitly close it by calling its close   method in order to release any other database resources  cursors  handles  etc   the connection may be holding on to  Actually  the safe pattern in Java is to close your ResultSet  Statement  and Connection  in that order  in a finally block when you are done with them  Something like this  Connection conn   null  PreparedStatement ps   null  ResultSet rs   null   try          Do stuff            catch  SQLException ex           Exception handling stuff           finally       if  rs    null            try               rs close              catch  SQLException e       Ignored               if  ps    null            try               ps close              catch  SQLException e       Ignored               if  conn    null            try               conn close              catch  SQLException e       Ignored              The finally block can be slightly improved into  to avoid the null check     finally       try   rs close      catch  Exception e       Ignored          try   ps close      catch  Exception e       Ignored          try   conn close      catch  Exception e       Ignored         But  still  this is extremely verbose so you generally end up using an helper class to close the objects in null-safe helper methods and the finally block becomes something like this    finally       DbUtils closeQuietly rs       DbUtils closeQuietly ps       DbUtils closeQuietly conn      And  actually  the Apache Commons DbUtils has a DbUtils class which is precisely doing that  so there isn t any need to write your own

User · Answer

It is enough to close just Statement and Connection  There is no need to explicitly close the ResultSet object  Java documentation says about java sql ResultSet   A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed  re-executed  or is used to retrieve the next result from a sequence of multiple results    Thanks BalusC for comments   quot I wouldn t rely on that  Some JDBC drivers fail on that  quot

User · Answer

Yes  You need to close the resultset  the statement and the connection  If the connection has come from a pool  closing it actually sends it back to the pool for reuse   You typically have to do this in a finally   block  such that if an exception is thrown  you still get the chance to close this    Many frameworks will look after this resource allocation deallocation issue for you  e g  Spring s JdbcTemplate  Apache DbUtils has methods to look after closing the resultset statement connection whether null or not  and catching exceptions upon closing   which may also help

[java] Closing database connections in Java

Examples related to java

Examples related to database-connection