Why Database Connections are not being closed in this case

Question

I am observing that the Database connections are not being closed my running the command on MYSQL Prompt

 show status like 'Conn%';

The Connections are kept on increasing

This is how i am making Database connections

package com.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;


public class DBConnection {
     final static Logger logger = Logger.getLogger(DBConnection.class);
 private static DataSource dataSource;
    static {
            try {
                  dataSource = (DataSource) new InitialContext().lookup("java:/comp/env/jdbc/MYDATABASE");
            } catch (NamingException e) {
                try {
                    throw new Exception("'jndifordbconc' not found in JNDI",e);
                } catch (Exception e1) {
                  logger.error(e1);
                }
            }
        }





 public static Connection getDBConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
               logger.error(e);
            return null;
        }

    }




    public static void close(Connection con)
    {
        if (con != null)
        {
            try
            {
                con.close();
            }
            catch (SQLException e)
            {
                   logger.error(e);
            }
        }
    }

    public static void close(Statement stmt, ResultSet rs) 
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            }
            catch (SQLException e)
            {
                   logger.error(e);
            }
        }
        if (stmt != null)
        {
            try
            {
                stmt.close();
            }
            catch (SQLException e)
            {
                  logger.error(e);
            }
        }
    }
}

<Resource name="jdbc/MYDATABASE"
      auth="Container"
      type="javax.sql.DataSource"
      driverClassName="com.mysql.jdbc.Driver"
       url="jdbc:mysql://localhost:3306/Test?allowMultiQueries=true"
      factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
     username="root"
      password="Meridian@123"
     initialSize="5"
      maxActive="25"
      maxIdle="10"
     minIdle="10"
    defaultTransactionIsolation="READ_COMMITTED"
     suspectTimeout="60"
     timeBetweenEvictionRunsMillis="3400"
     minEvictableIdleTimeMillis="5500"
    validationQuery="SELECT 1"
    validationInterval="3400"
    testOnBorrow="true"
    removeAbandoned="true"
    removeAbandonedTimeout="55"
    jmxEnabled = "true"
    closeMethod="close"
      />

And this is how i am making connections and closing connections

dbConnection = DBConnection.getDBConnection();

public class AutofillArea {
    final static Logger logger = Logger.getLogger(AutofillArea.class);
    @GET
    @Consumes("application/text")
    @Produces("application/json")
    public String getData(
            @QueryParam("city") String city ,
            @QueryParam("area") String  area 

            )
    {
        city = Utility.getProperString(city);
        area = Utility.getProperString(area);

        String response =" ";
        Connection dbConnection = null;
        PreparedStatement statePreparedStmt = null;
        ResultSet stateResultSet = null;
        JSONArray jsonarray = new JSONArray();
        try
        {
            String sql = "select distinct area from mytable where city = ?;";
            dbConnection = DBConnection.getDBConnection();
            statePreparedStmt = dbConnection.prepareStatement(sql);
            statePreparedStmt.setString(1 ,city);
            stateResultSet = statePreparedStmt.executeQuery();
            while(stateResultSet.next())
            {
                jsonarray.put(stateResultSet.getString("area"));
            }
            response = "jsonCallbackarea("+jsonarray.toString()+")";
        }
        catch(Exception e)
        {
            logger.error(e);
        }

        finally
        {
            try
            {
                DBConnection.close(statePreparedStmt,stateResultSet);   
            }
            catch(Exception e)
            {
                logger.error(e);
            }

            try
            {
                DBConnection.close(dbConnection);   
            }
            catch(Exception e)
            {
                logger.error(e);
            }

        }
        return response;
    }
}

Could you please let me know how to resolve the issue ??

Answer 1

You are using a connection pool. Your pool can create up to 25 connections. When you close a connection, it is not really closed, but released to the pool.