PreparedStatement in a loop, how to use

Question

I'm trying to store all the items that were created in the ITEM table and I wonder if I can do that:

    PreparedStatement stm = null;
    //String sql = "INSERT INTO ITEM (ID, TYPE, TITEL, UITGELEEND) VALUES ('%s', '%s', '%s', %b)";

    try {
        stm = db.prepareStatement("INSERT INTO ITEM (ID, TYPE, TITEL, UITGELEEND) VALUES (?, ?, ?, ?)");

        for (int n = 0; n < ItemLijst.getItems().size(); n++) {
            Item huidigItem = ItemLijst.getItemObvIdx(n);

            stm.setString(1, huidigItem.getID().toString());
            stm.setString(2, huidigItem.getType().toString());
            stm.setString(3, huidigItem.getTitel());
            stm.setString(4, String.valueOf(huidigItem.isUitgeleend()));
        }
        stm.executeUpdate();
        stm.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Or do I need to include the executeUpdate() in the loop? And the PreparedStatement? Or do I need to do an executeBatch()?

score 6 · Answer 1 · answered Jan 11 '16 at 01:03

6

Running a query inside a for loop is not the best practice. It is better if you use batch update as the following:

stm = db.prepareStatement("INSERT INTO ITEM (ID, TYPE, TITEL, UITGELEEND) VALUES (?, ?, ?, ?)");
db.setAutoCommit(false);  
for (int n = 0; n < ItemLijst.getItems().size(); n++) {
     Item huidigItem = ItemLijst.getItemObvIdx(n);
     stm.setString(1, huidigItem.getID().toString());
     stm.setString(2, huidigItem.getType().toString());
     stm.setString(3, huidigItem.getTitel());
     stm.setString(4,String.valueOf(huidigItem.isUitgeleend()));
     stm.addBatch();
     }
stm.executeBatch();
db.commit();

answered Jan 11 '16 at 01:03

Pritam Banerjee

17,953
10
93
108

Why the commit()? I used executeBatch() before and it worked without the commit. – aardbol Jan 11 '16 at 01:11
1

It is better to disable autocommit first before beginning the prepared statement. And then once everything is done, you should commit the transaction. – Pritam Banerjee Jan 11 '16 at 01:14
1

Refer to the auto commit part of the page : http://javarevisited.blogspot.com/2012/08/top-10-jdbc-best-practices-for-java.html – Pritam Banerjee Jan 11 '16 at 01:15

score 3 · Answer 2 · answered Jan 10 '16 at 20:41

3

You need to call executeUpdate for each item in the list, so it needs to be inside the loop. You only need to prepare the statement once so that should be outside of the loop.

So:

try {
    stm = db.prepareStatement("INSERT INTO ITEM (ID, TYPE, TITEL, UITGELEEND) VALUES (?, ?, ?, ?)");

    for (int n = 0; n < ItemLijst.getItems().size(); n++) {
        Item huidigItem = ItemLijst.getItemObvIdx(n);

        stm.setString(1, huidigItem.getID().toString());
        stm.setString(2, huidigItem.getType().toString());
        stm.setString(3, huidigItem.getTitel());
        stm.setString(4, String.valueOf(huidigItem.isUitgeleend()));

        stm.executeUpdate();
    }
    stm.close();
} catch (SQLException e) {
    e.printStackTrace();
}

answered Jan 10 '16 at 20:41

greg-449

109,219
232
102
145

2

Another possibility is to use addBatch/executeBatch, in which case you call addBatch inside the loop, then call executeBatch outside the loop. In my own programs, though, I tend to do what you did, putting the executeUpdate inside the loop. Lastly, there is the question of when to perform the transaction commit. – Bryan Pendleton Jan 10 '16 at 22:46
If you want to call `getGeneratedKeys()` on the `PreparedStatement`, would you call that outside of the loop? Or after each iteration (ie: within the loop)? – theyuv Nov 30 '21 at 19:24

PreparedStatement in a loop, how to use

2 Answers2

Linked