JAVA Swing JDBC 实现银行ATM机（1）
编写ATM机，实现ATM机的登录，查询，打印凭条，存款，取款，转账等功能
采用mysql数据库 druid连接池 JDBC JFrame navicat
1.建数据库表
atm表
CREATE TABLE NewTable (atmID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘机号’ ,astate varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ‘运行’ COMMENT ‘状态’ ,atmBalance decimal(30,0) NOT NULL DEFAULT 0 COMMENT ‘ATM余额’ ,
PRIMARY KEY (atmID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
bank表
CREATE TABLE NewTable (bankID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘银行ID’ ,bankname varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘银行名称’ ,
PRIMARY KEY (bankID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
bankcard表
CREATE TABLE NewTable (idCard varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘卡号’ ,password varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘密码’ ,userID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户id’ ,balance decimal(30,0) NOT NULL COMMENT ‘余额’ ,wrong int(10) NOT NULL COMMENT ‘密码错误次数’ ,state varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘银行卡状态’ ,bankID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘银行ID’ ,
PRIMARY KEY (idCard),
FOREIGN KEY (userID) REFERENCES user (userID) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (bankID) REFERENCES bank (bankID) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX userID (userID) USING BTREE ,
INDEX bankID (bankID) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
deposit表
CREATE TABLE NewTable (id int(30) NOT NULL AUTO_INCREMENT COMMENT ‘序号’ ,idCard varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘卡号’ ,dtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘存款时间’ ,dmoney decimal(10,0) NOT NULL COMMENT ‘金额’ ,atmID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘设备号’ ,mID varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘冠字号’ ,
PRIMARY KEY (id),
FOREIGN KEY (idCard) REFERENCES bankcard (idCard) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (atmID) REFERENCES atm (atmID) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX idCard (idCard) USING BTREE ,
INDEX atmID (atmID) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=6
ROW_FORMAT=COMPACT
;
CREATE DEFINER=root@localhost TRIGGER in AFTER INSERT ON NewTable
FOR EACH ROW begin
insert into receipt(idCard,type,money,time,atmID,mID)
values(new.idCard,’存款’,new.dmoney,new.dtime,new.atmID,new.mID);
update bankcard set balance = balance + new.dmoney where idCard = new.idCard;
end;
province表
CREATE TABLE NewTable (provinceID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘省份ID’ ,province varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘省份名称’ ,
PRIMARY KEY (provinceID),
INDEX province (province) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
receipt表
CREATE TABLE NewTable (id int(30) NOT NULL AUTO_INCREMENT ,idCard varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘卡号’ ,type varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘交易类型’ ,money decimal(30,0) NOT NULL COMMENT ‘金额’ ,time datetime NOT NULL COMMENT ‘时间’ ,atmID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘设备号’ ,mID varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘冠字号’ ,
PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=17
ROW_FORMAT=COMPACT
;
region表
CREATE TABLE NewTable (areaID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘地区ID’ ,area varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘地区名称’ ,
PRIMARY KEY (areaID)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
transfer表
CREATE TABLE NewTable (id int(30) NOT NULL AUTO_INCREMENT COMMENT ‘序号’ ,idCard varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘卡号’ ,tbankID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘交易卡号’ ,ttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘转账时间’ ,tmoney decimal(10,0) NOT NULL COMMENT ‘转账金额’ ,ttype varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘转账类型’ ,cost decimal(10,0) NOT NULL COMMENT ‘手续费’ ,atmID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘设备号’ ,mID varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘冠字号’ ,
PRIMARY KEY (id),
FOREIGN KEY (idCard) REFERENCES bankcard (idCard) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (atmID) REFERENCES atm (atmID) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX idCard (idCard) USING BTREE ,
INDEX atmID (atmID) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=9
ROW_FORMAT=COMPACT
;
CREATE DEFINER=root@localhost TRIGGER trans AFTER INSERT ON NewTable
FOR EACH ROW begin
update bankcard set balance = balance + new.tmoney where idCard = new.tbankID;
update bankcard set balance = balance - new.tmoney - new.cost where idCard = new.idCard;
if new.ttype=0
then
insert into receipt(idCard,type,money,time,atmID,mID)
values(new.idCard,’同行转账’,new.tmoney,new.ttime,new.atmID,new.mID);
end if;
if new.ttype=1
then
insert into receipt(idCard,type,money,time,atmID,mID)
values(new.idCard,’行外转账’,new.tmoney,new.ttime,new.atmID,new.mID);
insert into receipt(idCard,type,money,time,atmID,mID)
values(new.idCard,’转账手续费’,new.cost,new.ttime,new.atmID,new.mID);
end if;
end;
user表
CREATE TABLE NewTable (userID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户ID’ ,name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘姓名’ ,sex varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘性别’ ,age int(30) NOT NULL COMMENT ‘年龄’ ,provinceID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘省份ID’ ,areaID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘地区ID’ ,
PRIMARY KEY (userID),
INDEX provinceID (provinceID) USING BTREE ,
INDEX areaID (areaID) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
withdrawal表
CREATE TABLE NewTable (id int(30) NOT NULL AUTO_INCREMENT COMMENT ‘序号’ ,idCard varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘卡号’ ,wtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘取款时间’ ,wmoney decimal(10,0) NOT NULL COMMENT ‘金额’ ,atmID varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘设备号’ ,mID varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘冠字号’ ,
PRIMARY KEY (id),
FOREIGN KEY (idCard) REFERENCES bankcard (idCard) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (atmID) REFERENCES atm (atmID) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX idCard (idCard) USING BTREE ,
INDEX atmID (atmID) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;
CREATE DEFINER=root@localhost TRIGGER out AFTER INSERT ON NewTable
FOR EACH ROW begin
insert into receipt(idCard,type,money,time,atmID,mID)
values(new.idCard,’取款’,new.wmoney,new.wtime,new.atmID,new.mID);
update bankcard set balance = balance - new.wmoney where idCard = new.idCard;
end;
2.导入Jar包
mysql-connector-java-5.1.37-bin