Introduction
This article will describe how to set up database connection pooling within your tomcat web application instead of creating physical database connections. Creating physical database connections from your web page is resource intensive and using connections from the pool will speed up your web page's response time. We will use Apache Commons DBCP which comes with Tomcat do handle our connection pooling instead of relying on a 3rd party tool. For the below examples, we will use Oracle as our database though many other JDBC compliant databases should work in the same manner. For these examples, I'm using Tomcat 4.1.30.

Getting the Required Files
First you need a working copy of Tomcat set up on your computer. If you haven't installed tomcat and are unsure how to do so, you can read this article I wrote on how to set up tomcat with eclipse. Other than tomcat, you will need the JDBC driver for your database to connect to whichever database you are trying to connect to. If you are using Oracle and Java 1.3 you'll want to use the classes12.jar or classes12.zip JDBC driver which comes with Oracle client or can be downloaded from www.oracle.com. Note, if you're using Java 1.4 then you want to use the ojdbc14.jar Oracle JDBC driver. If you're using postgresql you can use download the latest JDBC driver from http://jdbc.postgresql.org/. If you're using Mysql you should be able to download the MySQL Connector/J JDBC driver from www.mysql.com under the download section. This should be all that's required other than tomcat itself to get connection pooling to work.

Finding A Home For Your Drivers
In these examples, the connection pool will be created when tomcat starts up so the JDBC drivers must be made available to tomcat in it's classpath when it boots up. Putting the JDBC drivers in your web apps classpath within <WEB APP>/WEB-INF/lib/ will not work because that appends the classpath of your web application and not for tomcat itself. So, you need to copy your JDBC driver to <TOMCAT_HOME>/common/lib/ for tomcat to see it when it boots up (Where <TOMCAT_HOME> is where you have tomcat installed). Since I'm using the ojdbc14.jar Oracle JDBC driver and have tomcat installed at c:\tomcat\ on my computer I've placed the file in c:\tomcat\common\lib\ojdbc14.jar. Now the next time Tomcat starts up, ojdbc14.jar will be available to it in it's classpath.

Configuring your server.xml and web.xml
Tweaking the server.xml
The next step is to define a "Resource Definition" in your web application context in your server.xml. Sounds a little more complicated than it really is. The server.xml file is the main configuration file for tomcat. It allows you to change port numbers, define your web application, and set other settings. Your web application should be defined in this file. Your server.xml file is located in <TOMCAT_HOME>/conf/server.xml or in my case it's c:\tomcat\conf\server.xml. Open up your server.xml with notepad or if you have trouble reading it, try opening it with wordpad. Search for a lines that looks like this:

<!-- Tomcat Root Context -->
<!--
<Context path="" docBase="ROOT" debug="0"/>
-->

That is an example definition for the web application named ROOT which is the main tomcat web application. As you can see it's commented out. You'll want to add your own context definition for your web application below this entry. In my example, I have a application called TigerStore. Within that context, you will define parameters to use for connection pooling. For my TigerStore example, this is what my context looks like:

<Context path="/TigerStore" docBase="/TigerStore" debug="5" reloadable="true" crossContext="true">

  <Resource 
           auth="Container" 
           description="Oracle database resource for TigerStore project" 
           name="jdbc/TigerStorePool" 
           scope="Shareable" 
           type="javax.sql.DataSource"/>

  <ResourceParams name="jdbc/TigerStorePool">

  <parameter>

  	<name>password</name>

  	<value>myDatabasePassword</value>

  </parameter>

  <parameter>

  	<name>username</name>

  	<value>myDatabaseUsername</value>

  </parameter>

  <parameter>

  	<name>url</name>

  	<value>jdbc:oracle:thin:@127.0.0.1:1521:orcl</value>

  </parameter>

  <parameter>

  	<name>driverClassName</name>

  	<value>oracle.jdbc.driver.OracleDriver</value>

  </parameter>

  <parameter>

  	<name>maxActive</name>

  	<value>500</value>

  </parameter>

  <parameter>

  	<name>maxIdle</name>

  	<value>30</value>

  </parameter>

  <parameter>

  	<name>maxWait</name>

  	<value>10000</value>

  </parameter>

  </ResourceParams>

  </Context>

I've highlighted the important pieces of the TigerStore context in orange. The name of my connection pool, which I will later call from within my java code is called jdbc/TigerStorePool. The maxActive, maxIdle, and maxWait parameters all have to do with how many database connections to open and for how long, but you can find more information about that on Apache's web site.

Tweaking your web.xml
Once the server.xml is set up for connection pooling, the next step is to configure your web applications web.xml to call the pool and to make it available to your web application. The web.xml file is a configuration file for your web application and is located at<WEB APP>/WEB-INF/web.xml or in the case of my TigerStore application, it's in c:\tomcat\webapps\TigerStore\WEB-INF\web.xml. My web.xml file looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app 

PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3
//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app id="WebApp">
	<display-name>TigerStore</display-name>


  <resource-ref>

  	<description>TigerStore Oracle Database</description>

  	<res-ref-name>jdbc/TigerStorePool</res-ref-name>

  	<res-type>javax.sql.DataSource</res-type>

  	<res-auth>Container</res-auth>

  </resource-ref>
 

  </web-app>

Again, the important piece that you would need to change is in orange. You need to change this to whatever you named your connection pool in your server.xml for your web application.

Writing the Java Code
The next step is to actually write the java code to create the connection from the connection pool. But first you will need to start tomcat, or restart it if you already have it running, for the changes to server.xml and web.xml to take effect. Next, within your web application create a new file called TestConnectionPool.jsp. It could really be called anything, but that's what I've called mine. Copy and paste this code into TestConnectionPool.jsp:

<%@ page import="java.sql.*,javax.sql.*,javax.naming.*" %>	
<%    
		try
		{
		  
          Connection conn = null;
		  Context initContext = new InitialContext();
		  Context envContext  = (Context) initContext.lookup("java:/comp/env");
		  DataSource ds = (DataSource) envContext.lookup("jdbc/TigerStorePool");
		  conn = ds.getConnection();
		  conn.close();
		  out.println("Connection opened and closed.");
		}
		catch (SQLException se)
		{
			out.println("<pre>Error: " + se.getMessage() + "</pre>");
			se.printStackTrace();
		}
%>
Page Done Processing.

If all goes well, you should see a message that says "Connection opened and closed" which means it was able to connect to your database using a connection from your new connection pool and then close the connection and return it to the pool. If you get an error, more detail can be found in the tomcat log file. You should now be able to reference a connection from the connection pool the same way you would a physical database connection.

Summary
In summary, here's the brief version on how to set up connection pooling in tomcat:

• Put your JDBC driver in <TOMCAT_HOME>/common/lib/
• Configure your context in server.xml to set up a connection pool.
• Configure your web.xml to call that connection pool.
• Write your java code to create the connection from the pool.

If you have trouble, please post your comments on this web page.

~ Troy Campano ~