CODEWITHSUNDEEP
javasqljdbcinsertbatch-processing
Frank_B
Frank_B

Reputation: 109

JBDC batch insert not looping over correctly

I'm currently trying to insert values from a String Array into the three columns within my database e.g. list 1 into column 1 and so forth. But having issues using the batch insert within the loop. Here is my current code and from what I can gather the only way to do this is by looping over each String Array inserting values unless there is a better way.

PreparedStatement stmt = conn.prepareStatement("INSERT INTO Scores (Home, Score, Away) VALUES (?, ?, ?)");

    String[] List1str = new String[List1.size()];
    List1str = List1.toArray(List1str);   
    String[] List2str = new String[List2.size()];
    List2str = List2.toArray(List2str);
    String[] List3str = new String[List3.size()];
    List3str = List3.toArray(List3str);

    for (String s1 : List1str) {
        stmt.setString(1, s1);
        for (String s2 : List2str) {
            stmt.setString(2, s2);
            for (String s3 : List3str) {
                stmt.setString(3, s3);
                    stmt.addBatch();
            }
        }
    }
    stmt.executeBatch();

Upvotes: 0

Views: 75

Answers (1)

Sergei N Lukin
Sergei N Lukin

Reputation: 575

You should bind all strings before do addBatch(). Currently your addBatch() in last nested loop.

If we take in assumption that all lists has same size, your code should be following:

for (int i=0; i<List1.size(); i++) {
    stmt.setString(1, List1[i]);
    stmt.setString(2, List2[i]);
    stmt.setString(3, List3[i]);
    stmt.addBatch();
}
stmt.executeBatch();

Upvotes: 1

Related Questions