1、设计好 银行系统的两张表
create table t_user (
accountnum varchar2(20) primary key,
username varchar2(16),
password varchar2(6),
balance number(8,2),
datetime date default sysdate
);
create table t_history (
hid number(10) primary key,
accountnumber varchar2(20),
money number(8,2),
content varchar2(50), --备注
time date default sysdate
);
create sequence seq_account
start with 1001
increment by 1;
create sequence seq_hid
start with 1
increment 1;
2、给t_history 的accountnumber 添加外键约束
alter table t_history add constraints fk_number foreign key(accountnumber) references t_user(accountnum);
3、完成界面的设计
package edu.com;
import java.sql.SQLException;
import java.util.Scanner;
public class TestBank {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
System.out.println("农大软件学院银行系统1.0");
System.out.println("0、退出");
System.out.println("1、开户");
System.out.println("2、存钱");
System.out.println("3、取钱");
System.out.println("4、转账");
while(true)
{
System.out.println("请输入系统选项[0-4]:");
Scanner scan=new Scanner(System.in);
int chooess=scan.nextInt();
switch(chooess){
case 0:
System.out.println("bye-bye");
System.exit(0);
break;
case 1:
Bank.kaihu();
break;
case 2:
Bank.cunqian();
break;
case 3:
Bank.quqian();
break;
case 4:
Bank.zhuanzhang();
break;
default :
break;
}
}
}
}
4、给相应的功能实现代码
package edu.com;
import java.util.Scanner;
import java.sql.*;
public class Bank {
public static Connection connection() throws SQLException, ClassNotFoundException{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
return conn;
}
public static void kaihu() throws ClassNotFoundException, SQLException{
Connection conn=connection();
Scanner scan=new Scanner(System.in);
System.out.println("请输入用户名:");
String name=scan.next();
System.out.println("请输入密码:");
String pwd=scan.next();
String sql="insert into t_user(accountnum,username,password,balance) values(seq_accountnum.nextval,?,?,100)";
PreparedStatement psmt=conn.prepareStatement(sql);
psmt.setString(1, name);
psmt.setString(2, pwd);
ResultSet rs=psmt.executeQuery();
if(rs.next())
{
System.out.println("开户成功!");
}
else
{
System.out.println("开户失败!");
}
}
public static void cunqian() throws ClassNotFoundException, SQLException{
Connection conn=connection();
Scanner scan=new Scanner(System.in);
System.out.print("请输入账号:");
String num=scan.next();
System.out.print("请输入密码:");
String pwd=scan.next();
System.out.print("请输入存款金额:");
float money=scan.nextFloat();
String sql1="update t_user set balance=balance+? where accountnum=?";
PreparedStatement psmt=conn.prepareStatement(sql1);
psmt.setFloat(1, money);
psmt.setString(2, num);
int rs1=psmt.executeUpdate();
String sql2="insert into t_history(hid,accountnumber,money,content) values(seq_hid.nextval,"+num+","+money+",'存款')";
psmt=conn.prepareStatement(sql2);
ResultSet rs2=psmt.executeQuery();
if(rs1>0)
{
System.out.println("存款成功!");
}
else
{
System.out.println("存款失败!");
}
}
public static void quqian() throws ClassNotFoundException, SQLException{
Connection conn=connection();
Scanner scan=new Scanner(System.in);
System.out.print("请输入账号:");
String num=scan.next();
System.out.print("请输入密码:");
String pwd=scan.next();
System.out.print("请输入取款金额:");
float money=scan.nextFloat();
String sql1="update t_user set balance=balance-? where accountnum=?";
PreparedStatement psmt=conn.prepareStatement(sql1);
psmt.setFloat(1, money);
psmt.setString(2, num);
int rs1=psmt.executeUpdate();
String sql2="insert into t_history(hid,accountnumber,money,content) values(seq_hid.nextval,"+num+","+money+",'取款')";
psmt=conn.prepareStatement(sql2);
ResultSet rs2=psmt.executeQuery();
if(rs1>0)
{
System.out.println("取款成功!");
}
else
{
System.out.println("取款失败!");
}
}
public static void zhuanzhang() throws ClassNotFoundException, SQLException
{
Connection conn=connection();
Scanner scan=new Scanner(System.in);
System.out.print("请输入账号:");
String num=scan.next();
System.out.print("请输入密码:");
String pwd=scan.next();
System.out.print("请输入对方账号:");
String no=scan.next();
System.out.print("请输入转账金额:");
float money=scan.nextFloat();
conn.setAutoCommit(false);
String sql1="update t_user set balance=balance-"+money+" where accountnum="+num+"";
String sql2="update t_user set balance=balance+"+money+" where accountnum="+no+"";
String sql3="insert into t_history(hid,accountnumber,money,content) values(seq_hid.nextval,"+num+","+money+",'转账')";
PreparedStatement pstmt=conn.prepareStatement(sql1);
int result1=pstmt.executeUpdate();
pstmt=conn.prepareStatement(sql2);
int result2=pstmt.executeUpdate();
pstmt=conn.prepareStatement(sql3);
ResultSet rs2=pstmt.executeQuery();
if(result1>0&&result2>0)
{
System.out.println("转账成功!");
conn.commit();
}
else
{
System.out.println("转账失败!");
conn.rollback();
}
}
}