Tomcat connection pool - a Tomcat JNDI DBCP connection pool example

Here's a quick demonstration of how to create a Tomcat connection pool (database connection pool) using the Tomcat DBCP library.

I'm not going to go into a detailed explanation here of how Tomcat DBCP works, other than to say that it works for me, and I've tried to include everything here that you'll need to implement your own Tomcat DBCP database connection pool in your web applications.

Part 1: Define the DBCP connection pool in your context.xml file

The first part of this Tomcat connection pool recipe is to create a file in the META-INF directory of your web application named context.xml. I show a copy of a working context.xml file below. Here's a brief description of this file:

1. The name of my web application is nagios.
2. I've given my resource the JNDI resource name of "jdbc/postgres".
3. All of the connection information for my Postgres database is shown here, including the name of the driver class, the URL, the username, and password.
4. Some of the other parameters that are shown are explained in the Tomcat DBCP "how to" page.

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

<Context path="/nagios" docBase="nagios" crossContext="true" reloadable="true" debug="1">

  <Resource name="jdbc/postgres" auth="Container"
            type="javax.sql.DataSource"
            driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql://localhost/nagios"
            username="nagiosuser"
            password="nagiospassword"
            maxActive="10"
            maxIdle="5"
            maxWait="-1"
            removeAbandoned="true"
            removeAbandonedTimeout="60"
            logAbandoned="true"
            />

</Context>

Step 2: Set up the JNDI DBCP connection pool in a servlet class

The second part of my recipe is to create a Java servlet class that connects to this Tomcat connection pool, and doles out the database connections upon request.

Here are a few comments about this Java servlet class:

1. You have to refer to the JNDI resource using the name java:/comp/env/jdbc/postgres.
2. I dole out the database connections with the getConnection method.
3. It's incredibly important for your applications to call the freeConnection method when they're finished using the connection. If you don't, you'll be amazed at how fast you can run out of database connections. :)
4. There are frameworks like Spring that normally handle a lot of the connection-pool grunt-work for you, but in my case I just wanted to add some simple database connection pooling to my web application without getting into Spring.

With that introduction, here's the source code for my Java servlet class:

package com.devdaily.nagios.database;

import javax.naming.InitialContext;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import javax.sql.*;

// This software is released under the terms of the Apache License 2.0; see:
// https://choosealicense.com/licenses/apache-2.0
//
// See this url for more information on Tomcat and JNDI:
// http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html
public class DBBroker extends HttpServlet
{
  private static DataSource datasource = null;

  /**
   * Setup our connection pool when this servlet is started.
   * Note that this servlet must be started before any other servlet that tries to
   * use our database connections.
   */
  public void init() throws ServletException
  {
    try
    {
      InitialContext initialContext = new InitialContext();
      if ( initialContext == null )
      {
        String message = "There was no InitialContext in DBBroker. We're about to have some problems.";
        System.err.println("*** " + message);
        throw new Exception(message);
      }

      // actual jndi name is "jdbc/postgres"
      datasource = (DataSource) initialContext.lookup( "java:/comp/env/jdbc/postgres" );

      if ( datasource == null )
      {
        String message = "Could not find our DataSource in DBBroker. We're about to have problems.";
        System.err.println("*** " + message);
        throw new Exception(message);
      }
    }
    catch (Exception e)
    {
      throw new ServletException(e.getMessage());
    }
  }

  /**
   * Dole out the connections here.
   */
  public static synchronized Connection getConnection() 
  throws SQLException
  {
    return datasource.getConnection();
  }

  /**
   * Must close the database connection to return it to the pool.
   */
  public static synchronized void freeConnection(Connection connection)
  {
    try
    {
      connection.close();
    }
    catch (Exception e)
    {
      System.err.println("DBBroker: Threw an exception closing a database connection");
      e.printStackTrace();
    }
  }

}

Part 3: Configure the Tomcat web.xml file

The third part of my recipe is to configure the web.xml file for my Java web application. There are two simple sections that I need to add to this file:

1. One section where I define my Postgres JNDI resource.
2. A second part where I configure my DBBroker servlet -- and very importantly -- tell it to load up first.

<!-- (a) set up the postgres jndi resource -->

<resource-ref>
  <description>Postgres data source</description>
  <res-ref-name>jdbc/postgres</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

<!-- (b) set up the DBBroker servlet -->
<servlet>
    <servlet-name>
        DBBroker
    </servlet-name>
    <servlet-class>
        com.devdaily.nagios.database.DBBroker
    </servlet-class>
    <load-on-startup>1</load-on-startup>
</servlet>

<servlet-mapping>
    <servlet-name>
        DBBroker
    </servlet-name>
    <url-pattern>
        /DBBroker
    </url-pattern>
</servlet-mapping>

Part 4: Copy the Postgres driver where Tomcat needs it

The fourth (and final) of my recipe for getting the Tomcat DBCP connection pool to work with Postgres is to copy the Postgres driver (the jar file) to a special place where Tomcat can grab it early in its startup process.

Specifically, you need to place the Postgres jar file in the Tomcat ${CATALINA_HOME}/common/lib directory, which on my MacBook Pro means copying the postgresql-8.0-311.jdbc2.jar file to this Tomcat directory:

/Users/al/tomcat-5.5.26/common/lib

I just finished configuring my Java web application with these Tomcat database connection pooling instructions, and tested the connection pooling, and it works great. As mentioned in a note above, I initially forgot to release my databases connections, and not surprisingly I ran out of connections as soon as I hit the limit I configured. I wasn't happy with my brain for doing this, but it turned out to be a nice way to prove that the connection pooling was working as expected.

Tomcat DBCP connection pool summary

I hope the four steps shown in my recipe will help you use Tomcat DBCP database connection pooling in your Java web applications.

Also, I hope these four steps make sense. I feel like if I try to explain more here I'll end up writing a huge tutorial, and that's not my goal, at least not for today. Hopefully if you're comfortable with Java, servlets, Tomcat, and also understand the proper layout of a Java web application (including the WEB-INF/web.xml file and the optional META-INF/context.xml file), the Tomcat DBCP connection pool stuff will make sense.