JDBC implementation of transaction submission and rollback examples

1. Review of commonly used methods in the Connection class

　1.1 Statement createStatement() throws SQLException;

　 Create a Statement instance (ie: create a SQL execution object)

　1.2 PreparedStatement prepareStatement(String sql) throws SQLException ;

　　Create a PreparedStatement object (ie: create a precompiled SQL execution object)

　1.3 void setAutoCommit(boolean autoCommit) throws SQLException;

　　Set the automatic submission of the transaction (false means to turn off automatic submission, true to start automatic submission)

　　1.4 void commit() throws SQLException;

　　 Manually commit transaction

　1.5 void rollback() throws SQLException;

　　 Manual rollback of transaction

2 Classic case where transaction rollback is required: Bank transfer case

　Transfer out and transfer in are one transaction. If the transfer out is successful but the transfer in fails, the transaction needs to be rolled back. Otherwise, the balance of the transferor will decrease but the balance of the transferor will not increase.

　Note: Submission of the transaction Rollback is called through the method provided by Connection; essentially the transaction still relies on the implementation of the database; The method of Connection essentially calls the database transaction mechanism.

　　2.1 Not using transaction control Transfer business

Disadvantages: If the transfer is successful, but the transfer fails, the balance of the transferor will be reduced, but the balance of the transferor will remain unchanged

Project structure chart

　　　

 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 }

Transfer business java source code

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

SQL statement

 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 }

java source code of database connection pool

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

Database information file

 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>

maven dependency files

　　2.2 Transfer business using transaction control

 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 }

Java source code of transfer business

　2.3 Encapsulate the automatic submission function, manual submission function, and manual rollback function into In one class

  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 }

DBUtil

 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 }

Transfer business java source code

The above is the detailed content of JDBC implementation of transaction submission and rollback examples. For more information, please follow other related articles on the PHP Chinese website!