jdbctemplate - 将 sql.oracle.ARRAY SimpleJdbcCall 传递给 StoredProcedure

Question

我使用 JBoss 7.1.Final 作为应用服务器，使用 Oracle 作为数据库。我们正在使用 Spring 框架 3.x 和 Java 6。尝试传入一个字符串数组并将它们在存储过程中转换为 varchars 数组。我还没有找到一个很好的例子。如果可以，请提供指向任何文档或以前的论坛帖子的指针。我已经搜索过，但没有找到似乎适用的。

存储过程定义为:

CREATE OR REPLACE PROCEDURE GET_TEST_CONTENTS
(IN_RR_ARRAY IN RR_ARRAY,
  IN_ORDER_STATE           IN  VARCHAR2,
  OUT_FLAG      OUT VARCHAR2,
  OUT_RETURN_CODE          OUT VARCHAR2,
  OUT_RETURN_DESC          OUT VARCHAR2,
  OUT_RETURN_TYPE          OUT VARCHAR2,
  OUT_RETURN_VAL           OUT NUMBER
  )

类型 RR_ARRAY 定义为:创建或替换键入 RR_ARRAY 作为 varchar2(15) 的表；

在我的 java 代码中我有:

jdbcTemplate = new JdbcTemplate(dataSource);

jdbcTemplate.setResultsMapCaseInsensitive(true);
this.getTestContents = new SimpleJdbcCall(jdbcTemplate)
        .withCatalogName("STAR")
        .withoutProcedureColumnMetaDataAccess()
        .withProcedureName("GET_TEST_CONTENTS")
        .declareParameters(
                new SqlParameter("IN_RR_ARRAY", OracleTypes.ARRAY,
                        "RR_ARRAY"),
                new SqlParameter("IN_ORDER_STATE", OracleTypes.VARCHAR),                        new SqlOutParameter("OUT_FLAG",
                OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_RETURN_VAL", OracleTypes.INTEGER), 
                new SqlOutParameter("OUT_RETURN_CODE", OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_RETURN_DESC", OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_RETURN_TYPE", OracleTypes.VARCHAR));

         //I get a different error here so creating new connection for testing
        //conn = jdbcTemplate.getDataSource().getConnection();

        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        conn = DriverManager.getConnection(jdbcURL, user, passwd);
                ArrayDescriptor desc = new ArrayDescriptor("STAR.RR_ARRAY", conn);
        ARRAY arr = new ARRAY(desc, conn, testArray); // testArray is just 
                                                      // String[] with 2 values
        Map<String, Object> hm = new HashMap<String, Object>();
        hm.put("IN_RR_ARRAY", arr);
        hm.put("IN_ORDER_STATE", stateCode);
        hm.put("OUT_FLAG", Types.VARCHAR);
        hm.put("OUT_RETURN_CODE", Types.VARCHAR);
        hm.put("OUT_RETURN_DESC", Types.VARCHAR);
        hm.put("OUT_RETURN_TYPE", Types.VARCHAR);

        SqlParameterSource in = new MapSqlParameterSource().addValues(hm);
        Map out = getTestContents .execute(in);

返回的堆栈跟踪是:

11:24:43,691 ERROR [com.test.repository.TestContentsDao] (http-localhost-127.0.0.1-8080-1) Error while calling GET_TEST_CONTENTS Stored procedure: org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call STAR.GET_TEST_CONTENTS(?, ?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17059]; Fail to convert to internal representation: oracle.sql.ARRAY@2a081f8f; nested exception is java.sql.SQLException: Fail to convert to internal representation: oracle.sql.ARRAY@2a081f8f
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:351) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181) [spring-jdbc-3.0.7.RELEASE.jar:3.0.7.RELEASE]
at com.test.repository.TestContentsDao.isGood(TestContentsDao.java:147) [classes:]

任何建议或示例或文档的指针将不胜感激

Answer 1

我找到了解决这个问题的办法。现在我使用这个字符串列表:

List<String> ndcList;

我将数组参数从 OracleTypes.ARRAY 更改为 java.sql.stypes.ARRAY 并在数组名称上指定了模式前缀。并更改了代码并在底部创建了一些新的便捷方法。

我需要包装连接并且必须将此依赖项添加到我的 pom:

<dependency>
    <groupId>jboss</groupId>
    <artifactId>jboss-common-jdbc-wrapper</artifactId>
    <version>3.2.3</version>
</dependency>

----方法代码从这里开始------------

jdbcTemplate = new JdbcTemplate(dataSource);

jdbcTemplate.setResultsMapCaseInsensitive(true);
this.getTestContents = new SimpleJdbcCall(jdbcTemplate)
        .withCatalogName("STAR")
        .withoutProcedureColumnMetaDataAccess()
        .withProcedureName("GET_TEST_CONTENTS")
        .declareParameters(
                new SqlParameter("IN_RR_ARRAY", java.sql.types.ARRAY, "STAR.RR_ARRAY"),
                new SqlParameter("IN_ORDER_STATE", OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_FLAG", OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_RETURN_VAL", OracleTypes.INTEGER),
                new SqlOutParameter("OUT_RETURN_CODE", OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_RETURN_DESC", OracleTypes.VARCHAR),
                new SqlOutParameter("OUT_RETURN_TYPE", OracleTypes.VARCHAR));

Map<String, Object> hm = new HashMap<String, Object>();
hm.put("IN_RR_ARRAY", new ScriptArray(ndcList));
hm.put("IN_ORDER_STATE", stateCode);
hm.put("OUT_FLAG", Types.VARCHAR);
hm.put("OUT_RETURN_CODE", Types.VARCHAR);
hm.put("OUT_RETURN_DESC", Types.VARCHAR);
hm.put("OUT_RETURN_TYPE", Types.VARCHAR);

SqlParameterSource in = new MapSqlParameterSource().addValues(hm);
Map out = getTestContents.execute(in);
----  method code ends here------------

public class ScriptArray extends AbstractSqlTypeValue {
    private List<String> values;

    public ScriptArray(List<String> values) {
        this.values = values;
    }

    public Object createTypeValue(Connection con, int sqlType,
                                  String typeName) throws SQLException {
        oracle.jdbc.OracleConnection wrappedConnection = con
                .unwrap(oracle.jdbc.OracleConnection.class);
        con = wrappedConnection;
        ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
        return new ARRAY(desc, con,
                (String[]) values.toArray(new String[values.size()]));
    }
}