Как выполнить несколько SQL-запросов в одной транзакции

Короткий ответ

Выключите автофиксацию, выполните запросы и вызовите commit(); при ошибке — 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;
        }
    }
}

Результат:

Transfer done

Откат при ошибке

Если второй запрос нарушает ограничение таблицы, 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 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));
            }
        }
    }
}

Результат:

Rolled back
Anna: 100
Oleg: 20

Метод-помощник

Чтобы не повторять commit и rollback, вынесите их в метод, который принимает действие:

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");
        }
    }
}

Результат:

2 rows saved

Частая ошибка

Выключить автофиксацию и забыть commit(). Изменения видны внутри того же соединения, поэтому ошибка незаметна, пока соединение не закроется — и все изменения не пропадут. Обратная ошибка — поймать исключение и не вызвать rollback(): тогда соединение остаётся с незавершённой транзакцией, а в пуле соединений она достанется следующему запросу.

Спасибо, что читаете!

Короткий ответ

Откат при ошибке

Метод-помощник

Частая ошибка

Функции из этого ответа

Спасибо, что читаете!