java连接mysql(二)

模拟转账成功时的业务场景

 1 import java.sql.*;
 2 
 3 public class TransactionDemo1 {
 4  
11 
12     public static void main(String[] args) throws SQLException, ClassNotFoundException {
13         String url = "jdbc:mysql://localhost/xh";
14         String username = "xiaohengdada";
15         String password = "123456";
16 
17         Class.forName("org.gjt.mm.mysql.Driver");
18         Connection conn = null;
19         PreparedStatement st = null;
20         ResultSet rs = null;
21 
22         try {
23             conn = DriverManager.getConnection(url, username, password);
24             conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
25             String sql1 = "update account set money=money+100 where name='A'";
26             st = conn.prepareStatement(sql1);
27             int num1 = st.executeUpdate();
28             if (num1 > 0) {
29                 System.out.println("succeed to update A ");
30             }
31             String sql2 = "update account set money=money-100 where name='B'";
32             st = conn.prepareStatement(sql2);
33             int num2 = st.executeUpdate();
34             if (num2 > 0) {
35                 System.out.println("succeed to update B ");
36             }
37             conn.commit();// 上面的两条SQL执行Update语句成功之后就通知数据库提交事务(commit)
38             System.out.println("成功！！！"); // log4j
39         } catch (Exception e) {
40             e.printStackTrace();
41         } finally {
42             // rs.close();
43             st.close();
44             conn.close();
45         }
46     }
47 }

 

 

 1 import java.sql.*;
 2 
 3 public class TransactionDemo1 {
 4     public static void main(String[] args) throws SQLException, ClassNotFoundException {
 5         String url = "jdbc:mysql://localhost/xh";
 6         String username = "xiaohengdada";
 7         String password = "123456";
 8 
 9         Class.forName("org.gjt.mm.mysql.Driver");
10         Connection conn = null;
11         PreparedStatement st = null;
12         ResultSet rs = null;
13 
14         try {
15             conn = DriverManager.getConnection(url, username, password);
16             conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
17             String sql1 = "update account set money=money-100 where name='A'";
18             st = conn.prepareStatement(sql1);
19             st.executeUpdate();
20             // 用这句代码模拟执行完SQL1之后程序出现了异常而导致后面的SQL无法正常执行，事务也无法正常提交，此时数据库会自动执行回滚操作
21             int x = 1 / 0;
22             String sql2 = "update account set money=money+100 where name='B'";
23             st = conn.prepareStatement(sql2);
24             st.executeUpdate();
25             conn.commit();// 上面的两条SQL执行Update语句成功之后就通知数据库提交事务(commit)
26             System.out.println("成功！！！");
27         } catch (Exception e) {
28             e.printStackTrace();
29         } finally {
30             st.close();
31             conn.close();
32         }
33     }
34 }

 1 import java.sql.*;
 2 
 3 public class TransactionDemo1 {
 4     public static void main(String[] args) throws SQLException, ClassNotFoundException {
 5         String url = "jdbc:mysql://localhost/xh";
 6         String username = "xiaohengdada";
 7         String password = "123456";
 8 
 9         Class.forName("org.gjt.mm.mysql.Driver");
10         Connection conn = null;
11         PreparedStatement st = null;
12         ResultSet rs = null;
13 
14           try{
15                 conn = DriverManager.getConnection(url,username,password);
16                 conn.setAutoCommit(false);//通知数据库开启事务(start transaction)
17                 String sql1 = "update account set money=money-100 where name='A'";
18                 st = conn.prepareStatement(sql1);
19                 st.executeUpdate();
20                 //用这句代码模拟执行完SQL1之后程序出现了异常而导致后面的SQL无法正常执行，事务也无法正常提交
21                 int x = 1/0;
22                 String sql2 = "update account set money=money+100 where name='B'";
23                 st = conn.prepareStatement(sql2);
24                 st.executeUpdate();
25                 conn.commit();//上面的两条SQL执行Update语句成功之后就通知数据库提交事务(commit)
26                 System.out.println("成功！！！");
27             }catch (Exception e) {
28                 try {
29                     //捕获到异常之后手动通知数据库执行回滚事务的操作
30                     conn.rollback();
31                 } catch (SQLException e1) {
32                     e1.printStackTrace();
33                 }
34                 e.printStackTrace();
35             }finally{
36                 
37             }
38     }
39 }

 

设置回滚点：

 1 import java.sql.*;
 2 
 3 public class TransactionDemo1 {
 4     public static void main(String[] args) throws SQLException, ClassNotFoundException {
 5         String url = "jdbc:mysql://localhost/xh";
 6         String username = "xiaohengdada";
 7         String password = "123456";
 8 
 9         Class.forName("org.gjt.mm.mysql.Driver");
10         Connection conn = null;
11         PreparedStatement st = null;
12         ResultSet rs = null;
13         Savepoint sp = null;
14 
15         try {
16             conn = DriverManager.getConnection(url, username, password);
17             conn.setAutoCommit(false);// 通知数据库开启事务(start transaction)
18 
19             String sql1 = "update account set money=money-100 where name='A'";
20             st = conn.prepareStatement(sql1);
21             st.executeUpdate();
22 
23             // 设置事务回滚点
24             // sp = conn.setSavepoint();
25 
26             String sql2 = "update account set money=money+100 where name='B'";
27             st = conn.prepareStatement(sql2);
28             st.executeUpdate();
29 
30             sp = conn.setSavepoint();
31             // 程序执行到这里出现异常，后面的sql3语句执行将会中断
32             int x = 1 / 0;
33 
34             String sql3 = "update account set money=money+100 where name='C'";
35             st = conn.prepareStatement(sql3);
36             st.executeUpdate();
37 
38             conn.commit();
39 
40         } catch (Exception e) {
41             try {
42                 /**
43                  * 我们在上面向数据库发送了3条update语句， sql3语句由于程序出现异常导致无法正常执行，数据库事务而已无法正常提交，
44                  * 由于设置的事务回滚点是在sql1语句正常执行完成之后，sql2语句正常执行之前,
45                  * 那么通知数据库回滚事务时，不会回滚sql1执行的update操作
46                  * 只会回滚到sql2执行的update操作，也就是说，上面的三条update语句中，sql1这条语句的修改操作起作用了
47                  * sql2的修改操作由于事务回滚没有起作用，sql3由于程序异常没有机会执行
48                  */
49                 conn.rollback(sp);// 回滚到设置的事务回滚点
50                 // 回滚了要记得通知数据库提交事务
51                 conn.commit();
52             } catch (SQLException e1) {
53                 e1.printStackTrace();
54             }
55             e.printStackTrace();
56         } finally {
57         }
58     }
59 }

 

posted @ 2016-07-21 18:53  笑哼  阅读(219)  评论(0)  编辑  收藏  举报