JDBC中sql的事务处理机制

1.jdbc工具

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

/**
 * JDBC的工具类
 */
public class JdbcUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /**
     * 文件的读取，只需要读取一次即可拿到这些值，
     * 使用静态代码块。
     */
    static{
        //读取资源文件，获取值
        try {
            //1.创建Properties集合类
            Properties pro =new Properties();
            //获取src路径下文件的方法--->ClassLoader 类加载器
            ClassLoader classLoader = JdbcUtils.class.getClassLoader();
            URL urlres = classLoader.getResource("jdbc.properties");
            String path = urlres.getPath();
            //System.out.println(path);
            //2.加载文件
           // pro.load(new FileReader("E:/IntelliJ IDEA_workspace/Database_project2020/itcast/day05_JDBC/src/jdbc.properties"));
            pro.load(new FileReader(path));
            //3.获得数据，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4.注册驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnetion() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    /**
     * 释放资源
     */
    public static void close(Statement stmt, Connection conn){
        if(null!=stmt){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(null!=conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet res,Statement stmt, Connection conn){
        if(null!=res){
            try {
                res .close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(null!=stmt){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(null!=conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement stmt1,Statement stmt2, Connection conn){
        if(null!=stmt1){
            try {
                stmt1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(null!=stmt2){
            try {
                stmt2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(null!=conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.事务处理

1. conn.setAutoCommit(false);开启事务
2. conn.commit();提交事务
3. conn.rollback();事务回滚

/**
 * 事务操作
 */
public class JdbcDemo10 {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        //1.获取连接
        try {
            conn = JdbcUtils.getConnetion();
            //开启事务
            conn.setAutoCommit(false);
            //2.定义sql语句
            //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对象
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            //4.设置参数
            pstmt1.setDouble(1,500);
            pstmt1.setInt(2,1);

            pstmt2.setDouble(1,500);
            pstmt2.setInt(2,2);
            //5.执行sql
            pstmt1.executeUpdate();
            //int i=1/0; //制作错误为了测试“事务管理”功能
            pstmt2.executeUpdate();
            conn.commit();

        } catch (Exception e) {
            //事务的回滚
            try {
                if(null != conn) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.close(pstmt1,pstmt2,conn);

        }

    }