代码之家 › 专栏 › 技术社区 › Armando Contestabile

仅指定具有值的参数的调用存储过程

mssql-jdbc parameters jdbc stored-procedures sql-server

4

Armando Contestabile · 技术社区 · 7 年前

在SQL Server 2016的一个实例中,我有一个包含数十个参数的存储过程。例如:

CREATE PROCEDURE spName (
    @par1 INT = NULL,
    @par2 VARCHAR(10) = NULL,
        ....
        ....
    @par98 INT = NULL,
    @par99 INT = NULL,
) AS
BEGIN
    ....
    ....
END

我有一个用C语言编写的客户端,它调用存储过程,只指定带有值的参数。前任:

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "spName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = dbConn;

cmd.Parameters.Add(new SqlParameter("par1", "val1"));
cmd.Parameters.Add(new SqlParameter("par47", "val47"));

...

cmd.ExecuteNonQuery();

它工作得很好!因此,执行该过程,只有2个参数(par1和par47)有值。其他参数保持默认值(NULL)。

我将使用Microsoft JDBC driver 6.2在Java客户机上执行相同的操作。我用 List<Map<String, Object>> ,所以一组参数名-->参数值。以下方法构建 PreparedStatement 对象:

private CallableStatement prepareStatement(String spName, Map<String, ?> parameters) throws SQLException {
    setupConnection();
    CallableStatement stmt = null;
    try {
        stmt = conn.prepareCall(getSpCallString(spName, parameters));
        if (parameters != null) {
            for (String parName : parameters.keySet())
                stmt.setObject(parName, parameters.get(parName));
        }
    } catch (SQLException e) {
        ApplicationLogging.severe("Cannot prepare callable statement", e);
        throw e;
    }
    return stmt;
}

方法getSpCallString()生成以下类型的字符串 { call spName ?,?, ... , ? } 有许多 ? 由于参数的个数带有一个传递给程序的值,所以不是所有99个参数。如果我有2个参数,它会生成字符串 { call spName ?,? } . 例如,通过传递par15=val15和par47=val47,它会引发以下异常:

com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.

我可以通过输入相同数量的调用命令来解决这个问题 ? 作为存储过程的参数数,但。。。我不知道每个存储过程的参数数量(及其位置)! 在C#中,这是简单的解决方法,因为参数仅用其名称分配,因此参数的数量和顺序实际上可以是一个黑匣子。

我可以在Java中以某种方式做到这一点吗?

1 回复 | 直到 7 年前

1

3

Gord Thompson 7 年前

这是一个 confirmed deficiency 在当前的命名参数支持实现中 CallableStatement 在mssql jdbc驱动程序中。尽管JDBC 4.2规范第13.3.2节指出。。。

命名参数只能用于指定没有默认值的值。

... 我们似乎需要为每个可能的参数提供一个参数占位符,而且似乎没有一种方法来指定 DEFAULT 对于参数,我们可能会忽略。

public static ResultSet executeStoredProcedureQuery(
        Connection conn, String spName, Map<String, Object> paramItems) 
        throws SQLException {
    StringBuffer sqlBuf = new StringBuffer("EXEC ");
    sqlBuf.append(spName);
    int paramCount = 1;
    for (String paramName : paramItems.keySet()) {
        sqlBuf.append(
                (paramCount++ > 1 ? ", " : " ") + 
                (paramName.startsWith("@") ? "" : "@") + paramName + "=?");
    }
    String sql = sqlBuf.toString();
    myLogger.log(Level.INFO, sql);
    // e.g., EXEC dbo.BreakfastSP @helpings=?, @person=?, @food=?
    PreparedStatement ps = conn.prepareStatement(sql);
    paramCount = 1;
    for (String paramName : paramItems.keySet()) {
        ps.setObject(paramCount++, paramItems.get(paramName));
    }
    return ps.executeQuery();
}

我们可以这样称呼它

// test data
Map<String, Object> paramItems = new HashMap<>();
paramItems.put("@person", "Gord");
paramItems.put("@food", "bacon");
paramItems.put("@helpings", 3);
//
ResultSet rs = executeStoredProcedureQuery(conn, "dbo.BreakfastSP", paramItems);