Вопрос-ответ

PreparedStatement IN clause alternatives?

PreparedStatement В альтернативных предложениях?

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

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

SELECT my_column FROM my_table where search_column IN (?)

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

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

Переведено автоматически
Ответ 1

Анализ различных доступных вариантов, а также плюсы и минусы каждого из них доступны в записи Жанны Боярской о пакетной обработке инструкций 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 метода.

Ответ 2

Решение для 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...
}
}
Ответ 3

На самом деле нет простого способа. Если целью является поддержание высокого коэффициента кэширования инструкций (т. Е. Не создавать инструкцию для каждого параметра 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++;
}
Ответ 4

Вы можете использовать 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
*/

}
java jdbc