3.30

于 2020-03-30 23:36:29 发布
阅读量320 收藏 1
点赞数 1
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Fish_Xx/article/details/105211880
要求
  1. 创建数据库、表
  • 数据库 Account
  • 创建一张表 t_ccount。有以下列
    • cardId：字符串，主键
    • password：字符串，非空
    • username：字符串，非空
    • balance：小数，非空
    • phone：字符串，非空
  1. 创建项目通过JDBC实现功能
  • 创建AccountSystem类，完成下列功能
    • 开户：控制台输入所有的账户信息，使用PreparedStatement添加至t_account表
    • 存款：控制台输入卡号、密码、存储金额进行修改
    • 取款：输入卡号、密码、取款金额
    • 转账：输入卡号、密码、对方卡号、转账金额进行修改
    • 修改密码：控制台输入卡号、密码，再输入新密码进行修改
    • 注销：控制台输入卡号、密码，删除对应的账户信息
创建数据库、表
CREATE DATABASE Account CHARACTER SET UTF8;
USE Account;
CREATE TABLE t_account(
	cardId VARCHAR(20) PRIMARY KEY,
	`password` VARCHAR(20) NOT NULL,
	userName VARCHAR(20) NOT NULL,
	balance DOUBLE NOT NULL,
	phone VARCHAR(11) NOT NULL
);

SELECT * FROM t_account;
创建项目
TestAccount

import java.util.Scanner;

public class TestAccount {
    public static void main(String[] args) {
        AccountSystem accountSystem = 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、退出");
            choice = scanner.nextInt();
            switch (choice) {
                case 1:
                    accountSystem.register();
                    break;
                case 2:
                    accountSystem.saveMoney();
                    break;
                case 3:
                    accountSystem.takeMoney();
                    break;
                case 4:
                    accountSystem.transfer();
                    break;
                case 5:
                    accountSystem.changePassword();
                    break;
                case 6:
                    accountSystem.cancellation();
                    break;
                case 0:
                    System.out.println("感谢使用 欢迎下次再来");
                    accountSystem.closeConnection();
                    return;
            }
        } while (true);
    }
}
AccountSystem
package com.qf.day3_30;

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 {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8", "root", "1234");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //开户
    public void register() {
        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 {
            String sql = "insert into t_account(cardId,password,userName,balance,phone) values (?,?,?,?,?);";

            //创建preparedStatement
            preparedStatement = connection.prepareStatement(sql);
            //占位符赋值
            preparedStatement.setString(1, cardId);
            preparedStatement.setString(2, password);
            preparedStatement.setString(3, userName);
            preparedStatement.setDouble(4, balance);
            preparedStatement.setString(5, phone);
            //执行sql语句
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("开户成功");
            } else {
                System.out.println("开户失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //检查身份
    private String checkId() {
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();

        String sql = "select password from t_account where cardId = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, cardId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                if (resultSet.getString(1).equals(password)) {
                    System.out.println("账号密码正确");
                    return cardId;
                } else {
                    System.out.println("用户名或密码错误");
                    return null;
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    //存款
    public void saveMoney() {
        String cardId = checkId();
        if (cardId != null) {
            System.out.println("请输入存款金额：");
            double money = scanner.nextDouble();
            if (money >= 0) {
                String sql = "update t_account set balance = balance + ? where cardId = ?;";
                try {
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setDouble(1, money);
                    preparedStatement.setString(2, cardId);
                    int check = preparedStatement.executeUpdate();
                    if (check > 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();
        }
    }

    //取款
    public void takeMoney() {
        String cardId = checkId();
        if (cardId != null) {
            System.out.println("请输入取款金额：");
            double money = scanner.nextDouble();
            if (money >= 0) {
                try {
                    String sql1 = "select balance from t_account where cardId = ?;";
                    preparedStatement = connection.prepareStatement(sql1);
                    preparedStatement.setString(1, cardId);
                    resultSet = preparedStatement.executeQuery();
                    if (resultSet.next() && resultSet.getDouble(1) >= money) {
                        String sql2 = "update t_account set balance = balance - ? where cardId = ?;";
                        preparedStatement = connection.prepareStatement(sql2);
                        preparedStatement.setDouble(1, money);
                        preparedStatement.setString(2, cardId);
                        int check = preparedStatement.executeUpdate();
                        if (check > 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() {
        String cardId = checkId();
        if (cardId != null) {
            System.out.println("请输入转账账号：");
            String tCardId = scanner.next();
            System.out.println("请输入转账金额：");
            double money = scanner.nextDouble();
            if (money >= 0) {

                try {
                    String sql1 = "select balance from t_account where cardId = ?;";
                    preparedStatement = connection.prepareStatement(sql1);
                    preparedStatement.setString(1, cardId);
                    resultSet = preparedStatement.executeQuery();

                    if (resultSet.next() && resultSet.getDouble(1) >= money) {
                        String sql2 = "select balance from t_account where cardId = ?;";
                        preparedStatement = connection.prepareStatement(sql2);
                        preparedStatement.setString(1, tCardId);
                        resultSet = preparedStatement.executeQuery();

                        if (resultSet.next()) {
                            String sql5 = "START TRANSACTION;";
                            preparedStatement = connection.prepareStatement(sql5);
                            preparedStatement.executeUpdate();
                            String sql3 = "update t_account set balance = balance - ? where cardId = ?;";
                            preparedStatement = connection.prepareStatement(sql3);
                            preparedStatement.setDouble(1, money);
                            preparedStatement.setString(2, cardId);
                            int check1 = preparedStatement.executeUpdate();
                            String sql4 = "update t_account set balance = balance + ? where cardId = ?;";
                            preparedStatement = connection.prepareStatement(sql4);
                            preparedStatement.setDouble(1, money);
                            preparedStatement.setString(2, tCardId);
                            int check2 = preparedStatement.executeUpdate();

                            if (check1 > 0 && check2 > 0) {
                                String sql6 = "commit;";
                                preparedStatement = connection.prepareStatement(sql6);
                                preparedStatement.executeUpdate();
                                System.out.println("转账成功");
                            } else {
                                String sql7 = "rollback;";
                                preparedStatement = connection.prepareStatement(sql7);
                                preparedStatement.executeUpdate();
                                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 changePassword() {
        String cardId = checkId();
        if (cardId != null) {
            System.out.println("请输入新密码：");
            String newPassword = scanner.next();
            System.out.println("重新输入新密码以确认：");
            String newPasswordRe = scanner.next();
            if (newPassword.equals(newPasswordRe)) {
                String sql = "update t_account set password = ? where cardId = ?;";
                try {
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, newPassword);
                    preparedStatement.setString(2, cardId);
                    int check = preparedStatement.executeUpdate();
                    if (check > 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 cancellation() {
        String cardId = checkId();
        if (cardId != null) {
            String sql = "delete from t_account where cardId = ?;";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, cardId);
                int check = preparedStatement.executeUpdate();
                if (check > 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();
                }
            }
        }
    }

}
Fish_Xx
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值