JDBC Transaction

JDBC Transaction Management allows us to group multiple SQL operations into a single unit. If all operations succeed, we commit the transaction; otherwise, we rollback to maintain data integrity.

Auto-Commit Mode in JDBC:

By default, JDBC runs in auto-commit mode, meaning each SQL statement is treated as a separate transaction.

Disable Auto-Commit for Manual Control:

conn.setAutoCommit(false);

After this, changes will not be committed until we explicitly call:

conn.commit(); // Save changes permanently

conn.rollback(); // Undo all changes

Performing Multiple Queries in a Transaction:

setAutoCommit(false) → Disables auto-commit.
executeUpdate() → Executes multiple SQL queries.
commit() → Commits all changes if no error occurs.
rollback() → Rolls back all changes if any error occurs.

import java.sql.*;

public class JDBCTransactionExample {

public static void main(String[] args) {

String url = "jdbc:mysql://localhost:3306/mydb";

String user = "root";

String password = "password";

try (Connection conn = DriverManager.getConnection(url, user, password)) {

conn.setAutoCommit(false); // Disable auto-commit

try (Statement stmt = conn.createStatement()) {

// Insert a new employee

stmt.executeUpdate("INSERT INTO employees (id, name, role) VALUES (101, 'Alice', 'Engineer')");

// Update another employee's role

stmt.executeUpdate("UPDATE employees SET role = 'Manager' WHERE id = 102");

conn.commit(); // ✅ Commit transaction if both queries succeed

System.out.println("Transaction committed successfully!");

} catch (SQLException e) {

conn.rollback(); // ❌ Rollback if any query fails

System.out.println("Transaction rolled back due to an error.");

e.printStackTrace();

}

} catch (SQLException e) {

e.printStackTrace();

}

Insert Multiple Records in a Transaction:

import java.sql.*;

public class JDBCTransactionPreparedStatement {

public static void main(String[] args) {

String url = "jdbc:mysql://localhost:3306/mydb";

String user = "root";

String password = "password";

try (Connection conn = DriverManager.getConnection(url, user, password)) {

conn.setAutoCommit(false); // Disable auto-commit

String sql = "INSERT INTO employees (id, name, role) VALUES (?, ?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

// Insert first record

pstmt.setInt(1, 103);

pstmt.setString(2, "Bob");

pstmt.setString(3, "Analyst");

pstmt.executeUpdate();

// Insert second record

pstmt.setInt(1, 104);

pstmt.setString(2, "Charlie");

pstmt.setString(3, "Developer");

pstmt.executeUpdate();

conn.commit(); // ✅ Commit transaction

System.out.println("Transaction committed successfully!");

} catch (SQLException e) {

conn.rollback(); // ❌ Rollback on error

System.out.println("Transaction rolled back.");

e.printStackTrace();

}

} catch (SQLException e) {

e.printStackTrace();

}

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.