问题：

Spring存储过程传递数组

曾永新

2023-03-14

我正在从Spring中调用一个存储过程，但是我需要向存储过程传递一个字符串数组。然而，到目前为止，我遇到了一个又一个错误。你知道哪里出了问题吗？

create or replace type type_string_array 
AS TABLE OF VARCHAR(255);

procedure sp_save_publication(
    p_id out t_tr_publication.id%type,
    p_tr_uid in t_tr_publication.tr_uid%type,
    p_title in t_tr_publication.title%type,
    p_item_title in t_tr_publication.item_title%type,
    p_cover_date in t_tr_publication.cover_date%type,
    p_issue in t_tr_publication.issue%type,
    p_sort_date in t_tr_publication.sort_date%type,
    p_volume in t_tr_publication.volume%type,
    p_page_begin in t_tr_publication.page_begin%type,
    p_page_end in t_tr_publication.page_end%type,
    p_accession_no in t_tr_publication.accession_no%type,
    p_issn in t_tr_publication.issn%type,
    p_doi in t_tr_publication.doi%type,
    p_doctypes in type_string_array,
    p_headings in type_string_array,
    p_keywords in type_string_array,
    p_organizations in type_string_array,
    p_publishers in type_string_array,
    p_sub_headings in type_string_array,
    p_sub_organizations in type_string_array,
    p_subjects in type_string_array
  ) AS
  BEGIN

    if p_doctypes.count > 0
    then

      for i in p_doctypes.FIRST .. p_doctypes.LAST
      loop

        pkg_logger.sp_log_error(
          p_code => null,
          p_message => p_doctypes(i),
          p_package_name => package_name,
          p_procedure_name => 'sp_save_publication'
        );

      end loop;

    end if;  

  END sp_save_publication;

public class SPSavePublication extends StoredProcedure {

    public SPSavePublication(JdbcTemplate jdbcTemplate) {

        super(jdbcTemplate,"PKG_THOMSON_REUTER.sp_save_publication");
        declareParameter(new SqlOutParameter("p_id", Types.INTEGER));
        declareParameter(new SqlParameter("p_tr_uid",Types.VARCHAR));
        declareParameter(new SqlParameter("p_title",Types.VARCHAR));
        declareParameter(new SqlParameter("p_item_title",Types.VARCHAR));
        declareParameter(new SqlParameter("p_cover_date",Types.VARCHAR));
        declareParameter(new SqlParameter("p_issue", Types.VARCHAR));
        declareParameter(new SqlParameter("p_sort_date",Types.DATE));
        declareParameter(new SqlParameter("p_volume",Types.VARCHAR));
        declareParameter(new SqlParameter("p_page_begin",Types.VARCHAR));
        declareParameter(new SqlParameter("p_page_end",Types.VARCHAR));
        declareParameter(new SqlParameter("p_accession_no",Types.VARCHAR));
        declareParameter(new SqlParameter("p_issn",Types.VARCHAR));
        declareParameter(new SqlParameter("p_doi",Types.VARCHAR));
        declareParameter(new SqlParameter("p_doctypes",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_headings",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_keywords",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_organizations",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_publishers",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_sub_headings",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_sub_organizations",Types.ARRAY,"TYPE_STRING_ARRAY"));
        declareParameter(new SqlParameter("p_subjects",Types.ARRAY,"TYPE_STRING_ARRAY"));
        setFunction(false);
        compile();
    }

    public long execute(String uid, String title, String itemTitle, String coverDate,
                        String issue, Date sortDate, String volume, String pageBegin,
                        String pageEnd, String accessionNo, String issn, String doi,
                        String[] doctypes, String[] headings, String[] keywords,
                        String[] organizations, String[] publishers, String[] subHeadings,
                        String[] subOrganizations, String[] subjects) {

        Map results = super.execute(uid,title,itemTitle,coverDate,issue,sortDate,volume,
                pageBegin,pageEnd,accessionNo,issn,doi,doctypes,headings,keywords,organizations,
                publishers,subHeadings,subOrganizations,subjects);
        return (int) results.get("p_id");
    }
}

我尝试运行以下测试。

@Test
    public void testSPSavePublication() {

        String[] docTypesArray = {"journal","doctypes"};

        SPSavePublication spSavePublication = new SPSavePublication(jdbcTemplate);
        spSavePublication.execute(null,null,null,null,null,new Date(),null,null,null,null,
                null,null,docTypesArray,docTypesArray,docTypesArray,docTypesArray,docTypesArray,
                docTypesArray,docTypesArray,docTypesArray);
    }

这是我得到的stacktrace。

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call PKG_THOMSON_REUTER.sp_save_publication(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; SQL state [99999]; error code [17059]; Fail to convert to internal representation: [Ljava.lang.String;@7b10472e; nested exception is java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.String;@7b10472e
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1137)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1173)
    at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:123)
    at org.company.app.procedures.thomsonreuter.SPSavePublication.execute(SPSavePublication.java:53)
    at org.company.app.ThomsonReutersTests.testSPSavePublication(ThomsonReutersTests.java:41)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:78)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:212)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:68)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Caused by: java.sql.SQLException: Fail to convert to internal representation: [Ljava.lang.String;@7b10472e
    at oracle.sql.ARRAY.toARRAY(ARRAY.java:244)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10472)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9966)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10590)
    at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:6119)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:249)
    at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.setObject(NewProxyCallableStatement.java:4025)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:432)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:150)
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:213)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1115)
    ... 36 more

共有1个答案

汤才捷

2023-03-14

由于Oracle中没有“内置”数组类型（至少直接对应于Java的数组类型），要成功地将Java数组作为参数传递给存储过程，还需要跳过几个环。事情是这样的：

定义一个可重用的SpringAbstractSqlTypeValue，它将表示字符串数组类型:

class OracleArrayType extends AbstractSqlTypeValue {

    private final Connection oracleCon;
    private final Object[] values;

    public OracleArrayType(final Connection oracleCon, final Object[] values) {
        this.oracleCon = oracleCon;
        this.values = values;
    }

    @Override
    protected Object createTypeValue(final Connection con, final int sqlType, final String typeName)
            throws SQLException {
        return new ARRAY(ArrayDescriptor.createDescriptor(typeName, oracleCon), oracleCon, values);
    }
}

此类型的实例将需要访问a）本机（“未包装的”）oracle连接（而不是代理）和b）传入的values数组。

public int execute(String uid, String title, String itemTitle, String coverDate, String issue, Date sortDate,
        String volume, String pageBegin, String pageEnd, String accessionNo, String issn, String doi,
        String[] doctypes, String[] headings, String[] keywords, String[] organizations, String[] publishers,
        String[] subHeadings, String[] subOrganizations, String[] subjects) {

    // Ensure proxy connection is closed properly
    try (final Connection connectionWrapper = getJdbcTemplate().getDataSource().getConnection()) {

        // Obtain native connection
        final Connection oracleConnection =
                getJdbcTemplate().getNativeJdbcExtractor().getNativeConnection(connectionWrapper);

        AbstractSqlTypeValue docTypeArray = new OracleArrayType(oracleConnection, doctypes);
        AbstractSqlTypeValue headingsArray = new OracleArrayType(oracleConnection, headings);
        AbstractSqlTypeValue keywordsArray = new OracleArrayType(oracleConnection, keywords);
        AbstractSqlTypeValue organizationsArray = new OracleArrayType(oracleConnection, organizations);
        AbstractSqlTypeValue publishersArray = new OracleArrayType(oracleConnection, publishers);
        AbstractSqlTypeValue subHeadingsArray = new OracleArrayType(oracleConnection, subHeadings);
        AbstractSqlTypeValue subOrganizationsArray = new OracleArrayType(oracleConnection, subOrganizations);
        AbstractSqlTypeValue subjectsArray = new OracleArrayType(oracleConnection, subjects);

        Map results = super.execute(uid, title, itemTitle, coverDate, issue, sortDate, volume,
                pageBegin, pageEnd, accessionNo, issn, doi, docTypeArray, headingsArray, keywordsArray,
                organizationsArray, publishersArray, subHeadingsArray, subOrganizationsArray, subjectsArray);

        return (int) results.get("p_id");
    } catch (SQLException e) {
        throw new DataRetrievalFailureException(e.getMessage()); // or whatever..
    }
}

这有点乏味，可能有更好的方法将其转换为自定义Oracle数组类型--但这种技术对我们来说很有效。

类似资料：

如何将数组传递到SQL Server存储过程

例如，我有一个员工列表。我想将这个列表作为一个表，并将它与另一个表连接起来。但是员工列表应该作为参数从C#传递。
存储过程，将表名作为参数传递

问题内容：我有大约六种通用但相当复杂的存储过程和函数，我想以一种更通用的方式使用它们。理想情况下，我希望能够将表名作为参数传递给过程，因为当前它是硬编码的。我所做的研究表明，我需要将过程中所有现有的SQL转换为使用动态SQL，以便从参数中拼接动态表名称，但是我想知道是否还有一种更简便的方法可以通过另一种方式引用该表？例如：如果是这样，如何从表名设置@MyTable变量？我正在使用SQL
oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)

本文向大家介绍oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)，包括了oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)的使用技巧和注意事项，需要的朋友参考一下 oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包认识存储过程和函数存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于
将复合类型的数组传递给存储过程

问题内容：我在形成文字时可能做错了。假设我有一个简单的存储过程，如下所示：的定义为：执行这样的查询：产生以下结果集：代替：我的文字是否有问题，还是应该以其他方式访问该字段？感谢您的任何建议。问题答案：指定输入的方式看起来不错，因为使用行和数组构造器语法可以观察到相同的行为：和：产生：如果添加：在循环内部，输出为：表明您实际上正在获取一个元组，其中“ message”是您期
调用传递表类型参数的存储过程

我在HANA数据库中创建了这个存储过程，它使用两个参数，一个是表类型，另一个是。现在我想在Java中调用这个过程，我写了这样的东西。有人能告诉我在调用此存储过程时，如何将对象作为参数中的表实体传递吗？
将数组传递给MySQL存储例程

问题内容：我需要将字符串数组作为参数传递给MySQL存储例程。该数组可能很长，其元素数量不是固定的。然后，我想将字符串值放入具有一列的内存表中，以便可以处理数据。我不知道这是否可以在MySQL中完成。也许需要肮脏的解决方法。例如，我有字符串值：现在，我想从MySQL 表中获取有关这些水果的数据。伪代码： Microsoft SQL Server允许您使用数据类型并将数组作为XML字符串提交，

Spring存储过程传递数组

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档