How to setup Connection Pooling usinig Apache/Tomcat

Posted at Tuesday, April 08, 2008
Setting up connection pooling for Apache/Tomcat can be quite tricky. Here is an example of how to configure and test connection pooling using Apache/Tomcat 5.5.X, Java SDK 5.0, and JDBC drivers for both MySQL and Oracle data sources.

I first create the Global Naming Resources - references to the databases - in the $CATALINA_HOME/conf/server.xml configuration file. You will want to place these references within the GlobalNamingResources attribute tag:

<Resource name="jdbc/TestMySQL"
auth="Container"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
type="javax.sql.DataSource"
driverClassName="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost:3307/mysql?autoReconnect=true"
username="root"
password="password"
maxActive="100"
maxIdle="20"
maxWait="1000"
removeAbandoned="true"
logAbandoned="true"
removeAbandonedTimeout="30"
/>

<Resource name="jdbc/TestOracle"
auth="Container"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PRO
TOCOL=TCP)(HOST=rac1-vip)(PORT=1525))(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip)(PORT
=1525))(CONNECT_DATA=(SERVICE_NAME=jlc.colestock.test)))"
username="test"
password="password"
maxActive="100"
maxIdle="20"
maxWait="1000"
removeAbandoned="true"
logAbandoned="true"
removeAbandonedTimeout="30"
/>

As you can see I have added an entry for a MySQL database as well as an Oracle Clustered Database. If following this example, you will want to replace any applicable values with those specific to your environment.

In order to make the aformentioned resources available via the specified JDBC Drivers, the database-specific jars need to be added to $CATALINA_HOME/common/lib. In my case:

For MySQL:

# ls -1 /var/lib/apache-tomcat/common/lib/mysql-connector-java-5.1.5-bin.jar
/var/lib/apache-tomcat/common/lib/mysql-connector-java-5.1.5-bin.jar


For Oracle:

# ls -1 $CATALINA_HOME/common/lib/ojdbc5.jar
/var/lib/apache-tomcat/common/lib/ojdbc5.jar

After performing these steps, you should restart Tomcat per your pre-established process.

Next, I create a Java web application for each data source to test their respective connection pool. Note that these are simply test apps and aren't production-ready example that use proper design patterns and so forth.

Here is the source code for the test web applications, named mysqlcp and oraclecp:

mysqlcp.tar
oraclecp.tar

mysqlcp.tar

mysqlcp/
mysqlcp/docs/
mysqlcp/classes/
mysqlcp/lib/
mysqlcp/src/
mysqlcp/src/com/
mysqlcp/src/com/example/
mysqlcp/src/com/example/TestMySQL.java
mysqlcp/web/
mysqlcp/web/testmysql.jsp
mysqlcp/etc/
mysqlcp/etc/web.xml
mysqlcp/etc/context.xml
mysqlcp/build.xml
mysqlcp/build/
mysqlcp/build/WEB-INF/
mysqlcp/build/WEB-INF/web.xml
mysqlcp/build/WEB-INF/classes/
mysqlcp/build/WEB-INF/classes/com/
mysqlcp/build/WEB-INF/classes/com/example/
mysqlcp/build/WEB-INF/classes/com/example/TestMySQL.class
mysqlcp/build/WEB-INF/lib/
mysqlcp/build/META-INF/
mysqlcp/build/META-INF/context.xml
mysqlcp/build/testmysql.jsp
mysqlcp/dist/
mysqlcp/dist/docs/
mysqlcp/dist/mysqlcp.war
mysqlcp/build.properties

oraclecp.tar

oraclecp/
oraclecp/docs/
oraclecp/classes/
oraclecp/lib/
oraclecp/src/
oraclecp/src/com/
oraclecp/src/com/example/
oraclecp/src/com/example/TestOracle.java
oraclecp/web/
oraclecp/web/testoracle.jsp
oraclecp/etc/
oraclecp/etc/web.xml
oraclecp/etc/context.xml
oraclecp/build.xml
oraclecp/build/
oraclecp/build/testoracle.jsp
oraclecp/build/WEB-INF/
oraclecp/build/WEB-INF/web.xml
oraclecp/build/WEB-INF/classes/
oraclecp/build/WEB-INF/classes/com/
oraclecp/build/WEB-INF/classes/com/example/
oraclecp/build/WEB-INF/classes/com/example/TestOracle.class
oraclecp/build/WEB-INF/lib/
oraclecp/build/META-INF/
oraclecp/build/META-INF/context.xml
oraclecp/dist/
oraclecp/dist/docs/
oraclecp/dist/oraclecp.war
oraclecp/build.properties


Each test application consists of a test connection class, a test jsp, as well as the requisite configuration files.

The applications' war files contain a context.xml configuration file, referencing the global resources put in server.xml; for example in the oraclecp.war:

<Context path="/oraclecp" docBase="oraclecp" debug="5" reloadable="true">
<ResourceLink global="jdbc/TestOracle" name="jdbc/TestOracle" type="javax.sql.Da
taSource"/>


In order to test these applications, simply unpack them and copy the war files to $CATALINA_HOME/webapps/. Tomcat will automcatically deploy both each application as well as its context, etc.

Once each application is deployed, call its test .jsp page. For MySQL, it should return a page listing the distinct names of users in mysql.user:



For Oracle, it should return a page listing the distinct names of users in sys.dba_users:



Labels: ,