JDBC 分层开发(day2 银行系统的代码优化DBCP技术)
文章目录
DBCP,事务,工具类封装
1.DBCP
DBCP:数据库连接池,是Java数据库连接池中的一种。由Apache开发。通过数据库链接会可以让程序自动的管理数据库连接的释放和断开。
作用:由于建立数据库连接是一个非常耗时耗资源的资源。所以数据库连接池预先同数据库建立了一些连接,并且把链接放入到内存中,应用程序需要建立数据库连接时直接从连接池中申请一个就可以,用完再放回去。
2.使用数据库连接池
2.1 导入Jar包
具体操作可以见我上篇博客
点这儿看我上一篇文章
所需要的jar包文件如下:
Jar包
2.2配置DBCP
BasicDataSource bts = new BasicDataSource();
bts.setDriverClassName("com.mysql.cj.jdbc.Driver");
//com.mysql.cj.jdbc.Driver mysql6 使用时候需要设置时区
//com.mysql.jdbc.Driver mysql5 使用时不需要设置时区
bts.setUrl(".....:3306/test1");
bts.setUsername("root");
bts.setPassword("1234");
bts.setMaxActive(10);
2.3原生DBCP的缺点
使用上面配置的DBCP数据库连接池存在硬编码的缺陷
2.4 使用资源文件来创建数据库连接池
详情见ConnUtil工具类
3.编写ConnUtil工具类
3.1 编写资源文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/class4?characterEncoding=utf8&serverTimezone=Shanghai
username=root
password=1234
maxActive=30
maxIdle=10
maxWait=60000
3.2编写代码
package com.lxk.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
/**
*
* <p>title: ConnUtil</p>
* <p>Description: </p>
* @author
* @date 2021年5月11日
* 1.获取数据库连接对象的方法
* 2.关闭数据库连接对象的方法
*/
public class ConnUtil {
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
private static DataSource ds;
//在静态代码块中进行数据源对象的加载
static{
try {
Properties properties = new Properties();
InputStream is = ConnUtil.class.getResourceAsStream("/mysql.properties");
properties.load(is);
ds = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
*
* @return
* @throws SQLException
* 获取数据库连接对象
*/
public static Connection getConn() throws SQLException{
Connection conn = threadLocal.get();
if(conn==null||conn.isClosed()) {
conn = ds.getConnection();
threadLocal.set(conn);
}
return conn;
}
public static void closeConn() {
try {
Connection conn = threadLocal.get();
if(conn!=null&&!conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
threadLocal.set(null);
}
}
}
4.事务
4.1 事务的概念
事务是应用程序中一系列严密的操作,所有操作必须全部完成,否则在每个操作中所作的所有更改都会被撤销(事务是一组数据库操作,要么一起成功,要不一起失败),也就是说事务具备原子性。
4.2 事务的四大特性
数据库事务正确执行的四个基本要素。ACID
原子性:整个事务中所有的操作,要不全部成功要不全部失败,不可能停滞在中间的某个环节,事务在执行过程中发生错误会被回滚到事务开始前的状态,就相当于事务没有执行过。
一致性:在事务开始之前和事务结束以后,数据库的完全性没有被破坏。
隔离性:
持久性:在事务完成之后,该事务对所修改的数据持久的保存到数据中,不会被回滚。
白话:
原子性:一组事务,要么成功要么回滚。
一致性:转账。无论事务是否执行成功,参与转账的两个账户金额的和是不变的。
隔离性:事务独立运行。一个事务处理后的结果如果影响了其他事务,那么其他事务会回滚。
持久性:
4.3 JDBC中如何处理事务
除了查询操作其他操作都需要事务。事务在service层中处理。
在JDBC中都是通过Connection来处理事务。Connection中有三个方法和事务相关
setAutoCommit(boolean):设置是否自动提交事务。如果为true表示自动提交,
此方法的默认值就是true,也就是说每执行一条sql都是一个单独的事务。如果设置为false那么
相当于开启了手动事务。
commit() 提交事务
rollback() 回滚事务
演示代码看视频资料
4.4 事务的隔离级别
4.4.1 事务的并发存在的问题
脏读:读取到另外一个事务没有提交的数据(一定要杜绝的)
不可重复读:两次读取的数据不一致(针对的一条数据)
幻读(虚读):读到到另外一个事务已提交的数据(针对的是一张表)
4.4.2 事务的隔离级别
1.串行化(SERIALIZABLE):不会出现任何并发问题。因为它是同一个数据的访问是串行的不是并发的。
2.可重复读(REPEATABLE_READ):可以防止脏读和不可重复读。不能处理幻读。
3.读已提交的数据(READ_COMMITTED):防止脏读。不能处理不可重复读和幻读。
4.读未提交的数据(READ_UNCOMMITTED):任何并发的问题都可能出现。
效率问题:4>3>2>1
mysql默认的 事务隔离级别是可重复读
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
主要优化代码
新增数据库连接池工具类
1.在项目的src目录下新建资源文件mysql.properties
2.在资源文件中添加以下内容用于给项目读取数据库信息
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/class04?characterEncoding=utf8&serverTimezone=Asia/Shanghai
username=root
password=root
maxActive=30
maxIdle=10
maxWait=6000
3.数据库连接工具类
connUtil.java
package com.yy.Util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
/**
* @ClassName: connUtil
* @Description:获取数据库连接对象,关闭数据库连接对象
* @author:
* @date: 2021年5月11日 上午11:17:08
* @Copyright:
*/
public class connUtil {
private static ThreadLocal<Connection> threadload=new ThreadLocal<>();
private static DataSource ds;
static {//数据源对象加载
try {
Properties properties = new Properties();
InputStream ins=connUtil.class.getResourceAsStream("/mysql.properties");
properties.load(ins);
ds=BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取数据库连接对象
public static Connection getConn() throws SQLException{
Connection conn=threadload.get();
if(conn==null||conn.isClosed()) {
conn=ds.getConnection();
threadload.set(conn);
}
return conn;
}
//关闭数据库连接对象
public static void closeConn() {
Connection conn=threadload.get();
try {
if(conn!=null&&!conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
threadload.set(conn);
}
}
}
重新优化后的代码
原版代码及详细结构看我上一篇文章
BankMain.java
package com.yy.view;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import com.yy.bean.BankCard;
import com.yy.bean.BankManager;
import com.yy.bean.BankUser;
import com.yy.controller.BankController;
/
* @ClassName: BankMain
* @Description:相当于视图层
* @author:
* @date: 2021年5月10日 下午4:00:38
* @Copyright:
*/
public class BankMain {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
BankController bankcontroller = new BankController();
//一级界面完成管理员和普通用户功能的选择调用
System.out.println("*************欢迎使用太平洋银行管理系统***********");
System.out.println("1.管理员登录");
System.out.println("2.普通用户登录");
System.out.println("********************************************");
System.out.println("请输入您的选择:");
int key1=sc.nextInt();
switch (key1) {
case 1://管理员登录
System.out.println("请输入管理员账户名:");
String ManagerName=sc.next();
System.out.println("请输入管理员密码:");
String ManagerPwd=sc.next();
BankManager bm = new BankManager();
bm.setMname(ManagerName);
bm.setMpass(ManagerPwd);
String mname=bankcontroller.ManagerLogin(bm);
if(mname!=null) {
System.out.println("欢迎管理员:"+mname+"使用本系统!");
}
boolean Flag1=true;
while(Flag1) {
System.out.println("===================");
System.out.println("1.新增用户信息");
System.out.println("2.新增银行卡信息");
System.out.println("3.修改银行卡密码");
System.out.println("4.删除银行卡");
System.out.println("5.查看所有银行卡信息");
System.out.println("6.根据手机号查看银行卡信息");
System.out.println("7.查看所有用户信息");
System.out.println("8.删除指定的用户");
System.out.println("===================");
System.out.println("请输入您的选择:");
int key2=sc.nextInt();
switch (key2) {
case 1://新增用户信息
BankUser bu = new BankUser();
System.out.println("请输入用户名:");
bu.setUname(sc.next());
System.out.println("请输入手机号:");
bu.setUphone(sc.nextInt());
System.out.println("请输入公司名称:");
bu.setCompany(sc.next());
System.out.println("请输入籍贯:");
bu.setHometown(sc.next());
boolean f=bankcontroller.Finduserbyphone(bu);
if(!f) {
if(bankcontroller.adduserInfo(bu)) {
System.out.println("添加成功!");
}
}else {
System.out.println("该手机号已存在!无法进行添加。");
}
break;
case 2://新增银行卡信息
BankCard bc=new BankCard();
BankUser bu2=new BankUser();
bc.setBankuser(bu2);
List<BankCard> list=new ArrayList<BankCard>();
System.out.println("请输入新建银行卡的密码:");
bc.setCpssword(sc.nextInt());
System.out.println("请输入新建银行卡的预存金额:");
bc.setCmoney(sc.nextDouble());
boolean f2=true;
while(f2) {//如果不按照正确的提示输入则一直循环,提醒重新输入为预期值为止
System.out.println("请输入新建银行卡的类型(储蓄卡/信用卡)");
String ctype=sc.next();
if(ctype.equals("储蓄卡")||ctype.equals("信用卡")) {
bc.setCtype(ctype);
f2=false;
}else{
System.out.println("请按提示重新输入!");
}
}
List<BankUser> list4=bankcontroller.showAllBankUserInfo();
for (BankUser bu4 : list4) {
System.out.println(bu4.getUid()+"——"+bu4.getUname()+"——"+bu4.getUphone());
}
System.out.println("请输入新建银行卡的新ID:");
bc.getBankuser().setUid(sc.nextInt());
if(bankcontroller.addcardInfo(bc)) {
System.out.println("添加成功!");
}
break;
case 3://修改银行卡信息
System.out.println("请输入要修改密码的银行卡卡号:");
Long cid=sc.nextLong();
boolean f3=bankcontroller.FindBankcardbycid(cid);
if(f3) {
System.out.println("请输入要修改的密码:");
int pwd=sc.nextInt();
boolean f4=true;
while(f4) {
System.out.println("请再次输入新密码:");
int newpwd=sc.nextInt();
if(pwd==newpwd) {
if(bankcontroller.updatecardpwd(cid, newpwd)) {
System.out.println("修改成功!");
f4=false;
}
}else {
System.out.println("两次密码输入不一致!");
}
}
}else {
System.out.println("该银行卡不存在!无法进行密码修改。");
}
break;
case 4://删除银行卡
System.out.println("请输入要修改密码的银行卡卡号:");
Long ncid=sc.nextLong();
boolean f4=bankcontroller.FindBankcardbycid(ncid);
if(f4) {
if(bankcontroller.deletecardbycid(ncid)) {
System.out.println("删除成功!");
}
}else {
System.out.println("该银行卡不存在!无需操作");
}
break;
case 5://查看所有银行卡信息
List<BankCard> list1=bankcontroller.showAllBankCardInfo();
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
System.out.println("银行卡号"+"\t\t"+"密码"+"\t\t"+"余额"+"\t\t"+"银行卡类型"+"\t\t"
+"用户号"+"\t\t"+"用户名"+"\t\t"+"电话"+"\t\t\t"+"公司"+"\t\t"+"籍贯");
for (BankCard bc2 : list1) {
System.out.println(bc2);
}
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
break;
case 6://根据条件(手机号)查看银行卡信息
System.out.println("请输入所绑定的手机号:");
int userphone=sc.nextInt();
List<BankCard> list2=bankcontroller.showBankCardInfobyphone(userphone);
if(list2.isEmpty()) {
System.out.println("对不起,您输入的手机号未绑定过任何银行卡!");
}else {
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
System.out.println("银行卡号"+"\t\t"+"密码"+"\t\t"+"余额"+"\t\t"+"银行卡类型"+"\t\t"
+"用户号"+"\t\t"+"用户名"+"\t\t"+"电话"+"\t\t\t"+"公司"+"\t\t"+"籍贯");
for (BankCard bc3 : list2) {
System.out.println(bc3);
}
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
}
break;
case 7://查看所有用户信息
List<BankUser> list3=bankcontroller.showAllBankUserInfo();
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
System.out.println("用户号"+"\t\t"+"姓名"+"\t\t"+"电话"+"\t\t\t"+"公司名"+"\t\t"+"籍贯");
for (BankUser bu1 : list3) {
System.out.println(bu1);
}
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
break;
case 8://删除指定的用户
System.out.println("请输入要删除的用户的手机号");
int deletephone =sc.nextInt();
if(bankcontroller.deleteUserandCardbyphone(deletephone)) {
System.out.println("删除成功!");
}
break;
default:
System.out.println("您的输入有误!");
break;
}
System.out.println("是否继续? Y/N");
if(sc.next().equals("N")) {
Flag1=false;
System.out.println("欢迎下次使用!");
}
}
break;
case 2://普通用户登录
System.out.println("请输入您的银行卡号:");
Long cardid=sc.nextLong();
System.out.println("请输入您的密码:");
int cardpwd=sc.nextInt();
BankCard bc=new BankCard();
bc.setCid(cardid);
bc.setCpssword(cardpwd);
boolean flag=bankcontroller.CardLogin(bc);
if(flag) {
System.out.println("登陆成功!");
}else {
System.out.println("登陆失败!");
}
boolean Flag2=true;
while(Flag2) {
System.out.println("===================");
System.out.println("1.取钱功能");
System.out.println("2.存钱功能");
System.out.println("3.转账功能");
System.out.println("4.查看自己银行卡信息");
System.out.println("===================");
System.out.println("请输入您的选择:");
int key3=sc.nextInt();
switch (key3) {
case 1:
System.out.println("请输入您要取的金额:");
int gmoney=sc.nextInt();
bankcontroller.getmoney(gmoney,cardid);
break;
case 2:
System.out.println("请输入您要存的金额:");
int smoney=sc.nextInt();
bankcontroller.setmoney(smoney,cardid);
break;
case 3:
System.out.println("请输入您要转账的银行卡号:");
Long ncardid=sc.nextLong();
System.out.println("请输入您要转账的 金额:");
int tmoney=sc.nextInt();
bankcontroller.transf(tmoney,cardid,ncardid);
System.out.println("转账成功!");
break;
case 4:
BankCard bc_my=bankcontroller.showcardInfobymycid(cardid);
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
System.out.println("银行卡号"+"\t\t"+"密码"+"\t\t"+"余额"+"\t\t"+"银行卡类型"+"\t\t"
+"用户号"+"\t\t"+"用户名"+"\t\t"+"电话"+"\t\t\t"+"公司"+"\t\t"+"籍贯");
System.out.println(bc_my);
System.out.println("---------------------------------------------------------------------------------------------------------------------------------------------------");
break;
default:
System.out.println("您的输入有误!");
break;
}
System.out.println("是否继续? Y/N");
if(sc.next().equals("N")) {
Flag2=false;
System.out.println("欢迎下次使用!");
}
}
break;
default:
System.out.println("您的输入有误!程序退出");
System.exit(0);
break;
}
}
}
BankController.java
package com.yy.controller;
import java.util.List;
import com.yy.bean.BankCard;
import com.yy.bean.BankManager;
import com.yy.bean.BankUser;
import com.yy.service.BankService;
import com.yy.service.impl.BankServiceImpl;
/**
* @ClassName: BankController
* @Description:控制层
* @author:
* @date: 2021年5月10日 下午12:17:37
* @Copyright:
*/
public class BankController {
BankService bankservice=new BankServiceImpl();
//管理员登录功能
public String ManagerLogin(BankManager bm) {
return bankservice.ManagerLogin(bm);//得到管理员账户名
}
public boolean CardLogin(BankCard bc) {
return bankservice.CardLogin(bc);//获得是否存在的标志
}
public List<BankCard> showAllBankCardInfo(){//查看所有的银行卡信息
return bankservice.showAllBankCardInfo();
}
public List<BankCard> showBankCardInfobyphone(int userphone){//通过手机号查询银行卡信息
return bankservice.showBankCardInfobyphone(userphone);
}
public List<BankUser> showAllBankUserInfo(){//查看所有用户信息
return bankservice.showAllBankUserInfo();
}
public boolean Finduserbyphone(BankUser bu) {//在添加用户信息之前先查询表中是否有该用户
return bankservice.Finduserbyphone(bu);
}
public boolean adduserInfo(BankUser bu) {//添加用户信息
return bankservice.adduserInfo(bu);
}
public boolean addcardInfo(BankCard bc){//添加银行卡信息
return bankservice.addcardInfo(bc);
}
public boolean FindBankcardbycid(Long cid) {//查询是否有这个卡号的银行卡
return bankservice.FindBankcardbycid(cid);
}
public boolean updatecardpwd(Long cid,int npwd) {//修改银行卡密码
return bankservice.updatecardpwd(cid, npwd);
}
public boolean deletecardbycid(Long cid) {
return bankservice.deletecardbycid(cid);
}
public BankCard showcardInfobymycid(Long cardid) {//用户登录之后查看自己的银行卡信息
return bankservice.showcardInfobymycid(cardid);
}
public boolean deleteUserandCardbyphone(int deletephone) {//同时删除用户以及他的银行卡
return bankservice.deleteUserandCardbyphone(deletephone);
}
public void getmoney(int gmoney, Long cardid) {//取款功能
bankservice.getmoney(gmoney, cardid);
}
public void setmoney(int smoney, Long cardid) {//存款功能
bankservice.setmoney(smoney, cardid);
}
public void transf(int tmoney, Long cardid, Long ncardid) {//转账功能
bankservice.transf(tmoney, cardid, ncardid);
}
}
BankService.java
package com.yy.service;
import java.util.List;
import com.yy.bean.BankCard;
import com.yy.bean.BankManager;
import com.yy.bean.BankUser;
/**
* @ClassName: BankService
* @Description:业务层的接口
* @author:
* @date: 2021年5月10日 下午3:58:49
* @Copyright:
*/
public interface BankService {
public String ManagerLogin(BankManager bm);
public boolean CardLogin(BankCard bc);
public List<BankCard> showAllBankCardInfo();
public List<BankCard> showBankCardInfobyphone(int userphone);
public boolean Finduserbyphone(BankUser bu);
public boolean adduserInfo(BankUser bu);
public List<BankUser> showAllBankUserInfo();
public boolean addcardInfo(BankCard bc);
public boolean FindBankcardbycid(Long cid);
public boolean updatecardpwd(Long cid,int npwd);
public boolean deletecardbycid(Long cid);
public BankCard showcardInfobymycid(Long cardid);
public int find_userbyphone(int deletephone);
public boolean deleteUserandCardbyphone(int deletephone);
public void getmoney(int gmoney, Long cardid);
public void setmoney(int smoney, Long cardid);
public void transf(int tmoney, Long cardid, Long ncardid);
}
BankServiceImpl.java
package com.yy.service.impl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import com.yy.Util.connUtil;
import com.yy.bean.BankCard;
import com.yy.bean.BankManager;
import com.yy.bean.BankUser;
import com.yy.dao.BankDao;
import com.yy.dao.impl.BankDaoImpl;
import com.yy.service.BankService;
/**
* @ClassName: BankServiceImpl
* @Description:处理请求的业务以及事务的处理
* @author:
* @date: 2021年5月10日 下午3:59:18
* @Copyright:
*/
public class BankServiceImpl implements BankService{
BankDao bankdao=new BankDaoImpl();
@Override
public String ManagerLogin(BankManager bm) {
try {
return bankdao.ManagerLogin(bm);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return "";
}
//如果查询返回值是1则说明查询到此人,返回是0则说明未查询到此人
@Override
public boolean CardLogin(BankCard bc) {
int flag=0;
try {
flag = bankdao.CardLogin(bc);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
if(flag>0)
return true;
return false;
}
@Override
public List<BankCard> showAllBankCardInfo() {
try {
return bankdao.showAllBankCardInfo();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return null;
}
@Override
public List<BankCard> showBankCardInfobyphone(int userphone) {
try {
return bankdao.showBankCardInfobyphone(userphone);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return null;
}
@Override
public boolean Finduserbyphone(BankUser bu) {
try {
if(bankdao.Finduserbyphone(bu)>0)
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public boolean adduserInfo(BankUser bu) {
try {
if(bankdao.adduserInfo(bu)>0)
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public List<BankUser> showAllBankUserInfo() {
try {
return bankdao.showAllBankUserInfo();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return null;
}
@Override
public boolean addcardInfo(BankCard bc) {
try {
if(bankdao.addcardInfo(bc)>0)
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public boolean FindBankcardbycid(Long cid) {
try {
if(bankdao.FindBankcardbycid(cid)>0)
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public boolean updatecardpwd(Long cid,int npwd) {
try {
if(bankdao.updatecardpwd(cid, npwd)>0)
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public boolean deletecardbycid(Long cid) {
try {
if(bankdao.deletecardbycid(cid)>0)
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public BankCard showcardInfobymycid(Long cardid) {
try {
return bankdao.showcardInfobymycid(cardid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return null;
}
@Override
public boolean deleteUserandCardbyphone(int deletephone) {
int flag1=0;
int flag2=0;
Connection conn=null;
try {
int uid=bankdao.find_userbyphone(deletephone);
if(uid>0) {
conn=connUtil.getConn();
conn.setAutoCommit(false);
flag1=bankdao.deleteUserbyuid(uid);
flag2=bankdao.deleteCardbyuid(uid);
conn.commit();
if(flag1>0&&flag2>0)
return true;
}else {
System.out.println("对不起,您删除的用户不存在!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
try {
conn.rollback();//如果事务执行异常则回滚
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally {
connUtil.closeConn();
}
return false;
}
@Override
public int find_userbyphone(int deletephone) {
try {
return bankdao.find_userbyphone(deletephone);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
return 0;
}
@Override
public void getmoney(int gmoney, Long cardid) {
try {
double omoney=bankdao.findmoney(cardid);
if((omoney-gmoney)>0) {
bankdao.getmoney(gmoney, cardid);
}
else {
System.out.println("对不起,您的账户余额不足!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
}
@Override
public void setmoney(int smoney, Long cardid) {
try {
bankdao.setmoney(smoney, cardid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
connUtil.closeConn();
}
}
@Override
public void transf(int tmoney, Long cardid, Long ncardid) {
Connection conn=null;
try {
double omoney=bankdao.findmoney(cardid);
if((omoney-tmoney)>0) {
conn=connUtil.getConn();
conn.setAutoCommit(false);
bankdao.getmoney(tmoney, cardid);
bankdao.setmoney(tmoney, ncardid);
conn.commit();
}else {
System.out.println("对不起,您的账户余额不足!不能转账。");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}finally {
connUtil.closeConn();
}
}
}
BankDao.java
package com.yy.dao;
import java.sql.SQLException;
import java.util.List;
import com.yy.bean.BankCard;
import com.yy.bean.BankManager;
import com.yy.bean.BankUser;
/**
* @ClassName: BankDao
* @Description:持久化层接口
* @author:
* @date: 2021年5月10日 下午3:57:03
* @Copyright:
*/
public interface BankDao {
public String ManagerLogin(BankManager bm) throws SQLException;
public int CardLogin(BankCard bc)throws SQLException;
public List<BankCard> showAllBankCardInfo()throws SQLException;
public List<BankCard> showBankCardInfobyphone(int userphone)throws SQLException;
public int Finduserbyphone(BankUser bu)throws SQLException;
public int adduserInfo(BankUser bu)throws SQLException;
public List<BankUser> showAllBankUserInfo()throws SQLException;
public int addcardInfo(BankCard bc)throws SQLException;
public int FindBankcardbycid(Long cid)throws SQLException;
public int updatecardpwd(Long cid,int npwd)throws SQLException;
public int deletecardbycid(Long cid)throws SQLException;
public BankCard showcardInfobymycid(Long cardid)throws SQLException;
public int deleteUserbyuid(int uid)throws SQLException;
public int deleteCardbyuid(int uid) throws SQLException;
public int find_userbyphone(int deletephone)throws SQLException;
public double findmoney(Long cardid) throws SQLException;
public void getmoney(int gmoney, Long cardid)throws SQLException;
public void setmoney(int smoney, Long cardid)throws SQLException;
}
BankDaoImpl.java
package com.yy.dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.net.ssl.HostnameVerifier;
import com.yy.Util.connUtil;
import com.yy.bean.BankCard;
import com.yy.bean.BankManager;
import com.yy.bean.BankUser;
import com.yy.dao.BankDao;
/**
* @ClassName: BankDaoImpl
* @Description:数据库操作
* @author:
* @date: 2021年5月10日 下午3:58:04
* @Copyright:
*/
public class BankDaoImpl implements BankDao{
//查询管理员的账户名
@Override
public String ManagerLogin(BankManager bm) throws SQLException {
String mname="";
Connection conn=connUtil.getConn();
String sql="select mname from bankmanager where mname=? and mpass=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, bm.getMname());
ps.setString(2, bm.getMpass());
ResultSet rs=ps.executeQuery();
while(rs.next()) {
mname=rs.getString("mname");
}
return mname;
}
//查询是否有这个银行账户,用于登录
@Override
public int CardLogin(BankCard bc) throws SQLException{
int flag=0;
Connection conn=connUtil.getConn();
String sql="select count(*) from bankcard where cid=? and cpassword=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, bc.getCid());
ps.setInt(2,bc.getCpssword());
ResultSet rs=ps.executeQuery();
while(rs.next()) {
flag=rs.getInt(1);
}
return flag;
}
@Override
//查询银行卡所有信息
public List<BankCard> showAllBankCardInfo() throws SQLException{
List<BankCard> list=new ArrayList<BankCard>();
Connection conn=connUtil.getConn();
String sql="select bc.*,bu.uname,bu.uphone,bu.company,bu.hometown from bankcard bc,bankuser bu where bc.uid=bu.uid";
Statement stat= conn.createStatement();
ResultSet rs=stat.executeQuery(sql);
while(rs.next()) {
BankCard bc=new BankCard();
BankUser bu=new BankUser();
bc.setBankuser(bu);
bc.setCid(rs.getLong("cid"));
bc.setCpssword(rs.getInt("cpassword"));
bc.setCmoney(rs.getDouble("cmoney"));
bc.setCtype(rs.getString("ctype"));
bc.getBankuser().setUid(rs.getInt("uid"));
bc.getBankuser().setUname(rs.getString("uname"));
bc.getBankuser().setUphone(rs.getInt("uphone"));
bc.getBankuser().setCompany(rs.getString("company"));
bc.getBankuser().setHometown(rs.getString("hometown"));
list.add(bc);
}
return list;
}
@Override
//通过手机号查询银行卡信息
public List<BankCard> showBankCardInfobyphone(int userphone) throws SQLException{
List<BankCard> list=new ArrayList<BankCard>();
Connection conn=connUtil.getConn();
String sql="select bc.*,bu.uname,bu.uphone,bu.company,bu.hometown from bankcard bc,bankuser bu where bc.uid=bu.uid and bu.uphone=?";
PreparedStatement ps= conn.prepareStatement(sql);
ps.setInt(1, userphone);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
BankCard bc=new BankCard();
BankUser bu=new BankUser();
bc.setBankuser(bu);
bc.setCid(rs.getLong("cid"));
bc.setCpssword(rs.getInt("cpassword"));
bc.setCmoney(rs.getDouble("cmoney"));
bc.setCtype(rs.getString("ctype"));
bc.getBankuser().setUid(rs.getInt("uid"));
bc.getBankuser().setUname(rs.getString("uname"));
bc.getBankuser().setUphone(rs.getInt("uphone"));
bc.getBankuser().setCompany(rs.getString("company"));
bc.getBankuser().setHometown(rs.getString("hometown"));
list.add(bc);
}
return list;
}
@Override
//通过手机查询是否有这个用户
public int Finduserbyphone(BankUser bu) throws SQLException{
int flag=0;
Connection conn=connUtil.getConn();
String sql="select count(*) from bankuser where uphone=?";
PreparedStatement ps= conn.prepareStatement(sql);
ps.setInt(1,bu.getUphone());
ResultSet rs=ps.executeQuery();
while(rs.next()) {
flag=rs.getInt(1);
}
return flag;
}
public int find_userbyphone(int deletephone) throws SQLException{
int flag=0;
Connection conn=connUtil.getConn();
String sql="select uid from bankuser where uphone=?";
PreparedStatement ps= conn.prepareStatement(sql);
ps.setInt(1,deletephone);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
flag=rs.getInt(1);
}
return flag;
}
@Override
//向用户表添加信息
public int adduserInfo(BankUser bu) throws SQLException{
int flag=0;
Connection conn=connUtil.getConn();
String sql="insert into bankuser(uname,uphone,company,hometown) values (?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, bu.getUname());
ps.setInt(2, bu.getUphone());
ps.setString(3, bu.getCompany());
ps.setString(4, bu.getHometown());
flag=ps.executeUpdate();
return flag;
}
@Override
//查询所有用户信息
public List<BankUser> showAllBankUserInfo() throws SQLException{
List<BankUser> list=new ArrayList<BankUser>();
Connection conn=connUtil.getConn();
String sql="select * from bankuser";
Statement stat= conn.createStatement();
ResultSet rs=stat.executeQuery(sql);
while(rs.next()) {
BankUser bu=new BankUser();
bu.setUid(rs.getInt(1));
bu.setUname(rs.getString(2));
bu.setUphone(rs.getInt(3));
bu.setCompany(rs.getString(4));
bu.setHometown(rs.getString(5));
list.add(bu);
}
return list;
}
@Override
public int addcardInfo(BankCard bc) throws SQLException {
int flag=0;
Connection conn=connUtil.getConn();
String sql="insert into bankcard(cpassword,cmoney,ctype,uid) values (?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, bc.getCpssword());
ps.setDouble(2, bc.getCmoney());
ps.setString(3, bc.getCtype());
ps.setInt(4, bc.getBankuser().getUid());
flag=ps.executeUpdate();
return flag;
}
@Override
//通过银行卡号查询
public int FindBankcardbycid(Long cid) throws SQLException {
int flag=0;
Connection conn=connUtil.getConn();
String sql="select count(*) from bankcard where cid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1,cid);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
flag=rs.getInt(1);
}
return flag;
}
//通过卡号修改密码
@Override
public int updatecardpwd(Long cid,int npwd) throws SQLException{
int flag=0;
Connection conn=connUtil.getConn();
String sql="update bankcard set cpassword=? where cid=?";
PreparedStatement ps= conn.prepareStatement(sql);
ps.setInt(1, npwd);
ps.setLong(2, cid);
flag=ps.executeUpdate();
return flag;
}
@Override
public int deletecardbycid(Long cid) throws SQLException {
int flag=0;
Connection conn=connUtil.getConn();
String sql="delete from bankcard where cid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, cid);
flag=ps.executeUpdate();
return flag;
}
@Override
public BankCard showcardInfobymycid(Long cardid)throws SQLException {
BankCard bc=new BankCard();
BankUser bu=new BankUser();
bc.setBankuser(bu);
Connection conn=connUtil.getConn();
String sql="select bc.*,bu.uname,bu.uphone,bu.company,bu.hometown from bankcard bc,bankuser bu where bc.uid=bu.uid and cid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1,cardid);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
bc.setCid(rs.getLong("cid"));
bc.setCpssword(rs.getInt("cpassword"));
bc.setCmoney(rs.getDouble("cmoney"));
bc.setCtype(rs.getString("ctype"));
bc.getBankuser().setUid(rs.getInt("uid"));
bc.getBankuser().setUname(rs.getString("uname"));
bc.getBankuser().setUphone(rs.getInt("uphone"));
bc.getBankuser().setCompany(rs.getString("company"));
bc.getBankuser().setHometown(rs.getString("hometown"));
}
return bc;
}
@Override
public int deleteUserbyuid(int uid) throws SQLException {
int flag=0;
Connection conn=connUtil.getConn();
String sql="delete from bankuser where uid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, uid);
flag=ps.executeUpdate();
return flag;
}
public int deleteCardbyuid(int uid) throws SQLException {
int flag=0;
Connection conn=connUtil.getConn();
String sql="delete from bankcard where uid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, uid);
return flag;
}
public double findmoney(Long cardid) throws SQLException{
double money=0;
Connection conn=connUtil.getConn();
String sql="select cmoney from bankcard where cid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, cardid);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
money=rs.getDouble(1);
}
return money;
}
@Override
public void getmoney(int gmoney, Long cardid) throws SQLException{
Connection conn=connUtil.getConn();
String sql="update bankcard set cmoney=cmoney-? where cid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDouble(1, gmoney);
ps.setLong(2, cardid);
ps.executeUpdate();
}
@Override
public void setmoney(int smoney, Long cardid) throws SQLException {
Connection conn=connUtil.getConn();
String sql="update bankcard set cmoney=cmoney+? where cid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDouble(1, smoney);
ps.setLong(2, cardid);
ps.executeUpdate();
}
}
今天的代码行大概估算就1100行左右,比昨天没优化过的代码少了大概500行左右
使用工具类大大减少了冗余的代码。