Friday, January 18, 2008

Creating a connection pool for Tomcat using Oracle driver

There are lot of documentation and various implementation of connection pool APIs floating around the web for JDBC drivers. Apache has a DBCP API that can be used with almost any JDBC driver.

But I was in a situation were I needed to have a Connection Pool with a non open source product for a standalone java application. Since our database vendor was Oracle; I was looking for a solution from Oracle itself.

When I searched internet; it came up with different APIs by oracle for implementing the connection pool, like OralceConnectionCacheImpl and OracleConnectionPoolDataSource. We soon found out that some of these APIs are not really pooling the connections; they are returning a new physical connection every time you ask for a connection! And most of these APIs are getting deprecated in future.

After browsing through their API set; we decided to go with the new API “oracle.jdbc.pool.OracleDataSource” for pooling! We tested this pooling in our test environment and made sure it works as expected.The connection pool configuration and the test case/results are below, we tested this with Tomcat 5.5. We changed the file context.xml for element “Resource” as below.

Adding the highlighted section to the Resource configuration section will switch a normal data source to a connection pooled data source. Notice that I have set MaxLimit as 4 for the test purpose!

The resulted page is given below, notice that I am trying to open 10 connections and after 4th connection, the pool returned null since I did not release the connections immediately! Also notice that I am printing the class name of the connection, and it shows “LogicalConnection” instead of “PhysicalConnection” !