Tomcat Connection Pooling

Here I am going to discuss Apache Tomcat Servlet/JSP container’s connection pool configuration Java Naming and Directory Interface (JNDI) resources. The connection pool we will look at is javax.sql.DataSource, which is a JDBC API for getting a connection instance to a database.


javax.sql.DataSource is a factory for getting connections to different physical data sources. javax.sql.DataSource interface is registered with the naming service based on JNDI API. A data source driver allows accessed to the database via DataSource interface. A DataSource object is looked up in the context based on registered through JNDI Resource. The connection to the data source, such as database is attempted when javax.sql.DataSource‘s getConnection() method is called. Connection pooling has many benefits and is part of the Java EE standard implemented by Tomcat. It has many benefits one of which is increased performance and reduced connection creation and removal overhead due to connection reuse by the connection pool.
Use of JDBC API allows for flexibility of changing data source implementation driver form MySQL to Oracle for example, and allows using improved data source specific connection pool implementations. It also abstracts away database connection related plumbing, and allows developers to focus on business logic.

Here We go ..

Create a dynamic project and named as TomcatConnectionPoolingExample

Step 1- Create a TestServlet in package com.tomcat.pool.

package com.tomcat.pool;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class TestServlet extends HttpServlet {

static Logger logger=Logger.getLogger(TestServlet.class);
   
    private DataSource dataSource;
    private Connection connection;
    private Statement statement;
   
    public void init() throws ServletException {
        try {
        logger.info("Begin init() of TestServlet");
            // Get DataSource
            Context initContext  = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup("jdbc/mydb");
            logger.info("End init() of TestServlet");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
    logger.info("Begin doGet() of TestServlet");
        ResultSet resultSet = null;
        try {
            // Get Connection and Statement
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            String query = "SELECT * FROM STUDENT";
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + resultSet.getString(2));
            }
            logger.info("End doGet() of TestServlet");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try { if(null!=resultSet)resultSet.close();} catch (SQLException e)
            {e.printStackTrace();}
            try { if(null!=statement)statement.close();} catch (SQLException e)
            {e.printStackTrace();}
            try { if(null!=connection)connection.close();} catch (SQLException e)
            {e.printStackTrace();}
        }
    }
}


Step 2- Create a log4j.properties and place this file inside src folder


log4j.properties

# Root logger option
log4j.rootLogger=DEBUG, stdout, file

# Redirect log messages to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

# Redirect log messages to a log file, support file rolling.
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=F:\\Logs\\log4j-application.log
log4j.appender.file.MaxFileSize=5MB
log4j.appender.file.MaxBackupIndex=10
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n


Step 3- Create context.xml and place this file inside META-INF


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

  <Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
               maxActive="2" maxIdle="1" maxWait="10000"
               username="root" password="root"
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/test"/>
</Context>

Step 4- Update the web.xml with the below Code

<resource-ref>
<description>MySQL Datasource example</description>
<res-ref-name>jdbc/mydb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
  </resource-ref>

Step 5- Place mysql-connector-java-5.1.27.jar and  log4j-1.2.14.jar in lib folder of WEB-INF

Step 6- Create a STUDENT table in test database as


CREATE TABLE IF NOT EXISTS `student` (
  `STUDENT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `STUDENT_NAME` varchar(100) NOT NULL,
  PRIMARY KEY (`STUDENT_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


You are all set. Execute the application ..