JDBC 事務(wù)

JDBC教程 - JDBC事務(wù)

事務(wù)將一組SQL語句視為一個邏輯單元，如果任何語句失敗，整個事務(wù)將失敗并回滾。

默認(rèn)情況下，JDBC連接處于自動提交模式，這意味著每個SQL語句在完成后都提交到數(shù)據(jù)庫。

要啟用手動事務(wù)，請使用Connection對象的setAutoCommit()方法。

例如，以下代碼關(guān)閉自動提交:

conn.setAutoCommit(false);

要提交更改，請?jiān)谶B接對象上調(diào)用commit()方法，如下所示:

conn.commit( );

要回滾對數(shù)據(jù)庫的更新，請使用以下代碼:

conn.rollback( );

以下示例顯示如何使用提交和回滾。

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees VALUES (1, "name")";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERT INTO Employees VALUES (2, "anotherName")";
   stmt.executeUpdate(SQL);
   conn.commit();
}catch(SQLException se){
   conn.rollback();
}

使用保存點(diǎn)

保存點(diǎn)定義事務(wù)中的回滾點(diǎn)。

如果在保存點(diǎn)之后發(fā)生錯誤，我們可以回滾以撤消所有更改或僅撤消在保存點(diǎn)之后進(jìn)行的更改。

Connection對象有兩個方法與保存點(diǎn)相關(guān)。

setSavepoint(String savepointName)定義新的保存點(diǎn)。它還返回一個Savepoint對象。

releaseSavepoint(Savepoint savepointName)刪除保存點(diǎn)。它需要一個Savepoint對象作為參數(shù)，它由setSavepoint()方法生成。

rollback(String savepointName)方法將工作回滾到指定的保存點(diǎn)。

以下示例說明了使用Savepoint對象:

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Employees VALUES (1, "name")";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERT INTO Employees VALUES (2, "new name")";
   stmt.executeUpdate(SQL);
   conn.commit();

}catch(SQLException se){
   conn.rollback(savepoint1);
}

例子

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION,
        DB_USER, DB_PASSWORD);

    PreparedStatement preparedStatementInsert = null;
    PreparedStatement preparedStatementUpdate = null;

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" + "(?,?,?,?)";

    String updateTableSQL = "UPDATE Person SET USERNAME =? "
        + "WHERE USER_ID = ?";

    java.util.Date today = new java.util.Date();
    dbConnection.setAutoCommit(false);

    preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
    preparedStatementInsert.setInt(1, 9);
    preparedStatementInsert.setString(2, "101");
    preparedStatementInsert.setString(3, "system");
    preparedStatementInsert.setTimestamp(4,
        new java.sql.Timestamp(today.getTime()));
    preparedStatementInsert.executeUpdate();

    preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    preparedStatementUpdate.setString(1, "new string");
    preparedStatementUpdate.setInt(2, 999);
    preparedStatementUpdate.executeUpdate();

    dbConnection.commit();
    dbConnection.close();
  }
}