本文通过一个转账异常的例子，介绍了在Java JDBC中如何管理事务。在未使用事务的情况下，转账操作出现错误导致数据不一致。通过添加事务处理，确保了在异常发生时能够回滚，保持数据的一致性。
相关文章
讲事务之前，我们先看一个例子：转账异常
jdbc工具类代码
package com.lingaolu.Utils;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* @author 林高禄
* @create 2020-06-23-11:12
*/
public class JdbcUtils {
private static String driver;
private static String url;
private static String userName;
private static String pw;
static{
try {
Properties p = new Properties();
ClassLoader classLoader = JdbcUtils.class.getClassLoader();
// 这个路径相对于src的路径来说
URL resource = classLoader.getResource("com/lingaolu/file/jdbc.properties");
String path = resource.getPath();
p.load(new FileReader(path));
driver = p.getProperty("driver");
url = p.getProperty("url");
userName = p.getProperty("user");
pw = p.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection createConnection() throws SQLException {
return DriverManager.getConnection(url, userName, pw);
}
public static void close(Statement stmt,Connection con){
if(null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null != con){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet set,Statement s,Connection con){
if(null != set){
try {
set.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(s,con);
}
}
Account实体类代码
package com.lingaolu.jdbcConnector;
/**
* @author 林高禄
* @create 2020-06-24-8:28
*/
public class Account {
private int id;
private String name;
private double balance;
private int myAge;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public int getMyAge() {
return myAge;
}
public void setMyAge(int myAge) {
this.myAge = myAge;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", balance=" + balance +
", myAge=" + myAge +
'}';
}
}
测试demo6的代码
- 查询打印李四和林帅的数据的方法fineAccount()
- 转账的方法transferAccounts()：把李四的500元转给林帅，李四把钱转出去后，人工制造除数为0的异常，是的林帅收不到钱
- 主方法：先打印，再转账，接着打印
package com.lingaolu.jdbcConnector;
import com.lingaolu.Utils.JdbcUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author 林高禄
* @create 2020-06-24-09:04
*/
public class Demo6 {
public static void main(String[] args) {
System.out.println("转账前");
fineAccount();
transferAccounts();
System.out.println("转账后");
fineAccount();
}
public static void fineAccount(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
List<Account> rerurnList = new ArrayList<>();
try {
con = JdbcUtils.createConnection();
// 定义sql
String sql = "select * from account where name=? or name=?";
// 获取执行的sql
pstmt = con.prepareStatement(sql);
System.out.println(sql);
// 给?赋值,setString表示类型，第一个参数表示在参数中的位置，第二个参数表示值
pstmt.setString(1,"李四");
pstmt.setString(2,"林帅");
// 执行查询，不需调传递sql
resultSet = pstmt.executeQuery();
Account acc = null;
while(resultSet.next()){
// 引号里的字段要与表里的一样
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
double balance = resultSet.getDouble("balance");
int age = resultSet.getInt("age");
acc = new Account();
acc.setId(id);
acc.setName(name);
acc.setBalance(balance);
acc.setMyAge(age);
rerurnList.add(acc);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(resultSet,pstmt,con);
}
rerurnList.forEach(System.out::println);
}
public static void transferAccounts() {
Connection con = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
con = JdbcUtils.createConnection();
// 定义sql
String sql1 = "update account set balance=balance-500 where name=?";
String sql2 = "update account set balance=balance+500 where name=?";
// 获取执行的sql
pstmt1 = con.prepareStatement(sql1);
pstmt2 = con.prepareStatement(sql2);
// 给?赋值,setString表示类型，第一个参数表示在参数中的位置，第二个参数表示值
pstmt1.setString(1, "李四");
pstmt2.setString(1, "林帅");
// 执行查询，不需调传递sql
pstmt1.executeUpdate();
int a = 0/0;
pstmt2.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.close(pstmt1, con);
JdbcUtils.close(pstmt2, con);
}
}
}
运行输出：
select * from account where name=? or name=?
Account{id=2, name='李四', balance=1500.0, myAge=16}
Account{id=11, name='林帅', balance=20000.0, myAge=18}
java.lang.ArithmeticException: / by zero
at com.lingaolu.jdbcConnector.Demo6.transferAccounts(Demo6.java:76)
at com.lingaolu.jdbcConnector.Demo6.main(Demo6.java:16)
select * from account where name=? or name=?
Account{id=2, name='李四', balance=1000.0, myAge=16}
Account{id=11, name='林帅', balance=20000.0, myAge=18}
从输出上看出，转账后李四的钱少了500，但是林帅的钱没有增加，这在实际上的业务中是有问题，所以我们要加事务，在转账的方法中加事务，修改后的代码如下
package com.lingaolu.jdbcConnector;
import com.lingaolu.Utils.JdbcUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author 林高禄
* @create 2020-06-24-09:04
*/
public class Demo6 {
public static void main(String[] args) {
System.out.println("转账前");
fineAccount();
transferAccounts();
System.out.println("转账后");
fineAccount();
}
public static void fineAccount(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
List<Account> rerurnList = new ArrayList<>();
try {
con = JdbcUtils.createConnection();
// 定义sql
String sql = "select * from account where name=? or name=?";
// 获取执行的sql
pstmt = con.prepareStatement(sql);
System.out.println(sql);
// 给?赋值,setString表示类型，第一个参数表示在参数中的位置，第二个参数表示值
pstmt.setString(1,"李四");
pstmt.setString(2,"林帅");
// 执行查询，不需调传递sql
resultSet = pstmt.executeQuery();
Account acc = null;
while(resultSet.next()){
// 引号里的字段要与表里的一样
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
double balance = resultSet.getDouble("balance");
int age = resultSet.getInt("age");
acc = new Account();
acc.setId(id);
acc.setName(name);
acc.setBalance(balance);
acc.setMyAge(age);
rerurnList.add(acc);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(resultSet,pstmt,con);
}
rerurnList.forEach(System.out::println);
}
public static void transferAccounts() {
Connection con = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
con = JdbcUtils.createConnection();
// 开启事务
con.setAutoCommit(false);
// 定义sql
String sql1 = "update account set balance=balance-500 where name=?";
String sql2 = "update account set balance=balance+500 where name=?";
// 获取执行的sql
pstmt1 = con.prepareStatement(sql1);
pstmt2 = con.prepareStatement(sql2);
// 给?赋值,setString表示类型，第一个参数表示在参数中的位置，第二个参数表示值
pstmt1.setString(1, "李四");
pstmt2.setString(1, "林帅");
// 执行查询，不需调传递sql
pstmt1.executeUpdate();
int a = 0/0;
pstmt2.executeUpdate();
// 没有问题提交事务
con.commit();
} catch (Exception e) {
e.printStackTrace();
// 捕获异常回滚
if(null != con){
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
JdbcUtils.close(pstmt1, con);
JdbcUtils.close(pstmt2, con);
}
}
}
运行输出：
select * from account where name=? or name=?
Account{id=2, name='李四', balance=1500.0, myAge=16}
Account{id=11, name='林帅', balance=20000.0, myAge=18}
java.lang.ArithmeticException: / by zero
at com.lingaolu.jdbcConnector.Demo6.transferAccounts(Demo6.java:80)
at com.lingaolu.jdbcConnector.Demo6.main(Demo6.java:17)
转账后
select * from account where name=? or name=?
Account{id=2, name='李四', balance=1500.0, myAge=16}
Account{id=11, name='林帅', balance=20000.0, myAge=18}
从输出中我们可以看出
出现异常了，所以李四和林帅的钱都没有变，因为回滚了，符合了实际的业务操作
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
