PreparedStatement IN clause alternatives?

Каковы наилучшие обходные пути для использования предложения SQL IN с экземплярами java.sql.PreparedStatement, которое не поддерживается для нескольких значений из-за проблем безопасности SQL-инъекций: один ? заполнитель представляет одно значение, а не список значений.

Рассмотрим следующий оператор SQL:

SELECT my_column FROM my_table where search_column IN (?)

Использование preparedStatement.setString( 1, "'A', 'B', 'C'" ); по сути, является нерабочей попыткой обойти причины использования ? в первую очередь.

Какие существуют обходные пути?

Анализ различных доступных вариантов, а также плюсы и минусы каждого из них доступны в записи Жанны Боярской о пакетной обработке инструкций Select в JDBC в журнале JavaRanch.

Предлагаемые варианты следующие:

• Подготовьте SELECT my_column FROM my_table WHERE search_column = ?, выполните его для каждого значения и ОБЪЕДИНИТЕ результаты на стороне клиента. Требуется только один подготовленный оператор. Медленно и болезненно.


• Подготовьте SELECT my_column FROM my_table WHERE search_column IN (?,?,?) и выполните его. Требуется один подготовленный оператор для каждого размера В списке. Быстро и очевидно.


• Подготовьте SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... и выполните его. [Или используйте UNION ALL вместо этих точек с запятой. --ред.] Требуется один подготовленный оператор для каждого размера В списке. Тупо медленно, строго хуже, чем WHERE search_column IN (?,?,?), поэтому я не знаю, почему блоггер вообще предложил это.


• Используйте хранимую процедуру для построения результирующего набора.


• Подготовьте N запросов разного размера В списке; скажем, со значениями 2, 10 и 50. Чтобы выполнить поиск в списке с 6 различными значениями, заполните запрос размером 10 так, чтобы он выглядел как SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Любой приличный сервер оптимизирует повторяющиеся значения перед выполнением запроса.

Ни один из этих вариантов не идеален.

Лучший вариант, если вы используете JDBC4 и сервер, который поддерживает x = ANY(y), - это использовать PreparedStatement.setArray как описано в anwser от Бориса.

Похоже, нет никакого способа заставить setArray работать со списками входящих.

Иногда инструкции SQL загружаются во время выполнения (например, из файла свойств), но требуют переменного количества параметров. В таких случаях сначала определите запрос:

query=SELECT * FROM table t WHERE t.column IN (?)

Затем загрузите запрос. Затем определите количество параметров перед его запуском. Как только количество параметров станет известно, запустите:

sql = any( sql, count );

Например:

/**
 * Converts a SQL statement containing exactly one IN clause to an IN clause
 * using multiple comma-delimited parameters.
 *
 * @param sql The SQL statement string with one IN clause.
 * @param params The number of parameters the SQL statement requires.
 * @return The SQL statement with (?) replaced with multiple parameter
 * placeholders.
 */
public static String any(String sql, final int params) {
    // Create a comma-delimited list based on the number of parameters.
    final StringBuilder sb = new StringBuilder(
        String.join(", ", Collections.nCopies(possibleValue.size(), "?")));

    // For more than 1 parameter, replace the single parameter with
    // multiple parameter placeholders.
    if (sb.length() > 1) {
        sql = sql.replace("(?)", "(" + sb + ")");
    }

    // Return the modified comma-delimited list of parameters.
    return sql;
}

Для определенных баз данных, где передача массива через спецификацию JDBC 4 не поддерживается, этот метод может облегчить преобразование условия предложения slow = ? в faster IN (?), которое затем может быть расширено путем вызова any метода.

Решение для PostgreSQL:

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

или

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

На самом деле нет простого способа. Если целью является поддержание высокого коэффициента кэширования инструкций (т. Е. Не создавать инструкцию для каждого параметра count), вы можете сделать следующее:

1. создайте инструкцию с несколькими (например, 10) параметрами:

   
   

   ... ГДЕ A В (?,?,?,?,?,?,?,?,?,?) ...




2. Привязать все фактические параметры

   
   

   setString(1,"foo"); setString(2, "bar");




3. Свяжите остальное как NULL

   
   

   SetNull(3, типы.VARCHAR) ... SetNull(10, типы.VARCHAR)

NULL никогда ничему не соответствует, поэтому он оптимизируется SQL plan builder.

Логику легко автоматизировать, когда вы передаете список в функцию DAO:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

Вы можете использовать Collections.nCopies для создания коллекции заполнителей и объединения их с помощью String.join:

List<String> params = getParams();
String placeHolders = String.join(",", Collections.nCopies(params.size(), "?"));
String sql = "select * from your_table where some_column in (" + placeHolders + ")";
try (   Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql)) {
    int i = 1;
    for (String param : params) {
        ps.setString(i++, param);
    }
    /*
     * Execute query/do stuff
     */
}