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是好的。使用Spring的存储过程混合 OraceleTypes.CURSOR 是错误的吗？我还能用什么？我也尝试了 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屋！