JDBC 事务

JDBC教程 - JDBC事务

事务将一组SQL语句视为一个逻辑单元，如果任何语句失败，整个事务将失败并回滚。

默认情况下，JDBC连接处于自动提交模式，这意味着每个SQL语句在完成后都提交到数据库。

要启用手动事务，请使用Connection对象的setAutoCommit()方法。

例如，以下代码关闭自动提交:

conn.setAutoCommit(false);

要提交更改，请在连接对象上调用commit()方法，如下所示:

conn.commit( );

要回滚对数据库的更新，请使用以下代码:

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();
}

使用保存点

保存点定义事务中的回滚点。

如果在保存点之后发生错误，我们可以回滚以撤消所有更改或仅撤消在保存点之后进行的更改。

Connection对象有两个方法与保存点相关。

setSavepoint(String savepointName)定义新的保存点。它还返回一个Savepoint对象。

releaseSavepoint(Savepoint savepointName)删除保存点。它需要一个Savepoint对象作为参数，它由setSavepoint()方法生成。

rollback(String savepointName)方法将工作回滚到指定的保存点。

以下示例说明了使用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();
  }
}