JDBC implementation of transaction submission and rollback examples

 1 package cn.xiangxu.entity; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.util.Scanner; 6  7 import cn.xiangxu.tools.DBUtil; 8  9 public class Test {10     public static void main(String[] args) {11         Scanner scanner = new Scanner(System.in);12         System.out.println("请输入转出用户名：");13         String outName = scanner.nextLine();14         System.out.println("请输入需要转出的资金额度：");15         Double money = Double.parseDouble(scanner.nextLine());16         System.out.println("请输入转入用户名：");17         String inName = scanner.nextLine();18         System.out.println("转出账户为：" + outName + "转出金额为：" + money + "转入账户为：" + inName);19         20         21         Connection conn = null;22         try {23             conn = DBUtil.getConnection(); // 实例化连接对象24             25 //            conn.setAutoCommit(false); // 关闭自动提交事务功能26             27             String sql = "UPDATE client "28                     + "SET account = account - ? " 
29                     + "WHERE name = ? ";30             PreparedStatement ps = conn.prepareStatement(sql);31             ps.setDouble(1, money);32             ps.setString(2, outName);33             Integer rs = ps.executeUpdate();34             if(rs > 0) {35                 System.out.println("转出成功");36             } else {37                 System.out.println("转出失败");38                 return; // 转出失败跳出函数，不再执行下面的语句；但是finally中的语句还是会执行的，因为就算天塌下来finally中的语句都会执行39             }40             41             System.out.println("======分割线=======");42             43             String sql_in = "UPDATE client "44                     + "SET account = account + ? " 
45                     + "WHERE name = ? ";46             PreparedStatement ps_in = conn.prepareStatement(sql_in);47             ps_in.setDouble(1, money);48             ps_in.setString(2, inName);49             Integer judge_in = ps_in.executeUpdate();50             if(judge_in > 0) {51                 System.out.println("转入成功");52 //                conn.commit(); // 转出、转入都成功就提交事务53             } else {54                 System.out.println("转入失败");55 //                conn.rollback(); // 转出成功、转入失败就回滚事务56             }57             58 //            conn.setAutoCommit(true); // 打开自动提交事务59             60         } catch (Exception e) {61             // TODO Auto-generated catch block62             e.printStackTrace();63         } finally {64             System.out.println("我是finally中的语句哟");65             try {66                 DBUtil.closeConnection();67             } catch (Exception e) {68                 // TODO Auto-generated catch block69                 e.printStackTrace();70             }71         }72     }73 }

1 CREATE TABLE client  (2     id INT (10)  PRIMARY KEY,3     name VARCHAR (10),4     pwd VARCHAR (10),5     account INT (20)6 );

 1 package cn.xiangxu.tools; 2  3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.sql.Connection; 6 import java.sql.SQLException; 7 import java.util.Properties; 8  9 import org.apache.commons.dbcp.BasicDataSource;10 11 public class DBUtil {12     /*13      * ThreadLocal用于线程跨方法共享数据使用14      * ThreadLocal内部有一个Map,  key为需要共享数据的线程本身,value就是其需要共享的数据15      */16     private static ThreadLocal<Connection> tl; // 声明一个类似于仓库的东西17     private static BasicDataSource dataSource; // 声明一个数据库连接池对象18     19     // 静态代码块，在类加载的时候执行，而且只执行一次20     static {21         tl = new ThreadLocal<Connection>(); // 实例化仓库对象22         dataSource = new BasicDataSource(); // 实例数据库连接池对象23 24         Properties prop = new Properties(); // 创建一个Properties对象用（该对象可以用来加载配置文件中的属性列表）25         InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("config/mysql.properties"); // 读取配置文件信息26         try {27             prop.load(is); // 加载配置文件中的属性列表28             29             String driverClassName = prop.getProperty("driverClassName"); // 获取属性信息30             String url = prop.getProperty("url");31             String username = prop.getProperty("username");32             String password = prop.getProperty("password");33             Integer maxActive = Integer.parseInt(prop.getProperty("maxActive"));34             Integer maxWait = Integer.parseInt(prop.getProperty("maxWait"));35             36             dataSource.setDriverClassName(driverClassName); // 初始化数据库连接池（即：配置数据库连接池的先关参数）37             dataSource.setUrl(url);38             dataSource.setUsername(username);39             dataSource.setPassword(password);40             dataSource.setMaxActive(maxActive);41             dataSource.setMaxWait(maxWait);42             43             is.close(); // 关闭输入流，释放资源44         } catch (IOException e) {45             // TODO Auto-generated catch block46             e.printStackTrace();47         } 
48         49     }50     51     /**52      * 创建连接对象（注意：静态方法可以直接通过类名来调用）53      * @return 连接对象54      * @throws Exception55      */56     public static Connection getConnection() throws Exception { 
57         try {58             Connection conn = dataSource.getConnection(); // 创建连接对象（利用数据库连接池进行创建）59             tl.set(conn); // 将连接对象放到仓库中60             return conn; 
61         } catch (Exception e) {62             // TODO Auto-generated catch block63             e.printStackTrace();64             throw e;65         }66     }67     68     /**69      * 关闭连接对象（注意：静态方法可以通过类名直接调用）70      * @throws Exception71      */72     public static void closeConnection() throws Exception {73         Connection conn = tl.get(); // 从仓库中取出连接对象74         tl.remove(); // 清空仓库75         if(conn != null) { // 判断连接对象是否释放资源76             try {77                 conn.close();78             } catch (Exception e) {79                 // TODO Auto-generated catch block80                 e.printStackTrace();81                 throw e;82             }83         }84     }85 86 }

1 # zhe shi zhu shi , yi ban bu yong zhong wen 
2 # deng hao liang bian mei you kong ge, mo wei mei you fen hao3 # hou mian bu neng you kong ge4 driverClassName=com.mysql.jdbc.Driver5 url=jdbc:mysql://localhost:3306/test6 username=root7 password=1828388 maxActive=1009 maxWait=3000

 1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 "> 2   <modelVersion>4.0.0</modelVersion> 3   <groupId>cn.xiangxu</groupId> 4   <artifactId>testJDBC</artifactId> 5   <version>0.0.1-SNAPSHOT</version> 6   <dependencies> 7       <dependency> 8           <groupId>mysql</groupId> 9           <artifactId>mysql-connector-java</artifactId>10           <version>5.1.37</version>11       </dependency>12       <dependency>13           <groupId>junit</groupId>14           <artifactId>junit</artifactId>15           <version>4.12</version>16       </dependency>17       <dependency>18           <groupId>commons-dbcp</groupId>19           <artifactId>commons-dbcp</artifactId>20           <version>1.4</version>21       </dependency>22   </dependencies>23 </project>

 1 package cn.xiangxu.entity; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.SQLException; 6 import java.util.Scanner; 7  8 import cn.xiangxu.tools.DBUtil; 9 10 public class Test {11     public static void main(String[] args) {12         Scanner scanner = new Scanner(System.in);13         System.out.println("请输入转出用户名：");14         String outName = scanner.nextLine();15         System.out.println("请输入需要转出的资金额度：");16         Double money = Double.parseDouble(scanner.nextLine());17         System.out.println("请输入转入用户名：");18         String inName = scanner.nextLine();19         System.out.println("转出账户为：" + outName + "转出金额为：" + money + "转入账户为：" + inName);20         21         22         Connection conn = null;23         try {24             conn = DBUtil.getConnection(); // 实例化连接对象25             26             conn.setAutoCommit(false); // 关闭自动提交事务功能27             28             String sql = "UPDATE client "29                     + "SET account = account - ? " 
30                     + "WHERE name = ? ";31             PreparedStatement ps = conn.prepareStatement(sql);32             ps.setDouble(1, money);33             ps.setString(2, outName);34             Integer rs = ps.executeUpdate();35             if(rs > 0) {36                 System.out.println("转出成功");37             } else {38                 System.out.println("转出失败");39                 return; // 转出失败跳出函数，不再执行下面的语句；但是finally中的语句还是会执行的，因为就算天塌下来finally中的语句都会执行40             }41             42             System.out.println("======分割线=======");43             44             String sql_in = "UPDATE client "45                     + "SET account = account + ? " 
46                     + "WHERE name = ? ";47             PreparedStatement ps_in = conn.prepareStatement(sql_in);48             ps_in.setDouble(1, money);49             ps_in.setString(2, inName);50             Integer judge_in = ps_in.executeUpdate();51             if(judge_in > 0) {52                 System.out.println("转入成功");53                 conn.commit(); // 转出、转入都成功就提交事务54             } else {55                 System.out.println("转入失败");56                 conn.rollback(); // 转出成功、转入失败就回滚事务57             }58             59             conn.setAutoCommit(true); // 打开自动提交事务60             61         } catch (Exception e) {62             // TODO Auto-generated catch block63             try {64                 conn.rollback(); // 捕获到异常后也需要进行事务回滚65             } catch (SQLException e1) {66                 // TODO Auto-generated catch block67                 e1.printStackTrace();68             } 
69             e.printStackTrace();70         } finally {71             System.out.println("我是finally中的语句哟");72             try {73                 DBUtil.closeConnection();74             } catch (Exception e) {75                 // TODO Auto-generated catch block76                 e.printStackTrace();77             }78         }79     }80 }

  1 package cn.xiangxu.tools;  2   3 import java.io.IOException;  4 import java.io.InputStream;  5 import java.sql.Connection;  6 import java.sql.SQLException;  7 import java.util.Properties;  8   9 import org.apache.commons.dbcp.BasicDataSource; 10  11 public class DBUtil { 12     /* 13      * ThreadLocal用于线程跨方法共享数据使用 14      * ThreadLocal内部有一个Map,  key为需要共享数据的线程本身,value就是其需要共享的数据 15      */ 16     private static ThreadLocal<Connection> tl; // 声明一个类似于仓库的东西 17     private static BasicDataSource dataSource; // 声明一个数据库连接池对象 18      19     // 静态代码块，在类加载的时候执行，而且只执行一次 20     static { 21         tl = new ThreadLocal<Connection>(); // 实例化仓库对象 22         dataSource = new BasicDataSource(); // 实例数据库连接池对象 23  24         Properties prop = new Properties(); // 创建一个Properties对象用（该对象可以用来加载配置文件中的属性列表） 25         InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("config/mysql.properties"); // 读取配置文件信息 26         try { 27             prop.load(is); // 加载配置文件中的属性列表 28              29             String driverClassName = prop.getProperty("driverClassName"); // 获取属性信息 30             String url = prop.getProperty("url"); 31             String username = prop.getProperty("username"); 32             String password = prop.getProperty("password"); 33             Integer maxActive = Integer.parseInt(prop.getProperty("maxActive")); 34             Integer maxWait = Integer.parseInt(prop.getProperty("maxWait")); 35              36             dataSource.setDriverClassName(driverClassName); // 初始化数据库连接池（即：配置数据库连接池的先关参数） 37             dataSource.setUrl(url); 38             dataSource.setUsername(username); 39             dataSource.setPassword(password); 40             dataSource.setMaxActive(maxActive); 41             dataSource.setMaxWait(maxWait); 42              43             is.close(); // 关闭输入流，释放资源 44         } catch (IOException e) { 45             // TODO Auto-generated catch block 46             e.printStackTrace(); 47         } 
 48          49     } 50      51     /** 52      * 创建连接对象（注意：静态方法可以直接通过类名来调用） 53      * @return 连接对象 54      * @throws Exception 55      */ 56     public static Connection getConnection() throws Exception { 
 57         try { 58             Connection conn = dataSource.getConnection(); // 创建连接对象（利用数据库连接池进行创建） 59             tl.set(conn); // 将连接对象放到仓库中 60             return conn; 
 61         } catch (Exception e) { 62             // TODO Auto-generated catch block 63             e.printStackTrace(); 64             throw e; 65         } 66     } 67      68     /** 69      * 关闭连接对象（注意：静态方法可以通过类名直接调用） 70      * @throws Exception 71      */ 72     public static void closeConnection() throws Exception { 73         Connection conn = tl.get(); // 从仓库中取出连接对象 74         tl.remove(); // 清空仓库 75         if(conn != null) { // 判断连接对象是否释放资源 76             try { 77                 conn.close(); 78             } catch (Exception e) { 79                 // TODO Auto-generated catch block 80                 e.printStackTrace(); 81                 throw e; 82             } 83         } 84     } 85      86     /** 87      * 在执行SQL语句前关闭JDBC的自动提交事务功能 88      * @throws SQLException 89      */ 90     public static void tansBegin() throws SQLException { 91         try { 92             tl.get().setAutoCommit(false); // 从仓库中获取连接对象并调用setAutoCommit来关闭自动提交事务功能 93         } catch(SQLException e) { 94             e.printStackTrace(); 95             throw e; 96         } 97     } 98      99     /**100      * 手动回滚功能101      * @throws SQLException102      */103     public static void transBack() throws SQLException {104         tl.get().rollback(); // 从仓库中获取连接对象并调用rollback来实现事务回滚操作105         tl.get().setAutoCommit(true); // 回滚启动事务自动提交功能106     }107     108     /**109      * 手动提交功能110      * @throws SQLException111      */112     public static void transCommit() throws SQLException {113         tl.get().commit(); // 从仓库中获取连接对象并调用commit来实现事务提交操作114         tl.get().setAutoCommit(true); // 提交后启动事务自动提交功能115     }116 117 }

 1 package cn.xiangxu.entity; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.SQLException; 6 import java.util.Scanner; 7  8 import cn.xiangxu.tools.DBUtil; 9 10 public class Test {11     public static void main(String[] args) {12         Scanner scanner = new Scanner(System.in);13         System.out.println("请输入转出用户名：");14         String outName = scanner.nextLine();15         System.out.println("请输入需要转出的资金额度：");16         Double money = Double.parseDouble(scanner.nextLine());17         System.out.println("请输入转入用户名：");18         String inName = scanner.nextLine();19         System.out.println("转出账户为：" + outName + "转出金额为：" + money + "转入账户为：" + inName);20         21         22         Connection conn = null;23         try {24             conn = DBUtil.getConnection(); // 实例化连接对象25             26             DBUtil.tansBegin(); // 关闭自动提交事务功能27             28             String sql = "UPDATE client "29                     + "SET account = account - ? " 
30                     + "WHERE name = ? ";31             PreparedStatement ps = conn.prepareStatement(sql);32             ps.setDouble(1, money);33             ps.setString(2, outName);34             Integer rs = ps.executeUpdate();35             if(rs > 0) {36                 System.out.println("转出成功");37             } else {38                 System.out.println("转出失败");39                 return; // 转出失败跳出函数，不再执行下面的语句；但是finally中的语句还是会执行的，因为就算天塌下来finally中的语句都会执行40             }41             42             System.out.println("======分割线=======");43             44             String sql_in = "UPDATE client "45                     + "SET account = account + ? " 
46                     + "WHERE name = ? ";47             PreparedStatement ps_in = conn.prepareStatement(sql_in);48             ps_in.setDouble(1, money);49             ps_in.setString(2, inName);50             Integer judge_in = ps_in.executeUpdate();51             if(judge_in > 0) {52                 System.out.println("转入成功");53                 DBUtil.transCommit(); // 转出、转入都成功就提交事务54             } else {55                 System.out.println("转入失败");56                 DBUtil.transBack(); // 转出成功、转入失败就回滚事务57             }58             59         } catch (Exception e) {60             // TODO Auto-generated catch block61             try {62                 DBUtil.transBack();// 捕获到异常后也需要进行事务回滚63             } catch (SQLException e1) {64                 // TODO Auto-generated catch block65                 e1.printStackTrace();66             } 
67             e.printStackTrace();68         } finally {69             System.out.println("我是finally中的语句哟");70             try {71                 DBUtil.closeConnection();72             } catch (Exception e) {73                 // TODO Auto-generated catch block74                 e.printStackTrace();75             }76         }77     }78 }