java - parameter - prepared statement using in



PreparedStatement IN cláusula alternativas? (19)

Limitações do operador in () são a raiz de todo mal.

Ele funciona para casos triviais, e você pode estendê-lo com "geração automática da declaração preparada", no entanto, está sempre tendo seus limites.

  • se você estiver criando uma instrução com um número variável de parâmetros, isso fará com que uma sobrecarga de análise de sql em cada chamada
  • em muitas plataformas, o número de parâmetros do operador in () é limitado
  • em todas as plataformas, o tamanho total do texto SQL é limitado, impossibilitando o envio de 2000 espaços reservados para o params
  • O envio de variáveis ​​de ligação de 1000-10k não é possível, pois o driver JDBC está tendo suas limitações

A abordagem in () pode ser boa o suficiente para alguns casos, mas não à prova de foguetes :)

A solução à prova de foguetes é para passar o número arbitrário de parâmetros em uma chamada separada (passando um par de parâmetros, por exemplo), e então ter uma visão (ou qualquer outra forma) para representá-los em SQL e usar em seu lugar. critério.

Uma variante de força bruta está aqui http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

No entanto, se você pode usar o PL / SQL, essa bagunça pode se tornar bem legal.

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;

Então você pode passar um número arbitrário de ids de clientes separados por vírgula no parâmetro e:

  • não terá nenhum atraso de análise, pois o SQL for select é estável
  • sem complexidade de funções pipeline - é apenas uma consulta
  • o SQL está usando uma junção simples, em vez de um operador IN, que é bastante rápido
  • afinal de contas, é uma boa regra geral não acertar o banco de dados com nenhuma seleção simples ou DML, uma vez que é a Oracle, que oferece anos mais do que MySQL ou mecanismos de banco de dados simples semelhantes. A PL / SQL permite que você esconda o modelo de armazenamento de seu modelo de domínio de aplicativo de maneira eficaz.

O truque aqui é:

  • precisamos de uma chamada que aceite a cadeia longa e armazene em algum lugar onde a sessão do banco de dados possa acessá-la (por exemplo, variável de pacote simples ou dbms_session.set_context)
  • então precisamos de uma visão que possa analisar isso para linhas
  • e então você tem uma visão que contém os ids que você está consultando, então tudo que você precisa é de uma simples união à tabela consultada.

A vista parece:

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

onde aux_in_list.getpayload se refere à string de entrada original.

Uma abordagem possível seria passar matrizes pl / sql (suportadas apenas pelo Oracle), no entanto, você não pode usá-las em SQL puro, portanto, uma etapa de conversão é sempre necessária. A conversão não pode ser feita em SQL, então, afinal, passar um clob com todos os parâmetros em string e convertê-lo em uma visão é a solução mais eficiente.

https://src-bin.com

Quais são as melhores soluções alternativas para usar uma cláusula SQL IN com instâncias de java.sql.PreparedStatement , que não é suportada para vários valores devido a problemas de segurança de ataque de injeção SQL: Um ? placeholder representa um valor, em vez de uma lista de valores.

Considere a seguinte instrução SQL:

SELECT my_column FROM my_table where search_column IN (?)

Usando preparedStatement.setString( 1, "'A', 'B', 'C'" ); é essencialmente uma tentativa de não trabalhar em uma solução alternativa das razões para usar ? em primeiro lugar.

Quais soluções alternativas estão disponíveis?


Answer #1

Apenas por completude: desde que o conjunto de valores não seja muito grande, você também pode simplesmente construir uma declaração como

... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?

que você poderia passar para prepare () e, em seguida, usar setXXX () em um loop para definir todos os valores. Isso parece complicado, mas muitos sistemas comerciais "grandes" rotineiramente fazem esse tipo de coisa até atingir os limites específicos do banco de dados, como 32 KB (acho que é) para instruções no Oracle.

É claro que você precisa garantir que o conjunto nunca seja excessivamente grande ou que cause erros no caso em que isso ocorre.


Answer #2

Aqui está uma solução completa em Java para criar a declaração preparada para você:

/*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;

    }

}

Answer #3

Depois de examinar várias soluções em diferentes fóruns e não encontrar uma boa solução, sinto que o hack abaixo que criei é o mais fácil de seguir e codificar:

Exemplo: suponha que você tenha vários parâmetros para passar na cláusula 'IN'. Basta colocar uma String fictícia dentro da cláusula 'IN', por exemplo, 'PARAM' denote a lista de parâmetros que serão colocados no lugar dessa String fictícia.

    select * from TABLE_A where ATTR IN (PARAM);

Você pode coletar todos os parâmetros em uma única variável String em seu código Java. Isso pode ser feito da seguinte forma:

    String param1 = "X";
    String param2 = "Y";
    String param1 = param1.append(",").append(param2);

Você pode anexar todos os seus parâmetros separados por vírgulas em uma única variável String, 'param1', no nosso caso.

Depois de coletar todos os parâmetros em uma única String você pode simplesmente substituir o texto fictício em sua consulta, ou seja, "PARAM" neste caso, com o parâmetro String, ou seja, param1. Aqui está o que você precisa fazer:

    String query = query.replaceFirst("PARAM",param1); where we have the value of query as 

    query = "select * from TABLE_A where ATTR IN (PARAM)";

Agora você pode executar sua consulta usando o método executeQuery (). Apenas certifique-se de que você não tenha a palavra "PARAM" em sua consulta em nenhum lugar. Você pode usar uma combinação de caracteres especiais e alfabetos em vez da palavra "PARAM" para garantir que não haja possibilidade de uma palavra como essa aparecer na consulta. Espero que você tenha a solução.

Nota: Embora essa não seja uma consulta preparada, ela faz o trabalho que eu queria que meu código fizesse.


Answer #4

Eu nunca tentei, mas seria .setArray () fazer o que você está procurando?

Atualização : Evidentemente não. setArray parece funcionar apenas com um java.sql.Array que vem de uma coluna ARRAY que você recuperou de uma consulta anterior, ou uma subconsulta com uma coluna ARRAY.


Answer #5

Eu suponho que você poderia (usando manipulação básica de string) gerar a string de consulta no PreparedStatement para ter um número de ? está combinando o número de itens na sua lista.

É claro que, se você está fazendo isso, está apenas a um passo de gerar um OR encadeado gigante em sua consulta, mas sem ter o número correto de ? na string de consulta, não vejo como mais você pode contornar isso.


Answer #6

Gere a string de consulta no PreparedStatement para ter um número de? Correspondente ao número de itens em sua lista. Aqui está um exemplo:

public void myQuery(List<String> items, int other) {
  ...
  String q4in = generateQsForIn(items.size());
  String sql = "select * from stuff where foo in ( " + q4in + " ) and bar = ?";
  PreparedStatement ps = connection.prepareStatement(sql);
  int i = 1;
  for (String item : items) {
    ps.setString(i++, item);
  }
  ps.setInt(i++, other);
  ResultSet rs = ps.executeQuery();
  ...
}

private String generateQsForIn(int numQs) {
    String items = "";
    for (int i = 0; i < numQs; i++) {
        if (i != 0) items += ", ";
        items += "?";
    }
    return items;
}

Answer #7

Minha solução alternativa é:

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;
/

Agora você pode usar uma variável para obter alguns valores em uma tabela:

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

Então, a declaração preparada poderia ser:

  "select * from TABLE where COL in (select * from table(split(?)))"

Saudações,

Javier Ibanez


Answer #8

Para algumas situações, o regexp pode ajudar. Aqui está um exemplo que eu verifiquei no Oracle e funciona.

select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')

Mas há várias desvantagens:

  1. Qualquer coluna aplicada deve ser convertida para varchar / char, pelo menos implicitamente.
  2. Precisa ter cuidado com caracteres especiais.
  3. Ele pode diminuir o desempenho - no meu caso, a versão IN usa varredura de índice e intervalo, e a versão REGEXP faz varredura completa.

Answer #9

Seguindo a ideia de Adam. Faça sua declaração preparada selecionar my_column em my_table, em que search_column em (#) crie uma String x e ​​preencha-a com um número de "?,?,?" dependendo da sua lista de valores Em seguida, basta alterar o # na consulta para o seu novo String x um preencher


Answer #10

Spring permite a passagem de java.util.Lists para NamedParameterJdbcTemplate , que automatiza a geração de (?,?,?, ...,?), Conforme apropriado para o número de argumentos.

Para Oracle, este blog discute o uso de oracle.sql.ARRAY (Connection.createArrayOf não funciona com o Oracle). Para isso você tem que modificar sua instrução SQL:

SELECT my_column FROM my_table where search_column IN (select COLUMN_VALUE from table(?))

A função de tabela oracle transforma a matriz passada em uma tabela como o valor utilizável na instrução IN .


Answer #11

Uma análise das várias opções disponíveis e os prós e contras de cada um estão disponíveis here .

As opções sugeridas são:

  • Prepare SELECT my_column FROM my_table WHERE search_column = ? , executá-lo para cada valor e UNION os resultados do lado do cliente. Requer apenas uma declaração preparada. Lenta e dolorosa.
  • Prepare SELECT my_column FROM my_table WHERE search_column IN (?,?,?) E execute-o. Requer uma declaração preparada por tamanho de lista de entrada. Rápido e óbvio.
  • Prepare SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... e executá-lo. [Ou use UNION ALL no lugar desses pontos-e-vírgulas. --ed] Requer uma declaração preparada por tamanho de lista IN. Estupidamente lento, estritamente pior do que WHERE search_column IN (?,?,?) , Então eu não sei porque o blogueiro sugeriu isso.
  • Use um procedimento armazenado para construir o conjunto de resultados.
  • Preparar N diferentes consultas de tamanho de lista de entrada; digamos, com 2, 10 e 50 valores. Para procurar uma lista de entrada com 6 valores diferentes, preencha a consulta de tamanho 10 para que seja semelhante a SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6) . Qualquer servidor decente otimizará os valores duplicados antes de executar a consulta.

Nenhuma dessas opções é ótima, no entanto.

Perguntas duplicadas foram respondidas nesses lugares com alternativas igualmente sãs, mas nenhuma delas é super excelente:

A resposta correta, se você estiver usando o JDBC4 e um servidor que suporte x = ANY(y) , é usar PreparedStatement.setArray conforme descrito aqui:

Não parece haver nenhuma maneira de fazer o setArray funcionar com listas IN, no entanto.

Algumas vezes, as instruções SQL são carregadas no tempo de execução (por exemplo, de um arquivo de propriedades), mas requerem um número variável de parâmetros. Nesses casos, primeiro defina a consulta:

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

Em seguida, carregue a consulta. Em seguida, determine o número de parâmetros antes de executá-lo. Depois que a contagem de parâmetros for conhecida, execute:

sql = any( sql, count );

Por exemplo:

/**
 * 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(
            new String(new char[params]).replace("\0", "?,")
    );

    // Remove trailing comma.
    sb.setLength(Math.max(sb.length() - 1, 0));

    // 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;
}

Para determinados bancos de dados em que a passagem de um array através da especificação JDBC 4 não é suportada, este método pode facilitar a transformação do slow = ? na condição de cláusula IN (?) mais rápida, que pode então ser expandida chamando o método any .


Answer #12

Você poderia usar o método setArray como mencionado neste javadoc :

PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"E1", "E2","E3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();

Answer #13

ao invés de usar

SELECT my_column FROM my_table where search_column IN (?)

use a instrução Sql como

select id, name from users where id in (?, ?, ?)

e

preparedStatement.setString( 1, 'A');
preparedStatement.setString( 2,'B');
preparedStatement.setString( 3, 'C');

ou usar um procedimento armazenado, esta seria a melhor solução, já que as instruções sql serão compiladas e armazenadas no servidor DataBase


Answer #14

Sormula suporta o operador SQL IN, permitindo que você forneça um objeto java.util.Collection como um parâmetro. Cria uma declaração preparada com um? para cada um dos elementos da coleção. Veja o Exemplo 4 (SQL no exemplo é um comentário para esclarecer o que é criado, mas não é usado pelo Sormula).


Answer #15

Apenas por completo e porque eu não vi mais ninguém sugerir isso:

Antes de implementar qualquer uma das sugestões complicadas acima, considere se a injeção SQL é de fato um problema em seu cenário.

Em muitos casos, o valor fornecido para IN (...) é uma lista de IDs que foram gerados de uma maneira que você pode ter certeza de que nenhuma injeção é possível ... (por exemplo, os resultados de uma seleção anterior, some_id de alguma_tabela onde alguma_condição.)

Se for esse o caso, você pode apenas concatenar esse valor e não usar os serviços ou a instrução preparada para ele ou usá-los para outros parâmetros dessa consulta.

query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";

Answer #16

Eu acabei de descobrir uma opção específica do PostgreSQL para isso. É um pouco um hack, e vem com seus próprios prós e contras e limitações, mas parece funcionar e não se limita a uma linguagem de desenvolvimento específica, plataforma ou driver PG.

O truque, é claro, é encontrar uma maneira de passar uma coleção de valores de comprimento arbitrário como um único parâmetro, e fazer com que o banco de dados reconheça isso como múltiplos valores. A solução que tenho trabalhado é construir uma string delimitada a partir dos valores na coleção, passar essa string como um único parâmetro e usar string_to_array () com a requisição de fundição para o PostgreSQL fazer uso apropriado dela.

Então, se você quiser procurar por "foo", "blah" e "abc", você pode concatená-los juntos em uma única string como: 'foo, blah, abc'. Aqui está o SQL direto:

select column from table
where search_column = any (string_to_array('foo,blah,abc', ',')::text[]);

Você obviamente mudaria o elenco explícito para o que quer que sua matriz de valores resultante fosse - int, text, uuid, etc. E porque a função está tomando um único valor de string (ou dois, suponho, se você quiser customizar o delimitador também), você pode passá-lo como um parâmetro em uma declaração preparada:

select column from table
where search_column = any (string_to_array($1, ',')::text[]);

Isso é flexível o suficiente para suportar coisas como comparações LIKE:

select column from table
where search_column like any (string_to_array('foo%,blah%,abc%', ',')::text[]);

Novamente, não há dúvida de que é um hack, mas funciona e permite que você ainda use instruções preparadas pré-compiladas que tenham * parâmetros distintos, com os benefícios de segurança e (talvez) de desempenho associados. É aconselhável e realmente performant? Naturalmente, isso depende, já que você tem a análise de string e, possivelmente, a transmissão antes de sua consulta ser executada. Se você está esperando enviar três, cinco, algumas dúzias de valores, com certeza, provavelmente está tudo bem. Alguns milhares? Sim, talvez não tanto. YMMV, limitações e exclusões se aplicam, sem garantia expressa ou implícita.

Mas isso funciona.


Answer #17

PreparedStatement não fornece uma boa maneira de lidar com a cláusula SQL IN. Por here "Você não pode substituir as coisas que se destinam a tornar-se parte da instrução SQL. Isso é necessário porque se o próprio SQL pode mudar, o O driver não pode pré-compilar a instrução, além de ter o bom efeito colateral de impedir ataques de injeção SQL. " Acabei usando a seguinte abordagem:

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);

Answer #18

Você pode usar Collections.nCopiespara gerar uma coleção de espaços reservados e se juntar a eles usando 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
     */
}




in-clause