java com。微软sqlserver。jdbc。SQLServerException:索引8超出范围

Question

我正在尝试以下代码：

public class StoredProcedureParam 
{
    private String m_sType; 
    private String m_sValue;
    private String m_sParamName;

    public StoredProcedureParam(String a_sParamName, String a_sType, String a_sValue)
    {
        m_sType = a_sType;
        m_sValue = a_sValue;
        m_sParamName = a_sParamName;
    }
}

ArrayList<StoredProcedureParam> spmArr = new ArrayList<StoredProcedureParam>();

spmArr.add(new StoredProcedureParam("sBridgePhone",             "NString",  "value1"));
spmArr.add(new StoredProcedureParam("sCallerPaidTelNumber",             "NString",  "value2"));
spmArr.add(new StoredProcedureParam("sTollFreeTelNumber",       "NString",  "default"));
spmArr.add(new StoredProcedureParam("sParticipantAccessCode",   "NString",  "value3"));
spmArr.add(new StoredProcedureParam("sHostPassword",            "NString",  "value4"));
spmArr.add(new StoredProcedureParam("tNowUtc",                  "output",   "timestamp"));
spmArr.add(new StoredProcedureParam("nStatusCode",              "output",   "Int"));

if (!m_jdbcWrapper.callStoredProcedure("{call spAddConference(?,?,?,?,?,?,?)}", spmArr)) {
    System.out.println("callAddConferenceSp - Failed to execute");
    return "";
}


public boolean callStoredProcedure(String a_sStoredProcedure, ArrayList<StoredProcedureParam> a_ParamList)
{                   
    try 
    {
        connect();          

        m_cStatement = m_Connection.prepareCall(a_sStoredProcedure,
                                            ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                            ResultSet.CONCUR_READ_ONLY);

        registerSpParams(a_ParamList);

        m_cStatement.execute();         
    } 
    catch (SQLException ex) 
    {
        ex.printStackTrace();
        return false;
    }
    finally 
    {
        try 
        {
            m_cStatement.close();
            System.out.println("JDBCWrapper - Statement Closed!");
            m_Connection.close();
            System.out.println("JDBCWrapper - Connection Closed!");             
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    }

    return true;
}


private void registerSpParams(ArrayList<StoredProcedureParam> a_ParamList) throws SQLException {

        if (a_ParamList.size() > 0)
        {
            for (int i = 1; i <= a_ParamList.size(); i++)
            {
                StoredProcedureParam spp = a_ParamList.get(i - 1);
                switch (TypesEnum.valueOf(spp.getType()))
                {
                    case Integer:
                        m_cStatement.setInt(spp.getParamName(), Integer.parseInt(spp.getValue()));
                        break;
                    case String: 
                        m_cStatement.setString(spp.getParamName(), spp.getValue());
                        break;
                    case NString:
                        m_cStatement.setNString(spp.getParamName(), spp.getValue());
                        break;
                    case Timestamp:
                        m_cStatement.setTimestamp(spp.getParamName(), Timestamp.valueOf(spp.getValue()));
                        break;
                    case output:
                        switch (TypesEnum.valueOf(spp.getValue()))
                        {
                            case Integer:
                                m_cStatement.registerOutParameter(spp.getParamName(), java.sql.Types.INTEGER);
                                break;
                            case Timestamp:
                                m_cStatement.registerOutParameter(spp.getParamName(), java.sql.Types.TIMESTAMP);
                                break;
                            default:
                                break;
                        }
                        break;
                    case bit:
                        m_cStatement.setByte(spp.getParamName(), Byte.parseByte(spp.getValue()));
                        break;
                    case Bigint:
                        m_cStatement.setBigDecimal(spp.getParamName(), BigDecimal.valueOf(Long.parseLong(spp.getValue())));
                        break;
                }
            }
        }
    }

出于某种原因，我在这门课上遇到了一个例外：

com.microsoft.sqlserver.jdbc.SQLServerException: The index 8 is out of range.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
      at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.registerOutParameter(SQLServerCallableStatement.java:75)
      at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.registerOutParameter(SQLServerCallableStatement.java:1735)
      at com.att.ecm.jdbcwrapper.JDBCWrapper.registerSpParams(JDBCWrapper.java:216)
      at com.att.ecm.jdbcwrapper.JDBCWrapper.callStoredProcedure(JDBCWrapper.java:138)
      at com.client.main.ConferenceStoredProcedures.callAddConferenceSp(ConferenceStoredProcedures.java:74)
      at com.main.Main.startFlow(Main.java:51)
      at com.main.Main.executeFlow(Main.java:34)
      at com.main.Main.main(Main.java:26)

不明白为什么？据我所知，我设置了7个问号和7个参数。 那么，为什么它会在第八个索引上抛出一个异常呢

你能给个建议吗

向你问好Tal

Answer 1

当我映射了一个参数，但实际上没有在查询中使用该参数时，这种情况就发生了。令人惊讶的是，参数并没有被忽略

Answer 2

你需要的是一个CallableStatement对象，你可以在上面prepareCall

Creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure.

要更改上述代码，需要在set方法中使用index

比如m_cStatement.setInt(i,...

Answer 3

问题是存储过程的参数比7多。它有9个。 当我在调用字符串中添加了两个参数和两个问号时，exction没有发生