在Java的JDBC使用中设置事务回滚的保存点的方法

这篇文章主要介绍了在Java的JDBC使用中设置事务回滚的保存点的方法,JDBC是Java用于连接各种数据库的API

当设置一个保存点在事务中定义一个逻辑回滚点。如果发生错误，过去一个保存点，则可以使用rollback方法来撤消要么所有的改变或仅保存点之后所做的更改。

Connection对象有两个新的方法，可帮助管理保存点：

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

releaseSavepoint(Savepoint savepointName): 删除一个保存点。请注意，它需要一个Savepoint 对象作为参数。这个对象通常是由setSavepoint()方法生成一个保存点。

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

下面的例子演示如何使用Savepoint对象：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

try{   //Assume a valid connection object conn   conn.setAutoCommit(false);   Statement stmt = conn.createStatement();       //set a Savepoint   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");   String SQL = "INSERT INTO Employees " +         "VALUES (106, 20, 'Rita', 'Tez')";   stmt.executeUpdate(SQL);   //Submit a malformed SQL statement that breaks   String SQL = "INSERTED IN Employees " +         "VALUES (107, 22, 'Sita', 'Tez')";   stmt.executeUpdate(SQL);   // If there is no error, commit the changes.   conn.commit();   }catch(SQLException se){   // If there is any error.   conn.rollback(savepoint1); }

在这种情况下没有上述INSERT语句会成功，一切都将被回滚。
下面是利用setSavepoint和事务教程描述回滚的例子。

基于对环境和数据库安装在前面的章节中做此示例代码已经解释。

复制下面的例子JDBCExample.java，编译并运行，如下所示：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117

//STEP 1. Import required packages import java.sql.*;   public class JDBCExample {   // JDBC driver name and database URL   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";   static final String DB_URL = "jdbc:mysql://localhost/EMP";     // Database credentials   static final String USER = "username";   static final String PASS = "password";     public static void main(String[] args) {   Connection conn = null;   Statement stmt = null;   try{    //STEP 2: Register JDBC driver    Class.forName("com.mysql.jdbc.Driver");      //STEP 3: Open a connection    System.out.println("Connecting to database...");    conn = DriverManager.getConnection(DB_URL,USER,PASS);      //STEP 4: Set auto commit as false.    conn.setAutoCommit(false);      //STEP 5: Execute a query to delete statment with    // required arguments for RS example.    System.out.println("Creating statement...");    stmt = conn.createStatement();     //STEP 6: Now list all the available records.    String sql = "SELECT id, first, last, age FROM Employees";    ResultSet rs = stmt.executeQuery(sql);    System.out.println("List result set for reference....");    printRs(rs);      // STEP 7: delete rows having ID grater than 104    // But save point before doing so.    Savepoint savepoint1 = conn.setSavepoint("ROWS_DELETED_1");    System.out.println("Deleting row....");    String SQL = "DELETE FROM Employees " +           "WHERE ID = 110";    stmt.executeUpdate(SQL);    // oops... we deleted too wrong employees!    //STEP 8: Rollback the changes afetr save point 2.    conn.rollback(savepoint1);     // STEP 9: delete rows having ID grater than 104    // But save point before doing so.    Savepoint savepoint2 = conn.setSavepoint("ROWS_DELETED_2");    System.out.println("Deleting row....");    SQL = "DELETE FROM Employees " +           "WHERE ID = 95";    stmt.executeUpdate(SQL);     //STEP 10: Now list all the available records.    sql = "SELECT id, first, last, age FROM Employees";    rs = stmt.executeQuery(sql);    System.out.println("List result set for reference....");    printRs(rs);      //STEP 10: Clean-up environment    rs.close();    stmt.close();    conn.close();   }catch(SQLException se){    //Handle errors for JDBC    se.printStackTrace();    // If there is an error then rollback the changes.    System.out.println("Rolling back data here....");   try{   if(conn!=null)       conn.rollback();    }catch(SQLException se2){      se2.printStackTrace();    }//end try     }catch(Exception e){    //Handle errors for Class.forName    e.printStackTrace();   }finally{    //finally block used to close resources    try{      if(stmt!=null)       stmt.close();    }catch(SQLException se2){    }// nothing we can do    try{      if(conn!=null)       conn.close();    }catch(SQLException se){      se.printStackTrace();    }//end finally try   }//end try   System.out.println("Goodbye!"); }//end main     public static void printRs(ResultSet rs) throws SQLException{    //Ensure we start with first row    rs.beforeFirst();    while(rs.next()){      //Retrieve by column name      int id = rs.getInt("id");      int age = rs.getInt("age");      String first = rs.getString("first");      String last = rs.getString("last");        //Display values      System.out.print("ID: " + id);      System.out.print(", Age: " + age);      System.out.print(", First: " + first);      System.out.println(", Last: " + last);    }    System.out.println();   }//end printRs() }//end JDBCExample

现在让我们来编译上面的例子如下：

?

1	C:>javac JDBCExample.java

当运行JDBCExample，它会产生以下结果：

?

1	C:>java JDBCExample

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Connecting to database... Creating statement... List result set for reference.... ID: 95, Age: 20, First: Sima, Last: Chug ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal ID: 110, Age: 20, First: Sima, Last: Chug   Deleting row.... Deleting row.... List result set for reference.... ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal ID: 110, Age: 20, First: Sima, Last: Chug   Goodbye!