综合练习--银行案例
创建数据库
数据库 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);
}
}
