您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 程式設計入門

JDBC 交易處理實作：Commit 與 Rollback 控制詳解

編輯：Java 程式設計入門

交易處理核心概念

資料庫交易確保一組 SQL 操作要么全數成功（Commit），要么全數失敗回滾，維護資料一致性。JDBC 預設自動提交模式，每條 SQL 即時生效；關閉自動提交後，需手動呼叫 commit() 或 rollback()。

完整程式碼：JDBCConnCommit.java（含查詢與更新封裝）

import java.sql.*;

public class JDBCConnCommit {
    private static String url = "";
    private static String username = "";
    private static String password = "";

    // 取得連線（同前例）
    public Connection conn() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            return DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException cnf) { System.out.println("driver not find:" + cnf); }
        catch (SQLException sqle) { System.out.println("can't connection db:" + sqle); }
        catch (Exception e) { System.out.println("Failed to load JDBC/ODBC driver."); }
        return null;
    }

    // 執行查詢並格式化輸出
    public void query(Connection con, String sql) throws SQLException, Exception {
        if (con == null) throw new Exception("database connection can't use!");
        if (sql == null) throw new Exception("check your parameter: 'sql'! don't input null!");

        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData rmeta = rs.getMetaData();
            int numColumns = rmeta.getColumnCount();

            while (rs.next()) {
                for (int i = 0; i < numColumns; i++) {
                    System.out.print(rs.getString(i + 1) + " ");
                }
                System.out.println();
            }
        } catch (Exception e) {
            System.out.println("query error: sql = " + sql);
            System.out.println("query error:" + e);
            throw new SQLException("query error");
        } finally {
            if (rs != null) try { rs.close(); } catch (Exception ignored) {}
            if (stmt != null) try { stmt.close(); } catch (Exception ignored) {}
        }
    }

    // 執行 INSERT/UPDATE/DELETE（無結果集）
    public void execute(Connection con, String sql) throws SQLException {
        if (con == null) return;
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            int i = stmt.executeUpdate(sql);
            System.out.println("update row:" + i);
        } catch (Exception e) {
            System.out.println("execute error: sql = " + sql);
            System.out.println(e);
            throw new SQLException("execute error");
        } finally {
            if (stmt != null) try { stmt.close(); } catch (Exception ignored) {}
        }
    }

    // 主程式（同前例）
    public static void main(String[] arg) {
        if (arg.length != 3) {
            System.out.println("use: java JDBCConnCommit url username password");
            return;
        }
        JDBCConnCommit oc = new JDBCConnCommit();
        oc.url = arg[0];
        oc.username = arg[1];
        oc.password = arg[2];
        // 此處可撰寫交易測試邏輯
    }
}

交易控制實戰範例（補充）

// 交易處理標準模式
Connection con = null;
try {
    con = getConnection();
    con.setAutoCommit(false); // 1. 關閉自動提交

    // 2. 執行多項業務操作
    executeUpdate(con, "UPDATE account SET balance = balance - 100 WHERE id = 'A'");
    executeUpdate(con, "UPDATE account SET balance = balance + 100 WHERE id = 'B'");

    con.commit(); // 3. 全部成功，提交交易
    System.out.println("交易成功");
} catch (SQLException e) {
    System.out.println("交易失敗：" + e);
    if (con != null) try { con.rollback(); } catch (SQLException ignored) {} // 4. 任一失敗，回滾
} finally {
    if (con != null) try { con.setAutoCommit(true); con.close(); } catch (SQLException ignored) {} // 5. 恢復預設並關閉
}