JDBC 结构优化2
结构优化2 - ATM系统(存,取,转,查)
1 Service
什么是业务?
- 代表用户完成的一个业务功能，可以由一个或多个DAO的调用组成。
- 软件所提供的一个功能叫业务。
核心思想:
-
用户角度: 功能即业务
- 转账这一功能就是一个业务
-
服务者角度: 业务包含多个步骤
- 从A账户扣钱
- 给B账户加钱
-
程序设计角度: 每一个步骤都是一个方法
-
AccountDao
- save() //存钱
- take() //取钱
-
AccountService
-
transfer(){ dao.take(A); dao.save(B); }
-
-
**目的：**DAO提高数据操作的重用性，Service提高业务功能的重用性。
2 事务
概念：
- 事务是一个原子操作，由一个或多个SQL语句组成。
- 在同一个事务当中，所有的SQL语句要么全部执行成功，要么全部失败。
事务的边界：
- 在JDBC中，先获得Connection对象。
- 开始事务：conn.setAutoCommit(false); #set autocommit=0;
- 手动提交事务：conn.commit(); #commit
- 手动回滚事务：conn.rollback(); #rollback;
在Service中，调用了多次DAO操作。每一个业务功能都要控制事务。
在Service层中控制事务：
问题: 当程序发生异常，事务回滚并没有成功。
原因：Service控制事务和DAO访问数据库时的连接对象，并非同一个
注意: 在一个业务流程中，要保证多个数据库访问操作的连接对象是同一个。
解决方法:
- 可以将整个线程中（单线程）中，存储一个共享值。
- 线程拥有一个类似Map的属性，键值对结构<ThreadLocal对象，值>。
3 ThreadLocal
ThreadLocal（线程本地变量）：
- 每一个线程通过ThreadLocal绑定一个连接对象。
- 在整个流程中任一环节可以存值或取值。
ThreadLocal应用流程：
ThreadLocal应用：
-
在释放资源的方法中，连接对象关闭后，提供threadLocal.remove()。
-
将关闭后的连接从当前ThreadLocal中移除。
4 事务的封装
现有问题：
- 事务的开启、提交、回滚的代码都写在了Service中。
- Service的主要职责是模拟现实业务功能。要将事务的控制封装在工具类中。
工具类封装事务控制方法：
- 开启事务：begin();
代码演示:
// 4 开启事务
public static void begin() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.setAutoCommit(false);
}
}
- 提交事务：commit();
代码演示:
// 5 提交事务
public static void commit() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.commit();
}
}
- 回滚事务：rollback();
代码演示:
// 6 回滚事务
public static void rollback() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.rollback();
}
}
- 关闭连接close(); 解除绑定连接
代码演示:
// 3 释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
//判断是否开启事务, 如果没有开启事务, 要关闭
//若开启事务, 则在事务结束后手动关闭
if (connection.getAutoCommit()) {
connection.close();
//从线程中移除
threadLocal.remove();
}
}
} catch (SQLException e) {
System.out.println("资源释放失败: "+e.getMessage());
throw new RuntimeException(e);
}
}
// 7 关闭事务连接
public static void close() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.close();
threadLocal.remove();
}
}
ATM系统
代码演示:
Dao层
Dao接口:
public interface AccountDao {
//查询
Account select(String cardNo, String password);
Account select(String cardNo);
//更新
//更新存钱
void updateSave(String cardNo, BigDecimal money);
//更新取钱
void updateTake(String cardNo, BigDecimal money);
//更新密码
void updatePassword(String cardNo, String password);
}
DaoImpl:
public class AccountDaoImpl implements AccountDao {
//登录
@Override
public Account select(String cardNo, String password) {
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection connection = null;
try {
// 1 获取连接
connection = DBUtils.getConnection();
// 2 创建预编译命令
String sql = "SELECT * FROM companydb.account where cardNo = ? And password = ?";
preparedStatement = connection.prepareStatement(sql);
// 给'?'占位符赋值
preparedStatement.setObject(1, cardNo);
preparedStatement.setObject(2, password);
// 3 执行命令
resultSet = preparedStatement.executeQuery();
Account account = null;
// 4 处理
if (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
BigDecimal money = resultSet.getBigDecimal("money");
account = new Account(id, name, money, cardNo, password);
}
return account;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
// 5 释放资源
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
}
//通过卡号查询
@Override
public Account select(String cardNo) {
ResultSet resultSet = null;
PreparedStatement preparedStatement = null;
Connection connection = null;
try {
// 1 获取连接
connection = DBUtils.getConnection();
// 2 创建预编译命令
String sql = "SELECT * FROM companydb.account where cardNo = ?";
preparedStatement = connection.prepareStatement(sql);
// 参数赋值
preparedStatement.setObject(1, cardNo);
// 3 执行命令
resultSet = preparedStatement.executeQuery();
Account account = null;
// 4 处理(获取结果集中的数据)
if (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
BigDecimal money = resultSet.getBigDecimal("money");
String password = resultSet.getString("password");
account = new Account(id, name, money, cardNo, password);
}
return account;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//关闭资源
DBUtils.closeAll(connection, preparedStatement, resultSet);
}
}
//存钱
@Override
public void updateSave(String cardNo, BigDecimal money) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1 获取连接
connection = DBUtils.getConnection();
//2 创建预编译SQL命令
String sql = "UPDATE companydb.account SET account.money = account.money + ? WHERE account.cardNo = ?";
preparedStatement = connection.prepareStatement(sql);
//2.1 参数赋值
preparedStatement.setObject(1, money);
preparedStatement.setObject(2, cardNo);
//3 执行命令
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//4 释放资源
DBUtils.closeAll(connection, preparedStatement, null);
}
}
//取钱
@Override
public void updateTake(String cardNo, BigDecimal money) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1 获取连接
connection = DBUtils.getConnection();
//2 创建预编译SQL命令
String sql = "UPDATE companydb.account SET account.money = account.money - ? WHERE account.cardNo = ?";
preparedStatement = connection.prepareStatement(sql);
//2.1 参数赋值
preparedStatement.setObject(1, money);
preparedStatement.setObject(2, cardNo);
//3 执行命令/处理
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//释放资源
DBUtils.closeAll(connection, preparedStatement, null);
}
}
//修改密码
@Override
public void updatePassword(String cardNo, String password) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1 获取连接
connection = DBUtils.getConnection();
String sql = "UPDATE companydb.account SET account.password = ? WHERE account.cardNo = ?";
//2 创建预编译SQL命令
preparedStatement = connection.prepareStatement(sql);
//2.1 参数赋值
preparedStatement.setObject(1, password);
preparedStatement.setObject(2, cardNo);
//3 执行命令/处理
preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//4 释放资源
DBUtils.closeAll(connection, preparedStatement, null);
}
}
}
entity
Account:
public class Account {
private Integer id;
private String name;
private BigDecimal money;
private String cardNo;
private String password;
public Account() {
}
public Account(Integer id, String name, BigDecimal money, String cardNo, String password) {
this.id = id;
this.name = name;
this.money = money;
this.cardNo = cardNo;
this.password = password;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public BigDecimal getMoney() {
return money;
}
public void setMoney(BigDecimal money) {
this.money = money;
}
public String getCardNo() {
return cardNo;
}
public void setCardNo(String cardNo) {
this.cardNo = cardNo;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
service层
service接口:
public interface AccountService {
//登录
Account login(String cardNo, String password);
//存钱
void save(String cardNo, BigDecimal money);
//取钱
void take(String cardNo, BigDecimal money);
//转账
void trans(String from, String to, BigDecimal money);
//查询余额
Account queryMoney(String cardNo, String password);
//修改密码
void modifyPassword(String cardNo, String oldPwd, String newPwd);
}
serviceImpl:
public class AccountServiceImpl implements AccountService {
//声明数据访问对象, 以调用Dao层操作
private static final AccountDaoImpl accountDao = new AccountDaoImpl();
//登录
public Account login(String cardNo, String password) {
//通过卡号和密码查询数据, 都正确时放回数据, 登录成功
Account account = accountDao.select(cardNo, password);
if (account == null) {
throw new RuntimeException("账户或密码错误");
}
return account;
}
//存钱
public void save(String cardNo, BigDecimal money) {
//添加限制
//因money是BigDecimal类型, 使用compareTo方法进行比较
//通过相减的方式进行比较判断
if (money.compareTo(new BigDecimal(0))<=0) {
throw new RuntimeException("存款金额必须大于0");
}
if (money.compareTo(new BigDecimal(50000))>0) {
throw new RuntimeException("存款金额必须小于50000");
}
accountDao.updateSave(cardNo, money);
}
//取款
public void take(String cardNo, BigDecimal money) {
if (money.compareTo(new BigDecimal(0))<=0) {
throw new RuntimeException("取款金额必须大于0");
}
if (money.compareTo(new BigDecimal(20000))>0) {
throw new RuntimeException("取款金额必须小于20000");
}
//通过卡号查询当前账户的余额
Account account = accountDao.select(cardNo);
if (money.compareTo(account.getMoney())>0) {
throw new RuntimeException("账户余额不足...");
}
accountDao.updateTake(cardNo, money);
}
//转账
public void trans(String from, String to, BigDecimal money) {
if (money.compareTo(new BigDecimal(0))<=0) {
throw new RuntimeException("转账金额必须大于0");
}
if (money.compareTo(new BigDecimal(20000))>0) {
throw new RuntimeException("转账金额必须小于20000");
}
//通过卡号查询转账账户的余额
Account account = accountDao.select(from);
if (money.compareTo(account.getMoney())>0) {
throw new RuntimeException("账户余额不足...");
}
//判断转账双方是否是同一人
if (from.equals(to)) {
throw new RuntimeException("不能给自己转账...");
}
//通过卡号查询判断接收方是否存在
if (accountDao.select(to) == null) {
throw new RuntimeException("对方账户不存在...");
}
//转账
try {
//开启事务, 调用线程中的连接
DBUtils.begin();
//使用同一连接
accountDao.updateTake(from, money);
accountDao.updateSave(to, money);
DBUtils.commit();//手动提交
} catch (Exception e) {
try {
DBUtils.rollback();//异常回滚
} catch (SQLException ex) {
e.printStackTrace();
}
throw new RuntimeException(e);
} finally {
//手动关闭
try {
DBUtils.close();//关闭事务连接
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//查询余额
public Account queryMoney(String cardNo, String password) {
AccountDao accountDao = new AccountDaoImpl();
Account account = accountDao.select(cardNo, password);
if (account==null) {
throw new RuntimeException("密码有误");
}
return account;
}
//修改密码
public void modifyPassword(String cardNo, String oldPwd, String newPwd) {
AccountDao accountDao = new AccountDaoImpl();
Account account = accountDao.select(cardNo,oldPwd);
if (account == null) {
throw new RuntimeException("原始密码有误...");
}
if (oldPwd.equals(newPwd)) {
throw new RuntimeException("新密码不能与原始密码相同");
}
accountDao.updatePassword(cardNo,newPwd);
}
}
test:
ATMSystem:
public class ATMSystem {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入卡号");
String cardNo = scanner.next();
System.out.println("请输入密码");
String password = scanner.next();
//创建业务对象
AccountService accountService = new AccountServiceImpl();
try {
Account account = accountService.login(cardNo, password);
boolean flag = true;
System.out.println("登录成功, 欢迎: "+account.getName());
do {
System.out.println("1 存钱, 2 取钱, 3 转账, 4 查询余额, 5 修改密码, 0 退出");
System.out.println("请选择:");
int choose = scanner.nextInt();
switch (choose) {
case 1:
try {
System.out.println("请输入存钱金额:");
BigDecimal money = scanner.nextBigDecimal();
accountService.save(account.getCardNo(), money);
System.out.println("存钱成功...");
} catch (Exception e) {
System.out.println(e.getMessage());
}
break;
case 2:
try {
System.out.println("请输入取款金额:");
BigDecimal money = scanner.nextBigDecimal();
accountService.take(account.getCardNo(), money);
System.out.println("取款成功...");
} catch (Exception e) {
System.out.println(e.getMessage());
}
break;
case 3:
System.out.println("请输入对方的卡号:");
String toCard = scanner.next();
System.out.println("请输入转账金额:");
BigDecimal money = scanner.nextBigDecimal();
try {
accountService.trans(account.getCardNo(),toCard,money);
System.out.println("转账成功...");
} catch (Exception e) {
System.out.println(e.getMessage());
}
break;
case 4:
System.out.println("请输入密码:");
String pwd = scanner.next();
try {
System.out.println(
"当前账户余额为: "
+ accountService.queryMoney(account.getCardNo(), pwd).getMoney()
);
} catch (Exception e) {
System.out.println(e.getMessage());
}
break;
case 5:
System.out.println("请输入原始密码");
String oldPwd = scanner.next();
System.out.println("请输入新密码");
String newPwd = scanner.next();
System.out.println("请再次输入新密码");
String againPwd = scanner.next();
if (newPwd.equals(againPwd)) {
try {
accountService.modifyPassword(account.getCardNo(), oldPwd, newPwd);
System.out.println("密码修改成功...");
} catch (Exception e) {
System.out.println(e.getMessage());
}
} else {
System.out.println("两次密码不一致...");
}
break;
case 0:
flag = false;
break;
default:
System.out.println("输入有误...");
}
} while (flag);
System.out.println("欢迎下次光临...");
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
util工具类
DbUtils:
public class DBUtils {
private static String url;
private static String user;
private static String pwd;
private static final ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
// 1 注册驱动
static {
try {
// 读取属性配置文件
Properties properties = new Properties();
FileInputStream fis = new FileInputStream("Properties/db.properties");
properties.load(fis);
fis.close();
// 变量赋值
String driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
pwd = properties.getProperty("pwd");
// 获取驱动对象
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
System.out.println("注册驱动失败: "+e.getMessage());
}
}
// 2 获取连接
public static Connection getConnection() {
try {
//从线程中获取连接
Connection connection = threadLocal.get();
//判断线程中是否有连接, 没有则从数据库中获取
if (connection == null) {
connection = DriverManager.getConnection(url,user,pwd);
//将连接放入线程中
threadLocal.set(connection);
}
//若线程中有连接, 则直接返回
return connection;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
// 3 释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
//判断是否开启事务, 如果没有开启事务, 要关闭
//若开启事务, 则在事务结束后手动关闭
if (connection.getAutoCommit()) {
connection.close();
//从线程中移除
threadLocal.remove();
}
}
} catch (SQLException e) {
System.out.println("资源释放失败: "+e.getMessage());
throw new RuntimeException(e);
}
}
// 4 开启事务
public static void begin() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.setAutoCommit(false);
}
}
// 5 提交事务
public static void commit() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.commit();
}
}
// 6 回滚事务
public static void rollback() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.rollback();
}
}
// 7 关闭事务连接
public static void close() throws SQLException{
Connection connection = DBUtils.getConnection();
if (connection != null) {
connection.close();
threadLocal.remove();
}
}
}
扫一扫
1455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
