Spring 的存储过程 - 从过程返回的结果总是空的 [英] Spring's Stored Procedure - results coming back from procedure always empty

问题描述

我正在使用 Spring 的 JdbcTemplate 和 StoredProcedure 类.我无法让存储过程类为我工作.

I am using Spring's JdbcTemplate and StoredProcedure classes. I am having trouble getting the stored procedure class to work for me.

我在 oracle 数据库上有一个存储过程.它的签名是

I have a stored procedure on an oracle database. Its signature is

CREATE OR REPLACE PROCEDURE PRC_GET_USERS_BY_SECTION
(user_cursor OUT Pkg_Types.cursor_type
 , section_option_in IN Varchar2
 , section_in IN Varchar2) AS ....

哪里

TYPE cursor_type IS REF CURSOR;

我创建了以下存储过程类来从 oracle 过程中获取信息

I have create the following stored procedure class to get information from the oracle procedure

    private class MyStoredProcedure extends StoredProcedure 
{
    public MyStoredProcedure(JdbcTemplate argJdbcTemplate) 
    {
        super(argJdbcTemplate, "PRC_GET_USERS_BY_SECTION");
        declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR));
        declareParameter(new SqlParameter("input1", Types.VARCHAR));
        declareParameter(new SqlParameter("input2", Types.VARCHAR));
        compile();          
    }


    public Map<String, Object> execute() {

        Map<String, Object> inParams = new HashMap<String, Object>();
        inParams.put("input1", "BG");
        inParams.put("input2", "FE");
        Map output = execute(inParams);
        return output;
    }
}

我在我的一个 DAO 类中的一个方法中调用它

I am calling this in a method in one of my DAO classes

    public List<String> getUserListFromProcedure() throws BatchManagerException
{
    MyStoredProcedure sp = new MyStoredProcedure( this.jdbcTemplate );
    Map<String, Object> result = new HashMap<String, Object>();
    try
    {
        result = sp.execute();
    }

    catch( DataAccessException dae) 
    {

    }
    System.out.println(result.size());
    return null;
}

然而地图的大小总是0，所以什么都没有回来.我知道数据库中有符合我输入条件的行.我也有使用 java.sql.CallableStatement 与 oracle 存储的 proc 交互的代码 - 所以 proc 很好.将 OraceleTypes.CURSOR 与 Spring 的存储过程混合使用是否错误?我还能用什么?我也试过 SqlReturnResultSet，但也没有用.

However the size of the map is always 0, so nothing comes back. I know that there are rows on the database which match my input criteria. Also I had code working which used java.sql.CallableStatement to interact with the oracle stored proc - so the proc is good. Is it wrong to mix OraceleTypes.CURSOR with Spring's Stored Procedure? What else can I use? I also tried SqlReturnResultSet and that didn't work either.

推荐答案

这里的问题是 Oracle 处理存储过程的方式与 JDBC 不兼容.Oracle 的 SP 通过 OUT 参数或作为游标的返回值返回结果集数据，并且必须对其进行特殊处理.这意味着您不能使用任何假定符合 JDBC 的 Spring JDBC 内容，您必须自己做.

The problem here is that Oracle's way of doing stored procedures is not JDBC compliant. Oracle's SPs return resultset data via OUT parameters or return values that are cursors, and they have to be handled specially. This means you cannot use any of Spring's JDBC stuff that assumes compliance with JDBC, you have to do it yourself.

在实践中，这意味着您必须使用 JdbcTemplate 和 CallableStatementCallback，这意味着手动 JDBC 编码比您理想中想要的要多得多，但我还没有想办法避免这种情况.

In practice, this means you have to use JdbcTemplate and CallableStatementCallback, which means a lot more manual JDBC coding than you'd ideally like, but I've yet to find a way to avoid this.

稍微说一下，我比较怀疑 JDBC 规范的编写与 Sybase(以及相关联的 SQL Server)的做事方式非常一致，因为在 JDBC 中处理存储过程的方式非常好适合这些系统(不适合 Oracle 系统).

On a slight aside, I rather suspect that the JDBC spec was written to conform closely to the Sybase (and, by association, SQL Server) way of doing things, because the way stored procedures are handled in JDBC is a remarkably good fit for those systems (and a poor fit for Oracle's).

这篇关于Spring 的存储过程 - 从过程返回的结果总是空的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！