java - Alternative di clausola PreparedStatement IN?




14 Answers

Un'analisi delle varie opzioni disponibili e i pro e i contro di ciascuno è disponibile here .

Le opzioni suggerite sono:

  • Prepara SELECT my_column FROM my_table WHERE search_column = ? , eseguilo per ogni valore e UNION i risultati lato client. Richiede solo una dichiarazione preparata. Lento e doloroso.
  • Prepara SELECT my_column FROM my_table WHERE search_column IN (?,?,?) . Richiede una dichiarazione preparata per dimensione della lista IN. Veloce e ovvio.
  • Prepara 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 = ? ; ... ed eseguirlo [Oppure usa UNION ALL al posto di quel punto e virgola. --ed] Richiede una istruzione preparata per dimensione-di-elenco-IN. Stupidamente lento, strettamente peggiore di WHERE search_column IN (?,?,?) , Quindi non so perché il blogger lo abbia suggerito.
  • Utilizzare una procedura memorizzata per costruire il set di risultati.
  • Preparare N diverse query di dimensioni dell'elenco; diciamo con 2, 10 e 50 valori. Per cercare un IN-elenco con 6 valori diversi, inserisci la query size-10 in modo che assomigli a SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6) . Qualsiasi server decente ottimizzerà i valori duplicati prima di eseguire la query.

Nessuna di queste opzioni è fantastica, però.

In questi luoghi sono state date risposte a domande duplicate con alternative ugualmente valide, ma nessuna di queste è super fantastica:

La risposta corretta, se si utilizza JDBC4 e un server che supporta x = ANY(y) , deve utilizzare PreparedStatement.setArray come descritto qui:

Non sembra che ci sia un modo per far funzionare setArray con gli elenchi IN.

Quali sono le migliori soluzioni alternative per l'utilizzo di una clausola SQL IN con istanze di java.sql.PreparedStatement , che non è supportata per più valori a causa di problemi di sicurezza di attacco SQL injection: Uno ? placeholder rappresenta un valore, piuttosto che un elenco di valori.

Si consideri la seguente dichiarazione SQL:

SELECT my_column FROM my_table where search_column IN (?)

Utilizzando preparedStatement.setString( 1, "'A', 'B', 'C'" ); è essenzialmente un tentativo non funzionante a una soluzione alternativa dei motivi per l'utilizzo ? innanzitutto.

Quali soluzioni alternative sono disponibili?




Nessun modo semplice AFAIK. Se l'obiettivo è di mantenere alto il rapporto cache delle istruzioni (cioè di non creare una dichiarazione per ogni conteggio dei parametri), puoi fare quanto segue:

  1. creare una dichiarazione con alcuni parametri (es. 10):

    ... DOVE UN IN (?,?,?,?,?,?,?,?,?,?) ...

  2. Lega tutti i parametri actuall

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

  3. Lega il resto come NULL

    setNull (3, Types.VARCHAR) ... setNull (10, Types.VARCHAR)

NULL non corrisponde mai a nulla, quindi viene ottimizzato dal builder del piano SQL.

La logica è facile da automatizzare quando si passa una lista in una funzione DAO:

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

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



Limitazioni dell'operatore in () è la radice di tutto il male.

Funziona per casi banali, e puoi estenderlo con "generazione automatica dell'istruzione preparata", ma ha sempre i suoi limiti.

  • se si sta creando un'istruzione con un numero variabile di parametri, ciò comporterà un sovraccarico di sql per ogni chiamata
  • su molte piattaforme, il numero di parametri dell'operatore in () è limitato
  • su tutte le piattaforme, la dimensione totale del testo SQL è limitata, rendendo impossibile l'invio di 2000 segnaposti per i parametri in
  • non è possibile inviare le variabili di binding di 1000-10k, poiché il driver JDBC sta avendo i suoi limiti

L'approccio in () può essere abbastanza buono per alcuni casi, ma non a prova di razzo :)

La soluzione a prova di razzo è quella di passare il numero arbitrario di parametri in una chiamata separata (passando un clob di parametri, ad esempio), e quindi avere una vista (o qualsiasi altro modo) per rappresentarli in SQL e utilizzare nel tuo dove criteri.

Una variante a forza bruta è qui http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

Tuttavia, se si può usare PL / SQL, questo pasticcio può diventare abbastanza pulito.

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;

Quindi è possibile passare un numero arbitrario di ID cliente separati da virgole nel parametro e:

  • non avrà alcun ritardo di analisi, poiché SQL per select è stabile
  • nessuna complessità delle funzioni pipeline - è solo una query
  • l'SQL sta usando un join semplice, invece di un operatore IN, che è abbastanza veloce
  • dopo tutto, è una buona regola per non colpire il database con una semplice selezione o DML, dal momento che è Oracle, che offre anni luce di più di MySQL o simili motori di database semplici. PL / SQL consente di nascondere il modello di archiviazione dal modello di dominio dell'applicazione in modo efficace.

Il trucco qui è:

  • abbiamo bisogno di una chiamata che accetta la stringa lunga e memorizza da qualche parte dove la sessione db può accedervi (es. semplice variabile del pacchetto o dbms_session.set_context)
  • quindi abbiamo bisogno di una vista che può analizzare questo a righe
  • e poi hai una vista che contiene gli id ​​che stai interrogando, quindi tutto ciò di cui hai bisogno è un semplice join alla tabella interrogata.

La vista assomiglia a:

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

dove aux_in_list.getpayload si riferisce alla stringa di input originale.

Un possibile approccio sarebbe quello di passare agli array pl / sql (supportati solo da Oracle), tuttavia non è possibile utilizzarli in puro SQL, quindi è sempre necessario un passaggio di conversione. La conversione non può essere eseguita in SQL, quindi dopo tutto, passare un clob con tutti i parametri nella stringa e convertirlo con una vista è la soluzione più efficiente.




La mia soluzione è:

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

Ora puoi usare una variabile per ottenere alcuni valori in una tabella:

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

Quindi, la dichiarazione preparata potrebbe essere:

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

Saluti,

Javier Ibanez




Suppongo che potresti (usando la manipolazione di base delle stringhe) generare la stringa di query in PreparedStatement per avere un numero di ? Corrisponde al numero di elementi nella tua lista.

Ovviamente se lo stai facendo sei solo ad un passo dal generare un OR gigante incatenato nella tua query, ma senza avere il numero giusto di ? nella stringa di query, non vedo in quale altro modo si possa aggirare questo problema.




provare a usare la funzione instr?

select my_column from my_table where  instr(?, ','||search_column||',') > 0

poi

ps.setString(1, ",A,B,C,"); 

Ammetto che si tratta di un trucco un po 'sporco, ma riduce le opportunità di iniezione sql. Funziona comunque in oracolo.




invece di usare

SELECT my_column FROM my_table where search_column IN (?)

usa la dichiarazione Sql come

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

e

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

oppure utilizzare una stored procedure questa sarebbe la soluzione migliore, poiché le istruzioni sql verranno compilate e archiviate nel server DataBase




Ecco una soluzione completa in Java per creare la dichiarazione preparata per te:

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

    }

}



Solo per completezza: finché l'insieme di valori non è troppo grande, si potrebbe anche semplicemente costruire una stringa come

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

che potresti poi passare a prepare (), e quindi usare setXXX () in un ciclo per impostare tutti i valori. Questo sembra schifo, ma molti "grandi" sistemi commerciali fanno regolarmente questo genere di cose finché non raggiungono i limiti specifici del DB, come 32 KB (penso che lo sia) per le dichiarazioni in Oracle.

Ovviamente è necessario assicurarsi che il set non sia mai eccessivamente grande, o fare il trapping degli errori nel caso in cui lo sia.




Generare la stringa di query in PreparedStatement per avere un numero di? Corrispondente al numero di elementi nel tuo elenco. Ecco un esempio:

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



Per alcune situazioni regexp potrebbe aiutare. Ecco un esempio che ho verificato su Oracle e funziona.

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

Ma c'è un numero di inconvenienti con esso:

  1. Qualsiasi colonna applicata dovrebbe essere convertita in varchar / char, almeno implicitamente.
  2. Devi stare attento con i personaggi speciali.
  3. Può rallentare le prestazioni, nel mio caso la versione IN utilizza la scansione di indice e intervallo e la versione REGEXP esegue la scansione completa.



Solo per completezza e perché non ho visto nessun altro suggerirlo:

Prima di implementare uno dei suggerimenti complicati di cui sopra, considerare se l'iniezione SQL è effettivamente un problema nel proprio scenario.

In molti casi il valore fornito a IN (...) è un elenco di ID che sono stati generati in modo da essere certi che non è possibile l'iniezione ... (ad es. I risultati di una selezione precedente some_id da some_table dove some_condition.)

In tal caso, è possibile concatenare questo valore e non utilizzare i servizi o l'istruzione preparata per questo oppure utilizzarli per altri parametri di questa query.

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



SetArray è la soluzione migliore ma non è disponibile per molti driver meno recenti. La seguente soluzione alternativa può essere utilizzata in 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);
}

Questa soluzione è migliore di altre brutte soluzioni a ciclo continuo in cui la stringa di query è costruita con iterazioni manuali




La mia soluzione alternativa (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 è la matrice che contiene i tuoi input / chiavi / campi ecc




Related