RSS

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

01 Jul

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: