Oracle - connection Pooling with spring framework

Question

We are trying to implement Oracle connection pooling with the help of Spring Framework. We are using DBCP connection pooling method. However the integration between DBCP and spring doesn't go down that well.

Problem that we face is that DBCP returns PoolableConnections Object while Oracle expects OracleConnection Objects. (Throws ClassCastException)

It seems that this problem has been handled in Oracle 11g. However I am curious as to how others have implemented Oracle connection pooling using spring framework for Oracle 10g (Using TOMCAT).

We use Ibatis as ORM framework.

I am sure there is a way. any help is appreciated.

Answer 1

I would use Oracles supplied solution, which in included in their ojdbc jars. The older way was with the class OracleConnectionPoolDataSource but now you can set a parameter on a regular OracleDataSource and get connection pooling.

Here is how to do it in Spring:

<bean id="datasource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
   <property name="connectionCachingEnabled" value="true" />
   <property name="URL" value="${jdbc.url}" />
   ...all your connection properties
   <property name="connectionCacheProperties">
      <props merge="default">
         <prop key="MinLimit>3</prop>
         <prop key="MaxLimit">20</prop>
      </props>
   </property>
</bean>

Answer 2

I use C3PO to establish the connection. It has also the advantage to do connection pooling for you. Just define a datasource bean of type e.g. com.mchange.v2.c3p0.ComboPooledDataSource (or similar) through your spring config files. Before I run into troubles with connection pooling, I even used one of spring (DriverManagerDataSource) that is not advised for production use, because it is not actually doing the connection pooling.

Here is an example spring configuration.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:XE"/>
    <property name="user" value="username"/>
    <property name="password" value="secret"/>
    <property name="minPoolSize" value="5"/>
    <property name="maxPoolSize" value="20"/>
    <property name="acquireIncrement" value="1"/>
    <property name="idleConnectionTestPeriod" value="100"/>
    <property name="maxStatements" value="0"/>
    <property name="checkoutTimeout" value="60000"/>

Spring then injects the dataSource bean into Hibernate and all is well. You will also need to have the c3pO jar file on your classpath...