综合案例--银行账户（增删改查）

最新推荐文章于 2022-04-21 11:32:36 发布

Willing卡卡

最新推荐文章于 2022-04-21 11:32:36 发布

阅读量1.1k

点赞数

分类专栏： JAVA 文章标签： mysql 数据库 jdbc sql

本文链接：https://blog.csdn.net/weixin_42601136/article/details/105268481

版权

JAVA 专栏收录该内容

23 篇文章 5 订阅

订阅专栏

综合练习–银行案例

创建数据库

数据库 Account

创建一张表 t_ccount
- cardId：字符串，主键
- password：字符串，非空
- username：字符串，非空
- balance：小数，非空
- phone：字符串，非空

#创建数据库
CREATE DATABASE Account CHARACTER SET utf8;
USE Account;
#创建表
CREATE TABLE t_account(
	cardId VARCHAR(20) UNIQUE PRIMARY KEY,#主键、唯一
	`password` VARCHAR(20) NOT NULL,#非空
	username VARCHAR(10) NOT NULL,
	balance DOUBLE NOT NULL,
	phone VARCHAR(11)
)CHARSET = utf8;

创建AccountSystem

开户：控制台输入所有的账户信息，使用PreparedStatement添加至t_account表
存款：控制台输入卡号、密码、存储金额进行修改
取款：输入卡号、密码、取款金额
转账：输入卡号、密码、对方卡号、转账金额进行修改
修改密码：控制台输入卡号、密码，再输入新密码进行修改
注销：控制台输入卡号、密码，删除对应的账户信息

/**
 * 开户：控制台输入所有的账户信息，使用PreparedStatement添加至t_account表
 * 存款：控制台输入卡号、密码、存储金额进行修改
 * 取款：输入卡号、密码、取款金额
 * 转账：输入卡号、密码、对方卡号、转账金额进行修改
 * 修改密码：控制台输入卡号、密码，再输入新密码进行修改
 * 注销：控制台输入卡号、密码，删除对应的账户信息
 */

import java.sql.*;
import java.util.Scanner;
public class AccountSystem {
    Scanner scanner = new Scanner(System.in);
    private static Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    //工具
    static {
        //重复的操作，就触发一次加载
        try {
            //1. 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8", "root", "root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //开户
    public void regiter() {
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();
        System.out.println("请输入用户名：");
        String username = scanner.next();
        System.out.println("请输入存款金额：");
        double balance = scanner.nextDouble();
        System.out.println("请输入手机号：");
        String phone = scanner.next();
        try {
            //3.创建PreparedStatement
            String sql = "insert into t_account(cardId,password,username,balance,phone)values (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //4.为占位符赋值
            preparedStatement.setString(1, cardId);
            preparedStatement.setString(2, password);
            preparedStatement.setString(3, username);
            preparedStatement.setDouble(4, balance);
            preparedStatement.setString(5, phone);
            //5.执行SQL语句
            int i = preparedStatement.executeUpdate();
            //6.处理结果
            if (i > 0) {
                System.out.println("开户成功！");
            } else {
                System.out.println("开户失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //存款
    public void saveMoney() {
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        System.out.println("请输入存款金额：");
        double money = scanner.nextDouble();
        if (money > 0) {
            //存款操作
            String sql = "update t_account set balance = balance + ? where cardId=? and password=?";
            try {
                //预编译
                preparedStatement = connection.prepareStatement(sql);
                //赋值
                preparedStatement.setDouble(1, money);
                preparedStatement.setString(2, cardId);
                preparedStatement.setString(3, password);
                //执行 接收返回结果
                int i = preparedStatement.executeUpdate();
                if (i > 0) {
                    System.out.println("存款成功！");
                } else {
                    System.out.println("存款失败！请核对用户名或密码！");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            System.out.println("输入的金额不正确！");
        }
    }

    //取款
    public void takeMoney() {
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        System.out.println("请输入取款金额：");
        double money = scanner.nextDouble();
        if (money > 0) {
            //取款操作
            //1.先查询cardId和password对应的数据，余额
            String sql = "select balance from t_account where cardId=? and password= ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, cardId);
                preparedStatement.setString(2, password);
                resultSet = preparedStatement.executeQuery();
                //得到的是单行单列的数据
                if (resultSet.next()) {
                    double balance = resultSet.getDouble(1);
                    if (money <= balance) {
                        //2.取款
                        String sql2 = "update t_account set balance = balance - ? where cardId=? and password= ?";
                        preparedStatement = connection.prepareStatement(sql2);
                        preparedStatement.setDouble(1, money);
                        preparedStatement.setString(2, cardId);
                        preparedStatement.setString(3, password);
                        int i = preparedStatement.executeUpdate();
                        if (i > 0) {
                            System.out.println("取款成功！欢迎下次再取");
                        }
                    } else {
                        System.out.println("余额不足");
                    }
                }else{
                    System.out.println("核实用户名或密码！");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        } else {
            System.out.println("输入的金额不正确");
        }
    }

    //转账
    public void transfer() {
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();
        System.out.println("请输入转账金额：");
        double money = scanner.nextDouble();
        if (money > 0) {
            //查询自身用户
            String sql = "select balance from t_account where cardid = ? and password = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, cardId);
                preparedStatement.setString(2, password);
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {//用户存在，获取用户的余额
                    double balance = resultSet.getDouble("balance");
                    if (money <= balance) {
                        System.out.println("请输入对方卡号：");
                        String toCardId = scanner.next();
                        String ss = "select * from t_account where cardid = ?";
                        preparedStatement = connection.prepareStatement(ss);
                        preparedStatement.setString(1, toCardId);
                        resultSet = preparedStatement.executeQuery();

                        if (resultSet.next()) {//如果查询到对方用户，再进行转账的操作
                            //减钱的账户
                            String sql2 = "update t_account set balance = balance - ? where cardid=?";
                            preparedStatement = connection.prepareStatement(sql2);
                            preparedStatement.setDouble(1, money);
                            preparedStatement.setString(2, cardId);
                            preparedStatement.executeUpdate();
                            //加钱的账户
                            String sql3 = "update t_account set balance = balance + ? where cardid = ?";
                            preparedStatement = connection.prepareStatement(sql3);
                            preparedStatement.setDouble(1, money);
                            preparedStatement.setString(2, toCardId);
                            preparedStatement.executeUpdate();
                        } else {
                            System.out.println("对方账户不存在");
                        }
                    }
                } else {
                    System.out.println("卡号或密码错误");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        } else {
            System.out.println("转账金额输入错误");
        }
    }

    //修改密码
    public void updatePwd() {
        System.out.println("请输入卡号:");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        String sql = "select * from t_account where cardId =? and password = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, cardId);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                System.out.println("请输入新密码:");
                String newPwd = scanner.next();
                String sql2 = "update t_account set password=? where cardId = ?";
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setString(1, newPwd);
                preparedStatement.setString(2, cardId);
                int i = preparedStatement.executeUpdate();
                if (i > 0) {
                    System.out.println("修改成功！");
                } else {
                    System.out.println("修改失败！");
                }
            } else {
                System.out.println("卡号或密码错误");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //注销用户
    public void destroy() {
        System.out.println("请输入卡号");
        String cardId = scanner.next();
        System.out.println("请输入密码");
        String password = scanner.next();
        System.out.println("确定要注销账户吗？");
        String answer = scanner.next();
        if (answer.equals("y")) {
            String sql = "delete from t_account where cardid = ? and password= ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, cardId);
                preparedStatement.setString(2, password);
                int i = preparedStatement.executeUpdate();
                if (i > 0) {
                    System.out.println("注销成功！");
                } else {
                    System.out.println("注销失败！");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            System.out.println("退出注销");
        }
    }
    //关闭
    public void closeConnection() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试类

/**
 * 测试类
 */
import java.util.Scanner;
public class TestAccount {
    public static void main(String[] args) {
        AccountSystem as = new AccountSystem();
        Scanner scanner = new Scanner(System.in);
        System.out.println("欢迎来到卡卡之家系统");
        int choice = 0;
        do{
            System.out.println("1、开户 2、存款 3、取款 4、转账 5、修改密码 6、注销 0、退出");
            System.out.println("请输入你需要的的操作：");
            choice = scanner.nextInt();
            switch (choice){
                case 1:
                    as.regiter();
                    break;
                case 2:
                    as.saveMoney();
                    break;
                case 3:
                    as.takeMoney();
                    break;
                case 4:
                    as.transfer();
                    break;
                case 5:
                    as.updatePwd();
                    break;
                case 6:
                    as.destroy();
                    break;
                case 0:
                    as.closeConnection();
                    return;
            }
        }while(choice!=0);
    }
}

Willing卡卡

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
打赏
0
评论
综合案例--银行账户（增删改查）

综合练习–银行案例创建数据库数据库 Account创建一张表 t_ccountcardId：字符串，主键password：字符串，非空username：字符串，非空balance：小数，非空phone：字符串，非空#创建数据库CREATE DATABASE Account CHARACTER SET utf8;USE Account;#创建表CREATE TA...
复制链接

扫一扫