Will the code have any impact on MySQL available connections?

Question

I have developed a Java web application which connects to MySQL Database using JDBC connector and making the pool of 2 connections. The application is deployed on tomcat server.

Mmy question is, does this code impacts to MySQL available connections if I deploy the application multiple times and the code doesn't have any line to close the available connection when I am shutting down the tomcat? Does tomcat take care of closing the connections when getting restarted?

Connection Util:

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class MySQLConnectionPool {


     public static DataSource setUpPool(){

         GenericObjectPool gPool = null;
         String dbName = "DBName";
         String userName = "Username";
         String password = "Password";
         String hostname = "Host";
         String port = "Port";
         try {
             Class.forName("com.mysql.jdbc.Driver");
            // Creates an Instance of GenericObjectPool That Holds Our Pool of Connections Object!
                gPool = new GenericObjectPool();
                gPool.setMaxActive(2);

                // Creates a ConnectionFactory Object Which Will Be Use by the Pool to Create the Connection Object!
                ConnectionFactory cf = new DriverManagerConnectionFactory("jdbc:mysql://" + hostname + ":" + port + "/" + dbName, userName, password);

                // Creates a PoolableConnectionFactory That Will Wraps the Connection Object Created by the ConnectionFactory to Add Object Pooling Functionality!
                PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, gPool, null, null, false, true);
         }catch (Exception e) {
            // TODO: handle exception
             System.out.println("Error: "+e.toString());
        }

            return new PoolingDataSource(gPool);
     }

}

DAO:

@Override
public ArrayList<User> getUserDetails(String environment, String MySQLQuery) {
    // TODO Auto-generated method stub
    ArrayList<User> users = new ArrayList<User>();
    Connection connObj = null;
    Statement stmt = null;
    ResultSet result = null;
    try {   
        DataSource dataSource = MySQLConnectionPool.setUpPool();

        // Performing Database Operation!
        System.out.println("\n=====Making A New Connection Object For Db Transaction=====\n");
        connObj = dataSource.getConnection();

        stmt = connObj.createStatement();
        result = stmt.executeQuery(MySQLQuery);
        while(result.next()) {
            //Some code here
        }

        System.out.println("\n=====Releasing Connection Object To Pool=====\n");            
    } catch(Exception sqlException) {

    } finally {
        try {
            // Closing ResultSet Object
            if(result != null) {
                result.close();
            }
            // Closing Statement Object
            if(stmt != null) {
                stmt.close();
            }
            // Closing Connection Object
            if(connObj != null) {
                connObj.close();
            }
        } catch(Exception sqlException) {

        }
    }
    return users;
}

Answer 1

If you stop the entire tomcat, then your connection to the database will be closed because the java executable running Tomcat will release the connection when stopping.

Even if you don't stop the server, if Java decides that your Connection object is no longer used, then it will be garbage collected. You just don't know when.

On another notes:

• You should probably look for ways to embed DataSource onto the server and obtain DataSource from the server: this page may help.
• You should rewrite your code to use try with resources (Java 7++) which take care on closing your resource correctly (your code is wrong).

With try with resources:

DataSource dataSource = MySQLConnectionPool.setUpPool();
try (Connection connObj = dataSource.getConnection()) {   
  try (Statement stmt = connObj.createStatement()) {
    try (ResultSet result = stmt.executeQuery(MySQLQuery)) {
      while(result.next()) {
        //Some code here
      }
    }
  } 
} catch (SQLException sqlException) {
  // do something
}

Or:

DataSource dataSource = MySQLConnectionPool.setUpPool();
try (Connection connObj = dataSource.getConnection();
     Statement stmt = connObj.createStatement();
     ResultSet result = stmt.executeQuery(MySQLQuery)
    ) {
  while(result.next()) {
    //Some code here
  }
} catch (SQLException sqlException) {
  // do something
}

As I stated above, your code is wrong because you may have an exception (at least, a SQLException) when closing the ResultSet, or a Statement, so the later object would never be released by your code:

    try {
        // Closing ResultSet Object
        if(result != null) {
            result.close(); // if it fails, stmt and connObj are not closed.
        }
        // Closing Statement Object
        if(stmt != null) {
            stmt.close(); // if it fails, connObj is not closed.
        }
        // Closing Connection Object
        if(connObj != null) {
            connObj.close();
        }
    } catch(Exception sqlException) {

    }

If you can't use try with resources (Java 6 is presumably no longer supported since decades, but who knows), then your code code should looks like:

Connection connObj = null;
Statement stmt = null;
ResultSet result = null;
try {
  // ... do whatever is needed
} finally {
  if(result != null) {
    try {result.close();} catch (Exception ignored) {}
  }
  if(stmt != null) {
    try {stmt.close();} catch (Exception ignored) {}
  }
  if(connObj != null) {
    try {connObj .close();} catch (Exception ignored) {}
  }
}