这篇文章主要介绍了在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!

begin tran 
save transaction aapoint
delete dbo.MIS_Group where ID=9
rollback tran aapoint
select * from dbo.MIS_Group

下面用伪代码说明ingres数据库事务执行失败后全部回滚的实现过程 ：
 
try
{

 savepoint(sp); //如果此处 保存点所依赖的transaction_handle事务句柄由connect（）连接数据库时获取，则创建保存点成功，可以用rollback(sp)实现全部回滚；如果此时事务句柄为空，或者不是下面要执行事务的事务句柄，则此处savepoint(sp)创建保存点失败，要实现全部回滚必须用rollback(NULL);

  result=sql_query(A);//第一个查询语句A
if(result==failure)
  throw 1;
  savepoint(sp1); //第一个保存点sp1

 result= sql_query(B);
if(result==failure) throw 2;
  savepoint(sp2);

 result= sql_query(C);
if(result==failure) throw 3;

//提交事务
commit();

}
catch(int i)
{
  if(i==1)
{//全部回滚
 rollback(NULL); //参数为空时就可以全部回滚 ，这是因为如果自动提交事务关闭之后，在commit之前的所有query语句都在一个事务中
}
else if(i==2)
{
//部分回滚
   rollback(sp1); //回滚到sp1
}
}

                    
一.sqlserver 事务。
一个事务中可以包含多个DML语句，一个DDL语句或者一个DCL语句。
事务中的语句要么全部执行，要么全部不执行。
事务具有四个特征（ACID）
a.原子性。事务要么执行，要么全部不执行。
c.一致性。事务把数据库从一个一致状态带入到另一个一致状态。
i.独立性。一个事务的执行不受其他事务的影响。
d.持续性。一个事务一旦提交就永久有效，不受关机等事件的影响。
事务在如下情况终止：
1.遇到rollback 或commit命令
2.遇到DDL或者DCL语句.
3.系统发生错误，崩溃或者退出。
二.事务设置保存点，利用save transaction a 和rollback transaction a语句，
如下
begin transaction

update test set hsmc = '木婉清' where recn = 19

save transaction a

delete from test where recn = 95

save transaction b

update test set hsmc = '段誉' where recn = 94

save transaction c

rollback transaction c

commit


--事务保存点以上的都将影响,当提交事务以后，只有保存点之前的语句被执行。
 
 


















本文转自鹅倌51CTO博客，原文链接：http://blog.51cto.com/kaixinbuliao/979833 ，如需转载请自行联系原作者