webentwicklung-frage-antwort-db.com.de

PreparedStatement mit Liste der Parameter in einer IN-Klausel

So legen Sie den Wert für die in-Klausel in einem preparStatement in JDBC fest, während Sie eine Abfrage ausführen.

Beispiel:

connection.prepareStatement("Select * from test where field in (?)");

Wenn diese In-Klausel mehrere Werte enthalten kann, wie kann ich das tun? Manchmal kenne ich die Liste der Parameter im Voraus oder manchmal nicht. Wie gehe ich mit diesem Fall um?

70
Harish

Was ich mache, ist ein "?" für jeden möglichen Wert. 

Zum Beispiel:

List possibleValues = ... 
StringBuilder builder = new StringBuilder();

for( int i = 0 ; i < possibleValue.size(); i++ ) {
    builder.append("?,");
}

String stmt = "select * from test where field in (" 
               + builder.deleteCharAt( builder.length() -1 ).toString() + ")";
PreparedStatement pstmt = ... 

Und dann glücklich die Params gesetzt

int index = 1;
for( Object o : possibleValue ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}
76
OscarRyz

Sie können die setArray-Methode verwenden, wie im folgenden Javadoc erwähnt:

http://docs.Oracle.com/javase/6/docs/api/Java/sql/PreparedStatement.html#setArray(int, Java.sql.Array)

Code:

PreparedStatement statement = connection.prepareStatement("Select * from test where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"A1", "B2","C3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();
43
madx

Sie können diesen Link überprüfen:

http://www.javaranch.com/journal/200510/Journal200510.jsp#a2

Es erläutert die Vor- und Nachteile verschiedener Methoden zum Erstellen von PreparedStatement mit in-Klausel.

BEARBEITEN:

Ein naheliegender Ansatz ist die dynamische Generierung des '?' Teil zur Laufzeit, aber ich möchte nicht einfach nur diesen Ansatz vorschlagen, da er je nach Art der Verwendung ineffizient sein kann (da die Variable PreparedStatement bei jeder Verwendung neu kompiliert werden muss).

14
ryanprayogo

Sie können ? in Ihrer Abfrage nicht durch eine beliebige Anzahl von Werten ersetzen. Jeder ? ist nur für einen einzelnen Wert ein Platzhalter. Um eine beliebige Anzahl von Werten zu unterstützen, müssen Sie dynamisch eine Zeichenfolge erstellen, die ?, ?, ?, ... , ? enthält. Die Anzahl der Fragezeichen entspricht der Anzahl der gewünschten Werte in Ihrer in-Klausel.

7
Asaph

Sie brauchen jdbc4, dann können Sie setArray verwenden!

In meinem Fall hat es nicht funktioniert, da der UUID-Datentyp in Postgres immer noch seine Schwachstellen zu haben scheint, aber für die üblichen Typen funktioniert es.

ps.setArray(1, connection.createArrayOf("$VALUETYPE",myValuesAsArray));

Ersetzen Sie natürlich $ VALUETYPE und myValuesAsArray durch die korrekten Werte.

Bemerkung folgt Marks Kommentar:

Ihre Datenbank und der Treiber müssen dies unterstützen! Ich habe Postgres 9.4 ausprobiert, aber ich denke, das wurde früher eingeführt. Sie benötigen einen jdbc 4-Treiber, andernfalls ist setArray nicht verfügbar. Ich habe den postgresql 9.4-1201-jdbc41-Treiber verwendet, der mit Spring Boot ausgeliefert wird

3

Sie möchten PreparedStatment nicht für dynamische Abfragen mit der IN-Klausel verwenden. Zumindest sind Sie sicher, dass Sie immer unter 5 Variablen oder einem kleinen Wert wie diesem sind, aber selbst so finde ich es eine schlechte Idee (nicht schrecklich, aber schlecht). Da die Anzahl der Elemente groß ist, wird es schlimmer (und schrecklich). 

Stellen Sie sich hundert oder tausend Möglichkeiten in Ihrer IN-Klausel vor:

  1. Es ist kontraproduktiv, Sie haben an Leistung und Speicherplatz verloren, weil Sie jedes Mal eine neue Anforderung zwischenspeichern, und PreparedStatement ist nicht nur für die SQL-Injection gedacht, sondern für die Leistung. In diesem Fall ist Statement besser.

  2. Ihr Pool hat ein Limit von PreparedStatment (-1 defaut, aber Sie müssen es einschränken), und Sie werden dieses Limit erreichen! Wenn Sie kein Limit oder ein sehr großes Limit haben, besteht ein gewisses Risiko für Speicherverluste und im Extremfall OutofMemory-Fehler. Wenn es sich also um ein kleines persönliches Projekt handelt, das von 3 Benutzern verwendet wird, ist das nicht dramatisch, aber Sie möchten nicht, dass Sie in einer großen Firma arbeiten und Ihre App von Tausenden von Menschen und Millionen Anfragen verwendet wird.

Einige Lektüre . IBM: Überlegungen zur Speicherauslastung bei Verwendung des Caching für vorbereitete Anweisungen

2
amdev
public static ResultSet getResult(Connection connection, List values) {
    try {
        String queryString = "Select * from table_name where column_name in";

        StringBuilder parameterBuilder = new StringBuilder();
        parameterBuilder.append(" (");
        for (int i = 0; i < values.size(); i++) {
            parameterBuilder.append("?");
            if (values.size() > i + 1) {
                parameterBuilder.append(",");
            }
        }
        parameterBuilder.append(")");

        PreparedStatement statement = connection.prepareStatement(queryString + parameterBuilder);
        for (int i = 1; i < values.size() + 1; i++) {
            statement.setInt(i, (int) values.get(i - 1));
        }

        return statement.executeQuery();
    } catch (Exception d) {
        return null;
    }
}
2

Sie können den Select-String (den "IN (?)" - Teil) durch eine einfache for-Schleife dynamisch erstellen, sobald Sie wissen, wie viele Werte Sie in die IN-Klausel einfügen müssen. Sie können dann die PreparedStatement-Instanz instanziieren.

2
rfk

Derzeit erlaubt MySQL nicht, mehrere Werte in einem Methodenaufruf festzulegen. Sie müssen es also unter Ihrer Kontrolle haben. Normalerweise erstelle ich eine vorbereitete Anweisung für eine vordefinierte Anzahl von Parametern und füge so viele Stapel hinzu, wie ich brauche.

    int paramSizeInClause = 10; // required to be greater than 0!
    String color = "FF0000"; // red
    String name = "Nathan"; 
    Date now = new Date();
    String[] ids = "15,21,45,48,77,145,158,321,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,358,1284,1587".split(",");

    // Build sql query 
    StringBuilder sql = new StringBuilder();
    sql.append("UPDATE book SET color=? update_by=?, update_date=? WHERE book_id in (");
    // number of max params in IN clause can be modified 
    // to get most efficient combination of number of batches
    // and number of parameters in each batch
    for (int n = 0; n < paramSizeInClause; n++) {
        sql.append("?,");
    }
    if (sql.length() > 0) {
        sql.deleteCharAt(sql.lastIndexOf(","));
    }
    sql.append(")");

    PreparedStatement pstm = null;
    try {
        pstm = connection.prepareStatement(sql.toString());
        int totalIdsToProcess = ids.length;
        int batchLoops = totalIdsToProcess / paramSizeInClause + (totalIdsToProcess % paramSizeInClause > 0 ? 1 : 0);
        for (int l = 0; l < batchLoops; l++) {
            int i = 1;
            pstm.setString(i++, color);
            pstm.setString(i++, name);
            pstm.setTimestamp(i++, new Timestamp(now.getTime()));
            for (int count = 0; count < paramSizeInClause; count++) {
                int param = (l * paramSizeInClause + count);
                if (param < totalIdsToProcess) {
                    pstm.setString(i++, ids[param]);
                } else {
                    pstm.setNull(i++, Types.VARCHAR);
                }
            }
            pstm.addBatch();
        }
    } catch (SQLException e) {
    } finally {
        //close statement(s)
    }

Wenn Sie nicht NULL setzen möchten, wenn keine weiteren Parameter vorhanden sind, können Sie den Code ändern, um zwei Abfragen und zwei vorbereitete Anweisungen zu erstellen. Die erste ist die gleiche, aber die zweite Anweisung für den Rest (Modulus) . In diesem speziellen Beispiel wäre dies eine Abfrage für 10 Parameter und eine für 8 Parameter. Sie müssen 3 Stapel für die erste Abfrage (zuerst 30 Parameter) und einen Stapel für die zweite Abfrage (8 Parameter) hinzufügen.

1
A Kunin
public class Test1 {
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        System.out.println("helow");
String where="where task in ";
        where+="(";
    //  where+="'task1'";
        int num[]={1,2,3,4};
        for (int i=0;i<num.length+1;i++) {
            if(i==1){
                where +="'"+i+"'";
            }
            if(i>1 && i<num.length)
                where+=", '"+i+"'";
            if(i==num.length){
                System.out.println("This is last number"+i);
            where+=", '"+i+"')";
            }
        }
        System.out.println(where);  
    }
}
1
user3870246

Sie können verwenden:

for( int i = 0 ; i < listField.size(); i++ ) {
    i < listField.size() - 1 ? request.append("?,") : request.append("?");
}

Dann :

int i = 1;
for (String field : listField) {
    statement.setString(i++, field);
}

Beispiel:

List<String> listField = new ArrayList<String>();
listField.add("test1");
listField.add("test2");
listField.add("test3");

StringBuilder request = new StringBuilder("SELECT * FROM TABLE WHERE FIELD IN (");

for( int i = 0 ; i < listField.size(); i++ ) {
    request = i < (listField.size() - 1) ? request.append("?,") : request.append("?");
}


DNAPreparedStatement statement = DNAPreparedStatement.newInstance(connection, request.toString);

int i = 1;
for (String field : listField) {
    statement.setString(i++, field);
}

ResultSet rs = statement.executeQuery();
1
Simon Barbier

versuchen Sie es mit diesem Code 

 String ids[] = {"182","160","183"};
            StringBuilder builder = new StringBuilder();

            for( int i = 0 ; i < ids.length; i++ ) {
                builder.append("?,");
            }

            String sql = "delete from emp where id in ("+builder.deleteCharAt( builder.length() -1 ).toString()+")";

            PreparedStatement pstmt = connection.prepareStatement(sql);

            for (int i = 1; i <= ids.length; i++) {
                pstmt.setInt(i, Integer.parseInt(ids[i-1]));
            }
            int count = pstmt.executeUpdate();
0
Narendra

Viele DBs verfügen über das Konzept einer temporären Tabelle. Selbst wenn Sie keine temporäre Tabelle haben, können Sie immer eine mit einem eindeutigen Namen generieren und sie löschen, wenn Sie fertig sind. Obwohl der Aufwand für das Erstellen und Löschen einer Tabelle groß ist, kann dies für sehr umfangreiche Vorgänge oder in Fällen, in denen Sie die Datenbank als lokale Datei oder im Arbeitsspeicher verwenden (SQLite), sinnvoll sein.

Ein Beispiel von etwas, bei dem ich gerade bin (mit Java/SqlLite):

String tmptable = "tmp" + UUID.randomUUID();

sql = "create table " + tmptable + "(pagelist text not null)";
cnn.createStatement().execute(sql);

cnn.setAutoCommit(false);
stmt = cnn.prepareStatement("insert into "+tmptable+" values(?);");
for(Object o : rmList){
    Path path = (Path)o;
    stmt.setString(1, path.toString());
    stmt.execute();
}
cnn.commit();
cnn.setAutoCommit(true);

stmt = cnn.prepareStatement(sql);
stmt.execute("delete from filelist where path + page in (select * from "+tmptable+");");
stmt.execute("drop table "+tmptable+");");

Beachten Sie, dass die von meiner Tabelle verwendeten Felder dynamisch erstellt werden.

Dies wäre noch effizienter, wenn Sie die Tabelle wiederverwenden können.

0
Jefferey Cave