课堂案例
查询一张表格:
第一种方式:直接使用列名
数据库中查询:
第二种方式 : 使用列名的下标
safeLogin:
普通Login:
综合案例:
- 开户:
目前数据库中的表格:
执行代码:
执行代码后SQL中的表格:
- 存款操作:
给6002id的存入5000元:
SQL中的表格:
- 取款操作
id为6004的李四取出1000元
如果取款金额超过余额的情况:
- 转账功能
卡号6002 给 卡号6004 转1000
转账结束之后SQL中的表格:
中间操作出现错误时:
卡号密码错误:
输入所要转入的卡号不存在:
输入的转账金额大于自己的余额:
5.修改密码
修改李四的密码为123456789
6 注销账户:
注销王五的账户:
源代码:
TestAccount 类:
package day42;
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.changePassword();
break;
case 6:
as.deleteCard();
break;
case 0:
as.closeConnection();
continue;
}
}while(choice!=0);
System.out.println(“退出成功,欢迎下次光临”);
}
}
方法类:
开户方法:
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 {
//1.注册驱动
//2.获得连接
//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 (Exception e) {
e.printStackTrace();
}finally {
if(preparedStatement!=null){
try {
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 (Exception e) {
e.printStackTrace();
}finally {
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (Exception 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){
//取款操作
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 拿到的就是余额
double balance = resultSet.getDouble(1);
if(money <= balance){
// 取款操作
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 (Exception e) {
e.printStackTrace();
}finally {
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement!=null){
try {
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();
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 balance1 = resultSet.getDouble(1);
System.out.println("请输入要转入卡号:");
String elsecardId = scanner.next();
String sql1 = "select balance from t_account where cardId = ? ";
preparedStatement=connection.prepareStatement(sql1);
preparedStatement.setString(1,elsecardId);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
double balance2 = resultSet.getDouble(1);
System.out.println("请输入要转入的金额:");
double money = scanner.nextDouble();
if( money <= balance1){
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 a = preparedStatement.executeUpdate();
String sql3 = "update t_account set balance = balance + ? where cardId = ?";
preparedStatement = connection.prepareStatement(sql3);
preparedStatement.setDouble(1,money);
preparedStatement.setString(2,elsecardId);
int b = preparedStatement.executeUpdate();
if(a>0 && b>0){
System.out.println("转账成功");
}
}else{
System.out.println("您的余额不足");
}
}else{
System.out.println("您输入的卡号不存在");
}
}else{
System.out.println("您输入的卡号或密码有误");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
修改密码方法:
public void changePassword(){
System.out.println(“请输入卡号:”);
String cardId = scanner.next();
System.out.println(“请输入密码:”);
String password = scanner.next();
String sql3 = "select username FROM t_account WHERE cardId = ? AND PASSWORD=? ";
try {
preparedStatement = connection.prepareStatement(sql3);
preparedStatement.setString(1,cardId);
preparedStatement.setString(2,password);
resultSet= preparedStatement.executeQuery();
if(resultSet.next()){
System.out.println("请输入新密码:");
String curpassword = scanner.next();
String sql4 = "UPDATE t_account SET PASSWORD = ? WHERE cardId = ? AND PASSWORD = ? ";
preparedStatement = connection.prepareStatement(sql4);
preparedStatement.setString(1,curpassword);
preparedStatement.setString(2,cardId);
preparedStatement.setString(3,password);
int a = preparedStatement.executeUpdate();
if(a>0){
System.out.println("密码修改成功");
}
}else{
System.out.println("您输入的卡号或密码错误");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
注销账户方法:
public void deleteCard(){
System.out.println(“请输入卡号:”);
String cardId = scanner.next();
System.out.println(“请输入密码:”);
String password = scanner.next();
String sql = "select username 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!=null){
String sql1 = "delete from t_account where cardId = ? and password = ?";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setString(1,cardId);
preparedStatement.setString(2,password);
int i = preparedStatement.executeUpdate();
if(i>0){
System.out.println("账户注销成功");
}
}else{
System.out.println("您输入的卡号或密码不正确");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
退出方法:
public void closeConnection(){
if(connection!=null){
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}