do I need a connection.commit() after executeBatch()?

Question

I have to check the code of a fellow coworker and I stumble on this piece of code:

private void pdate(JdbcTemplate jdbcTemplate, List<Long> saisineIdsToUpdate,Connection connection) throws SQLException {
    String sqlUpdate = "UPDATE SAISINES SAI WHERE SAI.IDSAISINE = ?"; //request simplified

    PreparedStatement psUpdate = connection.prepareStatement(sqlUpdate);

    for (Long saisineId : saisineIdsToUpdate) {
        psUpdate.setLong(1, saisineId );
        psUpdate.addBatch();

    }
    psUpdate.executeBatch();
    psUpdate.close();

The code works, the updates are done correctly, but I cannot find the trace of a connection.commit(); I wonder how it can work without the commit - could someone explain why ?

You must have the auto-commit mode enabled. – sp00m Nov 12 '14 at 13:41 — sp00m, Nov 12 '14 at 13:41

score 3 · Accepted Answer · answered Nov 12 '14 at 13:41

As explained here, JDBC-drivers commonly use autocommit, you can enable database-traces via DBMS-driver specific settings like showSQL or generateDDL in JPA.

To enable manual- transaction support instead of the auto-commit mode that the JDBC driver uses by default, use the Connection object's setAutoCommit() method. If you pass a boolean false to setAutoCommit( ), you turn off auto-commit. You can pass a boolean true to turn it back on again.

Thank you for the explanation ! – Makoto Nov 12 '14 at 13:58 — Makoto, Nov 12 '14 at 13:58

score 1 · Answer 2 · answered Nov 12 '14 at 13:42

1

if you set auto-commit on your connection object to false then we have to commit the transaction manually

connection.setAutoCommit(false);
// your code goes here
connection.commit();

if you don't set auto-commit then default its value is true and it will commit each record

answered Nov 12 '14 at 13:42

Prasad Khode

6,303
11
42
57

Thanks for your support ! – Makoto Nov 12 '14 at 13:58
Setting AutoCommit to false will not work with Oracle, error yielded will be ORA-12838: cannot read/modify an object after modifying it in parallel. – access_granted Jul 29 '19 at 20:54

do I need a connection.commit() after executeBatch()?

2 Answers2

Linked