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!



<resource name="jdbc/TestPool" auth="Container" scope="Shareable" type="oracle.jdbc.pool.OracleDataSource" driverclassname="oracle.jdbc.driver.OracleDriver" factory="oracle.jdbc.pool.OracleDataSourceFactory">connectionCachingEnabled="true" connectionCacheName="TestCache" connectionCacheProperties="{MaxStatementsLimit=10, MinLimit=0, InitialLimit=0, ValidateConnection=true, ConnectionWaitTimeout=10, MaxLimit=4}" url="jdbc:oracle:thin:@199.82.16.69:1535:TESTSID" user="scott" password="tiger" />

I tested this with the below JSP code on Tomcat 5.5 server.



<%@page import="java.util.*" %>
<%@page import="java.sql.*" %>
<%@page import="javax.sql.*" %>
<%@page import="javax.naming.*" %>
<%@page import="oracle.jdbc.pool.*" %>
<HTML>
<HEAD>
<TITLE>JSP Example</TITLE>
</HEAD>
<BODY BGCOLOR="#ffffcc">
<CENTER>
<%
// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
// Look up our data source
OracleDataSource ds = (OracleDataSource )envCtx.lookup("jdbc/TestPool");

ArrayList list = new ArrayList() ;
Connection conn=null;
out.println(" Opening connections <br>");
for (int i =0 ; i < 10 ; i ++ )
{ conn = ds.getConnection();
if ( conn != null ) list.add( conn );
out.println(conn+"<br>");
// don’t release here
}

// release all now
out.println(" Closing connections <br>");
while ( list.size() !=0 )
{ conn = (Connection )list.remove(0);
out.println(conn+"<br>");
conn.close();
}
%>
</CENTER>
</BODY>
</HTML>


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” !

Opening connections
oracle.jdbc.driver.LogicalConnection@3a0ab1
oracle.jdbc.driver.LogicalConnection@940f82
oracle.jdbc.driver.LogicalConnection@864e43
oracle.jdbc.driver.LogicalConnection@17c2891
null
null
null
null
null
null
Closing connections
oracle.jdbc.driver.LogicalConnection@3a0ab1
oracle.jdbc.driver.LogicalConnection@940f82
oracle.jdbc.driver.LogicalConnection@864e43
oracle.jdbc.driver.LogicalConnection@17c2891

10 comments:

Anonymous said...

I tried your exact code with Oracle 9i database, Tomcat 5.5.20 and still I get a new unique connection every time.

I used your test JSP as well as your context.xml Resource statement (changing the URL, user, pass) of course.

Also I printed the datasource's properties with

out.println("DataSource Props: " + ds.getConnectionProperties() );

and teh value was null. So I not sure teh connection properties defined in the Resource element are used.

Any advice?

Anonymous said...

hi
i tried according to the example illustrated
i used tomcat 5.0.28 and oracle 10g ,have configured server.xml, web.xml

i'm getting datasource but
datasource.getConnection() function returns : oracle.jdbc.driver.OracleDriver@xxxxx
rather than oracle.jdbc.driver.LogicalDriver@xxxxx

can you provide any informtion on this..

Anonymous said...

Hi Raees,

Thie cache haven't work for me either. I am runnig Tomcat 6.0, and Oracle 9i. Here is the log - each time a new connection is created.

Could you please advise. It is urgent!

Best Ragards,

Bob



Opening connections

oracle.jdbc.driver.OracleConnection@10facc4

oracle.jdbc.driver.OracleConnection@1d12e87

oracle.jdbc.driver.OracleConnection@1113859

oracle.jdbc.driver.OracleConnection@1d35a6f

oracle.jdbc.driver.OracleConnection@630d2a

oracle.jdbc.driver.OracleConnection@742ce7

oracle.jdbc.driver.OracleConnection@1c319b9

oracle.jdbc.driver.OracleConnection@d16578

oracle.jdbc.driver.OracleConnection@8cbddd

oracle.jdbc.driver.OracleConnection@1961a07

Closing connections

oracle.jdbc.driver.OracleConnection@10facc4

oracle.jdbc.driver.OracleConnection@1d12e87

oracle.jdbc.driver.OracleConnection@1113859

oracle.jdbc.driver.OracleConnection@1d35a6f

oracle.jdbc.driver.OracleConnection@630d2a

oracle.jdbc.driver.OracleConnection@742ce7

oracle.jdbc.driver.OracleConnection@1c319b9

oracle.jdbc.driver.OracleConnection@d16578

oracle.jdbc.driver.OracleConnection@8cbddd

oracle.jdbc.driver.OracleConnection@1961a07

Anonymous said...

I too was having the same problem but then I downloaded newer jdbc drivers and got the correct results below:

Opening connections
oracle.jdbc.driver.LogicalConnection@31f2a7
oracle.jdbc.driver.LogicalConnection@131c89c
oracle.jdbc.driver.LogicalConnection@1697b67
oracle.jdbc.driver.LogicalConnection@24c4a3
null
null
null
null
null
null
Closing connections
oracle.jdbc.driver.LogicalConnection@31f2a7
oracle.jdbc.driver.LogicalConnection@131c89c
oracle.jdbc.driver.LogicalConnection@1697b67
oracle.jdbc.driver.LogicalConnection@24c4a3

I downloaded the 10g rel2 jdbc drivers from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

Dhurba said...

Useful article. thanks

Anonymous said...

This article looks promising. I would be interested in seeing the web.xml elements that reference your datasource connection.

anthony said...

Many thanks Raees!

I had been doing a lot of head scratching and rereading of the Tomcat config page for Tomcat 6 as well as Oracle's site, but kept hitting various errors. whew!

Btw. This works with Tomcat6 and ojdbc14-10.1.0.5 dumped in the lib dir.

qnaguru said...

Good post, thats lot of useful information.

You can find here too:
http://qnatech.wordpress.com

Karthik Gandhi said...

Hi,

I tried with the above mentioned settings and your JSP.

But when I try to hit this JSP , it errors with below error:

java.lang.ClassCastException: oracle.jdbc.pool.OracleDataSource cannot be cast to oracle.jdbc.pool.OracleDataSource


My web.xml entry is as below:

DB Connection
jdbc/EstabServiceDS
oracle.jdbc.pool.OracleDataSource
Container


and I followed same entry as yours in tomcat's server.xml

Please let me know at the earliest if I have done something wrong in the configs.

Thanks and Regards
GeeKay

lee said...

Hi:

How can I get a oracle.jdbc.driver.OracleConnection in websphere7.0 ?