How to run several SQL queries in one transaction

Short answer

Turn off auto-commit, run the queries and call commit(); on an error call rollback():

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE accounts (name TEXT, balance INTEGER)");
            st.executeUpdate("INSERT INTO accounts VALUES ('Anna', 100), ('Oleg', 20)");
        }
 
        conn.setAutoCommit(false);
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("UPDATE accounts SET balance = balance - 30 WHERE name = 'Anna'");
            st.executeUpdate("UPDATE accounts SET balance = balance + 30 WHERE name = 'Oleg'");
            conn.commit();
            IO.println("Transfer done");
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }
}

Output:

Transfer done

Rolling back on an error

If the second query violates a table constraint, rollback() undoes the first one too:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE accounts (name TEXT, balance INTEGER CHECK (balance >= 0))");
            st.executeUpdate("INSERT INTO accounts VALUES ('Anna', 100), ('Oleg', 20)");
        }
 
        conn.setAutoCommit(false);
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("UPDATE accounts SET balance = balance + 50 WHERE name = 'Anna'");
            st.executeUpdate("UPDATE accounts SET balance = balance - 50 WHERE name = 'Oleg'");
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            IO.println("Rolled back");
        }
 
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT name, balance FROM accounts")) {
            while (rs.next()) {
                IO.println(rs.getString(1) + ": " + rs.getInt(2));
            }
        }
    }
}

Output:

Rolled back
Anna: 100
Oleg: 20

A helper method

To avoid repeating commit and rollback, move them into a method that takes an action:

import java.sql.*;
 
interface SqlAction {
    void run(Connection conn) throws SQLException;
}
 
void inTransaction(Connection conn, SqlAction action) throws SQLException {
    boolean oldMode = conn.getAutoCommit();
    conn.setAutoCommit(false);
    try {
        action.run(conn);
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        conn.setAutoCommit(oldMode);
    }
}
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        inTransaction(conn, c -> {
            try (Statement st = c.createStatement()) {
                st.executeUpdate("CREATE TABLE logs (msg TEXT)");
                st.executeUpdate("INSERT INTO logs VALUES ('start'), ('stop')");
            }
        });
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM logs")) {
            rs.next();
            IO.println(rs.getInt(1) + " rows saved");
        }
    }
}

Output:

2 rows saved

Common mistake

Turning off auto-commit and forgetting commit(). The changes are visible inside the same connection, so the mistake goes unnoticed until the connection closes — and all the changes are lost. The opposite mistake is catching an exception without calling rollback(): the connection keeps an unfinished transaction, and in a connection pool the next request inherits it.

Thanks for reading!

Short answer

Rolling back on an error

A helper method

Common mistake

Functions used in this recipe

Thanks for reading!