Replace your C3P0 or DBCP by Oracle pool (UCP)

on
Introduction : ODS or UCP?

I've been using DBCP and C3P0 for a long time. But, a few months ago, I started to have problems with them. For example, sometimes, no way to get any connection to my database (while it's still possible with my Oracle SQL Developer). So, I decided to look at the code of C3P0. What a surprise! Even if it's a very common pool for a lot of projects (such as Hibernate), it seems to be dead. No more development, code complicated with sub projects, no Maven integration (...ok, ok, even if you hate Maven, you have to admit that it is very efficient to modify the code of mavenized projects...). Thus, I looked for a new pool. Of course, I found the recent tomcat jdbc pool. But I finally decided to work with Oracle pool just because I only have Oracle databases.

So, this post is my history with Oracle pool.

When you look for pool features on Oracle doc website, you find to ways to implement it :
  • Oracle DataSource pool (also called ODS) and included in the driver jar file
  • Oracle Universal Connection Pool (UCP), which is packaged in its own jar file (ucp.jar)
ODS : the old way

First of all, ODS is the old way. Its interfaces are still usable BUT deprecated. See it by yourself by looking at the code of OracleConnectionCacheImpl. This class contains tuning parameters. This a an example of my Spring configuration with ODS :

<bean id="OracleNativePoolParent" class="oracle.jdbc.pool.OracleDataSource"
destroy-method="close" lazy-init="true" >
<property name="user" value="****" />
<property name="password" value="****" />
<property name="serverName" value="****" />
<property name="databaseName" value="****" />
<property name="driverType" value="thin" />
<property name="networkProtocol" value="tcp" />
<property name="portNumber" value="1521" />
<property name="connectionCachingEnabled" value="true" />
<property name="connectionCacheProperties">
<props merge="default">
<prop key="InitialLimit">1</prop>
<prop key="MinLimit">1</prop>
<prop key="MaxLimit">10</prop>
</props>
</property>
<property name="maxStatements" value="400" />
<property name="loginTimeout" value="20" />
<property name="connectionProperties">
<props merge="default">
<prop key="AutoCommit">false</prop>
</props>
</property>
</bean>


UCP : you should use it instead of ODS

Now, let's switch to UCP. Curiously, We need to use a factory to obtain an instance of this pool. This is my Spring configuration :


<bean id="OracleNativePoolParent" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource" lazy-init="true" >
<property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
<property name="user" value="****" />
<property name="password" value="****" />
<property name="URL" value="jdbc:oracle:thin:@myServer:1521:myDB" />
<property name="connectionWaitTimeout" value="30" />
<property name="minPoolSize" value="1"/>
<property name="maxPoolSize" value="10"/>
<property name="inactiveConnectionTimeout" value="3600"/>
<property name="validateConnectionOnBorrow" value="true"/>
<property name="maxStatements" value="400"/>
<property name="connectionProperties">
<props merge="default">
<prop key="AutoCommit">false</prop>
</props>
</property>
</bean>

Driver Oracle 11g blocked on startup

While initializing your pool, you have to wait 5 minutes then get an exception? Don't panic. It's just a little but with Sun Java on Linux OS. Add this JVM parameter :

-Djava.security.egd=file:///dev/urandom


Conclusion

Even if I didn't find all the options I had with C3P0, UCP seems to be efficient for me. I think that it's a best choice to work with Oracle database because it is officially supported. But, there's still a negative point. As I don't have the source code, it will be very difficult to qualify bugs and to understand advanced parameters (and I think that the official documentation is not exhaustive at all).