← 返回首页
JDBC教程(十三)
发表时间：2021-05-24 10:02:57
JDBC综合案例(ATM系统)

使用JDBC完成一个简单的银行ATM系统

项目整体结构图如下：

实现步骤：

1.在src下编写druid.properties属性文档

#druid.properties文件的配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=true
username=root
password=root
#初始化连接数量
initialSize=50
#最大连接数
maxActive=200
#最新空闲数量
minIdle=5
#最大超时时间
maxWait=3000

2.定义DruidDbUtil工具类

package com.oracle.jdbc.demo.util;

import com.alibaba.druid.pool.DruidDataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

public class DruidDBUtil {

    //声明druid连接池对象
    private static DruidDataSource dataSource = 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> tl = new ThreadLocal<Connection>();

    private DruidDBUtil() {

    }

    static {
        initPool();
    }

    //初始化数据库连接池
    private static void initPool() {
        try {
            Properties pro = new Properties();
            InputStream in = DruidDBUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(in); //读取属性文档配置信息
            String driverClassName = pro.getProperty("driverClassName");
            Class.forName(driverClassName);
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            initialSize = Integer.parseInt(pro.getProperty("initialSize"));
            maxActive = Integer.parseInt(pro.getProperty("maxActive"));
            maxWait = Integer.parseInt(pro.getProperty("maxWait"));
            minIdle = Integer.parseInt(pro.getProperty("minIdle"));

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

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


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

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

    //关闭所有数据库访问对象
    public static void closeAll() {

        try {

            if (DruidDBUtil.getConnection() != null && !DruidDBUtil.getConnection().isClosed())
                DruidDBUtil.getConnection().close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

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

        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        return pstm.executeUpdate();
    }

    //执行批处理
    public static int[] executeBatch(String sql,List<List> list) throws Exception{
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                for(int j=0; j< list.get(i).size();j++){
                    pstm.setObject(j + 1, list.get(i).get(j));
                }
                pstm.addBatch();
            }
        }
        return pstm.executeBatch();
    }


    //执行批处理返回主键的集合
    public static long[] executeBatchWithReturnKeys(String sql,List<List> list) throws Exception{
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        long[] keys = null;
        if (list != null) {
            keys = new long[list.size()];
            for (int i = 0; i < list.size(); i++) {
                keys[i] = executeUpdateWithReturnKey(sql,list.get(i));
            }
        }
        return keys;
    }


    //执行更新语句并且返回新增记录的主键值,主键值为long类型
    public static long executeUpdateWithReturnKey(String sql, List list) throws Exception {

        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        pstm.executeUpdate();
        ResultSet rs = pstm.getGeneratedKeys();
        long key = -1;
        //注意：这里必须把游标指向第一条记录。
        if(rs.next()){
            key = rs.getLong(1);
        }
        return key;
    }

    //所有的查询的方法
    public static ResultSet executeQuery(String sql, List list) throws Exception{

        PreparedStatement pstm = getConnection().prepareStatement(sql);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        return pstm.executeQuery();

    }

    //开启事务
    public static void startTransaction() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //提交事务
    public static void commit() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //回滚
    public static void rollback() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.rollback();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //释放资源
    public static void release() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.close();
            tl.remove();// 与线程池有关，解除关系
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //关闭事务
    public static void endTransaction() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3.设计Account类

package com.oracle.jdbc.demo.domain;

public class Account {

    private String cid; //银行卡账号
    private String name; //户主
    private String password; //取款密码
    private int balance; //余额
    private String modifydate; //最后修改日期

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getBalance() {
        return balance;
    }

    public void setBalance(int balance) {
        this.balance = balance;
    }

    public String getModifydate() {
        return modifydate;
    }

    public void setModifydate(String modifydate) {
        this.modifydate = modifydate;
    }

    @Override
    public String toString() {
        return "Account{" +
                "cid='" + cid + '\'' +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", balance=" + balance +
                ", modifydate='" + modifydate + '\'' +
                '}';
    }
}

4.设计AccountService类，该类封装了账户的所有业务逻辑方法。

package com.oracle.jdbc.demo.atm;

import com.oracle.jdbc.demo.domain.Account;
import com.oracle.jdbc.demo.util.DruidDBUtil;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;

//封装了银行ATM的常用方法
public class AccountService {

    public static Scanner sc = new Scanner(System.in);

    private String createAccountCid() {
        return UUID.randomUUID().toString().replaceAll("-", "");
    }

    //开户
    public Account createAccount() {
        String sql = "insert into account (cid,name,password,balance,modifydate) values (?,?,?,?,?)";
        List argsList = new ArrayList();
        String name;
        String password;
        try {
            System.out.println("请输入您的姓名:");
            name = sc.next();
            System.out.println("请设置您的银行卡密码：");
            password = sc.next();
            Account account = new Account();
            account.setCid(createAccountCid());
            account.setName(name);
            account.setPassword(password);
            account.setBalance(0);
            account.setModifydate(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
            argsList.add(account.getCid());
            argsList.add(account.getName());
            argsList.add(account.getPassword());
            argsList.add(account.getBalance());
            argsList.add(account.getModifydate());

            //开启事务
            DruidDBUtil.startTransaction();
            DruidDBUtil.executeUpdate(sql, argsList);
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            return account;
        } catch (Exception ex) {
            ex.printStackTrace();
            DruidDBUtil.rollback();
            DruidDBUtil.commit();
            return null;
        } finally {
            DruidDBUtil.closeAll();
        }
    }

    //存钱
    public boolean deposit(Account account, int money) {
        String sql = "update account set balance = balance + ? where cid = ?";
        List argsList = new ArrayList();
        argsList.add(money);
        argsList.add(account.getCid());

        try {
            DruidDBUtil.startTransaction();
            DruidDBUtil.executeUpdate(sql, argsList);
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            DruidDBUtil.rollback();
            DruidDBUtil.commit();
            return false;
        } finally {
            DruidDBUtil.closeAll();
        }
    }

    //取款
    public boolean withdraw(Account account, int money) {

        int balance =  queryBalanceByCid(account.getCid());

        if(balance< money){
            System.out.println("余额不足！");
            return false;
        }

        String sql = "update account set balance = balance - ? where cid = ?";
        List argsList = new ArrayList();
        argsList.add(money);
        argsList.add(account.getCid());

        try {
            DruidDBUtil.startTransaction();
            DruidDBUtil.executeUpdate(sql, argsList);
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            DruidDBUtil.rollback();
            DruidDBUtil.commit();
            return false;
        } finally {
            DruidDBUtil.closeAll();
        }
    }

    //转账
    public boolean transfer(String sourceId, String destId, int number) {

        int balance = queryBalanceByCid(sourceId);
        if(balance<number){
            System.out.println("余额不足！");
            return false;
        }

        String sql = "update account set balance = balance + ? where cid = ?";
        List<List> argsList = new ArrayList<List>();
        try{
            DruidDBUtil.startTransaction();
            List list1 = new ArrayList();
            list1.add(number*-1);
            list1.add(sourceId);

            List list2 = new ArrayList();
            list2.add(number);
            list2.add(destId);

            argsList.add(list1);
            argsList.add(list2);
            DruidDBUtil.executeBatch(sql,argsList);
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            return true;
        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            DruidDBUtil.commit();
            return false;
        }finally{
            DruidDBUtil.closeAll();
        }

    }

    //登录
    public Account login(String cid, String password) {
        Account account = null;
        String sql = "select * from account where cid=? and password=?";
        ResultSet rs = null;
        List argsList = new ArrayList();
        argsList.add(cid);
        argsList.add(password);
        try {
            rs = DruidDBUtil.executeQuery(sql, argsList);
            if (rs.next()) {
                account = new Account();
                account.setCid(rs.getString("cid"));
                account.setName(rs.getString("name"));
                account.setPassword(rs.getString("password"));
                account.setBalance(rs.getInt("balance"));
                account.setModifydate(rs.getString("modifydate"));
            }
            return account;

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

    //根据银行开户查询余额
    public int queryBalanceByCid(String cid) {
        ResultSet rs = null;
        String sql = "select balance from account where cid=? ";
        List argsList = new ArrayList();
        argsList.add(cid);
        try {
            rs = DruidDBUtil.executeQuery(sql, argsList);
            if (rs.next()) {
                return rs.getInt("balance");
            }
            return -1;
        } catch (Exception ex) {
            ex.printStackTrace();
            return -1;
        }
    }

    //查询卡号是否存在
    public boolean checkAccountIdExists(String cid){
        ResultSet rs = null;
        String sql = "select * from account where cid=? ";
        List argsList = new ArrayList();
        argsList.add(cid);
        try {
            rs = DruidDBUtil.executeQuery(sql, argsList);
            if (rs.next()) {
                return true;
            }
            return false;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }
}

5.设计程序的主界面类

package com.oracle.jdbc.demo.atm;

import com.oracle.jdbc.demo.domain.Account;

import java.util.Scanner;

public class AtmDemo {

    public static Scanner sc = new Scanner(System.in);
    public static int choice;
    public static AccountService accountService = new AccountService();
    public static Account currentAccount = null; //当前的银行账号

    //主界面
    public static void showMenu() {
        System.out.println("**************XXX银行ATM系统**************");
        System.out.println("1.开户");
        System.out.println("2.存款");
        System.out.println("3.取款");
        System.out.println("4.转账");
        System.out.println("5.退出系统");
        System.out.println("************************************");
        System.out.println("请选择(1-5):");
        choice = sc.nextInt();
    }

    public static void main(String[] args) {

        showMenu();
        while (true) {
            if (choice == 5) {
                break;
            }

            switch (choice) {
                case 1:
                    if ((currentAccount = accountService.createAccount()) != null) {
                        System.out.println("恭喜您，开户成功");
                        System.out.println(currentAccount);
                    }
                    break;
                case 2:
                    String cid, password;
                    int money;
                    System.out.println("请输入您的银行卡号：");
                    cid = sc.next();
                    System.out.println("请输入您的取款密码：");
                    password = sc.next();
                    if ((currentAccount = accountService.login(cid, password)) != null) {
                        System.out.println("请输入存款金额：");
                        money = sc.nextInt();
                        if (accountService.deposit(currentAccount, money)) {
                            System.out.println("存款成功！");
                            System.out.println("存款余额是：" + accountService.queryBalanceByCid(cid) + " 元");
                        }
                    } else {
                        System.out.println("对不起，该账号不存在！");
                    }
                    break;
                case 3:

                    System.out.println("请输入您的银行卡号：");
                    cid = sc.next();
                    System.out.println("请输入您的取款密码：");
                    password = sc.next();
                    if ((currentAccount = accountService.login(cid, password)) != null) {
                        System.out.println("请输入取款金额：");
                        money = sc.nextInt();
                        if (accountService.withdraw(currentAccount, money)) {
                            System.out.println("取款成功！");
                            System.out.println("存款余额是：" + accountService.queryBalanceByCid(cid) + " 元");
                        }
                    } else {
                        System.out.println("对不起，该账号不存在！");
                    }
                    break;

                case 4:
                    System.out.println("请输入您的银行卡号：");
                    cid = sc.next();
                    System.out.println("请输入您的取款密码：");
                    password = sc.next();
                    if ((currentAccount = accountService.login(cid, password)) != null) {
                        System.out.println("请输入转账卡号：");
                        String destId = sc.next();

                        if (accountService.checkAccountIdExists(destId)) {
                            System.out.println("请输入转账金额:");
                            money = sc.nextInt();

                            if (accountService.transfer(cid, destId, money)) {
                                System.out.println("转账成功!");
                                System.out.println("存款余额是：" + accountService.queryBalanceByCid(cid) + " 元");
                            } else {
                                System.out.println("转账失败!");
                            }

                        }else{
                            System.out.println("转账卡号不存在！");
                        }
                    }else{
                        System.out.println("对不起，该账号不存在！");
                    }
                    break;

            }
            showMenu();
        }
        System.out.println("感谢您的使用，byebye!");
    }
}

程序执行效果：