Programming Tutorials

Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java

By: Emiley J in JDBC Tutorials on 2007-10-12  

This tutorial gives you a close look at Jakarta Commons Database Connection Pool (DBCP) in Tomcat 5.0 and Tomcat 5.5. DBCP is the database connection pooling technology built into the popular open-source Tomcat servlet engine. Because it's included with Tomcat, you don't need to download or install any files or libraries.

However, depending on which database you are connecting to, you will have to download the appropriate JDBC driver. In this example we are connecting to MySQL. Normally, to deploy an application to the Tomcat servlet engine, you just copy the application's WAR file, or deployment directory, to the Tomcat webapps directory. However, to configure DBCP for your application, you need to do a bit more work: you need to add the application to the Tomcat server configuration file conf/server.xml.

To do this, you need to add a new <Context> entry to the Tomcat server.xml file. Open the server.xml file with your favorite editor and look for the Tomcat Root Context. You need to add the <Context> entry in the correct part of the server.xml file. For example, you can add it after the ROOT context and before the examples context, as in the snippet shown here:

<!-- Tomcat Root Context -->
<!--
<Context path="" docBase="ROOT" debug="0"/>
-->
<Context path="/myapp" docBase="roller" debug="0">
    <Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource" />
    <ResourceParams name="jdbc/mydb">
        <parameter>
            <name>factory</name>
            <!-- Use the following value for Tomcat 5.0:
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
-->
            <!-- Use this value for Tomcat 5.5: -->
            <value>org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory</value>
        </parameter>
        <parameter>
            <name>maxActive</name>
            <value>100</value>
        </parameter>
        <parameter>
            <name>maxWait</name>
            <value>100</value>
        </parameter>
        <parameter>
            <name>username</name>
            <value>scott</value>
        </parameter>
        <parameter>
            <name>password</name>
            <value>tiger</value>
        </parameter>
        <parameter>
            <name>driverClassName</name>
            <value>org.gjt.mm.mysql.Driver</value>
        </parameter>
        <parameter>
            <name>url</name>
            <value>jdbc:mysql://localhost:3306/mydb?autoReconnect=true</value>
        </parameter>
    </ResourceParams>
</Context>
<!-- Tomcat Examples Context -->
<Context path="/examples" docBase="examples" debug="0"
    reloadable="true" crossContext="true">
<Logger className="org.apache.catalina.logger.FileLogger"
    prefix="localhost_examples_log." suffix=".txt" timestamp="true" />

The preceding example shows how to configure DBCP for an application called myapp. The first entry inside the <Context> element is the <Resource> declaration. The <Resource> declaration declares a javax.sql.DataSource and binds it to the JNDI name jdbc/mydb. The <ResourceParams> element and the nested <parameter> elements within specify the DBCP parameters for the application's database connection pool. We've already discussed the maxActive and maxWait parameters. The driverClassName and url parameters are standard JDBC connection parameters.

Note The value for the factory parameter will vary based on the Tomcat distribution. Tomcat 5.0 and earlier versions used the Jakarta Commons DBCP library. Starting with Tomcat 5.5, the DBCP library has been changed from the Commons version to a Tomcat-specific library. If you want to double check that the value you use is correct, check the Tomcat JAR files in Tomcat's common\lib directory, find the BasicDataSourceFactory class, and use the fully qualified class name of the class you find.

The next code excerpt shows how to obtain a connection from the previous connection pool. First, you use JNDI to look up a javax.sql.DataSource interface, and then you ask that interface for a connection. When you've finished with the connection, you close it and return it to the pool for reuse. Closing the connection doesn't actually close the underlying physical connection; it just returns the connection to the pool.

javax.naming.InitialContext context = new InitialContext();
// Look up the data source
javax.sql.DataSource dataSource =
(javax.sql.DataSource)context.lookup ("java:comp/env/jdbc/mydb");
// Get a connection from the pool
java.sql.Connection conn = dataSource.getConnection();
// ...Use the connection...
// Close the connection to return it to the pool
conn.close();