目录
MonthlyConsumptionRecordsDaoImpl
(8)MonthlyConsumptionRecordsDao
(7)MonthlyConsumptionRecords(同上相似)
前言:
嗖嗖移动业务大厅是一个提供移动通信服务的地方,提供便利的服务：嗖嗖移动业务大厅为用户提供快捷、便利的通信服务,包括办理新号码、充值话费、办理套餐、办理流量等,让用户能够方便地满足自己的通信需求。
通过这段时间对java和mysql的学习完成这个嗖嗖移动业务大厅.
一、项目介绍
中国移动,中国联通,中国电信是国内3大通信运营商,每个运营商都提供了不同的品牌套餐来应对不同的用户群,比如北京移动主要有全球通,神州行,动感地带等3大品牌套餐,每种套餐的内容和费用不同,嗖嗖移动是一个假定的通信运营商,提供了话痨套餐,网虫套餐,超人套餐,各种套餐所包含的服务内容及费用如下表:
如实际使用中超出套餐内包含的通话时长,短信条数和上网流量,则按一下规则计费:
-
超出的通话: 0.2元/分
-
超出的短信:0.1元/条
-
超出的上网流量:0.1元/MB
主菜单:
(1)用户登录
(2)用户注册
(3)使用嗖嗖
(4)话费充值
(5)资费说明
(6)申请解冻(新加项目功能)
(7)退出系统
二级菜单:
(1)本月账单查询
(2)套餐余量查询
(3)打印消费详单
(4)套餐变更
(5)充值流量(新加项目功能)
(6)办理退网
二、功能模块
1、主要技术
-
面向对象的思想
-
封装,继承,多态,接口的使用
-
异常处理的合理使用
-
集合框架的使用
-
MySQL数据
-
JDBC操作数据库
2、文件结构
1.项目文件分为六个大的部分:常量包、数据访问层、实体类、业务层、显示层、工具类
2.其中数据访问层还包含dao接口实现类和接口
3.必须引入文件并解析
3、功能实现
mysql表
mysql语句:对表的创建 (可自行修改数据)
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.20 : Database - soso
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`soso` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
USE `soso`;
/*Table structure for table `tb_card` */
DROP TABLE IF EXISTS `tb_card`;
CREATE TABLE `tb_card` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`cardNumber` char(11) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号码',
`status` int(1) DEFAULT '0' COMMENT '状态: 0:可用 1:禁用',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_card` (`cardNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_card` */
insert into `tb_card`(`id`,`cardNumber`,`status`) values (1,'13652363333',1),(2,'15812346677',1),(3,'18890985868',1),(4,'13677478866',1),(5,'13666557788',1),(6,'13612476521',0),(7,'13609087438',0),(8,'13689011047',0),(9,'13698674344',0),(10,'13678239812',0),(11,'13677411438',0);
/*Table structure for table `tb_consuminfo` */
DROP TABLE IF EXISTS `tb_consuminfo`;
CREATE TABLE `tb_consuminfo` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`card_number` char(11) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '手机号码',
`type` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '消费类型',
`consum_data` int(11) DEFAULT NULL COMMENT '消费数据',
`consume_date` datetime DEFAULT NULL COMMENT ' 消费日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_consuminfo` */
insert into `tb_consuminfo`(`id`,`card_number`,`type`,`consum_data`,`consume_date`) values (6,'15812346677','通话',90,'2022-09-13 22:10:44'),(7,'15812346677','短信',5,'2022-09-13 22:12:04'),(12,'18890985868','短信',5,'2022-09-14 11:06:54'),(13,'13652363333','短信',50,'2022-09-14 11:15:18');
/*Table structure for table `tb_mobole_card` */
DROP TABLE IF EXISTS `tb_mobole_card`;
CREATE TABLE `tb_mobole_card` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`card_number` char(11) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号码',
`username` varchar(20) COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
`password` varchar(20) COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
`ser_package` int(11) NOT NULL COMMENT '所属套餐',
`money` double(7,2) DEFAULT NULL COMMENT '账户余额',
`status` int(1) DEFAULT '0' COMMENT '状态:0:正常 1:冻结',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_mobole_card` */
insert into `tb_mobole_card`(`id`,`card_number`,`username`,`password`,`ser_package`,`money`,`status`) values (1,'13677478866','zhangsan','123',1,10.00,0),(2,'13666557788','lisi','123456',3,10.00,1),(6,'13652363333','张三','123',2,21.00,0),(7,'15812346677','李四','123456',1,10.00,0),(8,'18890985868','王五','123',2,31.50,0);
/*Table structure for table `tb_monthly_consumption_records` */
DROP TABLE IF EXISTS `tb_monthly_consumption_records`;
CREATE TABLE `tb_monthly_consumption_records` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`card_number` char(11) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号码',
`consum_amount` double(7,2) DEFAULT '0.00' COMMENT '当月消费金额',
`real_talk_time` int(11) DEFAULT '0' COMMENT '当月实际通话时长(单位:分钟)',
`real_SMS_count` int(11) DEFAULT '0' COMMENT '当月实际发送短信条数',
`real_flow` int(11) DEFAULT '0' COMMENT '当月实际上网流量',
`consume_date` date DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_monthly_consumption_records` */
insert into `tb_monthly_consumption_records`(`id`,`card_number`,`consum_amount`,`real_talk_time`,`real_SMS_count`,`real_flow`,`consume_date`) values (1,'13677478866',0.00,590,80,0,'2022-09-01'),(12,'15812346677',0.00,90,5,0,'2022-09-01'),(13,'13677478866',10.00,650,50,0,'2022-08-01'),(17,'18890985868',0.00,0,0,0,'2022-09-01'),(18,'13652363333',5.00,0,50,0,'2022-09-01');
/*Table structure for table `tb_recharge_record` */
DROP TABLE IF EXISTS `tb_recharge_record`;
CREATE TABLE `tb_recharge_record` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`amount` double(7,2) NOT NULL COMMENT '充值金额',
`recharge_date` datetime DEFAULT NULL COMMENT '充值时间',
`card_number` char(11) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_recharge_record` */
insert into `tb_recharge_record`(`id`,`amount`,`recharge_date`,`card_number`) values (1,50.00,'2022-08-30 18:19:28','13666557788'),(5,100.00,'2022-09-09 17:04:05','13652363333'),(6,100.00,'2022-09-09 17:10:05','15812346677'),(7,100.00,'2022-09-13 15:51:50','18890985868');
/*Table structure for table `tb_scene` */
DROP TABLE IF EXISTS `tb_scene`;
CREATE TABLE `tb_scene` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`type` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '场景类型',
`data` int(11) DEFAULT NULL COMMENT '场景消费数据',
`description` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '场景描述',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_scene` */
insert into `tb_scene`(`id`,`type`,`data`,`description`) values (1,'通话',90,'问候客户,谁知其如此难缠,通话90分钟'),(2,'通话',30,'询问妈妈身体状况,本地通话30分钟'),(3,'短信',5,'参与环境保护实施方案问卷调查,发送短信5条'),(4,'短信',50,'告诉朋友本人已换手机号码,发送短信50条'),(5,'上网',1024,'和女朋友微信视频聊天,使用流量1GB'),(6,'上网',2048,'晚上手机在线追剧,一不留神睡着了,使用流量2GB');
/*Table structure for table `tb_serpackage` */
DROP TABLE IF EXISTS `tb_serpackage`;
CREATE TABLE `tb_serpackage` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`talk_time` int(11) DEFAULT NULL COMMENT '通话时长',
`sms_count` int(11) DEFAULT NULL COMMENT '短信条数',
`price` double(7,2) DEFAULT NULL COMMENT '套餐月资费',
`flow` int(11) DEFAULT NULL COMMENT '上网流量',
`type` int(11) DEFAULT NULL COMMENT '套餐类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_serpackage` */
insert into `tb_serpackage`(`id`,`talk_time`,`sms_count`,`price`,`flow`,`type`) values (1,600,100,58.00,0,1),(2,0,0,68.00,20480,2),(3,300,50,78.00,10240,3);
/*Table structure for table `tb_serpackage_type` */
DROP TABLE IF EXISTS `tb_serpackage_type`;
CREATE TABLE `tb_serpackage_type` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '套餐类型名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_serpackage_type` */
insert into `tb_serpackage_type`(`id`,`name`) values (1,'话痨套餐'),(2,'网虫套餐'),(3,'超人套餐');
/*Table structure for table `tb_flow` */
DROP TABLE IF EXISTS `tb_flow`;
CREATE TABLE `tb_flow` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`flow` int(11) COLLATE utf8mb4_bin NOT NULL COMMENT '上网流量',
`price` double(7,2) DEFAULT NULL COMMENT '流量资费',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_flow` */
insert into `tb_flow`(`id`,`flow`,`price`) values (1,10240,80),(2,20480,100);
/*Table structure for table `tb_flow_total` */
DROP TABLE IF EXISTS `tb_flow_total`;
CREATE TABLE `tb_flow_total` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`cardNumber` char(11) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号码',
`flow` int(11) DEFAULT NULL COMMENT '上网流量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*Data for the table `tb_flow_total` */
insert into `tb_flow_total`(`id`,`cardNumber`,`flow`) values (1,'13677478866',20480),(2,'13666557788',10240),(3,'13652363333',0),(4,'18890985868',20480);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
(一)constant[常量包]
(1)SystemConstant
public interface SystemConstant {
/**
* 用户卡正常状态
*/
int MOBOLE_STATUS_NORMAL = 0;
/**
* 用户卡冻结状态
*/
int MOBOLE_STATUS_FREEZE = 1;
/**
* 手机卡的正常状态
*/
int CARD_STATUS_NORMAL = 0;
/**
* 手机卡的冻结状态
*/
int CARD_STATUS_FREEZE = 1;
}(二)dao[数据访问层]
(1)impl[dao接口实现类所在包]
CardDaoImpl
public class CardDaoImpl implements CardDao {
@Override
public List<Card> queryByStatus(int status) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_card where status = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
List<Card> cards = queryRunner.query(conn, sql, new BeanListHandler<>(Card.class),status);
return cards;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public List<Card> queryAll() {
System.out.println("查询到所有的卡信息");
return null;
}
@Override
public Card queryById(Integer id) {
System.out.println("查询到id为" + id + "的卡信息");
return null;
}
@Override
public Card queryByNum(String num) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_card where cardNumber = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
Card card = queryRunner.query(conn, sql, new BeanHandler<Card>(Card.class),num);
return card;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int insert(Card card) {
return 0;
}
@Override
public int update(Card card) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "update tb_card set status = ? where cardNumber = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,card.getStatus(),card.getCardNumber());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
}
ConsuminfoDaoImpl
public class ConsuminfoDaoImpl implements ConsuminfoDao {
@Override
public List<Consuminfo> queryAll() {
return null;
}
@Override
public Consuminfo queryById(Integer id) {
return null;
}
@Override
public int insert(Consuminfo consuminfo) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "insert into tb_consuminfo(card_number,type,consum_data,consume_date) values (?,?,?,?)";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,consuminfo.getCardNumber(),consuminfo.getType(),consuminfo.getConsumData(), DateUtil.formatDate(consuminfo.getConsumeDate()));
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update(Consuminfo consuminfo) {
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
@Override
public List<Consuminfo> queryByCardNumberAndMonth(String cardNumber, int month,int year) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select type,consum_data consumData,consume_date consumeDate from tb_consuminfo where card_number = ? and Month(consume_date) = ? and Year(consume_date) = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
List<Consuminfo> consuminfos = queryRunner.query(conn, sql, new BeanListHandler<>(Consuminfo.class),cardNumber,month,year);
return consuminfos;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int deleteByNum(String num) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "delete tb_consuminfo from tb_consuminfo where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,num);
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
}
FlowDaoImpl
public class FlowDaoImpl implements FlowDao {
@Override
public List<Flow> queryAll() {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_flow";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
List<Flow> flows = queryRunner.query(conn, sql, new BeanListHandler<>(Flow.class));
return flows;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public Flow queryById(Integer id) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_flow where id = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
Flow flow = queryRunner.query(conn, sql, new BeanHandler<>(Flow.class),id);
return flow;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int insert(Flow flow) {
return 0;
}
@Override
public int update(Flow flow) {
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
}
FlowTotalDaoImpl
public class FlowTotalDaoImpl implements FlowTotalDao {
@Override
public List<FlowTotal> queryAll() {
return null;
}
@Override
public FlowTotal queryById(Integer id) {
return null;
}
@Override
public FlowTotal queryByCardNumber(String cardNo) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_flow_total where cardNumber = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
FlowTotal flowTotal = queryRunner.query(conn, sql, new BeanHandler<FlowTotal>(FlowTotal.class),cardNo);
return flowTotal;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int deleteByNum(String num) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "delete tb_flow_total from tb_flow_total where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,num);
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int insert(FlowTotal flowTotal) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "insert into tb_flow_total(cardNumber,flow) values (?,?)";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,flowTotal.getCardNumber(),flowTotal.getFlow());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update(FlowTotal flowTotal) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "update tb_flow_total set flow=? where cardNumber =?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,flowTotal.getFlow(),flowTotal.getCardNumber());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
}
MoboleCardDaoImpl
public class MoboleCardDaoImpl implements MoboleCardDao {
@Override
public MoboleCard queryByCardNo(String cardNo) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select id,card_number cardNumber,username,password,ser_package serPackage,money,status from tb_mobole_card where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
MoboleCard moboleCard = queryRunner.query(conn, sql, new BeanHandler<MoboleCard>(MoboleCard.class),cardNo);
return moboleCard;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public List<MoboleCard> queryAll() {
return null;
}
@Override
public MoboleCard queryById(Integer id) {
return null;
}
@Override
public int insert(MoboleCard moboleCard) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "insert into tb_mobole_card(card_number,username,password,ser_package,money,status) values (?,?,?,?,?,?)";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,moboleCard.getCardNumber(),moboleCard.getUsername()
,moboleCard.getPassword(),moboleCard.getSerPackage(),moboleCard.getMoney()
,moboleCard.getStatus());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update(MoboleCard moboleCard) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "update tb_mobole_card set money=? where card_number =?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,moboleCard.getMoney(),moboleCard.getCardNumber());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update1(MoboleCard moboleCard) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "update tb_mobole_card set ser_package = ? where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,moboleCard.getSerPackage(),moboleCard.getCardNumber());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update2(MoboleCard moboleCard) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "update tb_mobole_card set status = ? where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,moboleCard.getStatus(),moboleCard.getCardNumber());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
@Override
public int deleteByNum(String num) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "delete tb_mobole_card from tb_mobole_card where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,num);
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
}
MonthlyConsumptionRecordsDaoImpl
public class MonthlyConsumptionRecordsDaoImpl implements MonthlyConsumptionRecordsDao {
@Override
public List<MonthlyConsumptionRecords> queryAll() {
return null;
}
@Override
public MonthlyConsumptionRecords queryById(Integer id) {
return null;
}
@Override
public int insert(MonthlyConsumptionRecords monthlyConsumptionRecords) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "insert into tb_monthly_consumption_records(card_number,consum_amount,real_talk_time" +
",real_SMS_count,real_flow,consume_date) values (?,?,?,?,?,?)";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,monthlyConsumptionRecords.getCardNumber(),monthlyConsumptionRecords.getConsumAmount(),
monthlyConsumptionRecords.getRealTalkTime(),monthlyConsumptionRecords.getRealSmsCount(),
monthlyConsumptionRecords.getRealFlow(),DateUtil.formatDatetime(monthlyConsumptionRecords.getConsumeDate()));
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update(MonthlyConsumptionRecords monthlyConsumptionRecords) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "update tb_monthly_consumption_records set card_number = ?,consum_amount=?,real_talk_time=?" +
",real_SMS_count=?,real_flow=? where id = ? ";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,monthlyConsumptionRecords.getCardNumber(),monthlyConsumptionRecords.getConsumAmount(),
monthlyConsumptionRecords.getRealTalkTime(),monthlyConsumptionRecords.getRealSmsCount(),
monthlyConsumptionRecords.getRealFlow(),monthlyConsumptionRecords.getId());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
@Override
public MonthlyConsumptionRecords queryByCardNo(String cardNo) {
return null;
}
@Override
public MonthlyConsumptionRecords queryCurrentMonthRecprByCardNo(String cardNo, Date firstDayDate) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select id,card_number cardNumber,consum_amount consumAmount,real_talk_time realTalkTime," +
"real_SMS_count realSMSCount, real_flow realFlow,consume_date consumeDate from tb_monthly_consumption_records" +
" where card_number = ? and consume_date=?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
MonthlyConsumptionRecords monthlyConsumptionRecords = queryRunner.query(conn, sql, new BeanHandler<>(MonthlyConsumptionRecords.class),cardNo, DateUtil.formatDatetime(firstDayDate));
return monthlyConsumptionRecords;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int deleteByNum(String num) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "delete tb_monthly_consumption_records from tb_monthly_consumption_records where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,num);
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
}
RechargeRecordDaoImpl
public class RechargeRecordDaoImpl implements RechargeRecordDao {
@Override
public List<RechargeRecord> queryAll() {
return null;
}
@Override
public RechargeRecord queryById(Integer id) {
return null;
}
@Override
public int insert(RechargeRecord rechargeRecord) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "insert into tb_recharge_record(amount,recharge_date,card_number) values (?,?,?)";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,rechargeRecord.getAmount(), DateUtil.formatDate((Date) rechargeRecord.getRechargeDate()),rechargeRecord.getCardNumber());
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
@Override
public int update(RechargeRecord rechargeRecord) {
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
@Override
public int deleteByNum(String num) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "delete tb_recharge_record from tb_recharge_record where card_number = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
int row = queryRunner.update(conn,sql,num);
return row;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return 0;
}
}
SceneDaoImpl
public class SceneDaoImpl implements SceneDao {
@Override
public List<Scene> queryAll() {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_scene";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
List<Scene> scenes = queryRunner.query(conn, sql, new BeanListHandler<>(Scene.class));
return scenes;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn, null, null);
}
return null;
}
@Override
public Scene queryById(Integer id) {
return null;
}
@Override
public int insert(Scene scene) {
return 0;
}
@Override
public int update(Scene scene) {
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
}SerpackageDaoImpl
public class SerpackageDaoImpl implements SerpackageDao {
@Override
public List<Serpackage> queryAll() {
//jdbc操作
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql ="select * from tb_serpackage";
pstm = conn.prepareStatement(sql);
//执行
rs = pstm.executeQuery();
List<Serpackage> list = new ArrayList<>();
//解析rs,变成MoboleCard对象
Serpackage serpackage = null;
//如果还有下一个
while (rs.next()){
serpackage = new Serpackage();
serpackage.setId(rs.getInt("id"));
serpackage.setTalkTime(rs.getInt("talk_time"));
serpackage.setSmsCount(rs.getInt("sms_count"));
serpackage.setPrice(rs.getDouble("price"));
serpackage.setFlow(rs.getInt("flow"));
serpackage.setType(rs.getInt("type"));
list.add(serpackage);
}
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,pstm,rs);
}
return null;
}
@Override
public Serpackage queryById(Integer id) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select id,talk_time talkTime,sms_count smsCount,price,flow,type " +
"from tb_serpackage where id = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
Serpackage serpackage = queryRunner.query(conn, sql, new BeanHandler<Serpackage>(Serpackage.class),id);
return serpackage;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int insert(Serpackage serpackage) {
return 0;
}
@Override
public int update(Serpackage serpackage) {
return 0;
}
@Override
public int deleteById(Integer id) { return 0;
}
@Override
public Serpackage queryByType(Integer type) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select id,talk_time talkTime,sms_count smsCount,price,flow,type " +
"from tb_serpackage where type = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
Serpackage serpackage = queryRunner.query(conn, sql, new BeanHandler<Serpackage>(Serpackage.class),type);
return serpackage;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
}
SerpackageTypeDaoImpl
public class SerpackageTypeDaoImpl implements SerpackageTypeDao {
@Override
public List<SerpackageType> queryByAll() {
//jdbc操作
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql ="select * from tb_serpackage_type";
pstm = conn.prepareStatement(sql);
//执行
rs = pstm.executeQuery();
List<SerpackageType> list = new ArrayList<>();
//解析rs,变成MoboleCard对象
SerpackageType serpackageType = null;
//如果还有下一个
while (rs.next()){
serpackageType = new SerpackageType();
serpackageType.setId(rs.getInt("id"));
serpackageType.setName(rs.getString("name"));
list.add(serpackageType);
}
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,pstm,rs);
}
return null;
}
@Override
public SerpackageType querySerpackageTyptBy(Integer type) {
return null;
}
@Override
public List<SerpackageType> queryAll() {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select * from tb_serpackage_type";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
List<SerpackageType> serpackageTypes = queryRunner.query(conn, sql, new BeanListHandler<>(SerpackageType.class));
return serpackageTypes;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public SerpackageType queryById(Integer id) {
QueryRunner queryRunner = new QueryRunner();
//2.调用query()执行查询sql
String sql = "select name from tb_serpackage_type where id = ?";
Connection conn = null;
try {
conn = JDBCUtil.getConnection();
SerpackageType serpackageType = queryRunner.query(conn, sql, new BeanHandler<>(SerpackageType.class),id);
return serpackageType;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.close(conn,null,null);
}
return null;
}
@Override
public int insert(SerpackageType serpackageType) {
return 0;
}
@Override
public int update(SerpackageType serpackageType) {
return 0;
}
@Override
public int deleteById(Integer id) {
return 0;
}
}
(2)BaseDao
public interface BaseDao<T> {
/**
* 查询所有
*/
List<T> queryAll();
/**
* 根据主键查询
*/
T queryById(Integer id);
/**
* 添加记录
*/
int insert(T t);
/**
* 修改
*/
int update(T t);
/**
*根据主键删除
*/
int deleteById(Integer id);
}(3)CardDao
public interface CardDao extends BaseDao<Card> {
/**
* 根据状态查询卡
*/
List<Card> queryByStatus(int status);
/**
* 查询所有
* @return 卡的List集合
*/
List<Card> queryAll();
/**
* @param id
* @return 卡信息
*/
Card queryById(Integer id);
/**
* @param num
* @return 卡信息
*/
Card queryByNum(String num);
}
(4)ConsuminfoDao
public interface ConsuminfoDao extends BaseDao<Consuminfo> {
List<Consuminfo> queryByCardNumberAndMonth(String cardNumber, int month,int year);
/**
* 根据手机号删除信息
*/
int deleteByNum(String num);
}
(5)FlowDao
public interface FlowDao extends BaseDao<Flow> {
}
(6)FlowTotalDao
public interface FlowTotalDao extends BaseDao<FlowTotal> {
FlowTotal queryByCardNumber(String cardNo);
/**
* 根据手机号删除信息
*/
int deleteByNum(String num);
}
(7)MoboleCardDao
public interface MoboleCardDao extends BaseDao<MoboleCard> {
/**
* 根据手机号查询用户手机卡信息
*/
MoboleCard queryByCardNo(String cardNo);
/**
* 根据手机号删除用户手机卡信息
*/
int deleteByNum(String num);
/**
* 根据手机号修改用户手机卡套餐
*/
int update1(MoboleCard moboleCard);
/**
* 根据手机号修改用户手机卡是否冻结状态
*/
int update2(MoboleCard moboleCard);
}
(8)MonthlyConsumptionRecordsDao
public interface MonthlyConsumptionRecordsDao extends BaseDao<MonthlyConsumptionRecords> {
MonthlyConsumptionRecords queryByCardNo(String cardNo);
MonthlyConsumptionRecords queryCurrentMonthRecprByCardNo(String cardNo, Date firstDayDate);
/**
* 根据手机号删除信息
*/
int deleteByNum(String num);
}
(9)RechargeRecordDao
public interface RechargeRecordDao extends BaseDao<RechargeRecord> {
/**
* 根据手机号删除信息
*/
int deleteByNum(String num);
}
(10)SceneDao
public interface SceneDao extends BaseDao<Scene> {
}(11)SerpackageDao
public interface SerpackageDao extends BaseDao<Serpackage> {
/**
* 查询所有
* @return 套餐内容的List集合
*/
List<Serpackage> queryAll();
/**
* 通过套餐类型查询套餐明细
* @param
* @return 套餐信息
*/
Serpackage queryByType(Integer type);
}(12)SerpackageTypeDao
public interface SerpackageTypeDao extends BaseDao<SerpackageType> {
/**
* 查询所有
* @return 套餐名称的List集合
*/
List<SerpackageType> queryByAll();
/**
*
* @param
* @return 套餐信息
*/
SerpackageType querySerpackageTyptBy(Integer type);
}
(三)entity[实体类]
(1)BaseEntity
public class BaseEntity {
protected Integer id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
}(2)Card
public class Card extends BaseEntity {
private String cardNumber;
private Integer status;
public String getCardNumber() {
return cardNumber;
}
public Integer getStatus() {
return status;
}
public void setCardNumber(String cardNumber) {
this.cardNumber = cardNumber;
}
public void setStatus(Integer status) {
this.status = status;
}
public Card(String cardNumber, Integer status) {
this.cardNumber = cardNumber;
this.status = status;
}
public Card(){
}
@Override
public String toString() {
return "Card{" +
"id=" + id +
", cardNumber='" + cardNumber + '\'' +
", status=" + status +
'}';
}
}
(3)Consuminfo(同上相似)
(4)Flow(同上相似)
(5)FlowTotal(同上相似)
(6)MoboleCard(同上相似)
(7)MonthlyConsumptionRecords(同上相似)
(8)RechargeRecord(同上相似)
(9)Scene(同上相似)
(10)Serpackage(同上相似)
(11)SerpackageType(同上相似)
(四)service[业务层(功能业务类)]
主菜单
(1)登录
public MoboleCard login(){
System.out.print("请输入手机卡号:");
String cardNo = input.next();
System.out.print("请输入密码:");
String password = input.next();
//调用dao层类的方法
MoboleCardDaoImpl moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(cardNo);
if (moboleCard == null){//没有该手机号码
System.out.println("【友情提示】:卡号或者密码错误");
} else {//有该手机号码
//判断密码
if (!moboleCard.getPassword().equals(password)){
System.out.println("【友情提示】:卡号或者密码错误");
} else if (moboleCard.getStatus().intValue() == SystemConstant.MOBOLE_STATUS_FREEZE){
//冻结状态 1: 魔法数字, 可读性差, 容易出错, 使用常量替换
//public static final
System.out.println("【友情提示:】该手机号码已冻结,请联系工作人员!");
} else {
return moboleCard;//成功
}
}
return null;
}
(2)注册
public void register(){
//显示可用的卡
System.out.println("--------可选的卡号--------");
CardDao cardDao = new CardDaoImpl();
List<Card> cards = cardDao.queryByStatus(SystemConstant.CARD_STATUS_NORMAL);
for (int i = 0; i < cards.size(); i++) {
System.out.print((i+1) + "." +cards.get(i).getCardNumber() + "\t");
if ((i+1)%3 == 0){
System.out.println();
}
}
System.out.println();
//选择题卡
System.out.print("请选择你的卡号:");
int cardIndex = input.nextInt();
Card card = cards.get(cardIndex-1);
//显示所有的套餐
SerpackageTypeDaoImpl serpackageTypeDao = new SerpackageTypeDaoImpl();
List<SerpackageType> serpackageTypes = serpackageTypeDao.queryAll();
for (int i = 0; i < serpackageTypes.size(); i++) {
System.out.println((i+1) + "." +serpackageTypes.get(i).getName() + "\t");
}
//选择套餐
System.out.print("\n请选择套餐:");
int serpackageTypeIndex = input.nextInt();
SerpackageType serpackageType = serpackageTypes.get(serpackageTypeIndex-1);
//根据套餐类型查询套餐明细
SerpackageDaoImpl serpackageDao = new SerpackageDaoImpl();
Serpackage serpackage = serpackageDao.queryByType(serpackageType.getId());
System.out.print("请输入姓名:");
String name = input.next();
System.out.print("请输入密码:");
String pwd = input.next();
double money = 0;
do {
System.out.print("请输入预存话费金额:");
money = input.nextDouble();
if (money < serpackage.getPrice()){
System.out.println("你预存的话费不足以支付本月套餐资费,请重新输入!");
}
} while (money < serpackage.getPrice());
//往用户卡添加一条记录
MoboleCard moboleCard = new MoboleCard();
moboleCard.setCardNumber(card.getCardNumber());
moboleCard.setPassword(pwd);
moboleCard.setSerPackage(serpackageType.getId());
moboleCard.setStatus(SystemConstant.MOBOLE_STATUS_NORMAL);
moboleCard.setUsername(name);
moboleCard.setMoney(money - serpackage.getPrice());
MoboleCardDaoImpl moboleCardDao = new MoboleCardDaoImpl();
moboleCardDao.insert(moboleCard);
//往总流量信息中添加一条记录
FlowTotal flowTotal = new FlowTotal();
FlowTotalDao flowTotalDao = new FlowTotalDaoImpl();
flowTotal.setCardNumber(card.getCardNumber());
flowTotal.setFlow(serpackage.getFlow());
flowTotalDao.insert(flowTotal);
//修改卡的状态
card.setStatus(SystemConstant.CARD_STATUS_FREEZE);
cardDao.update(card);
//往充值记录表添加一条充值记录
RechargeRecord rechargeRecord = new RechargeRecord();
rechargeRecord.setAmount(money);
rechargeRecord.setRechargeDate(new Date());
rechargeRecord.setCardNumber(card.getCardNumber());
RechargeRecordDaoImpl rechargeRecordDao = new RechargeRecordDaoImpl();
rechargeRecordDao.insert(rechargeRecord);
System.out.println("【友情提示】:注册成功!");
System.out.println("你的卡的基本信息如下:");
System.out.println("卡号:" + moboleCard.getCardNumber() + " 用户名:" + moboleCard.getUsername() + " 当前余额:" + moboleCard.getMoney());
System.out.println("套餐信息:");
System.out.println("通话时长:" + serpackage.getTalkTime() + " 短信条数:" + serpackage.getSmsCount() +
" 套餐月资费:" + serpackage.getPrice() + " 上网流量:" + serpackage.getFlow());
} 
(3)使用嗖嗖
public void useSoso(){
System.out.println("--------使用嗖嗖--------");
System.out.print("请输入手机卡号:");
String cardNumber = input.next();
//根据手机卡号查询用户卡信息
MoboleCardDaoImpl moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(cardNumber);
if (moboleCard == null){//号码不存在
System.out.println("【错误提示】:卡号不存在,请重新输入!");
} else if (moboleCard.getStatus().intValue() == SystemConstant.MOBOLE_STATUS_FREEZE){
System.out.println("【错误提示】:卡号已经被冻结,请联系工作人员!");
} else {
//查询该卡的套餐
SerpackageDaoImpl serpackageDao = new SerpackageDaoImpl();
Serpackage serpackage = serpackageDao.queryById(moboleCard.getSerPackage());
//查询所有的场景
SceneDaoImpl sceneDao = new SceneDaoImpl();
List<Scene> scenes = sceneDao.queryAll();
//随机一个场景
Random random = new Random();
int index = random.nextInt(scenes.size());
Scene scene = scenes.get(index);
//查询卡当月月消费情况 根据手机号码与日期
MonthlyConsumptionRecordsDao mcrDao = new MonthlyConsumptionRecordsDaoImpl();
Date firstDay = DateUtil.getFirstDayDate(new Date());
MonthlyConsumptionRecords mcr = mcrDao.queryCurrentMonthRecprByCardNo(cardNumber, firstDay);
if (mcr == null){//没有消费记录
//初始化该卡当月消费记录
mcr = new MonthlyConsumptionRecords();
mcr.setCardNumber(cardNumber);
mcr.setConsumAmount(0.0);
mcr.setConsumeDate(firstDay);
mcr.setRealFlow(0);
mcr.setRealSmsCount(0);
mcr.setRealTalkTime(0);
//保存数据
mcrDao.insert(mcr);
//查询一次,保证得到新添加的月消费记录有id
mcr = mcrDao.queryCurrentMonthRecprByCardNo(cardNumber,firstDay);
}
FlowTotalDao flowTotalDao = new FlowTotalDaoImpl();
FlowTotal flowTotal = flowTotalDao.queryByCardNumber(cardNumber);
//计算卡的剩余余量
String type = "";//场景类型
int free = 0;//余量
if (scene.getType().equals("通话")){
//计算的通话的余量
type = "通话";
free = serpackage.getTalkTime() - mcr.getRealTalkTime();
mcr.setRealTalkTime(mcr.getRealTalkTime() + scene.getData());
} else if (scene.getType().equals("短信")){
//计算的短信的余量
type = "短信";
free = serpackage.getSmsCount() - mcr.getRealSmsCount();
mcr.setRealSmsCount(mcr.getRealSmsCount() + scene.getData());
} else if (scene.getType().equals("上网")){
//计算的上网的余量
type = "上网";
free = serpackage.getFlow() - mcr.getRealFlow();
mcr.setRealFlow(mcr.getRealFlow() + scene.getData());
}
//计算本次消费金额
double cost = 0.0;
//判断余量大于0,有余量 小于等于0:没有余量
//90 : 50 90 : 100
if (free <= 0){
cost = calcCost(type,scene.getData());
} else {//有余量
if (free - scene.getData() < 0){//余量不满足本次消费
cost = calcCost(type,scene.getData() - free);
}
}
//修改月消费记录
mcr.setConsumAmount(mcr.getConsumAmount() + cost);
//保存到数据库
mcrDao.update(mcr);
flowTotal.setFlow(flowTotal.getFlow() - mcr.getRealFlow());
flowTotal.setCardNumber(cardNumber);
flowTotalDao.update(flowTotal);
//修改用户卡余额
if (cost > 0){//本次消费产生金额
moboleCard.setMoney(moboleCard.getMoney() - cost);
moboleCardDao.update(moboleCard);
}
//新添加一条消费记录
Consuminfo consuminfo = new Consuminfo();
consuminfo.setCardNumber(cardNumber);
consuminfo.setType(type);
consuminfo.setConsumeDate(new Date());
consuminfo.setConsumData(scene.getData());
//保存到数据库
ConsuminfoDaoImpl consuminfoDao = new ConsuminfoDaoImpl();
consuminfoDao.insert(consuminfo);
//打印消费信息
System.out.println(scene.getDescription());
}
}private double calcCost(String type, int num) {
double cost = 0.0;
if (type.equals("通话")){
cost = 0.2 * num;
} else if (type.equals("短信")){
cost = 0.1 * num;
} else if (type.equals("上网")){
cost = 0.1 * num;
}
return cost;
} 
(4)话费充值
public void recharge(){
System.out.println("--------话费充值--------");
try {
System.out.print("请输入要充值的卡号:");
String cardNumber = input.next();
System.out.print("请输入充值金额:");
double money = input.nextDouble();
MoboleCard moboleCard = new MoboleCard();
MoboleCardDao moboleCardDao = new MoboleCardDaoImpl();
//总金额
double total = money + moboleCardDao.queryByCardNo(cardNumber).getMoney();
//修改记录表中的金额
moboleCard.setMoney(total);
moboleCard.setCardNumber(cardNumber);
moboleCardDao.update(moboleCard);
//往充值记录表添加一条充值记录
RechargeRecord rechargeRecord = new RechargeRecord();
rechargeRecord.setAmount(money);
rechargeRecord.setRechargeDate(new Date());
rechargeRecord.setCardNumber(cardNumber);
RechargeRecordDaoImpl rechargeRecordDao = new RechargeRecordDaoImpl();
rechargeRecordDao.insert(rechargeRecord);
System.out.println("【友情提示】:充值成功,卡上余额:" + moboleCard.getMoney() + "元");
} catch (Exception e){
System.out.println("【友情提示】:充值失败!卡号不存在或者充值金额小于0");
}
}
(5)资费说明
public void tariffDescription(){
SerpackageDao sd = new SerpackageDaoImpl();
SerpackageTypeDao std = new SerpackageTypeDaoImpl();
System.out.println("--------套餐说明--------");
System.out.println("序号\t套餐名称\t通话时长(分/月)\t短信条数(条/月)\t上网流量(MB/月)");
for (int i = 0; i < std.queryByAll().size(); i++) {
System.out.println(std.queryByAll().get(i).getId()+"\t"
+std.queryByAll().get(i).getName() +"\t\t"
+sd.queryAll().get(i).getTalkTime()+"\t\t\t\t"
+sd.queryAll().get(i).getSmsCount()+"\t\t\t\t"
+sd.queryAll().get(i).getFlow());
}
}
(6)申请解冻
public void applyThawing(){
System.out.println("--------申请解冻--------");
System.out.print("请输入你的号码:");
String cardNumber = input.next();
MoboleCardDao moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(cardNumber);
if (moboleCard.getMoney() > 0){
if (moboleCard.getStatus() == 1){
//修改号码状态为正常状态
moboleCard.setStatus(SystemConstant.CARD_STATUS_NORMAL);
moboleCard.setCardNumber(cardNumber);
moboleCardDao.update2(moboleCard);
System.out.println("你的号码解冻成功!");
} else {
System.out.println("【友情提示】:你的号码为正常状态,不需要解冻!");
}
} else {
System.out.println("【友情提示】:你的号码为欠费状态,无法解冻,请先充费!");
}
} 
二级菜单
(1)本月账单查询
public void billInquiry(){
System.out.println("--------本月账单查询--------");
//查询当前登录的手机号对应的MoboleCard对象
MoboleCardDao moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(SosoApp.loginCardNumber);
//查询套餐
SerpackageDao serpackageDao = new SerpackageDaoImpl();
Serpackage serpackage = serpackageDao.queryById(moboleCard.getSerPackage());
MonthlyConsumptionRecordsDao mcrDao = new MonthlyConsumptionRecordsDaoImpl();
MonthlyConsumptionRecords mcr = mcrDao.queryCurrentMonthRecprByCardNo(SosoApp.loginCardNumber,DateUtil.getFirstDayDate(new Date()));
System.out.println("你的卡号:"+moboleCard.getCardNumber()+",当月账单信息:");
System.out.println("套餐资费:"+serpackage.getPrice()+"元");
double total = serpackage.getPrice();//合计
if (mcr != null){
total += mcr.getConsumAmount();
}
System.out.println("合计:"+total+"元");
System.out.println("账户余额:"+moboleCard.getMoney()+"元");
} 
(2)套餐余量查询
public void marginQuery(){
System.out.println("--------套餐余量查询--------");
//查询当前登录的手机号对应的MoboleCard对象
MoboleCardDao moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(SosoApp.loginCardNumber);
//查询套餐
SerpackageDao serpackageDao = new SerpackageDaoImpl();
Serpackage serpackage = serpackageDao.queryById(moboleCard.getSerPackage());
MonthlyConsumptionRecordsDao mcrDao = new MonthlyConsumptionRecordsDaoImpl();
MonthlyConsumptionRecords mcr = mcrDao.queryCurrentMonthRecprByCardNo(SosoApp.loginCardNumber,DateUtil.getFirstDayDate(new Date()));
System.out.println("你的卡号:"+moboleCard.getCardNumber()+",套餐内剩余:");
int talkTime = serpackage.getTalkTime();//通话时长
int smsCount = serpackage.getSmsCount();//短信条数
int flow = serpackage.getFlow();//上网流量
if (mcr.getRealTalkTime() != null){
talkTime -= mcr.getRealTalkTime();
} else if (mcr.getRealSmsCount() != null){
smsCount -= mcr.getRealSmsCount();
}else if (mcr.getRealFlow() != null){
flow -= mcr.getRealFlow();
}
System.out.println("通话时长:"+talkTime+"分钟");
System.out.println("短信条数:"+smsCount+"条");
System.out.println("上网流量:"+flow+"MB");
}
(3)打印消费详单
public void detailedConsumptionList(){
System.out.println("--------打印消费详单--------");
System.out.print("请输入本年需要查询的月份(1-12):");
int month = input.nextInt();
int year = DateUtil.getYear(new Date());
ConsuminfoDao consuminfoDao = new ConsuminfoDaoImpl();
List<Consuminfo> consuminfos = consuminfoDao.queryByCardNumberAndMonth(SosoApp.loginCardNumber, month,year);
System.out.println("序号\t类型\t\t数据\t\t\t\t日期");
if (month >= 1 && month <= 12){
for (int i = 0; i < consuminfos.size(); i++) {
System.out.println(i+1 + "\t" + consuminfos.get(i).getType() + "\t\t"
+ consuminfos.get(i).getConsumData() + "\t\t" + consuminfos.get(i).getConsumeDate());
}
} else {
System.out.println("【友情提示】:对不起,不存在本卡号" + month + "月消费记录");
}
}
(4)套餐变更
public void packageChanges(){
SerpackageTypeDao std = new SerpackageTypeDaoImpl();
//查询当前登录的手机号对应的MoboleCard对象(用户卡信息)
MoboleCardDao moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(SosoApp.loginCardNumber);
System.out.println("--------套餐变更--------");
System.out.println("你现在的套餐为:" + std.queryById(moboleCard.getSerPackage()).getName());
//输出所有套餐
for (int i = 0; i < std.queryByAll().size(); i++) {
System.out.print(std.queryByAll().get(i).getId()+"."+std.queryByAll().get(i).getName() + "\t");
}
System.out.print("请选择(序号):");
int num = input.nextInt();
//根据套餐类型查询套餐明细
SerpackageDaoImpl serpackageDao = new SerpackageDaoImpl();
Serpackage serpackage = serpackageDao.queryByType(num);
//判断用户选择的套餐是否是用户卡现在套餐
if (moboleCard.getSerPackage() != num){
if (moboleCard.getMoney() < serpackage.getPrice()){
System.out.println("【友情提示】:对不起,您的余额不足以支付新套餐本月资费，请充值后办理变更套餐业务!");
} else {
//修改套餐的类型
moboleCard.setSerPackage(serpackage.getType());
moboleCard.setCardNumber(SosoApp.loginCardNumber);
moboleCardDao.update1(moboleCard);
moboleCard.setMoney(moboleCard.getMoney() - serpackage.getPrice());
moboleCard.setCardNumber(SosoApp.loginCardNumber);
moboleCardDao.update(moboleCard);
System.out.println("【友情提示】:更换套餐成功!" + std.queryByAll().get(num-1).getName()
+ ":通话时长:" + serpackage.getTalkTime()
+ "分钟/月,短信条数:" + serpackage.getSmsCount()
+ "条/月,上网流量:" + serpackage.getFlow()
+ "MB/月,月租:" + serpackage.getPrice() + "元/月");
}
} else {
System.out.println("【友情提示】:你已经是改套餐的用户,无需更换!");
}
} 
(5)充值流量
public void rechargeTraffic(){
System.out.println("--------充值流量--------");
FlowDao flowDao = new FlowDaoImpl();
System.out.println("序号\t上网流量(MB/月)\t价格(元)");
for (int i = 0; i < flowDao.queryAll().size(); i++) {
System.out.println(flowDao.queryAll().get(i).getId()+"\t\t"+flowDao.queryAll().get(i).getFlow()+"\t\t"+flowDao.queryAll().get(i).getPrice());
}
System.out.println("请选择要充值的流量套餐序号:");
int num = input.nextInt();
//通过序号获取流量信息
Flow flow = flowDao.queryById(num);
//通过号码获取卡信息
MoboleCardDaoImpl moboleCardDao = new MoboleCardDaoImpl();
MoboleCard moboleCard = moboleCardDao.queryByCardNo(SosoApp.loginCardNumber);
//获取卡余额
double money = moboleCard.getMoney();
if (money < flow.getPrice()){
System.out.println("你的话费不足以支付流量资费,请充值后购买!");
} else {
//修改余额
moboleCard.setMoney(money - flow.getPrice());
moboleCard.setCardNumber(SosoApp.loginCardNumber);
moboleCardDao.update(moboleCard);
//修改总流量
FlowTotalDao flowTotalDao = new FlowTotalDaoImpl();
FlowTotal flowTotal = flowTotalDao.queryByCardNumber(SosoApp.loginCardNumber);
flowTotal.setFlow(flow.getFlow() + flowTotal.getFlow());
flowTotal.setCardNumber(SosoApp.loginCardNumber);
flowTotalDao.update(flowTotal);
System.out.println("充值完成!");
}
}
(6)办理退网
public void withdrawalNetwork(){
System.out.println("--------办理退网--------");
System.out.print("是否确定办理退网(true/false):");
String result = input.next();
if (result.equals("true")){
CardDao cardDao = new CardDaoImpl();
Card card = cardDao.queryByNum(SosoApp.loginCardNumber);
//修改卡的状态
card.setStatus(SystemConstant.CARD_STATUS_NORMAL);
cardDao.update(card);
//删除用户该号码表记录
MoboleCardDaoImpl moboleCardDao = new MoboleCardDaoImpl();
moboleCardDao.deleteByNum(SosoApp.loginCardNumber);
RechargeRecordDao rechargeRecordDao = new RechargeRecordDaoImpl();
rechargeRecordDao.deleteByNum(SosoApp.loginCardNumber);
FlowTotalDao flowTotalDao = new FlowTotalDaoImpl();
flowTotalDao.deleteByNum(SosoApp.loginCardNumber);
ConsuminfoDao consuminfoDao = new ConsuminfoDaoImpl();
consuminfoDao.deleteByNum(SosoApp.loginCardNumber);
MonthlyConsumptionRecordsDao monthlyConsumptionRecordsDao = new MonthlyConsumptionRecordsDaoImpl();
monthlyConsumptionRecordsDao.deleteByNum(SosoApp.loginCardNumber);
System.out.println("【友情提示】:卡号" + SosoApp.loginCardNumber + "办理退网成功!");
} else {
System.out.println("【友情提示】:办理退网失败!");
}
} 
(五)ui[显示层(菜单显示)]
SosoApp
public class SosoApp {
public static String loginCardNumber = null;//保存当前登录的用户的手机号码
private SosoService sosoService = new SosoService();
public static void main(String[] args) {
new SosoApp().showFirstMenu();
}
/**
* 一级菜单
*/
public void showFirstMenu(){
System.out.println("-----------------------欢迎使用嗖嗖移动大厅-----------------------");
System.out.println("1.用户登录\t2.用户注册\t3.使用嗖嗖\t4.话费充值\t5.资费说明\t6.申请解冻\t7.退出系统");
System.out.print("请选择:");
Scanner input = new Scanner(System.in);
int choose = input.nextInt();
switch (choose){
case 1://登录
MoboleCard currenUser = sosoService.login();
loginCardNumber = currenUser.getCardNumber();
if (currenUser != null){
//显示二级菜单
showSecondMenu();
} else {//登录失败
//显示一级菜单
showFirstMenu();
}
break;
case 2://注册
sosoService.register();
//显示一级菜单
showFirstMenu();
break;
case 3://使用嗖嗖
sosoService.useSoso();
//显示一级菜单
showFirstMenu();
break;
case 4://话费充值
sosoService.recharge();
//显示一级菜单
showFirstMenu();
break;
case 5://资费说明
sosoService.tariffDescription();
//显示一级菜单
showFirstMenu();
break;
case 6://申请解冻
sosoService.applyThawing();
//显示一级菜单
showFirstMenu();
break;
case 7://退出系统
System.out.println("欢迎使用本软件,系统正在退出....");
System.exit(0);
break;
default:
System.out.println("【错误提示:】没有该选项,请重新输入~");
//回调
showFirstMenu();
break;
}
}
/**
* 二级菜单
*/
public void showSecondMenu(){
System.out.println("-----------------------嗖嗖移动用户菜单-----------------------");
System.out.println("1.本月账单查询\n2.套餐余量查询\n3.打印消费详单\n4.套餐变更\n5.充值流量\n6.办理退网");
System.out.print("请选择(输入1~6选择功能,，其他键返回上一级):");
Scanner input = new Scanner(System.in);
int choose = input.nextInt();
switch (choose){
case 1://本月账单查询
sosoService.billInquiry();
//返回上级菜单
showSecondMenu();
break;
case 2://套餐余量查询
sosoService.marginQuery();
//返回上级菜单
showSecondMenu();
break;
case 3://打印消费详单
sosoService.detailedConsumptionList();
//返回上级菜单
showSecondMenu();
break;
case 4://套餐变更
sosoService.packageChanges();
//返回上级菜单
showSecondMenu();
break;
case 5://充值流量
sosoService.rechargeTraffic();
//返回上级菜单
showSecondMenu();
break;
case 6://办理退网
sosoService.withdrawalNetwork();
//返回上级菜单
showFirstMenu();
break;
default:
//返回一级菜单
showFirstMenu();
break;
}
}
}
(六)util[工具类]
(1)DateUtil
public class DateUtil {
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private static SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
/**
* 把java.util.Date转换为yyyy-MM-dd HH:mm:ss格式字符串
*/
public static String formatDate(Date date){
return sdf.format(date);
}
/**
* 把java.util.Date转换为yyyy-MM-dd格式字符串
*/
public static String formatDatetime(Date date){
return sdf1.format(date);
}
/**
* 把java.util.Date转换为yyyy格式字符串
*/
public static int getYear(Date date){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
int year = calendar.get(Calendar.YEAR);
return year;
}
/**
* 得到指定日期的当月1号的日期
* @param date
* @return
*/
public static Date getFirstDayDate(Date date){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
//把日期修改为1
calendar.set(Calendar.DATE,1);
//转为String类型
return calendar.getTime();
}
}
(2)JDBCUtil
public class JDBCUtil {
static String db_properties = "db.properties";
static Properties properties = new Properties();
static {
// 包裹选中的代码, ctrl + alt + T
try {
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream(db_properties);
properties.load(is);
Class.forName(properties.getProperty("jdbc.driver"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(properties.getProperty("jdbc.url"),properties.getProperty("jdbc.username"),properties.getProperty("jdbc.password"));
}
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
//6.关闭资源 倒序关 先开的后关, 后开的先关
try {
if(resultSet!=null){
resultSet.close();
}
if(preparedStatement!=null){
preparedStatement.close();
}
if(connection!=null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
(七)db.properties
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/soso?useUnicode=true&characterEncoding=utf8&useSSL=false
jdbc.username = mysql数据库名字
jdbc.password = 数据库密码(八)test[测试类]
public class CardDaoTest {
//测试方法
@Test //使用该注解，标记在方法上，表示这个方法是一个测试方法
// 测试方法的要求: 1．返回值只能是void 2．方法必须是无参方法 3.该方法不能是静态方法
public void queryAll() {
}
@Test
public void queryById() {
}
}三、错误解决
错误引起原因:
数据库的表名和实体类的变量名不一致
解决:
给查找语句添加别名
四、项目总结与收获
- 跟着老师的思路,渐渐完成了各个项目功能,学会了怎么搭建项目的结构
- 遇到问题并渐渐学会了解决问题
- 可以自己独立完成一些新的功能
- 对所学java知识掌握的更加牢固
20
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
