Вызов хранимой процедуры с указанием только параметров со значением

В экземпляре 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).

Я бы сделал то же самое с клиентом Java, использующим драйвер JDBC Microsoft 6.2. Я указываю параметры с List<Map<String, Object>>, так что список из пары parameterName ->parameterValue. Следующий метод создает 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?

Источник

2 ответа

Решение

Это подтвержденный недостаток текущей реализации поддержки именованных параметров для CallableStatement в драйвере mssql-jdbc. Несмотря на раздел 13.3.2 спецификации JDBC 4.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);
Источник

Если использование сторонней библиотеки для облегчения вызова таких процедур является для вас вариантом, то jOOQ, безусловно, поможет благодаря своему генератору кода для хранимых процедур , который создает заглушки для каждой из ваших процедур, делая такие вызовы безопасными. Он включает в себя поддержку:

  • Табличные функции
  • Табличные параметры
  • Параметры по умолчанию
  • Входные/выходные параметры
  • Необязательное возвращаемое значение процедур
  • Получение необъявленных счетчиков обновлений и наборов результатов
  • Гораздо более

В вашем случае вы можете написать:

      Spname sp = new Spname();
sp.setPar1("val1");
sp.setPar47("val47");
sp.execute(configuration); // The object containing your JDBC connection
sp.getResults();           // The result set(s) and update counts, if any

За кулисами JDBCCallableStatementсоздается так же, как и вручную:

      try (CallableStatement s = c.prepareCall(
    "{ ? = call [dbo].[spName] (@par1 = ?, @par47 = ?) }"
)) {
    // Get the optional procedure return value that all procedures might return
    s.registerOutParameter(1, Types.INTEGER); 

    s.setString(2, "val1");
    s.setString(3, "val47");
    s.execute();
    // Lengthy procedure to fetch update counts and result set(s)
}

Ознакомьтесь с этой статьей, если вы хотите получить общее количество обновлений и набор(ы) результатов с помощью JDBC .

Отказ от ответственности: я работаю в компании jOOQ.

Источник
Другие вопросы по тегам