JDBC 事务转账案例

0.创建表

tb_account

/*
 Navicat Premium Data Transfer

 Source Server         : mysql57
 Source Server Type    : MySQL
 Source Server Version : 50738
 Source Host           : localhost:13306
 Source Schema         : powernode_jdbc

 Target Server Type    : MySQL
 Target Server Version : 50738
 File Encoding         : 65001

 Date: 18/10/2022 10:42:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_account
-- ----------------------------
DROP TABLE IF EXISTS `tb_account`;
CREATE TABLE `tb_account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `balance` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of tb_account
-- ----------------------------
INSERT INTO `tb_account` VALUES (1, 'zhangsan', 500);
INSERT INTO `tb_account` VALUES (2, 'lisi', 7000);

SET FOREIGN_KEY_CHECKS = 1;

1.导入mysql的jar包

2.db.properties配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:13306/powernode_jdbc?characterEncoding=utf-8&useSSL=false
username=root
password=root

3.JDBCUtil工具类

package util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    private  JDBCUtil(){

    }
    private static Properties properties = null;
    static {
        // 1.加载驱动
        try {
            InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            properties = new Properties();
            properties.load(inputStream);
//            Class.forName("com.mysql.jdbc.Driver");//快买京东
            Class.forName(properties.getProperty("driverClassName"));//快买京东
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取链接对象
    public static Connection getConnection(){
        // 2.获取链接对象
        Connection connection = null;
        try {
//            connection = DriverManager.getConnection("jdbc:mysql://localhost:13306/test","root","root");
            connection = DriverManager.getConnection(properties.getProperty("url"),
                    properties.getProperty("username"),
                    properties.getProperty("password"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;

    }

    // 释放资源
    public static void releaseSource(PreparedStatement preparedStatement, Connection connection) {
        releaseSource(null,preparedStatement,connection);
    }

    public static void releaseSource(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){

        try {
            if (resultSet!=null){
                resultSet.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if (preparedStatement!=null){
                preparedStatement.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if (connection!=null){
                connection.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void releaseSource(AutoCloseable ... closeables){
        for (AutoCloseable closeable : closeables) {
            try {
                if (closeable!=null){
                    closeable.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

4.事务转账案例

package 事务_转账案例;

import util.JDBCUtil;

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

public class TransactionTransferAccountsDemo {
    public static void main(String[] args) throws SQLException {
        // 需求： 从lisi 手里 转账给zhangsan 500元
        /**
         * 1.先查询 lisi 账户中是否金额充足
         * 2.如果充足，再对lisi账户 通过修改操作，扣除500元
         * 3. 同时给 zhangsan账户增加500元
         */
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();

            connection.setAutoCommit(false);// 开启手动提交事务

            String sql = "select * from tb_account where name  =? and balance >= ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"lisi");
            preparedStatement.setInt(2,500);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                // 有能力转账
                sql = "update tb_account set balance = balance - ? where name = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,500);
                preparedStatement.setString(2,"lisi");
                preparedStatement.executeUpdate();
                int i = 1/0;//发生异常了
                sql = "update tb_account set balance = balance + ? where name = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1,500);
                preparedStatement.setString(2,"zhangsan");
                preparedStatement.executeUpdate();

                connection.commit();// 提交事务，释放锁

            }
        } catch (Exception e) {
            e.printStackTrace();
            connection.rollback();// 回滚，释放锁
        }finally {
            // 释放资源
            JDBCUtil.releaseSource(resultSet,preparedStatement,connection);
        }


    }
}