[java] Alternative di clausola PreparedStatement IN?


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

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?




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.




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




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.




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



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;

    }

}



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



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.




È 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
     */
}



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 .




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.




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



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.




Related