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), вы можете сделать следующее:
создайте инструкцию с несколькими (например, 10) параметрами:
... ГДЕ A В (?,?,?,?,?,?,?,?,?,?) ...
Привязать все фактические параметры
setString(1,"foo"); setString(2, "bar");
Свяжите остальное как 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
*/
}