Need of DataSource for oracle JDBC driver with enable/disable CacheConnection

Question

There are Oracle 11g2 and application Java based of spring

Need of DataSource for oracle JDBC driver with enable/disable CacheConnection functional in runtime - i.e. if CacheConnection is enable new connection not established if there are free connection in DataSource, if CacheConnection is disable always new connection established and existence is closing after put in idle

Early we used apache DataSource:

<bean id="datasourceClassic" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
                <property name="driverClassName" value="oracle.jdbc.OracleDriver" /> 
                <property name="url" value="jdbc:oracle:oci:@TEST" />
                <property name="username" value="TEST" />
                <property name="password" value="TEST" />
                <property name="maxActive" value="10" />
                <property name="defaultAutoCommit" value="false" /> 
</bean>

-- but this data source don't have functional for enable/disable CacheConnection

We tested OracleDataSource:

        <property name="URL" value="jdbc:oracle:oci:@TEST" />
        <property name="user" value="TEST" />
        <property name="password" value="TEST" />
        <property name="connectionCachingEnabled" value="true" />
        <property name="connectionCacheProperties">
            <props> 
                <prop key="MinLimit">0</prop> 
                <prop key="MaxLimit">1</prop> 
                <prop key="InitialLimit">0</prop> 
                <prop key="InactivityTimeout">10</prop> 
                <prop key="ConnectionWaitTimeout">10</prop>
                <prop key="ValidateConnection">true</prop>
            </props> 
        </property>
    </bean>

-- have this functional but it work only if explicity set up this options in context.xml, and method setConnectionCachingEnabled depreceted from runtime

Oracle since 11g2 recomended use Universal Connection Pool (UCP):

<bean id="datasource2" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
        <property name="connectionPoolName" value="TEST"/>

        <property name="URL" value="jdbc:oracle:oci:@TEST" />
        <property name="user" value="TEST" />
        <property name="password" value="TEST" />
        <property name="initialPoolSize" value="0" />
        <property name="minPoolSize" value="0" />
        <property name="maxPoolSize" value="1" />
        <property name="validateConnectionOnBorrow" value="true" />
    </bean>

-- but this pool also didn't have functional to enable/disable CacheConnection...

Any idea of what DataSource can satisfy my requirements?

UPDATE:
I test next trik with org.apache.commons.dbcp.BasicDataSource:

/* "Disable" CacheConnection  */
BasicDataSource bds = ... //get DataSource
bds.setMaxIdle(0);

and:

/* "Enable" CacheConnection */
BasicDataSource bds = ... //get DataSource
bds.setMaxIdle(bds.getMaxActive());

Works like <property name="connectionCachingEnabled" value="true" /> - what do you think about this?

[UPDATE] I test next trik with org.apache.commons.dbcp.BasicDataSource: BasicDataSource.setMaxIdle(0) - for disable and BasicDataSource.setMaxIdle(bds.getMaxActive()) - for enable. What do you think about this? — Testus

climmunk climmunk · Accepted Answer · 2011-04-20T23:12:41

Are you able to create a data source which has caching enabled and one which has caching disabled? If so, you could create two different DataSource beans with different names and configurations in your ApplicationContext. You could autowire both in your application and switch between them dynamically.

public class MyClass {
  @Autowired
  @Qualifier("datasourceCacheDisabled")
  DataSource cacheDisabledDataSource;
  @Autowired
  @Qualifier("datasourceCacheEnabled")
  DataSource cacheEnabledDataSource;
  DataSource dataSource=null;

  boolean enableCache;
  public MyClass() {
    // enable by default
    enableDataSourceCaching();
  }
  public void enableDataSourceCaching() {
    enableCache=true;
    dataSource=cacheEnabledDataSource;
  }
  public void disableDataSourceCaching() {
    enableCache=false;
    dataSource=cacheDisabledDataSource;
  }
  // code which uses the current dataSource
  ...
}

Alternately, if you need beans with the DataSource wired already, you could create two ApplicationContexts and toggle between them. Note that the latter solution prevents results caching; I'm not sure about the effects on caching with the first solution.

Need of DataSource for oracle JDBC driver with enable/disable CacheConnection

1 Answers