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()?

Answer 1

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

Answer 2

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