Batch Inserts with PreparedStatement

Direct Answer

Reuse one PreparedStatement, bind each row, call addBatch(), and periodically call executeBatch(). A transaction can make a chosen batch atomic and avoid committing each row separately. The correct commit and chunk size depends on rollback needs, memory, locks, log growth, driver limits, and database capacity; one enormous batch and one commit are not always best.

Code Example

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
con.setAutoCommit(false);
try (PreparedStatement ps = con.prepareStatement(sql)) {
    int n = 0;
    for (User u : users) {
        ps.setString(1, u.name());
        ps.setString(2, u.email());
        ps.addBatch();                       // queue
        if (++n % 1000 == 0) ps.executeBatch(); // flush every 1000
    }
    ps.executeBatch();                       // remaining
    con.commit();
} catch (SQLException e) {
    con.rollback();
    throw e;
} finally {
    con.setAutoCommit(true);
}

Common Mistakes & Pitfalls

• executeUpdate in the loop: defeats batching — use addBatch.
• Re-preparing per row: prepare once outside the loop.
• No commit / auto-commit on: each item commits separately.
• One enormous batch: flush every N rows to bound memory.