Why JDBC connections needs to close in finally block?

Question

Connection conn = null;
Statement stmt = null; 
ResultSet rs = null;
try
{
    conn = geting the connection object ( using DriverManager.getConnection() method or using connection pool)
    stmt = conn.createStatement ("select ...");
   // some other logic here
}
catch (SQLException e)
{
    // handling the exceptions
}
finally
{

}

Here my question is while closing the connection object in the following cases what are the problems it will come.

1. Assume if there is no exception occurred, in try block there it self closing the connection object.

   try {
        // same above code 
        stmt.close();
        conn.close();
   } catch(Exception ex) {
       // handling the exceptions
   } finally {
   
   }
   

2. If some exception is occured so it will goes to catch block, there it self closing the connection object.

   try {
        // same above code 
   
   } catch(Exception ex) {
       // handling the exceptions
       stmt.close();
       conn.close();
   } finally {
   
   }
   

3. Closing the connection object in finally block.

   try {
        // same above code 
   
   } catch(Exception ex) {
       // handling the exceptions
   
   } finally {
       stmt.close();
       conn.close();
   } 
   

4. Difference between closing the connection object using close() method and closing the pooled connection using close().

Note: Please don't say closing the connection object in finally block is good. I know that one. If i keep the close connection in try block, catch block is there any problems please explain.

Answer 1

It is recommended to close the connection in the finally block because if you have multiple catch blocks (as you should: you should never catch generic Exception) you will not have to re-write the closing statements.

A finally block will always be executed after a try, no matter what happens. So if you get a NullPointerException, or some other exception you didn't handle, with the finally block you will be sure that your resources are closed properly.

But if you are on java 7, I'd suggest to use the try-with-resources block

Answer 2

As database is consuming more resources, it is always recommended to close your connection after your processing is done whether successfully or not.

You said that adding conn.close() in try, this is fine if your query runs without any exception and what if your query has any issues, the connection would not be closed. So it is always recommended to close your connection in finally block which is executed always irrespective of whether exception occurred or not. Also if you try to put the closing statement in catch block and your trying to handle multiple exceptions you will need to repeat your code which is not recommended.

Answer 3

Answer is that 'We must close the connection after using it.' But the actual question is why we must close the connection after using?.

1.) When we create a connection with a remote database server lets say MySQL Server, It also keeps a connection open for you. Due there is a also a limitation/configuration at database server end for number of connection allowed. If we don't close the connection from our end it will remain open and such way after a short while number of allowed connections will be exceeded at database server end and we will not be able to make further connection with that database.

2.) Auto release connections time setting at database server side.

If we don't close connection from our end and we don't execute any query for certain period of time (As I think default time setting in MySql is 28800 sec. means 8 Hrs. Although we can change it.) it will automatically release the connection from server end.

Important! What will happen in that situation when your jdbc Connection object is really released at database server end and you think it is still connected because you never disconnected/closed it and you are trying to executing query on that connection using statement. Really critical.

That's why we must always close the connection and execute statement on connected connection object.

Answer 4

The finally block is always executed, including the following cases:

• Normal termination of the try-block.

• Exceptional termination of the try-block when exception is handled in some catch.

• Exceptional termination when exception is propagated to the caller.

• A return statement in the middle of the block:

  try {
     if(something) return false; // finally is executed here as well
     ...
  } finally { ... }
  

• A break or continue statement if the whole try-catch is inside the loop:

  while(condition) {
      try {
          if(something) continue; // finally is executed here
          else if(somethingElse) break; // finally is executed here
          ...
      }
      finally {
      }
  }
  

Thus no matter how you exit the try block, the finally will be executed and you should not care about checking every possible exiting way. This is really convenient.

Answer 5

finally block always execute :

try{
 // code logic
 }catch(SQLException ex){
 // catch exception
 }finally{
   if (dbConnection != null){
            dbConnection.close();
       }
 }

Answer 6

• What happens if an exception you're not handling gets thrown? (I hope you're not catching Throwable...)
  • What happens if you return from inside the try block?
  • What happens if the catch block throws an exception?

A finally block makes sure that however you exit that block (modulo a few ways of aborting the whole process explicitly), it will get executed. That's important for deterministic cleanup of resources.

Note : Also for preventing application resource leaking we have to close connection anyway..!!

Thanks.!!

Answer 7

You can close connection in try or in catch block, but will tell you why is good to close connection in finally Block

Suppose you have try catch block as below:

try{
stmt1..
stmt2..
conn.close
}
catch(exception e){
}

Now suppose exception is raised by stm1 then your connection will remain open.

Now consider you are closing connection in catch block..what if no exception raised then control will never come in catch block.. here connection is still open.. I hope i am able to clear few of you doubts.