MapSqlParameterSource与存储过程的NamedParameterJdbcTemplate映射不正确 [英] MapSqlParameterSource Does Not Map Properly with NamedParameterJdbcTemplate for a stored procedure

问题描述

我正在尝试使用带有NamedParameterJdbcTemplate的映射参数(Spring 3.0)来调用存储过程，但是出于某些奇怪的原因，某些值未传递给该存储过程(Oracle 11.2)！

I am trying to call a stored procedure using mapped parameters with NamedParameterJdbcTemplate (Spring 3.0), but for some strange reason certain values do not get passed to the stored procedure (Oracle 11.2)!

相关存储过程的接口:

PROCEDURE my_stored_proc(
  h_id IN NUMBER,
  h_type IN VARCHAR2,
  h_status in varchar2  DEFAULT null,
  h_end_date IN DATE DEFAULT null,
  h_reason IN VARCHAR2 DEFAULT null,
  h_rating IN VARCHAR2 DEFAULT null,
  h_position IN VARCHAR2 DEFAULT null,
  h_rater_id IN VARCHAR2 DEFAULT null,
  h_start_date IN DATE DEFAULT null,
  h_rater IN VARCHAR2 DEFAULT null,
  h_supervisor IN VARCHAR2 DEFAULT null,
  h_grade IN VARCHAR2 DEFAULT null)

出于我的功能目的(并非全部)，我只需要传递一小部分参数:

I only need to pass a small subset of parameters for the purpose of my functionality (not all):

String sql = "{call my_stored_proc(:h_id,:h_type,:h_reason,:h_position)}";

MapSqlParameterSource sqlParamMap = new MapSqlParameterSource();
sqlParamMap.addValue("h_id", myObj.getHId(), Types.NUMERIC);
sqlParamMap.addValue("h_type", myObj.getHType(), Types.VARCHAR);
sqlParamMap.addValue("h_reason", myObj.getHReason(), Types.VARCHAR);
sqlParamMap.addValue("h_position", myObj.getHPosition(), Types.VARCHAR);

NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
template.execute(sql, sqlParamMap, new PreparedStatementCallback<Boolean> {

    @Override
    public Boolean doInPreparedStatement(PreparedStatement ps)
            throws SQLException, DataAccessException {
        return ps.execute();
    }
);

如您所见，我没有使用所有存储过程的参数-仅基于此特定功能的必需和必需.但是，当我让my_stored_proc()简单地将参数的值写入表时，即使myObj.getHReason()和myObj.getHPosition()都具有适当的值，h_reason和h_position还是空的.

As you can see, I am not utilizing all the stored procedure's parameters -- only the required and needed based on this particular functionality. Nonetheless, when I let my_stored_proc() to simply write the values of the parameters to a table, h_reason and h_position are empty even though during debugging myObj.getHReason() and myObj.getHPosition() both have proper values.

如果我通过以下来自SQL的匿名块执行存储过程:

If I execute the stored procedure via the following anonymous block from SQL:

DECLARE
  h_id NUMBER;
  h_type VARCHAR2(200);
  h_reason VARCHAR2(200);
  h_position VARCHAR2(200);
begin
  h_id := 12352;
  h_type := 'Z';
  h_reason := 'L';
  h_position := 'P';

my_stored_proc(
    h_id => h_id,
    h_type => h_type,
    h_reason => h_reason,
    h_position => h_position
  );
END;

存储过程确实插入了一组正确的值，因此不是存储过程有问题.即使不是全部使用，我是否需要指定所有参数?

The stored procedure does insert a right set of values, so it is not the stored procedure that is at fault. Do I need to specify all the parameters even if not all are utilized?

推荐答案

根据我的经验，我在MapSqlParameterSource上遇到了很多问题，从那时起，我转而使用Map<String, Object>来解决变量绑定的问题.

In my experience, I have had numerous issues with MapSqlParameterSource and ever since, I have switched to using Map<String, Object> to resolve the issue of variable binding.

这篇关于MapSqlParameterSource与存储过程的NamedParameterJdbcTemplate映射不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！