[JAVA100例] 第59例：JDBC 事務處理——連接、查詢、提交與回滾

本例演示完整的 JDBC 事務處理流程：關閉自動提交、批量執行 DML、條件查詢、異常回滾、資源釋放。使用 Oracle 數據庫為例，代碼結構清晰分層。

核心類結構

public class JDBCConnCommit {
  private static String url, username, password;

  // 1. 獲取連接
  public Connection conn() {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      return DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException | SQLException | Exception e) { ... return null; }
  }

  // 2. 查詢
  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 = con.createStatement();
    ResultSet 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();
    }
    rs.close(); stmt.close();
  }

  // 3. 執行 DML (insert/update/delete)
  public void execute(Connection con, String sql) throws SQLException {
    if (con == null) return;
    Statement stmt = con.createStatement();
    int i = stmt.executeUpdate(sql);
    System.out.println("update row: " + i);
    stmt.close();
  }

  // 4. 事務演示
  public void demo() {
    Connection conn = conn();
    try {
      conn.setAutoCommit(false);  // 關鍵：開啟事務
      String sql = "";
      for (int i = 0; i < 4; i++) {
        sql = "insert into TBL_USER(id,name,password) values(seq_user.nextval,'tom','haorenpingan')";
        execute(conn, sql);
      }
      sql = "select * from TBL_USER where name='tom' order by id";
      query(conn, sql);
      sql = "delete from TBL_USER where name='tom'";
      execute(conn, sql);
      conn.commit();  // 全部成功提交
    } catch (SQLException se) {
      try { conn.rollback(); } catch (Exception e) {}  // 異常回滾
      System.out.println(se);
    } catch (Exception e) { System.out.println(e); }
    finally {
      try { conn.close(); } catch (SQLException e) {}
    }
  }

  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];
    oc.demo();
  }
}

事務處理關鍵點

最佳實踐增強

1. 使用 PreparedStatement：防 SQL 注入、預編譯性能更好
2. 批量處理：addBatch() + executeBatch() 減少網絡往返
3. 保存點：Savepoint sp = conn.setSavepoint(); conn.rollback(sp); 部分回滾
4. 連接池：HikariCP/Druid 替代 DriverManager 直連
5. 聲明式事務：Spring @Transactional 替代手動管理

現代 JDBC 模板

try (Connection conn = dataSource.getConnection()) {
  conn.setAutoCommit(false);
  try (PreparedStatement ps = conn.prepareStatement(sql)) {
    // 設置參數 ps.setString(1, val)...
    ps.executeUpdate();
  }
  conn.commit();
} catch (SQLException e) {
  // 連接自動關閉，事務自動回滾（若未提交）
  throw new RuntimeException(e);
}

try-with-resources (Java 7+) 自動關閉資源，簡化 finally 代碼。