Spring – Oracle Connection Pool Configuration

Spring – Oracle Connection Pool Configuration

This is basic data source (Only testing)

<bean id="datasource1"

  class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 

     <property name="driverClassName"> 

            <value>oracle.jdbc.driver.OracleDriver</value> 

     </property> 

     <property name="url"> 

<value>ORACLE URL</value> 

     </property> 

     <property name="username"> 

<value>user id</value> 

     </property> 

     <property name="password"> 

<value>user password</value> 

     </property> 

 </bean>

This is dbcp data source (Preferred for Testing.)

<bean id="datasource2"

class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">    <property name="driverClassName"> 

          <value>org.apache.commons.dbcp.BasicDataSource</value> 

 </property> 

 <property name="url"> 

          <value>ORACLE URL</value> 

 </property> 

 <property name="username"> 

          <value>user id</value> 

</property> 

<property name="password"> 

          <value>user password</value> 

</property> 

<property name="initialSize" value="5"/> 

<property name="maxActive" value="20"/> 

</bean>

This is Oracle Connection Pool (Production Quality)

<bean id="connectionPool1" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"> 

 <property name="connectionCachingEnabled" value="true" />

<property name="connectionCacheName" value="pool1" /> 

 <property name="URL"> 

          <value>ORACLE URL</value> 

 </property> 

 <property name="user"> 

          <value>user id</value> 

</property> 

<property name="password"> 

          <value>user password</value> 

</property> 

<property name="connectionCacheProperties"> 

       <value> 

         MinLimit:1 

         MaxLimit:5 

         InitialLimit:1 

         ConnectionWaitTimeout:120 

         InactivityTimeout:180 

         ValidateConnection:true 

       </value> 

</property> 

</bean>

**Oracle Connection Pool is better than DBCP

• To create pool you must set connectionCachingEnabled=true, otherwise the datasource would be serving physical connections
• Always set connectionCacheName for each datasource. And if you want separate datasources to be separate connection pools those names have to be unique. This is because Oracle implementation uses static singleton to manage "named" pools. connectionCacheName property references one of those "named" pools. So if two datasources have the same connectionCacheName they would actually be the using the same pool instance. If connectionCacheName is left blank, Oracle would generate a pseudo-random name and would create a new pool. The problem comes when you reload the application. If connectionCacheName is blank Oracle would create a new pool every time application is reloaded thus leaking resources.
• connectionCacheProperties do have to be specified the way they are in the example.
• Unlike DBCP, Oracle pool can check if connection is alive without running a SQL through it.
• Unlike DBCP, Oracle pool sticks to connection pool limits and timeouts.
• You can take advantage of Oracle proprietary failover mechanisms