27.3 JDBC 事务的处理

目录

1 JDBC 事务的处理

1.1 事务api介绍

1.2 事务练习

1.2.1 封装JDBC工具类

1.2.2 代码实现

---

1 JDBC 事务的处理

准备数据

--  创建表
CREATE TABLE account(  -- 创建一个取款机
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),  -- 用户名
	balance DOUBLE     -- 钱数
);
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

 

1.1 事务api介绍

Connection  接口中与事务有关的方法

1. void setAutoCommit(boolean autoCommit)  参数是 true 或 false。如果设置为 false，表示关闭自动提交，相当于开启事务
2. void commit() 提交事务
3. void rollback()  回滚事务

1.2 事务练习

项目结构：

1.2.1 封装JDBC工具类

src目录下创建 jdbc.properties文件

url=jdbc:mysql:///db3
user=root
password=123
driver=com.mysql.jdbc.Driver

 创建JDBC工具类：JDBCUtil.java

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类
 */
public class JDBCUtil {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            //1 读取配置文件
            Properties properties = new Properties();//创建Properties集合
            // pro.load(new FileReader("D:\\IdeaProjects\\itcast\\day04_jdbc\\src\\jdbc.properties"));

            //2 获取src路径下的文件方式-- ClassLoader 类加载器
            ClassLoader classLoader = JDBCUtil.class.getClassLoader();//获取类加载器
            URL resource = classLoader.getResource("jdbc.properties");//通过类加载器获取jdbc.properties文件的url路径
            String path = resource.getPath();//将获取到的url路径转化成字符串形式
            // System.out.println(path);///D:/IdeaProjects/lws/out/production/day04_jdbc/jdbc.properties
            properties.load(new FileReader(path));
            //3 获取数据 赋值
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            //4  注册驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 增删改操作释放资源
     */
    public static void close(Connection connection, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

1.2.2 代码实现

import com.lws.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 事务操作
 */
public class JDBCDemo10 {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement1=null;
        PreparedStatement preparedStatement2=null;
        try {
            connection= JDBCUtil.getConnection();
            // 开启事务
            connection.setAutoCommit(false);
            //2.1 张三 - 500
            String sql1 = "update account set balance = balance - ? where id = ?";
            //2.2 李四 + 500
            String sql2 = "update account set balance = balance + ? where id = ?";
            //3 获取执行sql对象
            preparedStatement1=connection.prepareStatement(sql1);
            preparedStatement2=connection.prepareStatement(sql2);
            //4 设置参数
            preparedStatement1.setDouble(1,500);
            preparedStatement1.setInt(2,1);
            preparedStatement2.setDouble(1,500);
            preparedStatement2.setInt(2,2);
            //5 执行sql
            preparedStatement1.executeUpdate();
            preparedStatement2.executeUpdate();
            //6 提交事务
            connection.commit();
        } catch (Exception e) {
            //事务回滚
            if(connection!=null){
                try {
                    connection.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            JDBCUtil.close(connection,preparedStatement1);
            JDBCUtil.close(connection,preparedStatement2);
        }
    }

}