druid不兼容ojdbc8 java.sql.SQLException: closed Statement

问题现象

java Druid 程序在执行sql 时随机性抛出 java.sql.SQLException: closed Statement

java.sql.SQLException: 关闭的语句
	at oracle.jdbc.driver.OracleClosedStatement.exitImplicitCacheToActive(OracleClosedStatement.java:3125) ~[ojdbc8.jar:21.1.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.exitImplicitCacheToActive(OraclePreparedStatementWrapper.java:1181) ~[ojdbc8.jar:21.1.0.0.0]
	at com.alibaba.druid.util.OracleUtils.exitImplicitCacheToActive(OracleUtils.java:84) ~[druid-1.2.20.jar:?]
	at com.alibaba.druid.pool.PreparedStatementPool.get(PreparedStatementPool.java:65) ~[druid-1.2.20.jar:?]
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:364) ~[druid-1.2.20.jar:?]

初步分析:

Druid 启用PreparedStatementCache 后,程序需要执行SQL 时会从Druid Connection pools 获取 Connection ,获取的这个 Connection 如果是同段代码上N 次(N >0) 释放过的 (Connection.close 在启用连接池时只会释放到 connection pools ) connection.prepareStatement 时先是从PreparedStatementPool.get( key),key 为需要执行的SQL,但在ojdbc8 PreparedStatementPool.get 得到的是 OracleClosedStatement (ojdbc6 不会),OracleUtils 获取到缓存的PreparedStatement 需要执行 exitImplicitCacheToActive,OracleClosedStatement.exitImplicitCacheToActive() 是直接 throw 如下:

  //ojdbc.jar  oracle.jdbc.driver.OracleClosedStatement 
   public void exitImplicitCacheToActive() throws SQLException {
        throw (SQLException)((SQLException)DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), this.errorCode).fillInStackTrace());
    }

所以会出现随机性的 throw SQLException,具体是哪个环节把PreparedStatementPool 设置为 OracleClosedStatement ,有兴趣的朋友可以从 PreparedStatement.close() 代码分析,在这里还是得回到如何解决问题.

建议性解决方法:

1. 对性能要求不高

可以设置 maxPoolPreparedStatementPerConnectionSize = 0(默认是10),poolPreparedStatements 不用管理,设置了也无效.

(com.alibaba.druid.pool.DruidAbstractDataSource#setMaxPoolPreparedStatementPerConnectionSize )
    public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        if (maxPoolPreparedStatementPerConnectionSize > 0) {
            this.poolPreparedStatements = true;
        } else {
            this.poolPreparedStatements = false;
        }

        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

2. 保守性解决:
   回退到ojdbc6.jar
3. 其它方案:
   可以考虑换其它 DB 连接池验证,或是换国内数据库.