通过JDBC写一个类似于银行ATM机逻辑的练习

通过JDBC写一个类似于银行ATM机逻辑的练习

1.首先我们要弄清楚实现的业务流程

 

练习的整体结构图如下：

 2.准备工作

①在src根目录下编写的druid.properties属性文档

#MYSQL
driverClassName=com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true&serverTimezone=UTC&rewriteBatchedStatements=true
#用户名
username=root
#用户密码
password=root
#初始化连接数量
initialSize=5
#最大连接数
maxActive=50
#最新空闲数量
minIdle=20
#最大超时时间
maxWait=60000

②定义自己的DruidDBUtil工具类

package demo.util

import com.alibaba.druid.pool.DruidDataSource;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

public class DruidDBUtil {

    //声明druid连接池对象
    public static DruidDataSource druidDataSource = null;
    private static Connection conn = null;
    private static PreparedStatement pstmt = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    //初始化连接数
    private static int initialSize;
    //最大活动链接数
    private static int maxActive;
    //最小闲置连接数
    private static int minIdle;
    //连接耗尽时间最大等待获取连接时间
    private static long maxWait;

    private static ThreadLocal<Connection> t1 = new ThreadLocal<Connection>();

    private DruidDBUtil() {

    }

    static {
        initPool();
    }

    private static void initPool() {
        try {
            Properties properties = new Properties();
            InputStream in = DruidDBUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(in);
            String driverClassName = properties.getProperty("driverClassName");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            initialSize = Integer.parseInt(properties.getProperty("initialSize"));
            maxActive = Integer.parseInt(properties.getProperty("maxActive"));
            maxWait = Integer.parseInt(properties.getProperty("maxWait"));
            minIdle = Integer.parseInt(properties.getProperty("minIdle"));

            //创建druid数据源
            druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(url);
            druidDataSource.setUsername(username);
            druidDataSource.setPassword(password);

            //设置连接池中初始连接数
            druidDataSource.setInitialSize(initialSize);
            //设置最大连接数
            druidDataSource.setMaxActive(maxActive);
            //设置最小的闲置连接数
            druidDataSource.setMinIdle(minIdle);
            //设置最大的连接等待时间
            druidDataSource.setMaxWait(maxWait);

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //由于使用了数据库连接池，我们不采用单例模式
    public static Connection getConnection() throws Exception {
        //先从当前线程上获得连接
        Connection conn = t1.get();
        try {
            if (conn == null || conn.isClosed()) {
                //从连接池中获取连接对象
                conn = druidDataSource.getConnection();
                //将连接绑到当前线程上
                t1.set(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }


    //关闭所有数据库访问对象
    public static void closeAll() {
        try {
            if (pstmt != null) {
                pstmt.close();
                if (DruidDBUtil.getConnection() != null && !DruidDBUtil.getConnection().isClosed()) {
                    DruidDBUtil.getConnection().close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //所有的增删改的方法
    public static int executeUpdate(String sql, List list) throws Exception {
        //增加第