Setting up a connection pool on Apache Tomcat 7.x – Part 1

13 Jun

TopologyRecently I installed Apache Tomcat 7.0.41 on my Macbook Pro having Mountain Lion OS X. I wanted to explore the setup of connection pool and its usage. Tomcat 7.x introduces the tomcat jdbc pool, which is recommended instead of DBCP and provides several advantages over DBCP.

I wanted to use the connections in the connection pool with a Spring MVC application that it turn fetched rows from a table of an Oracle 11g R2 appliance running through Virtual box.

In order to set up the connection pool, here is a set of steps:

  1. Stop tomcat -My tomcat installation is located at /Library/apache-tomcat-7.0.41. The environment variable CATALINA_HOME is set to this path. Stop Tomcat with the command:
    $CATALINA_HOME/bin/ stop
  2. Download 11g Release 2 ( ojdbc6.jar or whichever version of the jar matches your Oracle database version. Copy this jar to directory:
  3. Place the database login credentials in $CATALINA_HOME/conf/ Some may argue that it isn’t a good practice to keep passwords in plain text like this. There are techniques available to encrypt the password and decrypt it later. However, there are other ways to secure this file – such as applying file permissions.
    # Datasource connection login details
  4. Add the tag for JNDI resource link in $CATALINA_HOME/conf/context.xml. The connection pool would be referenced later through JNDI. Refer to this link for more details on configuring JNDI for tomcat
    <ResourceLink global="jdbc/oralocal" name="jdbc/oralocal"
    type="javax.sql.DataSource" />
  5. Add the entry below in $CATALINA_HOME/conf/server.xml. It has to be placed within the GlobalNamingResources tags. In this case, I’m using the SID orcl which comes with the Oracle developer days virtual database appliance. Refer to Apache Tomcat JDBC pool for details on the attributes used. Steps 2 to 5 are all that is needed to setup the connection pool:
       <Resource auth="Container" 
            maxActive="20" maxIdle="10" maxWait="10000" 
            validationQuery="SELECT SYSDATE FROM DUAL"/>
  6. Start tomcat – Start tomcat with the command:
    $CATALINA_HOME/bin/ start
  7. In order to consume the connections in the connection pool in my Spring MVC application, I put a bean referring to the JNDI data source in my Spring Application context file:
        <bean id="dbDataSource"
            <property name="jndiName" value="java:comp/env/jdbc/oralocal" />
            <property name="resourceRef" value="true" />
            <property name="lookupOnStartup" value="true" />
  8. Finally, in web.xml of my application I declared the spring context loader listener and specified the path of the application context file. This loads the application context:

In part II of this post, I shall dive deeper into the connection pool by creating a Spring MVC application and use the connection pool.


Posted by on June 13, 2013 in Open Source


Tags: ,

2 responses to “Setting up a connection pool on Apache Tomcat 7.x – Part 1

  1. Clemente Roettger

    November 27, 2020 at 4:09 AM

    Pretty! This was an extremely wonderful post. Thanks for supplying these details.

  2. Mafalda Petrucelli

    November 28, 2020 at 11:15 PM

    Valuable info. Fortunate me I found your site accidentally, and I’m shocked why this twist of fate did not took place in advance! I bookmarked it.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: