RSS

Tag Archives: Tomcat

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

In my previous blog post, I walked through the steps in setting up a tomcat jdbc connection pool. I created a simple spring MVC application to consume the pool. On start up of tomcat, the pool gets created with 10 idle connections. This number can be changed by setting the maxIdle attribute of the Resource tag in server.xml. The minimum idle connections is 10, even if you set this attribute to a lower value.

After tomcat is started, I can view the idle connections in Oracle Enterprise Manager (OEM). I search sessions on Module = “JDBC Thin Client”. I get to see the 10 idle connections as shown below:

Oracle Enterprise Manager screen of connections

Oracle Enterprise Manager screen of connections

When I shutdown tomcat and re-query for sessions, I will see none – as seen below. The tomcat jdbc connection pool gets cleanly closed upon tomcat shutdown:

Connection pool shutdown

Oracle Enterprise Manager screen of connections

What happens if the oracle database instance becomes un-available after the pool has been setup and the application makes use of the connection pool? We could get a stack trace similar to the one below:


INFO: Initialization processed in 2636 ms
Jul 1, 2013 10:06:31 AM org.apache.naming.NamingContext lookup
WARNING: Unexpected exception resolving reference
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
	at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:536)
	at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:228)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
	at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
	at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:702)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:634)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:488)
	at org.apache.tomcat.jdbc.pool.ConnectionPool.(ConnectionPool.java:144)
	at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
	at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
	at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:539)
	at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:237)
	at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:143)
	at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:843)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:154)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:831)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:168)
	at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1093)
	at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:672)
	at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:271)
	at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
	at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
	at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:724)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.startup.Catalina.start(Catalina.java:684)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:322)
	at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:456)

However, when the oracle database instance gets available, to restore the pool tomcat server needs to be bounced. This would trigger to pool creation and then the application is able to perform its functionality. I’ve included the source code of the HROnline application. It reads a list of employee records from the HR database that comes pre-installed with the Oracle virtual box database application. When connected, the list of employees would appear on the page:

Employee list

HR Online employee list

Click here to download the source code.

 
Leave a comment

Posted by on July 1, 2013 in Open Source

 

Tags: , , ,

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: ,