using Oracle JDBC driver implicit caching feature

Question

I am pretty sure that somebody else already asked this question, but I still couldn't find a satisfactory answer to it. So, here is my scenario: I want to use the Oracle's JDBC driver implicit statement caching (documented here: http://docs.oracle.com/cd/B28359_01/java.111/b31224/stmtcach.htm#i1072607)

I need to use the connections from a 3rd party JDBC pool provider (to be more specific, Tomcat JDBC) and I have no choice there.

The problem is that the way to enable the implicit caching is a two-step process (accordingly to the documentation):

1.

Call setImplicitCachingEnabled(true) on the connection or Call OracleDataSource.getConnection with the ImplicitCachingEnabled property set to true. You set ImplicitCachingEnabled by calling OracleDataSource.setImplicitCachingEnabled(true)

2.

In addition to calling one of these methods, you also need to call OracleConnection.setStatementCacheSize on the physical connection. The argument you supply is the maximum number of statements in the cache. An argument of 0 specifies no caching.

I can live with 1 (somehow I can configure my pool to use the OracleDataSource as a primary connection factory and on that I can set the OracleDataSource.setImplicitCachingEnabled(true)). But at the second step, I already need the connection to be present in order to call the setStatementCacheSize.

My question is if there is any possibility to specify at the data source level a default value for the statementCacheSize so that I can get from the OracleDataSource connections that are already enabled for implicit caching.

PS: some related questions I found here: Oracle jdbc driver: implicit statement cache or setPoolable(true)?

Update (possible solution):

Eventually I did this:

1. Created a native connection pool using oracle.jdbc.pool.OracleDataSource.
2. Created a tomcat JDBC connection pool using org.apache.tomcat.jdbc.pool.DataSource that uses the native one (see the property dataSource).
3. Enabled via AOP a poincut so that after the execution of 'execution(public java.sql.Connection oracle.jdbc.pool.OracleDataSource.getConnection())' I pickup the object and perform the setting I wanted.

The solution works great; I am just unhappy that I had to write some boilerplate to do it (I was expecting a straight-forward property).

Answer 1

The white paper Oracle JDBC Memory Management says that

The 11.2 drivers also add a new property to enable the Implicit Statement Cache.

oracle.jdbc.implicitStatementCacheSize

The value of the property is an integer string, e.g. “100”. It is the initial size of the statement cache. Setting the property to a positive value enables the Implicit Statement Cache. The default is “0”. The property can be set as a System property via -D or as a connection property via getConnection.