自定义事务控制
转账案例
我们先用springboot+druid连接池模拟一个转账的问题。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.12</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.duohoob</groupId>
<artifactId>spring</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring</name>
<description>for spring</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.12</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
DruidHelper
package com.duohoob.spring.helper;
import com.alibaba.druid.pool.DruidDataSource;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
public class DruidHelper {
private DruidHelper() {
}
private static DruidDataSource druidDataSource = new DruidDataSource();
static {
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql://localhost:3306/spring");
druidDataSource.setUsername("root");
druidDataSource.setPassword("root123");
}
public static DruidDataSource getInstance() {
return druidDataSource;
}
}
BankAccountDao
package com.duohoob.spring.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.springframework.stereotype.Repository;
import com.duohoob.spring.helper.DruidHelper;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
@Repository
public class BankAccountDao {
public void updateByAccountNo(String accountNo, String money) throws Exception {
// TODO Auto-generated method stub
Connection connection = DruidHelper.getInstance().getConnection();
String sql = "UPDATE bank_account SET money = money + ? WHERE account_no = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, money);
statement.setString(2, accountNo);
statement.executeUpdate();
// 关闭
statement.close();
connection.close();
}
}
BankAccountService
这里有个人为制造的bug：System.out.println(1/0);
package com.duohoob.spring.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.duohoob.spring.dao.BankAccountDao;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
@Service
public class BankAccountService {
@Autowired
private BankAccountDao bankAccountDao;
public void transfer() throws Exception {
// TODO Auto-generated method stub
// 张三向李四转账100
bankAccountDao.updateByAccountNo("张三", "-100");
System.out.println(1/0);
bankAccountDao.updateByAccountNo("李四", "+100");
}
}
BankAccountController
package com.duohoob.spring.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.duohoob.spring.service.BankAccountService;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
@RestController
@RequestMapping("/bankAccount")
public class BankAccountController {
@Autowired
private BankAccountService bankAccountService;
@RequestMapping("/transfer")
public String transfer() throws Exception {
bankAccountService.transfer();
return "success";
}
}
bank_account
测试
启动springboot，然后访问：http://localhost:8080/bankAccount/transfer，
我们再看数据库，发现张三的钱少了100，但是李四的钱并没有增加100，
我们接下来就是要解决这个问题。
添加事务控制
实现事务控制，归根结底要通过操作Connection来实现，
Connection.commit，提交事务、Connection.rollback，回滚事务，Connection默认自动提交。
如果要用同一个事务控制两个update，那么就必须使用同一个数据库连接Connection，
怎么做呢？
我们给当前线程当前线程绑定一个数据库连接Connection，线程内数据库操作都使用这同一个Connection。
如何实现？
修改获取数据库连接Connection的方式。
添加
ConnectionHelper
package com.duohoob.spring.helper;
import java.sql.Connection;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
public class ConnectionHelper {
private ConnectionHelper() {
}
private static ConnectionHelper connectionHelper = new ConnectionHelper();
public static ConnectionHelper getInstance() {
return connectionHelper;
}
/**
* 维护当前线程的变量
*/
private ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
/**
* 获取当前线程的Connection
* @return
* @throws Exception
*/
public Connection getCurrentConn() throws Exception {
Connection connection = threadLocal.get();
if (null == connection) {
connection = DruidHelper.getInstance().getConnection();
// 存入ThreadLocal
threadLocal.set(connection);
}
return connection;
}
}
修改
BankAccountDao
package com.duohoob.spring.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.springframework.stereotype.Repository;
import com.duohoob.spring.helper.ConnectionHelper;
import com.duohoob.spring.helper.DruidHelper;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
@Repository
public class BankAccountDao {
public void updateByAccountNo(String accountNo, String money) throws Exception {
// TODO Auto-generated method stub
// Connection connection = DruidHelper.getInstance().getConnection();
// 获取当前线程绑定的Connection
Connection connection = ConnectionHelper.getInstance().getCurrentConn();
String sql = "UPDATE bank_account SET money = money + ? WHERE account_no = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, money);
statement.setString(2, accountNo);
statement.executeUpdate();
// 关闭
// statement.close();
// connection.close();
}
}
修改
BankAccountService
package com.duohoob.spring.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.duohoob.spring.dao.BankAccountDao;
import com.duohoob.spring.helper.ConnectionHelper;
/**
* @author yangwei
*
* @date 2022年10月5日
*/
@Service
public class BankAccountService {
@Autowired
private BankAccountDao bankAccountDao;
public void transfer() throws Exception {
// TODO Auto-generated method stub
try {
// 关闭Connection事务自动提交
ConnectionHelper.getInstance().getCurrentConn().setAutoCommit(false);
// 张三向李四转账100
bankAccountDao.updateByAccountNo("张三", "-100");
System.out.println(1/0);
bankAccountDao.updateByAccountNo("李四", "+100");
// 提交事务
ConnectionHelper.getInstance().getCurrentConn().commit();
} catch (Exception e) {
// TODO: handle exception
// 回滚事务
ConnectionHelper.getInstance().getCurrentConn().rollback();
System.out.println("发生异常，事务已回滚。");
e.printStackTrace();
}
}
}
再运行测试发现问题解决。