使用 Oracle JDBC 驱动程序隐式缓存功能

2021-12-30 00:00:00 oracle11g driver oracle jdbc

我很确定其他人已经问过这个问题，但我仍然找不到满意的答案.所以，这是我的场景:我想使用 Oracle 的 JDBC 驱动程序隐式语句缓存(记录在这里:http://docs.oracle.com/cd/B28359_01/java.111/b31224/stmtcach.htm#i1072607)

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)

我需要使用来自第 3 方 JDBC 池提供程序(更具体地说，Tomcat JDBC)的连接，但我别无选择.

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.

在连接上调用 setImplicitCachingEnabled(true)或者使用 ImplicitCachingEnabled 调用 OracleDataSource.getConnection属性设置为true.您可以通过调用设置 ImplicitCachingEnabledOracleDataSource.setImplicitCachingEnabled(true)

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.

除了调用其中一种方法外，还需要调用物理连接上的 OracleConnection.setStatementCacheSize.这您提供的参数是缓存中的最大语句数.参数 0 指定不缓存.

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.

我可以接受 1(不知何故我可以配置我的池以使用 OracleDataSource 作为主要连接工厂，然后我可以设置 OracleDataSource.setImplicitCachingEnabled(true)>).但在第二步，我已经需要存在连接才能调用 setStatementCacheSize.

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.

我的问题是，是否有可能在数据源级别为 statementCacheSize 指定默认值，以便我可以从已经存在的 OracleDataSource 连接中获取启用隐式缓存.

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:我在这里找到的一些相关问题:Oracle jdbc 驱动:隐式语句缓存还是 setPoolable(true)?

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

更新(可能的解决方案):

最终我做到了:

  1. 使用 oracle.jdbc.pool.OracleDataSource 创建本机连接池.
  2. 使用 org.apache.tomcat.jdbc.pool.DataSource 创建了一个使用本机连接池的 tomcat JDBC 连接池(请参阅属性 dataSource).
  3. 通过 AOP 启用一个 poincut，以便在执行execution(public java.sql.Connection oracle.jdbc.pool.OracleDataSource.getConnection())"后我拾取对象并执行我想要的设置.
  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).

推荐答案

白皮书 OracleJDBC 内存管理说

11.2 驱动程序还添加了一个新属性来启用隐式语句缓存.

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

oracle.jdbc.implicitStatementCacheSize

该属性的值是整数字符串，例如100".它是语句的初始大小缓存.将该属性设置为正值可启用隐式语句缓存.默认值为0".该属性可以设置为系统属性通过 -D 或作为连接属性通过 getConnection.

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.

相关文章