Java reference

Connection.setAutoCommit()

Turns auto-commit on or off. With false several queries form a transaction: commit() or rollback().

Available since: Java 1.1

Syntax

void setAutoCommit(boolean autoCommit) throws SQLException

Parameters

Parameter	Type	Description
autoCommit	boolean	true saves each query immediately (the default), false only after commit().

Examples

A money transfer: both changes are saved together:

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 (PreparedStatement ps = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE name = ?")) {
            ps.setInt(1, -30);
            ps.setString(2, "Anna");
            ps.executeUpdate();
            ps.setInt(1, 30);
            ps.setString(2, "Oleg");
            ps.executeUpdate();
        }
        conn.commit();
 
        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:

Anna: 70
Oleg: 50

An error halfway through — rollback() undoes the debit that already happened:

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: " + e.getMessage());
        }
 
        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: [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: balance >= 0)
Anna: 100
Oleg: 20

Notes

Auto-commit is on by default, and each query becomes permanent immediately. A transaction is needed when several changes must all happen or none of them. After setAutoCommit(false) you must call commit(), otherwise the changes are lost when the connection closes, and rollback() on an error. If the connection goes back to a pool, restore setAutoCommit(true). Intermediate rollback points are created with setSavepoint().

Related recipes

How to run several SQL queries in one transaction

In the tutorial

Transactions — Transactions in JDBC — auto-commit mode, setAutoCommit(false), commit and rollback, transferring money between accounts and a reliable pattern with try-with-resources.

Thanks for reading!