Best Way to Close JDBC Connection Object

In this post, we will discuss why we should close the JDBC connection object, what are the different ways to close the JDBC objects, and what is the best way among those.

Previously we were learning how to establish a JDBC connection with Oracle, JDBC connection with MySQL, JDBC Connection in Eclipse IDE, some Coding standards & guidelines for JDBC which should be followed at the time of developing a JDBC application. So, we don’t think much about the coding standards. In those applications, we have simply closed the JDBC objects in the main method as,

// close connection
rs.close();
st.close();
con.close();

Where, rs.close() closes the ResultSet object, st.close() closes the Statement object and con.close() closes the Connection with database software.

Now, it’s time to talk about coding standards, what are different ways to close the JDBC objects and which is best?

Why we should close the JDBC connection at the end of the application?

The JDBC objects must be closed at the end of the application. Assume we are not closing the JDBC objects, and if many times this application will run then every time objects are created but they are not closed. Hence after a short time period, the memory will be filled up and it can stop the JVM execution. That’s why It is always better to close the database/resource objects after usage.

The order of closing the JDBC object:-

We should close the JDBC objects in reverse order of the object creation. The object creation order is:- con (Connection) then st (Statement) and at last the rs (ResultSet) object, so we should first close the rs (ResultSet) object, then st (Statement) object and at last con (Connection) object.

Writing JDBC closing statements in finally block

We should write the JDBC objects closing statements inside the finally block. The finally block is given in the exception handling for writing the resource-related logic. The finally block execution is compulsory after the execution of the try/catch block.

Why we should write JDBC objects closing statements inside the finally block, not in the try or catch block?

If we don’t want to write the JDBC closing statements inside the finally block then we have to write it inside try block (for success) and also inside all catch blocks (for failure).

But in this way, we need to write the same JDBC closing statements repeatedly. If there are 3 catch blocks are there then we need to write the JDBC closing statements 4 times, 3 times for each catch blocks when the exception is raised, and 1 time for try block when no exception is raised. It gives a code redundancy problem.

So, we should write the JDBC closing statements inside the finally block. The execution of the finally block is compulsory after the execution of try or catch block. Hence JDBC closing statements will be definitely executed.

Using try inside finally block to close connection

The JDBC closing statement can raise the SQLException so to handle those exceptions we should write those closing statements inside try block.

// not recommended approach
finally{
   // close JDBC objects
   try{
      rs.close();
      st.close();
      con.close();
   } catch(SQLException se) {
      se.printStackTrace();
   }
}

It isn’t a good approach to close the JDBC statements. Before the JDBC closing statement, we should check for the null. If the objects are not null then only close the JDBC objects.

// not recommended approach
finally{
   // close JDBC objects
   try{
      if(rs!=null && st!=null && con!=null) {
         rs.close();
         st.close();
         con.close();
      }
   } catch(SQLException se) {
      se.printStackTrace();
   }
}

Problem with the above code:- If at least one JDBC object is null then the if block will not be executed, and all JDBC objects will remain unclosed and writing finally block will be useless. For example:- If the exception raised at the statements rs.close() then the remaining JDBC objects will not be closed. So, it is not recommended to write this logic.


Best approach to close JDBC connection object

// best approach
finally{
   // close JDBC objects
   try {
      if(rs!=null) rs.close();
   } catch (SQLException se) {
      se.printStackTrace();
   }
   try {
      if(st!=null) st.close();
   } catch (SQLException se) {
      se.printStackTrace();
   }
   try {
      if(con!=null) con.close();
   } catch (SQLException se) {
      se.printStackTrace();
   }
}

Now, each JDBC object is closed separately. Exception raised for one object will not be reflected in the other JDBC objects, and if one object is null then other objects will not be disturbed and they will be closed independently. It is recommended to use this approach to close the JDBC objects.

  • The rs.close() closes the ResultSet object and doesn’t allow to process the ResultSet object further.
  • The st.close() closes the Statement object that is we can’t send further queries to database software using that statement object.
  • The con.close() closes the connection with database software.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *