트랜잭션 제출 및 롤백 예제의 JDBC 구현
- 零下一度원래의
- 2017-07-20 19:05:113297검색
1. Connection 클래스에서 일반적으로 사용되는 메소드 검토
1.1 명령문 createStatement()가 SQLException을 발생시킵니다.
명령문 인스턴스 생성(예: SQL 실행 객체 생성)
1.2 ReadyStatement prepareStatement(String sql) throws SQLException ;
PreparedStatement 객체 생성(예: 사전 컴파일된 SQL 실행 객체 생성)
1.3 void setAutoCommit(boolean autoCommit) throws SQLException;
트랜잭션 자동 제출 설정(false는 자동 제출을 끄는 것을 의미, true 자동 제출을 시작하려면)
1.4 void commit ()가 sqlexception을 던지기 transfer case
이체 아웃과 이체 인은 하나의 거래입니다. 이체 아웃이 성공했지만 이체 인이 실패하면 트랜잭션을 롤백해야 합니다. 그렇지 않으면 이체자의 잔액은 줄어들지만 이체자의 잔액은 줄어들지 않습니다.
참고: 트랜잭션 롤백 제출은
Connection에서 제공하는메소드를 통해 호출됩니다. 기본적으로 트랜잭션은
데이터베이스 구현에 의존합니다.Connection 메소드는 기본적으로 데이터베이스 트랜잭션 메커니즘을 호출합니다.
2.1 거래관리 미사용 이체업무단점 : 이체 성공, 이체 실패 시 이체자의 잔액은 줄어들지만 이체자의 잔액은 그대로 유지
프로젝트 구조도
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 }
비즈니스 Java 소스 코드 전송
1 CREATE TABLE client (2 id INT (10) PRIMARY KEY,3 name VARCHAR (10),4 pwd VARCHAR (10),5 account INT (20)6 );
SQL 문
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 소스 코드
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>maven 종속 파일
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 소스 코드 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 }
비즈니스 Java 소스 코드 전송위 내용은 트랜잭션 제출 및 롤백 예제의 JDBC 구현의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!