Transaction Management

import java.sql.Connection;
import java.sql.SQLException;

/**
 * Transaction control methods on Connection interface.
 */
public interface Connection {
    /**
     * Sets auto-commit mode.
     * When true, each statement is automatically committed.
     * When false, statements must be explicitly committed or rolled back.
     *
     * @param autoCommit true for auto-commit, false for manual transactions
     * @throws SQLException if mode cannot be set
     */
    void setAutoCommit(boolean autoCommit) throws SQLException;

    /**
     * Gets the current auto-commit mode.
     *
     * @return true if auto-commit is enabled
     * @throws SQLException if check fails
     */
    boolean getAutoCommit() throws SQLException;

    /**
     * Commits the current transaction.
     * Makes all changes since last commit/rollback permanent.
     *
     * @throws SQLException if commit fails
     */
    void commit() throws SQLException;

    /**
     * Rolls back the current transaction.
     * Undoes all changes since last commit/rollback.
     *
     * @throws SQLException if rollback fails
     */
    void rollback() throws SQLException;

    /**
     * Closes the connection.
     * If auto-commit is false and transaction is active, behavior depends
     * on implementation (PostgreSQL driver rolls back by default).
     *
     * @throws SQLException if close fails
     */
    void close() throws SQLException;
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

// Example 1: Basic transaction
public class BasicTransactionExample {
    public static void transferFunds(Connection conn, int fromAccount,
                                     int toAccount, double amount)
            throws SQLException {
        // Disable auto-commit to start transaction
        conn.setAutoCommit(false);

        try {
            // Debit from account
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
                pstmt.setDouble(1, amount);
                pstmt.setInt(2, fromAccount);
                pstmt.executeUpdate();
            }

            // Credit to account
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
                pstmt.setDouble(1, amount);
                pstmt.setInt(2, toAccount);
                pstmt.executeUpdate();
            }

            // Commit transaction
            conn.commit();
            System.out.println("Transfer completed");

        } catch (SQLException e) {
            // Roll back on error
            conn.rollback();
            System.err.println("Transfer failed, rolled back");
            throw e;
        } finally {
            // Restore auto-commit
            conn.setAutoCommit(true);
        }
    }
}

// Example 2: Transaction with try-with-resources
public class TryWithResourcesTransaction {
    public static void executeInTransaction(Connection conn) throws SQLException {
        boolean originalAutoCommit = conn.getAutoCommit();

        try {
            conn.setAutoCommit(false);

            // Perform database operations
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO audit_log (action, timestamp) VALUES (?, ?)")) {
                pstmt.setString(1, "USER_LOGIN");
                pstmt.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
                pstmt.executeUpdate();
            }

            try (PreparedStatement pstmt = conn.prepareStatement(
                    "UPDATE users SET last_login = ? WHERE id = ?")) {
                pstmt.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));
                pstmt.setInt(2, 123);
                pstmt.executeUpdate();
            }

            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(originalAutoCommit);
        }
    }
}

import java.sql.Connection;
import java.sql.SQLException;

/**
 * Transaction isolation level constants and methods.
 */
public interface Connection {
    // Isolation level constants
    int TRANSACTION_NONE = 0;
    int TRANSACTION_READ_UNCOMMITTED = 1; // Treated as READ COMMITTED in PostgreSQL
    int TRANSACTION_READ_COMMITTED = 2;   // PostgreSQL default
    int TRANSACTION_REPEATABLE_READ = 4;
    int TRANSACTION_SERIALIZABLE = 8;

    /**
     * Sets the transaction isolation level.
     * Must be called before starting a transaction.
     *
     * @param level Isolation level constant
     * @throws SQLException if level cannot be set
     */
    void setTransactionIsolation(int level) throws SQLException;

    /**
     * Gets the current transaction isolation level.
     *
     * @return Current isolation level
     * @throws SQLException if level cannot be retrieved
     */
    int getTransactionIsolation() throws SQLException;
}

import java.sql.Connection;
import java.sql.SQLException;

// Example 1: Set isolation level
public class IsolationLevelExample {
    public static void executeWithIsolation(Connection conn) throws SQLException {
        // Save current level
        int originalLevel = conn.getTransactionIsolation();

        try {
            // Set to REPEATABLE READ for consistent reads
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            conn.setAutoCommit(false);

            // Execute queries - will see consistent snapshot
            // of database as of transaction start

            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            // Restore original level
            conn.setTransactionIsolation(originalLevel);
            conn.setAutoCommit(true);
        }
    }
}

// Example 2: SERIALIZABLE for strict consistency
public class SerializableExample {
    public static void serializableTransaction(Connection conn) throws SQLException {
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        conn.setAutoCommit(false);

        try {
            // Perform operations that require serializable isolation
            // PostgreSQL will detect serialization conflicts and
            // may throw SQLException with SQLSTATE 40001

            conn.commit();

        } catch (SQLException e) {
            if ("40001".equals(e.getSQLState())) {
                // Serialization failure - retry transaction
                System.out.println("Serialization conflict, retry needed");
            }
            conn.rollback();
            throw e;
        }
    }
}

import java.sql.Connection;
import java.sql.Savepoint;
import java.sql.SQLException;

/**
 * Savepoint methods for partial transaction rollback.
 */
public interface Connection {
    /**
     * Creates an unnamed savepoint in the current transaction.
     *
     * @return Savepoint object
     * @throws SQLException if savepoint cannot be created
     */
    Savepoint setSavepoint() throws SQLException;

    /**
     * Creates a named savepoint in the current transaction.
     *
     * @param name Savepoint name
     * @return Savepoint object
     * @throws SQLException if savepoint cannot be created
     */
    Savepoint setSavepoint(String name) throws SQLException;

    /**
     * Rolls back to the specified savepoint.
     * Undoes all changes after the savepoint but keeps changes before it.
     *
     * @param savepoint Savepoint to roll back to
     * @throws SQLException if rollback fails
     */
    void rollback(Savepoint savepoint) throws SQLException;

    /**
     * Releases the savepoint and frees resources.
     * The savepoint cannot be used after being released.
     *
     * @param savepoint Savepoint to release
     * @throws SQLException if release fails
     */
    void releaseSavepoint(Savepoint savepoint) throws SQLException;
}

/**
 * Savepoint interface.
 */
public interface Savepoint {
    /**
     * Gets the savepoint ID.
     * Valid only for unnamed savepoints.
     *
     * @return Savepoint ID
     * @throws SQLException if savepoint is named
     */
    int getSavepointId() throws SQLException;

    /**
     * Gets the savepoint name.
     * Valid only for named savepoints.
     *
     * @return Savepoint name
     * @throws SQLException if savepoint is unnamed
     */
    String getSavepointName() throws SQLException;
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Savepoint;
import java.sql.SQLException;

// Example 1: Basic savepoint usage
public class SavepointExample {
    public static void useSavepoints(Connection conn) throws SQLException {
        conn.setAutoCommit(false);

        try {
            // First operation
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO orders (customer_id, total) VALUES (?, ?)")) {
                pstmt.setInt(1, 123);
                pstmt.setDouble(2, 100.00);
                pstmt.executeUpdate();
            }

            // Create savepoint after successful order
            Savepoint savepoint1 = conn.setSavepoint("after_order");

            try {
                // Second operation (might fail)
                try (PreparedStatement pstmt = conn.prepareStatement(
                        "UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?")) {
                    pstmt.setInt(1, 10);
                    pstmt.setInt(2, 456);
                    int rows = pstmt.executeUpdate();

                    if (rows == 0) {
                        throw new SQLException("Product not found");
                    }
                }

            } catch (SQLException e) {
                // Roll back only the inventory update
                System.out.println("Rolling back to savepoint");
                conn.rollback(savepoint1);
                // Order insert is still valid
            }

            // Commit the transaction (or whatever is left of it)
            conn.commit();

        } catch (SQLException e) {
            // Roll back entire transaction
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

// Example 2: Multiple savepoints
public class MultipleSavepointsExample {
    public static void complexTransaction(Connection conn) throws SQLException {
        conn.setAutoCommit(false);

        try {
            // Step 1: Insert user
            Savepoint sp1 = conn.setSavepoint("user_created");
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO users (name) VALUES (?)")) {
                pstmt.setString(1, "John Doe");
                pstmt.executeUpdate();
            }

            // Step 2: Insert profile
            Savepoint sp2 = conn.setSavepoint("profile_created");
            try {
                try (PreparedStatement pstmt = conn.prepareStatement(
                        "INSERT INTO profiles (user_id, bio) VALUES (?, ?)")) {
                    pstmt.setInt(1, 123);
                    pstmt.setString(2, "Bio text");
                    pstmt.executeUpdate();
                }
            } catch (SQLException e) {
                conn.rollback(sp2); // Keep user, discard profile
            }

            // Step 3: Insert preferences
            try {
                try (PreparedStatement pstmt = conn.prepareStatement(
                        "INSERT INTO preferences (user_id, theme) VALUES (?, ?)")) {
                    pstmt.setInt(1, 123);
                    pstmt.setString(2, "dark");
                    pstmt.executeUpdate();
                }
            } catch (SQLException e) {
                // This error is minor, continue without preferences
                System.out.println("Preferences not saved, continuing");
            }

            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

package org.postgresql.jdbc;

/**
 * Automatic savepoint modes.
 * Controls when the driver automatically creates savepoints.
 */
public enum AutoSave {
    /**
     * Never create automatic savepoints.
     * Default behavior - application must handle errors.
     */
    NEVER,

    /**
     * Always create a savepoint before each query.
     * Allows automatic recovery from query errors without
     * aborting the entire transaction.
     * Higher overhead but maximum safety.
     */
    ALWAYS,

    /**
     * Create savepoints conservatively.
     * Driver automatically creates savepoints in certain scenarios
     * to enable better error recovery.
     * Good balance of safety and performance.
     */
    CONSERVATIVE;

    /**
     * Returns the string value of this enum for use in connection properties.
     *
     * @return Lowercase string value ("never", "always", or "conservative")
     */
    public String value();

    /**
     * Parses a string value and returns the corresponding AutoSave enum constant.
     *
     * @param value String value (case-insensitive)
     * @return AutoSave enum constant
     * @throws IllegalArgumentException if value is invalid
     */
    public static AutoSave of(String value);
}

package org.postgresql;

import org.postgresql.jdbc.AutoSave;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * AutoSave configuration on PGConnection.
 */
public interface PGConnection extends Connection {
    /**
     * Gets the current autosave mode.
     *
     * @return Current AutoSave setting
     */
    AutoSave getAutosave();

    /**
     * Sets the autosave mode.
     *
     * @param autoSave AutoSave mode to use
     */
    void setAutosave(AutoSave autoSave);
}

import org.postgresql.PGConnection;
import org.postgresql.jdbc.AutoSave;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

// Example 1: Configure autosave in connection properties
public class AutoSaveConnection {
    public static Connection getConnectionWithAutoSave() throws SQLException {
        String url = "jdbc:postgresql://localhost/mydb?autosave=conservative";
        return DriverManager.getConnection(url, "user", "password");
    }
}

// Example 2: Set autosave programmatically
public class ProgrammaticAutoSave {
    public static void useAutoSave(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Enable conservative autosave
        pgConn.setAutosave(AutoSave.CONSERVATIVE);
        conn.setAutoCommit(false);

        try {
            // Even if one of these statements fails, others can succeed
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO logs (message) VALUES (?)")) {
                pstmt.setString(1, "Log entry 1");
                pstmt.executeUpdate();
            }

            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO logs (message) VALUES (?)")) {
                // This might fail due to constraint violation
                pstmt.setString(1, "Duplicate key");
                pstmt.executeUpdate();
            } catch (SQLException e) {
                // With autosave, this error doesn't abort the transaction
                System.out.println("One insert failed: " + e.getMessage());
            }

            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO logs (message) VALUES (?)")) {
                pstmt.setString(1, "Log entry 3");
                pstmt.executeUpdate();
            }

            // Commit successful operations
            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

// Example 3: ALWAYS autosave for maximum safety
public class AlwaysAutoSave {
    public static void maximumSafety(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);
        pgConn.setAutosave(AutoSave.ALWAYS);

        conn.setAutoCommit(false);

        try {
            // Each statement is protected by automatic savepoint
            // If any statement fails, only that statement is rolled back

            for (int i = 0; i < 100; i++) {
                try (PreparedStatement pstmt = conn.prepareStatement(
                        "INSERT INTO data (value) VALUES (?)")) {
                    pstmt.setInt(1, i);
                    pstmt.executeUpdate();
                } catch (SQLException e) {
                    // Log error but continue
                    System.out.println("Failed to insert " + i);
                }
            }

            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }
}