事务实现
配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8
username=root
password=13579
initialSize=10
maxActive=50
minIdle=5
maxWait=10000
DbUtils
package utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* 2020/9/5
* 15:19
* zmx
*/
public class DbUtils {
private static DataSource dataSource;
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
static {
Properties properties = new Properties();
InputStream rs = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(rs);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException("连接池创建失败",e);
}
}
// 连接池返回
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
Connection conn = threadLocal.get();
// 第一次访问时连接必为空
try {
if (conn == null) {
conn = dataSource.getConnection();
threadLocal.set(conn);
}
return conn;
} catch (SQLException e) {
throw new RuntimeException("连接获取失败", e);
}
}
// 自动关闭,实际是将连接放回连接池,给 可能被使用事务的 增删改 方法使用 功能使用
public static void CloseAuto(Connection conn){
try {
if (conn != null) {
// 事务未开启时 关闭连接
if (conn.getAutoCommit()){
conn.close();;
threadLocal.remove();
}
}
} catch (SQLException e) {
throw new RuntimeException("连接关闭失败", e);
}
}
// 事务四方法
public static void begin(){
Connection conn = getConnection();
if (conn != null) {
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException("事务开启失败",e);
}
}
}
public static void commit(){
Connection conn = getConnection();
if (conn != null) {
try {
conn.commit();
} catch (SQLException e) {
throw new RuntimeException("事务提交失败",e);
}
}
}
public static void rollback(){
Connection conn = getConnection();
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
throw new RuntimeException("事务回滚失败",e);
}
}
}
public static void closeNotAuto(){
Connection conn = getConnection();
if (conn != null) {
try {
conn.close();
threadLocal.remove();
} catch (SQLException e) {
throw new RuntimeException("事务连接关闭失败",e);
}
}
}
}
domain
package domain;
/**
* 2020/9/5
* 14:31
* zmx
*/
public class Bank {
private int id;
private double money;
public Bank() {
}
public Bank(int id, double money) {
this.id = id;
this.money = money;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Bank{" +
"id=" + id +
", money=" + money +
'}';
}
}
DAO
public interface BankDao {
// 根据ID查询
boolean query(int id);
// 余额
double balance(Bank bank);
// 存取钱
void save(int id, double money);
void take(int id, double money);
}
package dao.impl;
import dao.BankDao;
import domain.Bank;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.DbUtils;
import java.sql.Connection;
import java.sql.SQLException;
/**
* 2020/9/5
* 16:12
* zmx
*/
public class BankDaoImpl implements BankDao {
// 查询语句直接使用连接池 增删改使用具体连接
QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
@Override
public boolean query(int id) {
try {
Bank query = qr.query("select id,money from bank where id = ?", new BeanHandler<>(Bank.class), id);
if (query != null) {
return true;
}
return false;
} catch (SQLException e) {
throw new RuntimeException("用户查询失败",e);
}
}
@Override
public double balance(Bank bank) {
try {
Object query = qr.query("select money from bank where id = ?", new ScalarHandler<>(), bank.getId());
return (double)query;
} catch (SQLException e) {
throw new RuntimeException("余额查询失败",e);
}
}
@Override
public void save(int id, double money) {
Connection conn = DbUtils.getConnection();
try {
qr.update(conn,"update bank set money =money + ? where id = ?" ,money,id);
} catch (SQLException e) {
throw new RuntimeException("存钱失败",e);
}finally {
DbUtils.CloseAuto(conn);
}
}
@Override
public void take(int id, double money) {
Connection conn = DbUtils.getConnection();
try {
qr.update(conn, "update bank set money =money + ? where id = ?", money, id);
} catch (SQLException e) {
throw new RuntimeException("扣钱失败", e);
}finally {
DbUtils.CloseAuto(conn);
}
}
}
Service
public interface BankService {
void trans(Bank bank, int toId, double money);
}
package service.impl;
import dao.BankDao;
import dao.impl.BankDaoImpl;
import domain.Bank;
import service.BankService;
import utils.DbUtils;
/**
* 2020/9/5
* 16:28
* zmx
*/
public class BankServiceImpl implements BankService {
private BankDao bankDao = new BankDaoImpl();
@Override
public void trans(Bank bank, int toId, double money) {
int id = bank.getId();
if (!bankDao.query(toId)){
throw new RuntimeException("对方不存在");
}
if (bankDao.balance(bank)<money){
throw new RuntimeException("余额不足");
}
try {
DbUtils.begin();
bankDao.take(id,money);
// 模拟可能发生的错误,测试事务回滚
int i = 10/0;
bankDao.save(toId,money);
DbUtils.commit();
} catch (Exception e) {
DbUtils.rollback();
System.out.println("事务回滚成功");
throw e;
} finally {
DbUtils.closeNotAuto();
}
}
}
Test
public class Test {
public static void main(String[] args) {
Bank bank = new Bank(1,0);
BankService bankService = new BankServiceImpl();
try {
bankService.trans(bank,2,1000);
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
}
}
自定义工具类
配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&charterEncoding
user=root
password=13579
DriverManager获取链接,内省反射实现对象属性赋值
package utils;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.Properties;
/**
* 2020/9/5
* 13:59
* zmx
*/
public class DbUtil {
private static String driver;
private static String url;
private static String user;
private static String password;
static {
Properties properties = new Properties();
InputStream rs = DbUtil.class.getClassLoader().getResourceAsStream("db.properties");
try {
properties.load(rs);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException("注册驱动失败",e);
}
}
public static Connection getConnection(){
Connection connection = null;
try {
connection= DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
throw new RuntimeException("获取连接失败",e);
}
return connection;
}
public static void update(String sql,Object...params){
Connection conn = getConnection();
try {
PreparedStatement ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
}
ps.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("数据库更新失败",e);
}
}
public static <T> T query(Class<T> clazz,String sql, Object...params){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
conn =getConnection();
T t = null;
try {
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
}
rs = ps.executeQuery();
// 获得结果集原数据
ResultSetMetaData metaData = rs.getMetaData();
while (rs.next()){
t=clazz.newInstance();
for (int i = 0; i < metaData.getColumnCount(); i++) {
// 获得结果集列名
String columnLabel = metaData.getColumnLabel(i + 1);
// 使用内省
PropertyDescriptor pd = new PropertyDescriptor(columnLabel,clazz);
if (pd != null) {
Method writeMethod = pd.getWriteMethod();
writeMethod.invoke(t,rs.getObject(columnLabel));
}else {
continue;
}
}
}
}catch (Exception e) {
throw new RuntimeException("查询失败",e);
}
return t;
}
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs){
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException("连接关闭失败",e);
}
}
// 关闭连接
public static void closeAll(Connection conn, PreparedStatement ps, ResultSet rs){
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException("连接关闭失败",e);
}
}
}