JDBC 结构优化2

JDBC 结构优化2

结构优化2 - ATM系统(存,取,转,查)

1 Service

什么是业务?

• 代表用户完成的一个业务功能，可以由一个或多个DAO的调用组成。
• 软件所提供的一个功能叫业务。

核心思想:

• 用户角度: 功能即业务

  • 转账这一功能就是一个业务

• 服务者角度: 业务包含多个步骤

  1. 从A账户扣钱
  2. 给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 {//登录@Overridepublic 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);}}//通过卡号查询@Overridepublic 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);}}//存钱@Overridepublic 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);}}//取钱@Overridepublic 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);}}//修改密码@Overridepublic 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();}}
}