E. Rowlands E. Rowlands - 1 month ago 6
Java Question

If a JDBC Connection object's scope is local, does it need to be explicitly closed? (MySQL database used)

I have a method that is of type

Connection
. It returns the
Connection
value once it connects to the database. Here is the code:

public Connection establishConnection()
{

Connection conn = null;
try
{
this.readLogin(); // prompts user to input for user, pass and host variables using Scanner class
this.createDatabaseIfNeeded(); // creates chessleaguedb if not found
conn = DriverManager.getConnection
("jdbc:mysql://"+host+":3306/chessleaguedb", user, pass);
System.out.println("Successfully connected to chessleaguedb");

}
catch (SQLException z )
{
// logic
}
return conn;
}


I then create an instance of this method's class in my menu class; call the above method and pass its return value to a Connection object in the menu class (I do this just to keep things clean, and hopefully adhere to good OO design principles) :

DatabaseConnection startConnection = new DatabaseConnection(); // class the above method is located in
Connection connect = startConnection.establishConnection();


My question is this: because an active connection is being opened in the above method, do I need to explicitly close it in the method? Or because the
Connection
object in that method is local and will not exist after the method ends, is that necessary? I can't close it, because calling the relevant close method after
return conn;
gives me an unreachable statement error in NetBeans. EDIT* I cannot use Pooling as not Java EE, and cannot use open source software to handle it as work must be my own as this is university work (2nd year).

Answer

Yes, you need to write & handle the closeConnection() in your DatabaseConnection class, otherwise it will create a connection leak in the application. Very soon, your application will run out of connections.

You need to ensure that the below closeConnection() being called in the finally block of the same method from where you are calling establishConnection(), otherwise resource (connection) will escape (causing leaks).

public void closeConnection(Connection conn) {
   try {
        conn.close();
   } catch(SQLException sqlexe) {
     // Connection closing failed
    //Log exception
  }
}  

But, it is NOT a best practice to handle the connections explicitly like this, rather try to use a connection pooling mechanism.

You can look at here for connection pooling.

Comments