JDBC使用事务

JDBC使用事务

import java.sql.*;

/*
JDBD事务机制：
    1.JDBC中的事务是自动提交的，什么是自动提交？
        只要执行任意一条DML语句，则自动提交一次。这是JDBC默认的事务行为
        但是在实际业务中，通常是N条DML语句共同联合完成的，必须保证这些DML
        语句同时成功或同时失败。

    2.
    SQL脚本:
        drop table if exists t_act;
        create table t_act(
            actno varchar(255);
            balance double(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 JDBCTest10 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // 1.注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            // 2.获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");
            // 将自动提交机制改为手动提交
            connection.setAutoCommit(false);
            // 3.获取PreparedStatement操作对象
            String sql = "update t_act set balance=? where actno=?";
            preparedStatement = connection.prepareStatement(sql);
            //给占位符传值
            preparedStatement.setDouble(1,10000);
            preparedStatement.setString(2,"111");
            // 4.执行SQL语句
            int count = preparedStatement.executeUpdate();

            /*String s = null;
            s.toString();*/

            //再给占位符传值
            preparedStatement.setDouble(1,10000);
            preparedStatement.setString(2,"222");
            //执行SQL语句
            count += preparedStatement.executeUpdate();

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

            //手动提交
            connection.commit();

            // 获取PreparedStatement操作对象
            sql = "select actno,balance from t_act where actno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"111");
            // 执行SQL语句
            resultSet = preparedStatement.executeQuery();
            // 处理查询结果集
            if(resultSet.next()){
                String actno = resultSet.getString("actno");
                double balance = resultSet.getDouble("balance");
                System.out.println(actno + "账户余额为：" + balance);
            }

        } catch (SQLException throwables) {
            // 手动回滚
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            throwables.printStackTrace();
        } finally {
            // 6.释放资源
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}