В терминалах, совместимых с Xterm, вы можете показать изображение непосредственно в терминале. [D0]
[!d1]
см. [D0] мой ответ на «Изображение PPM для ASCII-искусства в Python»Здесь доступны различные доступные варианты, а также плюсы и минусы каждого из них здесь .
Предлагаемые варианты:
SELECT my_column FROM my_table WHERE search_column = ?
, выполните его для каждого значения, а UNION - на стороне клиента. Требуется только одно подготовленное заявление. Медленный и болезненный. SELECT my_column FROM my_table WHERE search_column IN (?,?,?)
и выполните его. Требуется один подготовленный оператор в размере IN-списка. Быстро и очевидно. SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...
и выполните его. [Или используйте UNION ALL
вместо этих точек с запятой. --ed] Требуется один подготовленный оператор в размере IN-списка. Глубоко медленно, строго хуже, чем WHERE search_column IN (?,?,?)
, поэтому я не знаю, почему блоггер даже предложил его. 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
, как описано здесь :
Кажется, что нет способа заставить setArray
работать с IN-lists.
Я столкнулся с рядом ограничений, связанных с подготовленным оператором:
Среди предлагаемых решений я бы выбрал тот, который не уменьшает производительность запроса и уменьшает количество запросов. Это будет # 4 (партия нескольких запросов) из ссылки @Don или указание значений NULL для ненужных '?' знаки, предложенные В.Дужевым
Изучив различные решения на разных форумах и не найдя подходящего решения, я чувствую, что я получил нижний взломанный код, который проще всего отслеживать и кодировать:
Пример. Предположим, у вас есть несколько параметров для перейдите в предложение «IN». Просто поместите фиктивную строку в предложение «IN», скажем, «PARAM» обозначить список параметров, которые будут появляться на месте этой фиктивной строки.
select * from TABLE_A where ATTR IN (PARAM);
Вы можете собрать все параметров в одну переменную String в вашем Java-коде. Это можно сделать следующим образом:
String param1 = "X";
String param2 = "Y";
String param1 = param1.append(",").append(param2);
Вы можете добавить все ваши параметры, разделенные запятыми, в одну переменную String, 'param1', в нашем случае.
После сбора всех параметры в одну строку вы можете просто заменить фиктивный текст в вашем запросе, то есть «PARAM» в этом случае, с параметром String, т. е. param1. Вот что вам нужно сделать:
String query = query.replaceFirst("PARAM",param1); where we have the value of query as
query = "select * from TABLE_A where ATTR IN (PARAM)";
Теперь вы можете выполнить свой запрос с помощью метода executeQuery (). Просто убедитесь, что в вашем запросе нет слова «PARAM». Вы можете использовать комбинацию специальных символов и алфавитов вместо слова «PARAM», чтобы убедиться, что в запросе нет такого слова. Надеюсь, вы получили решение.
Примечание. Хотя это не подготовленный запрос, он выполняет работу, которую я хотел, чтобы мой код выполнял.
Просто для полноты: пока набор значений не слишком велик, вы могли бы также просто построить строку с выражением, подобным
... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?
, который вы могли бы затем перейти к prepare (), а затем использовать setXXX () в цикле для установки всех значений. Это выглядит yucky, но многие «большие» коммерческие системы обычно делают такие вещи до тех пор, пока они не достигнут ограничений, специфичных для БД, таких как 32 КБ (я думаю, это так) для операторов в Oracle.
Конечно, вы необходимо убедиться в том, что набор никогда не будет необоснованно большим или будет захват ошибок в том случае, если это будет.
Вот полное решение в Java для создания подготовленного оператора для вас:
/*usage:
Util u = new Util(500); //500 items per bracket.
String sqlBefore = "select * from myTable where (";
List<Integer> values = new ArrayList<Integer>(Arrays.asList(1,2,4,5));
string sqlAfter = ") and foo = 'bar'";
PreparedStatement ps = u.prepareStatements(sqlBefore, values, sqlAfter, connection, "someId");
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Util {
private int numValuesInClause;
public Util(int numValuesInClause) {
super();
this.numValuesInClause = numValuesInClause;
}
public int getNumValuesInClause() {
return numValuesInClause;
}
public void setNumValuesInClause(int numValuesInClause) {
this.numValuesInClause = numValuesInClause;
}
/** Split a given list into a list of lists for the given size of numValuesInClause*/
public List<List<Integer>> splitList(
List<Integer> values) {
List<List<Integer>> newList = new ArrayList<List<Integer>>();
while (values.size() > numValuesInClause) {
List<Integer> sublist = values.subList(0,numValuesInClause);
List<Integer> values2 = values.subList(numValuesInClause, values.size());
values = values2;
newList.add( sublist);
}
newList.add(values);
return newList;
}
/**
* Generates a series of split out in clause statements.
* @param sqlBefore ""select * from dual where ("
* @param values [1,2,3,4,5,6,7,8,9,10]
* @param "sqlAfter ) and id = 5"
* @return "select * from dual where (id in (1,2,3) or id in (4,5,6) or id in (7,8,9) or id in (10)"
*/
public String genInClauseSql(String sqlBefore, List<Integer> values,
String sqlAfter, String identifier)
{
List<List<Integer>> newLists = splitList(values);
String stmt = sqlBefore;
/* now generate the in clause for each list */
int j = 0; /* keep track of list:newLists index */
for (List<Integer> list : newLists) {
stmt = stmt + identifier +" in (";
StringBuilder innerBuilder = new StringBuilder();
for (int i = 0; i < list.size(); i++) {
innerBuilder.append("?,");
}
String inClause = innerBuilder.deleteCharAt(
innerBuilder.length() - 1).toString();
stmt = stmt + inClause;
stmt = stmt + ")";
if (++j < newLists.size()) {
stmt = stmt + " OR ";
}
}
stmt = stmt + sqlAfter;
return stmt;
}
/**
* Method to convert your SQL and a list of ID into a safe prepared
* statements
*
* @throws SQLException
*/
public PreparedStatement prepareStatements(String sqlBefore,
ArrayList<Integer> values, String sqlAfter, Connection c, String identifier)
throws SQLException {
/* First split our potentially big list into lots of lists */
String stmt = genInClauseSql(sqlBefore, values, sqlAfter, identifier);
PreparedStatement ps = c.prepareStatement(stmt);
int i = 1;
for (int val : values)
{
ps.setInt(i++, val);
}
return ps;
}
}
Просто для полноты и потому, что я не видел, чтобы кто-либо еще предлагал это:
Перед тем, как применить какие-либо сложные предложения, рассмотрите вопрос о том, действительно ли SQL-инъекция является проблемой в вашем сценарии.
Во многих случаях значение, предоставляемое IN (...), представляет собой список идентификаторов, которые были сгенерированы таким образом, что вы можете быть уверены, что инъекция невозможна ... (например, результаты предыдущий select some_id from some_table, где some_condition.)
Если это так, вы можете просто связать это значение и не использовать службы или подготовленный оператор для него или использовать их для других параметров этого запроса.
query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";
Он работает для тривиальных случаев, и вы можете расширить его с помощью «автоматической генерации подготовленного заявления», однако он всегда имеет свои пределов.
Подход in () может быть достаточно хорош для некоторых случаев, но не для защиты от ракеты:)
Ракетно-защитное решение состоит в том, чтобы передать произвольное количество параметров в отдельный вызов (например, путем передачи клока параметров), а затем иметь представление (или любой другой способ) для представления их в SQL и использовать в вашем месте критерии.
Вариант грубой силы здесь http://tkyte.blogspot.hu/2006/06/varying-in-lists.html
Однако, если вы можете использовать PL / SQL, этот беспорядок может стать довольно опрятным.
function getCustomers(in_customerIdList clob) return sys_refcursor is
begin
aux_in_list.parse(in_customerIdList);
open res for
select *
from customer c,
in_list v
where c.customer_id=v.token;
return res;
end;
Затем вы можете передать произвольное количество идентификаторов клиентов, разделенных запятыми, в параметре и:
Трюк здесь:
Вид выглядит так:
create or replace view in_list
as
select
trim( substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1)
- instr (txt, ',', 1, level) -1 ) ) as token
from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1
, где aux_in_list.getpayload ссылается на исходную строку ввода.
Возможным подходом было бы передать массивы pl / sql (поддерживаемые только Oracle), однако вы можете ' t использовать их в чистом SQL, поэтому шаг преобразования всегда необходим. Преобразование не может быть выполнено в SQL, поэтому, в конце концов, самым эффективным решением является передача clob со всеми параметрами в строке и преобразование его в представление.
Spring позволяет передавать java.util.Lists в NamedParameterJdbcTemplate , который автоматизирует генерацию (?,?,?, ...,?), в зависимости от количества аргументов.
Для Oracle в этом блоге сообщение обсуждается использование oracle.sql.ARRAY (Connection.createArrayOf не работает с Oracle). Для этого вам нужно изменить инструкцию SQL:
SELECT my_column FROM my_table where search_column IN (select COLUMN_VALUE from table(?))
Функция таблицы oracle table преобразует переданный массив в таблицу как значение, которое можно использовать в инструкции IN
.
Неприятный обход, но, безусловно, возможно использовать вложенный запрос. Создайте временную таблицу MYVALUES со столбцом в ней. Вставьте свой список значений в таблицу MYVALUES. Затем выполните
select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )
Ужасно, но жизнеспособной альтернативой, если ваш список значений очень велик.
Этот метод имеет дополнительное преимущество потенциально лучших планов запросов от оптимизатора (проверьте страница для нескольких значений, таблицы могут быть только один раз вместо одного значения и т. д.) могут сэкономить накладные расходы, если ваша база данных не кэширует подготовленные операторы. Ваши «INSERTS» должны выполняться в пакетном режиме, и таблица MYVALUES может потребоваться изменить, чтобы иметь минимальную блокировку или другие высоконадежные защиты.
Мое обходное решение:
create or replace type split_tbl as table of varchar(32767);
/
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
Теперь вы можете использовать одну переменную для получения некоторых значений в таблице:
select * from table(split('one,two,three'))
one
two
three
select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
value1 AAA
value2 BBB
Итак, подготовленный оператор может быть:
"select * from TABLE where COL in (select * from table(split(?)))"
С уважением,
Хавьер Ибанез
Sormula поддерживает оператор SQL IN, позволяя вам предоставить объект java.util.Collection в качестве параметра. Он создает подготовленное заявление с? для каждого из элементов коллекции. См. Пример 4 (SQL в примере - комментарий, чтобы уточнить, что создано, но не используется Sormula).
вместо использования
SELECT my_column FROM my_table where search_column IN (?)
использовать Sql Statement как
select id, name from users where id in (?, ?, ?)
и
preparedStatement.setString( 1, 'A');
preparedStatement.setString( 2,'B');
preparedStatement.setString( 3, 'C');
или использовать хранимую процедуру, это было бы лучшее решение, так как операторы sql будут скомпилированы и сохранены на сервере DataBase
Вот как я решил это в своем приложении. В идеале вы должны использовать StringBuilder вместо использования + для строк.
String inParenthesis = "(?";
for(int i = 1;i < myList.size();i++) {
inParenthesis += ", ?";
}
inParenthesis += ")";
try(PreparedStatement statement = SQLite.connection.prepareStatement(
String.format("UPDATE table SET value='WINNER' WHERE startTime=? AND name=? AND traderIdx=? AND someValue IN %s", inParenthesis))) {
int x = 1;
statement.setLong(x++, race.startTime);
statement.setString(x++, race.name);
statement.setInt(x++, traderIdx);
for(String str : race.betFair.winners) {
statement.setString(x++, str);
}
int effected = statement.executeUpdate();
}
Использование переменной типа x выше вместо конкретных чисел помогает много, если вы решите изменить запрос позже.
Существуют различные альтернативные подходы, которые мы можем использовать для предложения IN в PreparedStatement.
int i = 1;
for(; i <=ids.length; i++){
ps.setInt(i, ids[i-1]);
}
//set null for remaining ones
for(; i<=PARAM_SIZE;i++){
ps.setNull(i, java.sql.Types.INTEGER);
}
Здесь вы можете узнать подробнее об этих альтернативных подходах здесь .
Я никогда не пробовал, но будет ли .setArray () делать то, что вы ищете?
Обновление: очевидно, нет. Кажется, что setArray работает с java.sql.Array, который исходит из столбца ARRAY, который вы извлекли из предыдущего запроса, или подзапроса с столбцом ARRAY.
PreparedStatement не предоставляет никакого хорошего способа работы с предложением SQL IN. Per http://www.javaranch.com/journal/2005/Journal200510.jsp#a2 «Вы не можете заменить вещи, которые должны стать частью инструкции SQL. Это необходимо, потому что если сам SQL может измениться, драйвер не может предварительно скомпилировать оператор, а также имеет хороший побочный эффект предотвращения атак SQL-инъекций ». В итоге я использовал следующий подход:
String query = "SELECT my_column FROM my_table where search_column IN ($searchColumns)";
query = query.replace("$searchColumns", "'A', 'B', 'C'");
Statement stmt = connection.createStatement();
boolean hasResults = stmt.execute(query);
do {
if (hasResults)
return stmt.getResultSet();
hasResults = stmt.getMoreResults();
} while (hasResults || stmt.getUpdateCount() != -1);
SetArray - лучшее решение, но оно недоступно для многих старых драйверов. Следующий способ обхода может быть использован в java8
String baseQuery ="SELECT my_column FROM my_table where search_column IN (%s)"
String markersString = inputArray.stream().map(e -> "?").collect(joining(","));
String sqlQuery = String.format(baseSQL, markersString);
//Now create Prepared Statement and use loop to Set entries
int index=1;
for (String input : inputArray) {
preparedStatement.setString(index++, input);
}
. Это решение лучше, чем другие уродливые решения для цикла, где строка запроса построена с помощью ручных итераций
Мое обходное решение (JavaScript)
var s1 = " SELECT "
+ "FROM table t "
+ " where t.field in ";
var s3 = '(';
for(var i =0;i<searchTerms.length;i++)
{
if(i+1 == searchTerms.length)
{
s3 = s3+'?)';
}
else
{
s3 = s3+'?, ' ;
}
}
var query = s1+s3;
var pstmt = connection.prepareStatement(query);
for(var i =0;i<searchTerms.length;i++)
{
pstmt.setString(i+1, searchTerms[i]);
}
SearchTerms
- это массив, который содержит ваши входные / ключи / поля и т. д.
попробуйте использовать функцию instr?
select my_column from my_table where instr(?, ','||search_column||',') > 0
, тогда
ps.setString(1, ",A,B,C,");
По общему признанию, это немного грязный взлома, но это уменьшает возможности для SQL-инъекции. Все равно работает в oracle.
Следуя идее Адама. Сделайте свой подготовленный оператор вроде select my_column из my_table, где search_column in (#) Создайте строку x и заполните ее числом «?,?,?» в зависимости от вашего списка значений. Затем просто измените # в запросе на новую строку String x наполнитель
В некоторых ситуациях regexp может помочь. Вот пример, который я проверил в Oracle, и он работает.
select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')
Но с ним существует ряд недостатков: