【24-25】事务自动提交

事务自动提交

1. JDBC中只要执行任意一条DML语句，事务就提交一次

演示事务自动提交

package _24_事务自动提交;

import java.sql.*;

public class 演示事务自动提交 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            // 1. register driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. get connection
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/sunlie",
                    "root",
                    "root"
            );
            // 3 create database operation object
            String sql;
            int count;
            sql = "update dept set dname = ? where deptno = ?";
            ps = conn.prepareStatement(sql);

            // 4 execute SQL statement
            ps.setString(1,"XXX");
            ps.setInt(2,40);
            count = ps.executeUpdate();
            System.out.println("count = " + count);
            // 这里打断点，查询表中数据发现数据更改，也就是事务提交了
            // 体现了事务的自动提交

            ps.setString(1,"YYY");
            ps.setInt(2,30);
            count = ps.executeUpdate();
            System.out.println("count = " + count);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. close resource
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

事务手动提交

1. conn.setAutoCommit(false) 关闭自动提交
2. conn.commit() 手动提交
3. 出现异常 conn.rollback() 回滚事务

演示银行转账

package _24_事务自动提交;

import java.sql.*;

/**
 * sql:
 * drop table if exists t_act;
 * create table t_act(
 *      actno bigint,
 *      balance double(7,2) -- 7表示有效数字个数，2表示小数位个数
 * );
 *
 * insert into t_act(actno, balance) values(111, 20000);
 * insert into t_act(actno, balance) values(222, 0);
 * commit;
 * select * from t_act;
 */
public class 银行转账演示事务 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/sunlie",
                    "root",
                    "root"
            );
            // * 关闭自动提交
            conn.setAutoCommit(false);

                String sql;
                int count = 0;
                sql = "update t_act set balance = ? where actno = ?";
                ps = conn.prepareStatement(sql);
                ps.setDouble(1, 10000);
                ps.setInt(2,111);
                count += ps.executeUpdate();

                ps.setDouble(1, 10000);
                ps.setInt(2, 222);
                count += ps.executeUpdate();

                System.out.println(count == 2? "转账成功" : "转账失败");

            // * 事务结束手动提交
            conn.commit();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            // * 出现异常 手动回滚
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 6. close resource
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}