Tomcat Connection Pooling Example

by Steven J. Owens (unless otherwise attributed)

This is just a simple, straight example of how to configure connection pooling in Tomcat 4.1.30, using the Jakarta-Commons DBCP (Database Connection Pooling) that comes bundled with Tomcat. This example assumes you're using MySQL.

Prepare your Database

There are three things you should take care of first, to use this example:

  • Figure out what JDBC driver you need, get it, download it, put it where tomcat can find it.
  • In your database server: create the database you'll be accessing.
  • In your database server: set the username and password you'll be using to get at the database,

    Setting up the database and setting your database username/password are database-specific, and there are a gazillion web pages out there on how to do that in mysql. However, just because I hate having to go dig up information, here's a quickie example of how I usually do it using mysql:

    $ mysql -u root -p
    Password: yourmysqlrootpasswordhere
    mysql> create database mygloriouswebapp;
    mysql> grant all on mygloriouswebapp.* to glorious identified by "foobar" ;
    mysql> grant all on mygloriouswebapp.* to glorious@localhost identified by "foobar" ;
    mysql> exit ;

    I have been told that there are performance-related downsides to granting all permissions, so I would recommend that you make sure to come back and tweak the permissions for this user before going into production.

    If you're using something besides mysql (like, say, postgres) then be an adult and google for how to set it up.

    The JDBC driver is more java/tomcat-specific. MySQL's JDBC driver is available at the MySQL website (, duh). For example, in my personal installation of tomcat for development work, my jar is:


    Now, the exact location of where your jar file needs to go can actually get to be a bit of a hairy topic, due to the details of how classloading works in a servlet engine. In a nutshell, there are several different lib directories in your java and tomcat installation, and the result will be an entire hierarchy of classloaders, with some classloaders having some jars. Which lib directory you put the jar in will determine which code can load it. This can cause frustrating and tricky behavior, which I'm not really going to go into here, but you should be aware of if and can read more about it at:

    Get Your Facts Straight

    There are essentially three facts you have to know or decide:

  • the resource name you're going to use for your pool and connection
  • the URL for the JDBC connection to your database
  • the class name of the JDBC driver you're going to use

    ...and three places you have to enter those facts:

  • enter the resource name, database URL and JDBC Driver class name in a set of resource tags in the webapp's tag set, which will be either in tomcat/conf/server.xml, or in tomcat/webapps/webappname.xml.
    (Note that with tomcat, if you have no server.xml then tomcat will look for the context tags in webapps/webappname.xml, but if you have a server.xml, tomcat will ignore webapps/webappname.xml).
  • import the database resource in your webapp's web.xml, in tomcat/webapps/webappname/WEB-INF/web.xml
  • in your code, you have to know what JNDI "initial context" to request the database connection from. This process is a standard part of JDBC 2.

    Okay, so here we go. The parts you have to edit are in bold red. If you're color blind and also unable to see the bold for some reason, do a case-insensitive search for "foo".

    The resource definition

    In the <context> tag set (in either server.xml, or in tomcat/webapps/webappname.xml):

              <Resource name="jdbc/FooDB"
              <ResourceParams name="jdbc/FooDB">
                <!-- Ask tomcat to watch out for unclosed connections and close them -->
                <!-- 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 0 for no limit.
                <!-- Maximum number of idle dB connections to retain in pool.
                     Set to 0 for no limit.
                <!-- 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.
                <!-- MySQL dB username and password for dB connections  -->
                <!-- The JDBC connection url for connecting to your MySQL dB.
                     The autoReconnect=true argument to the url makes sure that the
                     mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
                     connection.  mysqld by default closes idle connections after 8 hours.

    Note that the number of connections and other details are the default values that come with tomcat. This will work for a lot of stuff, but you will eventually have to tune your numbers to fit your application.

    Importing the Resource into your Webapp

    In tomcat/webappname/WEB-INF/web.xml:

           <description>DB Connection for Foo project</description> 

    Looking Up Your InitialContext

    In the application code:

        public Connection getConnection() {
                try {
                        Context context = new InitialContext();
                        if (context == null ) { throw new Exception("Boom - No Context"); }
                        DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/FooDB");
                        if (datasource == null ) { throw new Exception("Boom - No DataSource"); }
                        return datasource.getConnection();
                } catch (Exception e) {
                        e.printStackTrace() ;
                        throw new RuntimeException("Database Not Available.") ;

    Smoke Test

    Fire up tomcat and give it a try. Keep an eye on the logs (typically in tomcat/logs) and look for error messages. I've found that typically there's a webapp log as well as the general tomcat log (catalina.out on tomcat 4.x), and sometimes errors will show up the webapp log and not in the tomcat log. It took me a while to get in the habit of checking in both logs.

    See original (unformatted) article


    Verification Image:
    Your Email Address:
    Confirm Address:
    Please Post:
    Copyright: By checking the "Please Post" checkbox you agree to having your feedback posted on notablog if the administrator decides it is appropriate content, and grant compilation copyright rights to the administrator.
    Message Content: