Transaction Support

Multiple-Write Consistency

Suppose two records must be saved together. If the second insert fails, the first must not remain in the database. Start with an empty table whose primary key is id:

CREATE TABLE user_info (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

These two calls deliberately use the same primary key:

import net.hasor.dbvisitor.jdbc.core.JdbcTemplate;

JdbcTemplate jdbc = new JdbcTemplate(dataSource);
jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
        new Object[] { 1001, "Alice" });
jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
        new Object[] { 1001, "Bob" });

With auto-commit enabled and no transaction started, Alice is committed first. Bob then fails with a duplicate-key exception, but Alice remains in the table.

To roll back both writes on failure, start again with an empty table and put the calls in a transaction:

import net.hasor.dbvisitor.transaction.TransactionTemplate;
import net.hasor.dbvisitor.transaction.TransactionTemplateManager;
import net.hasor.dbvisitor.transaction.support.TransactionHelper;

TransactionTemplate tx = new TransactionTemplateManager(
        TransactionHelper.txManager(dataSource));

tx.execute(status -> {
    jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
            new Object[] { 1001, "Alice" });
    jdbc.executeUpdate("INSERT INTO user_info (id, name) VALUES (?, ?)",
            new Object[] { 1001, "Bob" });
    return null;
});

Bob's failure leaves the callback as an exception and rolls back Alice's insert. Neither record remains. If the second ID is changed to 1002, both inserts succeed and commit together.

Note

Use the same dataSource for jdbc and the transaction manager. Do not catch and suppress the failure inside the callback; if you must catch it, call status.setRollback().

The calling method must handle or declare Throwable from execute. The Builder API and Mapper can also participate in the transaction; see Transaction Template.