java Alternative di clausola PreparedStatement IN?




13 Answers

Soluzione per 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));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

o

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));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}
java security jdbc prepared-statement in-clause

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?




Una soluzione spiacevole, ma certamente fattibile, è l'uso di una query nidificata. Creare una tabella temporanea MYVALUES con una colonna al suo interno. Inserisci il tuo elenco di valori nella tabella MYVALUES. Quindi esegui

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

Brutta, ma una valida alternativa se la tua lista di valori è molto grande.

Questa tecnica ha il vantaggio aggiuntivo di piani di query potenzialmente migliori dell'ottimizzatore (controllare una pagina per più valori, tabelle può essere una sola volta invece una volta per valore, ecc.) Può risparmiare sull'overhead se il database non memorizza nella cache le istruzioni preparate. I "INSERTI" dovrebbero essere eseguiti in batch e la tabella MYVALUES potrebbe dover essere ottimizzata per avere un blocco minimo o altre protezioni con sovraccarico elevato.




Non l'ho mai provato, ma .setArray () fa quello che stai cercando?

Aggiornamento : Evidentemente no. setArray sembra funzionare solo con un file java.sql.Array proveniente da una colonna ARRAY recuperata da una query precedente o una sottoquery con una colonna ARRAY.




Ecco come l'ho risolto nella mia applicazione. Idealmente, dovresti usare un StringBuilder invece di usare + per le stringhe.

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

Usare una variabile come x sopra invece di numeri concreti aiuta molto se si decide di cambiare la query in un secondo momento.




È possibile utilizzare il metodo setArray come indicato in questo 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();



Sormula supporta l'operatore SQL IN consentendo di fornire un oggetto java.util.Collection come parametro. Crea una dichiarazione preparata con un? per ciascuno degli elementi la collezione. Vedi l' esempio 4 (SQL in example è un commento per chiarire cosa viene creato ma non viene usato da Sormula).




Mi sono imbattuto in una serie di limitazioni relative alla dichiarazione preparata:

  1. Le istruzioni preparate vengono memorizzate nella cache solo all'interno della stessa sessione (Postgres), quindi funzionerà solo con il pool di connessioni
  2. Un sacco di diverse istruzioni preparate come proposto da @BalusC possono causare il riempimento eccessivo della cache e le dichiarazioni precedentemente memorizzate nella cache verranno eliminate
  3. La query deve essere ottimizzata e utilizzare gli indici. Sembra ovvio, tuttavia, ad esempio, l'istruzione ANY (ARRAY ...) proposta da @Boris in una delle risposte principali non può utilizzare indici e la query sarà lenta nonostante la memorizzazione nella cache
  4. L'istruzione preparata memorizza nella cache anche il piano di query e i valori effettivi di tutti i parametri specificati nell'istruzione non sono disponibili.

Tra le soluzioni proposte, sceglierei quella che non riduce le prestazioni della query e riduce il numero di query. Questo sarà il n. 4 (batch di poche query) dal link @Don o la specifica di valori NULL per "non" non necessari? segna come proposto da Vladimir Dyuzhev




Spring consente di passare java.util.Lists a NamedParameterJdbcTemplate , che automatizza la generazione di (?,?,?, ...,?), Come appropriato per il numero di argomenti.

Per Oracle, questo post sul blog discute l'uso di oracle.sql.ARRAY (Connection.createArrayOf non funziona con Oracle). Per questo devi modificare la tua dichiarazione SQL:

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

La funzione della tabella dell'oracolo trasforma l'array passato in un valore di tabella utilizzabile nell'istruzione IN .




Seguendo l'idea di Adam. Fai in modo che la tua istruzione preparata si selezioni my_column da my_table dove search_column in (#) Crea una stringa x e la riempia con un numero di "?,?,?" a seconda dell'elenco di valori Quindi, basta modificare il # nella query per la nuova stringa x popolata




Esistono diversi approcci alternativi che possiamo utilizzare per la clausola IN in PreparedStatement.

  1. Utilizzo di query singole: prestazioni e risorse più lente
  2. Utilizzo di StoredProcedure - Il più veloce ma specifico del database
  3. Creazione di query dinamiche per PreparedStatement - Good Performance ma non ottiene il vantaggio della memorizzazione nella cache e PreparedStatement viene ricompilato ogni volta.
  4. Usa NULL nelle query PreparedStatement: prestazioni ottimali, funziona alla grande quando conosci il limite degli argomenti della clausola IN. Se non ci sono limiti, puoi eseguire query in batch. Lo snippet di codice di esempio è;

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

Puoi controllare maggiori dettagli su questi approcci alternativi here .




Dopo aver esaminato varie soluzioni in diversi forum e non trovando una buona soluzione, sento che il trucco sottostante che ho creato è il più semplice da seguire e codificare:

Esempio: supponiamo di avere più parametri da passare nella clausola 'IN'. Basta inserire una stringa fittizia all'interno della clausola 'IN', diciamo, "PARAM" non denota l'elenco dei parametri che verranno immessi al posto di questa stringa fittizia.

    select * from TABLE_A where ATTR IN (PARAM);

È possibile raccogliere tutti i parametri in una singola variabile String nel codice Java. Questo può essere fatto come segue:

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

È possibile aggiungere tutti i parametri separati da virgole in una variabile String singola, "param1", nel nostro caso.

Dopo aver raccolto tutti i parametri in una singola stringa, puoi semplicemente sostituire il testo fittizio nella tua query, ad es. "PARAM" in questo caso, con il parametro String, cioè param1. Ecco cosa devi fare:

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

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

Ora puoi eseguire la tua query usando il metodo executeQuery (). Assicurati di non avere la parola "PARAM" nella tua query ovunque. Puoi usare una combinazione di caratteri speciali e alfabeti invece della parola "PARAM" per assicurarti che non ci sia alcuna possibilità che una parola di questo tipo arrivi nella query. Spero tu abbia la soluzione.

Nota: anche se questa non è una query preparata, fa il lavoro che volevo che il mio codice facesse.




PreparedStatement non fornisce alcun modo valido per gestire la clausola SQL IN. Per here "Non è possibile sostituire le cose che sono destinate a diventare parte dell'istruzione SQL. Ciò è necessario perché se lo stesso SQL può cambiare, il il driver non può precompilare la dichiarazione e ha anche il bell'effetto collaterale di prevenire attacchi di SQL injection. " Ho finito per usare il seguente approccio:

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



È possibile utilizzare Collections.nCopiesper generare una raccolta di segnaposto e unirsi a loro utilizzando 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
     */
}



Related