Configure JNDI Connection Pooling:

Configure JNDI Connection Pooling:

For establishing the connection pool i have created a dynamic web-project with SampleConn, with MySql Database and loggers. So, make sure we have the mysql.jar in tomcat working lib directory and log4j.jar in your project library.

Here i am listing out the steps to follow:

------------------------------------------------------------------------------------------
Step 1) Configure web.xml file

Here i am invoking a servlet on startup of server (GetConnPool)

---------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>SampleConn</display-name>
  <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/JIT</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
  <servlet>
    <servlet-name>Connection</servlet-name>
    <servlet-class>com.jit.servlets.GetConnPool</servlet-class>;
    <load-on-startup>1</load-on-startup>
  </servlet>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>
----------------------------------------------------------------

Step 2) Create a context.xml in META-INF of your Project

-----------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<Context privileged="true" antiResourceLocking="false"
    antiJARLocking="false" debug="1" reloadable="true" path="">
   
     <!-- maxActive: Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL dB username and password for dB connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->
   
    <!-- url: The JDBC connection url for connecting to your MySQL dB.
         -->
   
   
    <Resource name="jdbc/JIT" auth="Container" type="javax.sql.DataSource"
         username="root"
         password="root" maxActive="-1" maxIdle="-1" maxWait="-1"
         driverClassName="com.mysql.jdbc.Driver"
         url="jdbc:mysql://localhost:3306/sample?autoReconnect=true"
         />
   
</Context>

--------------------------------------------------------------------

Step 3) Configure your log4j.properties file in your project source directory

--------------------------------------------------------------------

# Set root category priority to INFO and its only appender to CONSOLE.
log4j.rootCategory=DEBUG, CONSOLE
#log4j.rootCategory=INFO, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.Threshold=DEBUG
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=[%d{dd MMM yyyy HH:mm:ss}] %l - %m%n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.Threshold=DEBUG
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=[%d{dd MMM yyyy HH:mm:ss}] %l - %m%n

log4j.logger.org.apache.commons.digester.Digester=info


--------------------------------------------------------------------
Step 4)  GetConnPool.java
Here in the init() i am invoking & closing the connection

---------------------------------------------------------------------
package com.jit.servlets;
/**
 * @author Vinay Guntaka
 */
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jit.connections.ConnectionUtil;

/**
 * Servlet implementation class GetConnPool
 */
public class GetConnPool extends HttpServlet {
    private static final long serialVersionUID = 1L;
      
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetConnPool() {
        super();
        // TODO Auto-generated constructor stub
    }
     @Override
    public void init() throws ServletException {
        // TODO Auto-generated method stub
        super.init();
       
        ConnectionUtil.closeConnection(ConnectionUtil.getConnection());
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}

------------------------------------------------
Step 5) ConnectionUtil.Java

This is the main program that we are fetching the JNDI Connection Pool Object
------------------------------------------------
package com.jit.connections;
/**
 * @author Vinay Guntaka
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

import org.apache.log4j.Logger;

public class ConnectionUtil
{
    //Initlizing the Logger
    private static final Logger logger = Logger.getLogger(ConnectionUtil.class);
   
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context) initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("jdbc/JIT");
            conn = ds.getConnection();
            if (conn != null) {
                logger.info("Successfully Obtained Connection From ConnectionPool");
            }
            return conn;
        } catch (SQLException se) {
            logger.error(se.getMessage());
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return conn;
    }
    public static Connection closeConnection(Connection conn){
        try {
            if(conn != null){
                conn.close();
                conn = null;
                logger.info("Successfully Closed Connection From ConnectionPool");
               
            }
        } catch (SQLException se) {
            logger.error(se.getMessage());
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return conn;
    }

   
}
----------------------------------------------------------------------