spring ArrayIndexOutOfBoundsException with SimpleJdbcCall

Question

This is the Oracle procedure I'm trying to call:

 PROCEDURE GetCoreReportExtras
( pnAssignment IN NUMBER,
  pnUserRole in NUMBER,
  psAreaMenu in VARCHAR2,
  pnAreaLevel in NUMBER,
  curReportList OUT outcur,
  psLDO in VARCHAR2 default 'none',
  pnAcisNumber in NUMBER default 0);

Here's my Java code:

private class GetStandardReportExtrasSPV2{
    int nAreaLevel;
    int nAssignment;
    int nUserRole;
    int nAcisNum = 0;
    String strAreaMenu;     
    String strLDO = null;

    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcCall procGetReportExtras;

    public GetStandardReportExtrasSPV2(DataSource ds, int nUserRole, String strAreaMenu,
            int nAssignment, int nAreaLevel, String strLDO, int nAcisNum) {

        this.simpleJdbcTemplate = new SimpleJdbcTemplate(ds);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procGetReportExtras =
                new SimpleJdbcCall(jdbcTemplate)
                    .withCatalogName("package")
                    .withProcedureName("proc")
                    .returningResultSet(REPORT_LIST,
                                ParameterizedBeanPropertyRowMapper.newInstance(Report.class));

    }

    public List<Report> getReportsList() {

        Map<String, Object> params = new HashMap<String, Object>();

        params.put(USER_ASSIGNMENT, nAssignment);
        params.put(USER_ROLE, nUserRole);
        params.put(AREA_MENU, strAreaMenu);
        params.put(AREA_LEVEL, nAreaLevel);
        params.put(SEGMENT, strLDO);
        params.put(ACIS_NUMBER, nAcisNum);

        SqlParameterSource in = new MapSqlParameterSource()
        .addValues(params);

        Map m = procGetReportExtras.execute(new HashMap<String, Object>(0), in);
        return (List) m.get(REPORT_LIST);
    }

}

When getReportsList() calls execute(), I get the following Exception:

java.lang.ArrayIndexOutOfBoundsException: 2 org.springframework.jdbc.core.metadata.CallMetaDataContext.matchInParameterValuesWithCallParameters(CallMetaDataContext.java:555) org.springframework.jdbc.core.simple.AbstractJdbcCall.matchInParameterValuesWithCallParameters(AbstractJdbcCall.java:419) org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:364) org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:173)

Any hints as to what I'm doing wrong?

Answer 1

The issue is here

Map m = procGetReportExtras.execute(new HashMap<String, Object>(0), in);

You are calling the overloaded method that takes in a Object .... This method takes

optional array containing the in parameter values to be used in the call. Parameter values must be provided in the same order as the parameters are defined for the stored procedure.

In your call, those two parameters happen to be an empty HashMap and a SqlParameterSource.

The call fails in CallMetaDataContext#matchInParameterValuesWithCallParameters(). Source:

public Map<String, ?> matchInParameterValuesWithCallParameters(Object[] parameterValues) {
    Map<String, Object> matchedParameters = new HashMap<String, Object>(parameterValues.length);
    int i = 0;
    for (SqlParameter parameter : this.callParameters) {
        if (parameter.isInputValueProvided()) {
            String parameterName =  parameter.getName();
            matchedParameters.put(parameterName, parameterValues[i++]); // fails here
        }
    }
    return matchedParameters;
}

It's expecting that the array you pass has 7 elements (because that's what your stored procedure expects), but it only has 2, the HashMap and the SqlParameterSource. When it tries to access the 3rd one (index 2), it throws an ArrayIndexOutOfBoundsException.

You probably wanted to use the execute() method that accepts an SqlParameterSource

Map m = procGetReportExtras.execute(in);

Answer 2

You are calling the wrong SimpleJDBCCall.execute(Object... args); use SimpleJDBCCall.execute(SqlParameterSource)

procGetReportExtras.execute(in);