Tomcat 9 w/Apache DBCP + Spring 5 + Oracle 12c + SqlArrayValue [英] Tomcat 9 w/ Apache DBCP + Spring 5 + Oracle 12c + SqlArrayValue

问题描述

我们有运行在 Orale WebLogic 上的现有 SpringMVC J2EE 应用程序，我们正尝试将其迁移到 Apache Tomcat.一切似乎都有效，只是我们在通过 JDBC 将数组值绑定到数据库时遇到问题.这是在 WebLogic 上成功完成的方式.

We have existing SpringMVC J2EE application that runs on Orale WebLogic which we are attempting to migrate to Apache Tomcat. Everything seems to work except we are having issues binding array values through JDBC into the Database. Here is how that was being done successfully on WebLogic.

SqlParameterSource in = new MapSqlParameterSource()
                .addValue("i_username", user.getUsername())
                .addValue("i_statuses",
                        new SqlArrayValue<String>(statuses,
                                "VARCHAR_TABLE_T"));

Map<String, Object> out = myDatabaseProc.execute(in);

statuses 变量是一个字符串数组，而 VARCHAR_TABLE_T 是一个相同的 Oracle DB 类型.SqlArrayValue 是一个 Spring 类，专门用于 JDBC 中的 Oracle 数组处理.

The statuses variable is a String array and VARCHAR_TABLE_T is an Oracle DB type of the same. SqlArrayValue is a Spring class specifically for Oracle array handling in JDBC.

当代码执行时，我们得到以下错误(同样，这仅在 Tomcat 上被破坏).

When the code is executed we get the following error (again, this is only broken on Tomcat).

java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection
        at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(TypeDescriptor.java:803) ~[ojdbc8.jar:12.2.0.1.0]
        at oracle.sql.TypeDescriptor.<init>(TypeDescriptor.java:585) ~[ojdbc8.jar:12.2.0.1.0]
        at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:258) ~[ojdbc8.jar:12.2.0.1.0]
        at org.springframework.data.jdbc.support.oracle.SqlArrayValue.createTypeValue(SqlArrayValue.java:90) ~[spring-data-oracle-1.2.1.RELEASE.jar:?]
        at org.springframework.jdbc.core.support.AbstractSqlTypeValue.setTypeValue(AbstractSqlTypeValue.java:60) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:293) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:147) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:209) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1090) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1147) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:412) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:372) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
        at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

我在网上找到了一堆关于解包连接以便能够执行供应商特定操作的信息，但是似乎 Spring 应该为我做这件事?我尝试在 Tomcat 的 server.xml(我在网上找到的)中将 accessToUnderlyingConnectionAllowed 设置为 true，我们在其中设置了连接资源，但这并没有影响行为.

I found a bunch online about unwrapping the connection to be able to perform vendor specific actions, however it seems that Spring should be doing that for me? I tried setting accessToUnderlyingConnectionAllowed to true in Tomcat's server.xml (something I had found online) where we have the connection resource setup, but that did not affect the behavior.

这是来自 server.xml 的资源.

Here is the resource from server.xml.

<Resource name="jdbc/datasource" auth="Container"
          type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:thin:@//db.domain.com:port/sid"
          username="user" password="password"
          maxTotal="15" maxIdle="3" maxWaitMillis="-1"
          accessToUnderlyingConnectionAllowed="true" />

推荐答案

看来 spring-data-jdbc-ext 已经停产了.SqlArrayValue 类只是没有解开连接.我最终放弃了 JDBC 扩展并滚动了我自己的非常相似的类来处理解包连接.这现在适用于 WebLogic 和 Tomcat(accessToUnderlyingConnectionAllowed 设置为 true)DBCP 和 Tomcat 的本机连接池(不需要 accessToUnderlyingConnectionAllowed).

It appears that spring-data-jdbc-ext is EOL. The SqlArrayValue class simply isn't unwrapping the connection. I ended up jettisoning the JDBC extensions and rolling my own very similar class which handles unwrapping the connection. This now works on WebLogic and Tomcat (with accessToUnderlyingConnectionAllowed set to true) DBCP and Tomcat's native connection pool (no accessToUnderlyingConnectionAllowed required).

import java.sql.Connection;
import java.sql.SQLException;

import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;

import oracle.jdbc.OracleConnection;

public class OracleSqlArrayValue<T> extends AbstractSqlTypeValue {
    private T[] values;
    private String defaultTypeName;

    public OracleSqlArrayValue(T[] values) {
        this.values = values;
    }

    public OracleSqlArrayValue(T[] values, String defaultTypeName) {
        this.values = values;
        this.defaultTypeName = defaultTypeName;
    }

    @Override
    protected Object createTypeValue(Connection conn, int sqlType,
            String typeName) throws SQLException {

        if (typeName == null && defaultTypeName == null) {
            throw new InvalidDataAccessApiUsageException(
                    "No type named defined. Instantiate class with default type name.");
        }

        if (!conn.isWrapperFor(OracleConnection.class)) {
            throw new InvalidDataAccessApiUsageException(
                    "Unable to unwrap OracleConnection. Ensure you are connecting to an Oracle DB.");
        }

        return conn.unwrap(OracleConnection.class).createOracleArray(
                typeName != null ? typeName : defaultTypeName, values);
    }
}

这篇关于Tomcat 9 w/Apache DBCP + Spring 5 + Oracle 12c + SqlArrayValue的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！