一.项目需求:
功能介绍:
在这个阶段项目中初步了解了项目的分层:
首先创建一个文件夹lib存放项目的依赖包:
我这里的依赖包分别是c3p0连接池、JDBC工具包以及JDBC驱动
然后项目的具体分层如下:
bean层:这个层主要是实体类,方便我们后续的数据操作
dao层:数据持久层,程序和数据库进行交互的部分
各个实体类涉及到的数据库操作都在这个层完成,首先写出各个实体类在Dao层的接口,在接口中写出功能实现所可能需要的增、删、改、查方法,然后再写出实现类,这样条理更加清晰
service层:服务层,项目功能实现的地方,依赖与Dao层,因为很多功能牵扯到数据的改变,必须和数据库交互,和Dao层一样,分为接口和实现类
view层:视图层,用户可见的操作界面,此项目只用了简单的控制台输出来作为视图。
各个功能的具体实现:
用户登录:
Dao层:
@Override
public MoboleCard findMoboleCardByCardNumber(String CardNumber) throws SQLException {
//从数据库中查找指定号码,若找到则说明已经注册,只需要验证密码
String sql = "SELECT * FROM `tb_mobole_card` WHERE `card_number` = ?";
return qr.query(sql,new BeanHandler<MoboleCard>(MoboleCard.class),CardNumber);
}
service层:
public String login(String cardNumber, String password) {
try {
MoboleCard moboleCard = dao.findMoboleCardByCardNumber(cardNumber);
if(moboleCard == null){
//查看数据库中是否有此账号
return "账号未注册!";
}
if(!moboleCard.getPassword().equals(password)){
//密码验证
return "密码错误!";
}
if(moboleCard.getStatus() == 1){
return "账号已冻结!";
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return "登录成功!";
}
用户注册:
dao层:
public boolean addMoboleCard(MoboleCard moboleCard) throws SQLException {
Connection con = JdbcUtil.getConnection();
String sql = "INSERT INTO `tb_mobole_card`(`card_number`,`username`,`password`,`ser_package`,`money`,`status`) VALUES(?,?,?,?,?,?)";
Object params[] = {
moboleCard.getCard_number(),moboleCard.getUsername(),moboleCard.getPassword(),
moboleCard.getSer_package(),moboleCard.getMoney(),moboleCard.getStatus()
};
int num = qr.update(con,sql,params);
JdbcUtil.release(con);
return num > 0 ? true : false;
}
service层:
public String register(MoboleCard moboleCard) {
boolean value = false;
try {
JdbcUtil.beginTransaction();
boolean result = dao.addMoboleCard(moboleCard);
if(result){
value = cardDao.updateCardStatus(moboleCard.getCard_number(),1);
}
JdbcUtil.commitTransaction();
} catch (SQLException throwables) {
try {
JdbcUtil.rollBackTransaction();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}
return value ? "注册成功!":"注册失败!";
}
使用嗖嗖:
使用嗖嗖功能是最复杂的功能,牵扯到各种套餐数据的更改以及卡上余额的修改,由于各个表之间相互关联,一个表发生改变,关联表也要更改,所以涉及到事务。
public boolean useSoso(String cardNumber, Scene scene, double money) {
try {
//开启事务
JdbcUtil.beginTransaction();
//添加消费记录
Consuminfo consuminfo = new Consuminfo();
consuminfo.setCard_number(cardNumber);
consuminfo.setType(scene.getType());
consuminfo.setConsum_data(scene.getData());
consuminfo.setConsume_date(new Date());
consuminfoDao.addConsuminfo(consuminfo);
//修改月消费记录,没有则插入
Calendar cl = Calendar.getInstance();
int year = cl.get(Calendar.YEAR);
int month = cl.get(Calendar.MONTH);
MonthlyConsumptionRecords records =
monthlyConsumptionRecordsDao.findCardNumberMonthlyRecord(cardNumber.trim(),year,month+1);
if(records == null){
records = new MonthlyConsumptionRecords();
records.setCard_number(cardNumber);
records.setConsum_amount(serPackageDao.findSerPackageByCardNumber(cardNumber).getPrice()+money);
records.setConsume_date(new Date());
if(scene.getType().equals("通话")){
records.setReal_talk_time(scene.getData());
}
if(scene.getType().equals("上网")){
records.setReal_flow(scene.getData());
}
if(scene.getType().equals("短信")){
records.setReal_SMS_count(scene.getData());
}
monthlyConsumptionRecordsDao.addMonthlyRecord(records);
}else {
records.setConsum_amount(records.getConsum_amount()+money);
if(scene.getType().equals("通话")){
records.setReal_talk_time(records.getReal_talk_time()+ scene.getData());
}
if(scene.getType().equals("上网")){
records.setReal_flow(records.getReal_flow() + scene.getData());
}
if(scene.getType().equals("短信")){
records.setReal_SMS_count(records.getReal_SMS_count()+ scene.getData());
}
records.setConsume_date(new Date());
monthlyConsumptionRecordsDao.updateMonthlyRecord(records);
}
//修改账户余额
MoboleCard moboleCard = dao.findMoboleCardByCardNumber(cardNumber);
moboleCard.setMoney(moboleCard.getMoney()-money);
dao.updateMoboleCard(moboleCard);
JdbcUtil.commitTransaction();
return true;
} catch (SQLException throwables) {
try {
JdbcUtil.rollBackTransaction();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}
return false;
}
//添加消费记录的dao层方法:
public boolean addConsuminfo(Consuminfo consuminfo) throws SQLException {
Connection con = JdbcUtil.getConnection();
String sql = "INSERT INTO `tb_consuminfo`(`card_number`,`type`,`consum_data`,`consume_date`) VALUES(?,?,?,?)";
Object params[] = {
consuminfo.getCard_number(),consuminfo.getType(),consuminfo.getConsum_data(),consuminfo.getConsume_date()
};
int num = qr.update(con,sql,params);
JdbcUtil.release(con);
return num > 0 ? true:false;
}
//如果有该月的消费记录则更改该月消费记录的数据:
public boolean updateMonthlyRecord(MonthlyConsumptionRecords records) throws SQLException {
Connection con = JdbcUtil.getConnection();
String sql = "UPDATE `tb_monthly_consumption_records` SET `card_number` = ? , `consum_amount` = ? ," +
"`real_talk_time` = ? ,`real_SMS_count` = ? ,`real_flow` = ? , `consume_date` = ? " +
"WHERE `id` = ?";
Object params[] = {
records.getCard_number(),records.getConsum_amount(), records.getReal_talk_time(),
records.getReal_SMS_count(),records.getReal_flow(),
new java.sql.Date(records.getConsume_date().getTime()),records.getId()
};
int num = qr.update(con,sql,params);
JdbcUtil.release(con);
return num >0 ? true : false ;
}
//若没有则添加该月的消费记录:
public boolean addMonthlyRecord(MonthlyConsumptionRecords records) throws SQLException {
Connection con = JdbcUtil.getConnection();
String sql = "INSERT INTO `tb_monthly_consumption_records` (`card_number`,`consum_amount`,`real_talk_time`," +
"`real_SMS_count`,`real_flow`,`consume_date`) VALUES(?,?,?,?,?,?)";
Object params[] = {
records.getCard_number(),records.getConsum_amount(), records.getReal_talk_time(),
records.getReal_SMS_count(),records.getReal_flow(),
new java.sql.Date(records.getConsume_date().getTime())
};
int num = qr.update(con,sql,params);
JdbcUtil.release(con);
return num >0 ? true : false ;
}
//注意:
牵扯到事务,要确保事务内的sql执行使用的是同一个Connection,这里直接使用的是工具类,具体原理下次出个讲解文章
话费充值:
增加一条充值记录,并且修改账号余额
public boolean rechargeMoney(MoboleCard moboleCard) {
try {
dao.updateMoboleCard(moboleCard);
return true;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
//更新账户余额
public boolean updateMoboleCard(MoboleCard moboleCard) throws SQLException {
Connection con = JdbcUtil.getConnection();
String sql = "UPDATE `tb_mobole_card` SET `card_number` = ?,`username` = ?,`password` = ?,`ser_package` = ?," +
"`money` = ?,`status` = ? WHERE `id` = ?";
Object params[] = {
moboleCard.getCard_number(),moboleCard.getUsername(),moboleCard.getPassword(),moboleCard.getSer_package(),
moboleCard.getMoney(),moboleCard.getStatus(),moboleCard.getId()
};
int num = qr.update(con,sql,params);
JdbcUtil.release(con);
return num > 0 ? true:false;
//增加充值记录
public boolean addRechargeRecord(RechargeRecord rechargeRecord) {
try {
rechargeRecordDao.addRechargeRecord(rechargeRecord);
return true;
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public boolean addRechargeRecord(RechargeRecord rechargeRecord) throws SQLException {
Connection con = JdbcUtil.getConnection();
String sql = "insert into `tb_recharge_record` (`amount`,`recharge_date`,`card_number`) values(?,?,?)";
Object params[] = {
rechargeRecord.getAmount(),new java.sql.Date(rechargeRecord.getRecharge_date().getTime()),rechargeRecord.getCard_number()
};
int num = qr.update(con,sql,params);
JdbcUtil.release(con);
return num>0 ? true :false;
}
资费说明:这个方法比较简单,就是查看所有的套餐
public List<SerPackage> findAllSerPackage() {
try {
return dao.findAllSerPackage();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}//返回一个集合,里面是所有的套餐的对象
public List<SerPackage> findAllSerPackage() throws SQLException {
String sql = "SELECT * FROM `tb_serpackage`";
return qr.query(sql,new BeanListHandler<SerPackage>(SerPackage.class));
}//BeanListHandler是结果集处理器,这里用的是JDBC工具类里面封装好了的,将结果集处理成Bean实体类型的集合
本月账单查询:
public MonthlyConsumptionRecords findCardNumberMonthlyRecord(String cardNumber, int year, int month) {
//查询某卡号指定年月的月消费记录
try {
return dao.findCardNumberMonthlyRecord(cardNumber, year, month);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public MonthlyConsumptionRecords findCardNumberMonthlyRecord(String cardNumber, int year, int month) throws SQLException {
String sql = "SELECT * FROM `tb_monthly_consumption_records` WHERE `card_Number` = ? AND YEAR(`consume_date`) = ? AND MONTH(`consume_date`) = ?";
return qr.query(sql,new BeanHandler<MonthlyConsumptionRecords>(MonthlyConsumptionRecords.class),cardNumber,year,month);
}
套餐余量查询:根据套餐包含的通话时长,短信条数,以及月消费计算余量
public static void serPackageSurplus(){
String cardNumber = currentCard.get();
//查询该账号对应套餐
SerPackage serPackage = serPackageService.findSerPackageByCardNumber(cardNumber);
//查询该账号该月消费信息
Calendar cl = Calendar.getInstance();
//获取年月信息
int year = cl.get(Calendar.YEAR);
int month = cl.get(Calendar.MONTH) + 1;
//找出对应年月的月消费记录
MonthlyConsumptionRecords monthlyConsumptionRecords = monthlyConsumptionRecordsService.findCardNumberMonthlyRecord(cardNumber,year,month);
System.out.println("*********套餐余量查询**********");
System.out.println("您的卡号是"+cardNumber+",套餐内剩余:");
int talk_time = serPackage.getTalk_time() - monthlyConsumptionRecords.getReal_talk_time();
if(talk_time < 0 ){
talk_time = 0;
}
System.out.println("通话时长:"+talk_time+"分钟");
int sms_count = serPackage.getSms_count() - monthlyConsumptionRecords.getReal_SMS_count();
if(sms_count < 0 ){
sms_count = 0;
}
System.out.println("短信条数:"+sms_count+"条");
int flow = serPackage.getFlow()-monthlyConsumptionRecords.getReal_flow();
if(flow < 0 ){
flow = 0;
}
System.out.println("套餐流量:"+flow+"GB");
}
打印消费详情:打印对应卡号所使用过的服务以及对应消费
public List<Consuminfo> findmMonthConsuminfoByCardNumber(String cardNumber, int month) {
//因为一般都不可能直进行一次消费所以返回的是一个集合
try {
return dao.findmMonthConsuminfoByCardNumber(cardNumber,month);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public List<Consuminfo> findmMonthConsuminfoByCardNumber(String cardNumber, int month) throws SQLException {
String sql = "SELECT * FROM `tb_consuminfo` WHERE `card_number` = ? AND YEAR(`consume_date`) = YEAR(NOW()) AND MONTH(`consume_date`) = ? ";
return qr.query(sql,new BeanListHandler<Consuminfo>(Consuminfo.class),cardNumber,month);
}
套餐变更:修改套餐,并扣去新套餐的消费
public String altersepakage(String cardnumber, String pakagename) {
try {
//获得当前号码的套餐
SerPackage serPackage = serPackageDao.findSerPackageByCardNumber(cardnumber);
String type = serPackage.getName();
//和要变更的套餐对比
if(type.equals(pakagename)){
return "【友情提示】:您已是该套餐的用户,无需更改";
}else {
//获得新套餐的资费
List<SerPackage> list = serPackageDao.findAllSerPackage();
double money = 0;
for(SerPackage value:list){
if(value.getName().equals(pakagename)){
serPackage = value;
money = value.getPrice();
}
}
//看账号余额够不够支付新套餐本月资费
MoboleCard moboleCard = findMoleInfoByCardNumber(cardnumber);
if(moboleCard.getMoney()<money){//余额不够支付
return "【友情提示】:对不起,您的余额不足以支付新套餐的本月资费,请充值后办理套餐变更的业务!";
}
//开启事务
JdbcUtil.beginTransaction();
//添加新的消费记录
Consuminfo consuminfo = new Consuminfo();
consuminfo.setCard_number(cardnumber);
consuminfo.setConsum_data((int)(money));
consuminfo.setType("新套餐开通");
consuminfo.setConsume_date(new Date());
consuminfoDao.addConsuminfo(consuminfo);
//修改月消费记录,没有则插入
Calendar cl = Calendar.getInstance();
int year = cl.get(Calendar.YEAR);
int month = cl.get(Calendar.MONTH);
MonthlyConsumptionRecords records =
monthlyConsumptionRecordsDao.findCardNumberMonthlyRecord(cardnumber.trim(),year,month+1);
//没有月消费记录
if(records==null){
records = new MonthlyConsumptionRecords();
records.setCard_number(cardnumber);
records.setConsum_amount(money);
records.setConsume_date(new Date());
monthlyConsumptionRecordsDao.addMonthlyRecord(records);
}else {
records.setConsum_amount(records.getConsum_amount()+money);
records.setConsume_date(new Date());
monthlyConsumptionRecordsDao.updateMonthlyRecord(records);
}
//余额足够支付,更改新套餐并扣除余额
moboleCard.setSer_package(serPackage.getId());
moboleCard.setMoney(moboleCard.getMoney()-money);
dao.updateMoboleCard(moboleCard);
JdbcUtil.commitTransaction();
}
String str = "【友情提示】:更换套餐成功!"+serPackage.getName()+":通话时长:"+serPackage.getTalk_time()+"分钟/月,短信条数:"+serPackage.getSms_count()
+"条/月,上网流量:"+serPackage.getFlow()+"GB/月,月租:"+serPackage.getPrice()+"元/月";
return str;
} catch (SQLException e) {
try {
JdbcUtil.rollBackTransaction();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}
return "未知错误,更改套餐失败";//这里是事务出现错误回滚的情况
}
总结:
该项目基本上运用了第一阶段java的面向对象,封装,接口等知识,然后还有JDBC,主要难点个人感觉是表与表之间数据的关联,操作时需要考虑全面,然后就是事务具体的原理。