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
