事务
在jdbc中使用使用的基本步骤:
调用 Connection 对象的 setAutoCommit(false) 以取消自动提交事务
在所有的 SQL 语句都成功执行后,调用 commit()方法提交事务
在出现异常时,调用 rollback()方法回滚事务
若此时 Connection 没有被关闭,还可能被重复使用,则需要恢复其自动提交状态 setAutoCommit(true)
在插入函数中增加事务
@Override
public int insertUser(User user) {
String sql = "insert into user_sql(name,password,birthday) value(?,?,?)";
Connection conn = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try{
System.out.println("sql------>"+sql);
conn= DBUtil.getConnection();
conn.setAutoCommit(false);//开启事务
statement=conn.prepareStatement(sql);
statement.setString(1,user.getUsername());
statement.setString(2,user.getPassword());
statement.setDate(3, new Date(new java.util.Date().getTime()));
int rows=statement.executeUpdate();
conn.commit();//提交事务
return rows;
} catch (SQLException throwables) {
throwables.printStackTrace();
try{
conn.rollback();//回滚
}catch (SQLException ex){
ex.printStackTrace();
}
return -1;
}finally {
DBUtil.closeAll(conn,statement,null);
}
}
事务应用(转账模块)
在dao层中的impl中UserDaoImpl中添加转钱函数
@Override
public int transfer(int id,int money,Connection conn){
String sql = "update user_sql set balance=balance+? where id=? ";
PreparedStatement statement = null;
ResultSet resultSet = null;
try{
System.out.println("sql------>"+sql);
conn.setAutoCommit(false);
statement=conn.prepareStatement(sql);
statement.setInt(1,money);
statement.setInt(2,id);
int rows=statement.executeUpdate();
return rows;
} catch (SQLException throwables) {
throwables.printStackTrace();
return -1;
}
在service层中的UserService定义接口
public interface UserService {
Boolean transferAccount(int formId,int toId,int money);
}
调用转钱函数实现两个用户之间进行转钱
public class UserServiceImpl implements UserService {
private UserDao userDao=new UserDaoImpl();
@Override
public Boolean transferAccount(int formId,int toId,int money){
Connection conn= DBUtil.getConnection();
try{
conn.setAutoCommit(false);
userDao.transfer(formId,-money,conn);
// int i=1/0;
userDao.transfer(toId,money,conn);
conn.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}finally {
DBUtil.closeAll(conn,null,null);
}
return null;
}
public static void main(String[] args) {
UserService userService=new UserServiceImpl();
userService.transferAccount(3,4,500);
}
}
其中当用户formId转向用户toId中发现异常时候,数据库会进行回滚。
优化1
对上面代码进行优化
public static Connection getConnection(){
String url="jdbc:mysql://127.0.0.1:3306/java_learn?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
String username="root";
String password="200625";
Connection conn=threadLocal.get();
//如果没有连接,创建一个
if(conn==null){
try{
Driver driver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(driver);
conn = DriverManager.getConnection(url, username, password);
if(conn ==null){
throw new RuntimeException("链接获取异常");
}
//创建之后加入threadLocal
threadLocal.set(conn);
}
catch (Exception exception){
exception.printStackTrace();
throw new RuntimeException("链接获取异常!");
}
}
return conn;
}
@Override
public int transfer(int id,int money){
String sql = "update user_sql set balance=balance+? where id=? ";
Connection conn=DBUtil.getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
try{
System.out.println("sql------>"+sql);
conn.setAutoCommit(false);
statement=conn.prepareStatement(sql);
statement.setInt(1,money);
statement.setInt(2,id);
int rows=statement.executeUpdate();
return rows;
} catch (SQLException throwables) {
throwables.printStackTrace();
return -1;
}finally {
DBUtil.closeAll(null,statement,null);
}
}
在utrl层中创建Transaction类
public class Transaction {
/**
* 开始方法
*/
public static void begin() {
Connection conn= DBUtil.getConnection();
try{
conn.setAutoCommit(false);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* 提交的方法
*/
public static void commit() {
Connection conn=DBUtil.getConnection();
try {
conn.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void rollback() {
Connection conn=DBUtil.getConnection();
try {
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void close() {
Connection conn=DBUtil.getConnection();
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public class UserServiceImpl implements UserService {
private UserDao userDao=new UserDaoImpl();
@Override
public Boolean transferAccount(int formId,int toId,int money){
Transaction.begin();
try{
userDao.transfer(formId,-money);
int i=1/0;
userDao.transfer(toId,money);
Transaction.commit();
return true;
} catch (Exception throwables) {
throwables.printStackTrace();
Transaction.rollback();
}finally {
Transaction.close();
}
return false;
}
public static void main(String[] args) {
UserService userService=new UserServiceImpl();
userService.transferAccount(3,4,500);
}
}
但是上面代码会出现一些问题,在同一个线程中连续执行两次后就会出现问题,因为在第一个中结束后关闭了连接,所以还需要再优化。
public static ThreadLocal THREADLOCAL=new ThreadLocal<>(); 改为公共权限
在关闭代码中增加 conn.close();
THREADLOCAL.remove();