Java Spring 的存储过程 - 从过程返回的结果总是空的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2154386/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring's Stored Procedure - results coming back from procedure always empty
提问by aos
I am using Spring's JdbcTemplate and StoredProcedure classes. I am having trouble getting the stored procedure class to work for me.
我正在使用 Spring 的 JdbcTemplate 和 StoredProcedure 类。我无法让存储过程类为我工作。
I have a stored procedure on an oracle database. Its signature is
我在 oracle 数据库上有一个存储过程。它的签名是
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 ....
where
在哪里
TYPE cursor_type IS REF CURSOR;
I have create the following stored procedure class to get information from the oracle procedure
我创建了以下存储过程类以从 oracle 过程中获取信息
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;
}
}
I am calling this in a method in one of my DAO classes
我在我的一个 DAO 类中的一个方法中调用它
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;
}
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.CallableStatementto interact with the oracle stored proc - so the proc is good. Is it wrong to mix OraceleTypes.CURSORwith Spring's Stored Procedure? What else can I use? I also tried SqlReturnResultSetand that didn't work either.
然而地图的大小总是 0，所以什么都没有回来。我知道数据库中有符合我输入条件的行。此外，我有代码工作，用于java.sql.CallableStatement与 oracle 存储的 proc 交互 - 所以 proc 很好。OraceleTypes.CURSOR和Spring的存储过程混用有错吗？我还能用什么？我也试过了SqlReturnResultSet，也没用。
采纳答案by skaffman
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.
这里的问题是 Oracle 处理存储过程的方式不符合 JDBC。Oracle 的 SP 通过 OUT 参数或作为游标的返回值返回结果集数据，并且必须对其进行特殊处理。这意味着您不能使用任何假定符合 JDBC 的 Spring JDBC 内容，您必须自己做。
In practice, this means you have to use JdbcTemplateand 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.
实际上，这意味着您必须使用JdbcTemplateand CallableStatementCallback，这意味着手动 JDBC 编码比理想情况下要多得多，但我还没有找到避免这种情况的方法。
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).
稍微说一下，我更怀疑 JDBC 规范的编写与 Sybase（以及相关联的 SQL Server）的做事方式非常一致，因为在 JDBC 中处理存储过程的方式非常适合那些系统（并且不适合 Oracle 的）。
回答by Sahaya Reegan
The problem is simple if you don't pass a RowMapper while declaring the outparam which is a CURSOR. Spring is going to return {}I.e empty cursor.
如果在声明作为 CURSOR 的 outparam 时不传递 RowMapper，问题就很简单。Spring 将返回{}即空游标。
declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR)); - returns empty {}
declareParameter(new SqlOutParameter("output", OracleTypes.CURSOR, new ApplicationMapper()); - returns result
Where ApplicationMapper is my custom mapper which implements RowMapper.
其中 ApplicationMapper 是我的自定义映射器，它实现了 RowMapper。
