jdbc4TransactionsAndBatch

1package pkg11jdbc;2 3import java.sql.Connection;4import java.sql.DriverManager;5import java.sql.PreparedStatement;6import java.sql.ResultSet;7import java.sql.SQLException;8import java.sql.Statement;9 10/*11 * jdbc4TransactionsAndBatch.java12 * ------------------------------13 * Transactions (commit/rollback) and batch updates for correctness & speed.14 *15 * DEFINITION:16 *   A transaction groups statements into an all-or-nothing unit (ACID). Batching17 *   sends many statements to the DB in one round trip for throughput.18 *19 * KEY POINTS:20 *   - setAutoCommit(false) starts manual transaction control.21 *   - commit() makes changes permanent; rollback() undoes them on error.22 *   - Savepoints allow partial rollback within a transaction.23 *   - addBatch()/executeBatch() drastically reduce per-statement overhead.24 *25 * Runs for real with an in-memory DB driver; otherwise prints guidance.26 */27public class jdbc4TransactionsAndBatch {28 29    static Connection tryConnect() {30        for (String url : new String[]{"jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1",31                                       "jdbc:sqlite::memory:", "jdbc:hsqldb:mem:demo"}) {32            try { return DriverManager.getConnection(url); } catch (SQLException ignored) {}33        }34        return null;35    }36 37    public static void main(String[] args) throws SQLException {38        Connection conn = tryConnect();39        if (conn == null) {40            System.out.println("No in-memory DB driver found. This demo would:");41            System.out.println("  1. setAutoCommit(false)");42            System.out.println("  2. batch-insert 5 rows, then commit()");43            System.out.println("  3. attempt a bad transfer and rollback() to keep balances consistent");44            System.out.println("Add h2.jar to the classpath to run it for real.");45            return;46        }47 48        try (conn) {49            try (Statement st = conn.createStatement()) {50                st.execute("CREATE TABLE account (id INT PRIMARY KEY, balance INT)");51            }52 53            // BATCH insert inside a transaction54            conn.setAutoCommit(false);55            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO account VALUES (?, ?)")) {56                for (int i = 1; i <= 5; i++) {57                    ps.setInt(1, i);58                    ps.setInt(2, 100);59                    ps.addBatch();                 // queue, don't send yet60                }61                int[] counts = ps.executeBatch();  // one round trip62                conn.commit();63                System.out.println("Batch inserted " + counts.length + " rows, committed.");64            }65 66            // A transfer that fails — demonstrate rollback keeps data consistent67            try {68                try (PreparedStatement debit  = conn.prepareStatement(69                         "UPDATE account SET balance = balance - 50 WHERE id = 1");70                     PreparedStatement credit = conn.prepareStatement(71                         "UPDATE account SET balance = balance + 50 WHERE id = 999")) { // no such row72                    debit.executeUpdate();73                    int credited = credit.executeUpdate();74                    if (credited == 0) throw new SQLException("destination account missing");75                    conn.commit();76                }77            } catch (SQLException e) {78                conn.rollback();79                System.out.println("Transfer failed -> rolled back: " + e.getMessage());80            }81 82            // Verify account 1 still has 100 (debit undone)83            try (Statement st = conn.createStatement();84                 ResultSet rs = st.executeQuery("SELECT balance FROM account WHERE id = 1")) {85                if (rs.next()) System.out.println("Account 1 balance after rollback: " + rs.getInt(1));86            }87        }88    }89}