RSS

Tag Archives: Spring

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

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/catalina.sh stop
  2. Download 11g Release 2 (11.2.0.2.0) ojdbc6.jar or whichever version of the jar matches your Oracle database version. Copy this jar to directory:
     $CATALINA_HOME/lib
  3. Place the database login credentials in $CATALINA_HOME/conf/catalina.properties. 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
    db.user=hr
    db.password=hr
  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" 
            driverClassName="oracle.jdbc.driver.OracleDriver" 
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" 
            maxActive="20" maxIdle="10" maxWait="10000" 
            name="jdbc/oralocal" 
            password="${db.password}" 
            removeAbandoned="true" 
            removeAbandonedTimeout="90" 
            type="javax.sql.DataSource" 
            url="jdbc:oracle:thin:@localhost:1521:orcl" 
            username="${db.user}" 
            testOnBorrow="true"         
            jdbcInterceptors=
    "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
            org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
            org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport" 
            validationQuery="SELECT SYSDATE FROM DUAL"/>
  6. Start tomcat – Start tomcat with the command:
    $CATALINA_HOME/bin/catalina.sh 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"
            autowire="byName">
            <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:
    <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>/WEB-INF/spring/applicationContext.xml</param-value>
    </context-param>
    <listener>
    <listener-class>
    org.springframework.web.context.ContextLoaderListener
    </listener-class>
    </listener>

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.

 
Leave a comment

Posted by on June 13, 2013 in Open Source

 

Tags: ,