JDBC Java transaction rollback demo with batch inserts

In this tutorial, we will see how JDBC Java transactions rollbacks are useful, with a real world example and a full Java demo. We will look at the particular case of batch inserts.

1) Batch inserts without rollback

Let’s use the following SQL table:

CREATE TABLE customer (name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL);

Starting from a JDBC connection object called conn:

java.sql.Connection conn;

, the batch inserts code is:

int batchSize = 100000;
try (Statement stmt = conn.createStatement()) {

    for (int i = 0; i < batchSize; i++) {
        String sql = "insert into customer (name,email) values ('" + i + "','" + i + "')";
        if (i == batchSize / 2)
            sql = "bad sql";

        stmt.addBatch(sql);
    }
    stmt.executeBatch();
} catch (BatchUpdateException ignored) {
}

This is a very classical batch insert code (as explained in details here), with one small tweak: in the middle of the batch (when i == batchSize / 2), we write some wrong SQL query syntax (“bad sql”).

If we execute this code, we can see that the first half of the entries is inserted into the database (100,000 / 2 ≈ 49,919):

Total rows in DB: 49919

Then, after the SQL error in the middle of the batch, no more entries were inserted.

This can be a problem for financial transactions and other SQL operations, that often require atomicity (as explained here). What we want, is that when a query in the batch fails, then the whole batch is rolled back. This is equivalent to saying that the database is modified if and only if ALL the batch inserts are successful.

2) Batch inserts WITH rollback

To obtain the atomic transactional behavior explained above, we can use the JDBC commit / rollback API:

conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {

    for (int i = batchSize; i < 2 * batchSize; i++) {
        String sql = "insert into customer (name,email) values ('" + i + "','" + i + "')";
        if (i == (3 * batchSize) / 2)
            sql = "bad sql";

        stmt.addBatch(sql);
    }
    stmt.executeBatch();

    conn.commit();
} catch (BatchUpdateException ignored) {
    conn.rollback();
}

First we do Connection.setAutoCommit(false) so that instructions sent to the JDBC driver are not persisted in the database until we call Connection.commit(). The point of this, is that if something goes wrong, we will call Connection.rollback() instead.

Running the code above, we obtain:

Total rows in DB: 49919

This means that 0 new rows were inserted in database after the first 49919 ones that were created in part 1). Indeed, there was an error in the middle of the batch, so the whole batch got cancelled. Hence, atomicity is respected.

3)Batch inserts WITH rollback, NO error

To verify that everything is working, let’s run the batched inserts with rollback, but without any SQL error in the middle of the batch. The code is:

conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
    for (int i = batchSize; i < 2 * batchSize; i++) {
        String sql = "insert into customer (name,email) values ('" + i + "','" + i + "')";
        stmt.addBatch(sql);
    }
    stmt.executeBatch();

    conn.commit();
} catch (BatchUpdateException ignored) {
    conn.rollback();
}

Notice how this code was removed:

        if (i == (3 * batchSize) / 2)
            sql = "bad sql";

When we run the above program, we obtain:

Total rows in DB: 149919

It means that 100,000 rows were inserted after part 2). Indeed: 149,919 – 49,919 = 100,000. This is the expected behavior: when there is no error in the batch, all the rows should be created in the database.

4) Full Java demo

That’s it for this tutorial. If you have a question, please leave a reply below, we answer within 24h !