银行转账案例演示

1.需求：一个账号fromAccount向另一个账号toAccount转入money元钱
2.分析：

• 检查两个账号是否存在，不存在的话，结束转账行为
• 检查转出账号的里金额是否充足，不充足，结束转账行为,充足的话，进行扣款money元
• 转入账号进行增加money元
  3.数据准备参考上一篇
  https://www.cnblogs.com/dch-21/p/12920178.html

public class Test { public static void main(String[] args) { Scanner scanner=new Scanner(System.in); System.out.println("请输入出账账号:"); String fromAccount=scanner.nextLine(); System.out.println("请输入进账账号:"); String toAccount=scanner.nextLine(); System.out.println("请输入转账金额:"); double money=scanner.nextDouble(); oneToOne(fromAccount,toAccount,money); } public static void oneToOne(String fromAccount,String toAccount,double money){ if(fromAccount==null||fromAccount.length()==0){ System.out.println("输入不正确"); return; } if(toAccount==null||toAccount.length()==0){ System.out.println("输入不正确"); return; } if(money<0){ System.out.println("输入不正确"); return; } Connection connection=null; PreparedStatement ps=null; ResultSet resultSet=null; try{ connection= DBUtil.getConnection(); String sql="select * from bank_account where account_id=?"; ps=connection.prepareStatement(sql); ps.setString(1,fromAccount); resultSet = ps.executeQuery(); if(!resultSet.next()){ System.out.println("出账账号有误"); return; } double balance = resultSet.getDouble("account_balance"); ps=connection.prepareStatement(sql); ps.setString(1,toAccount); resultSet = ps.executeQuery(); if(!resultSet.next()){ System.out.println("进账账号有误"); return; } String sql1="update bank_account set account_balance=account_balance+? where account_id=?"; ps=connection.prepareStatement(sql1); ps.setDouble(1,-money); ps.setString(2,fromAccount); int i = ps.executeUpdate(); System.out.println(i+"条数据受到影响"); ps=connection.prepareStatement(sql1); ps.setDouble(1,money); ps.setString(2,toAccount); int j = ps.executeUpdate(); System.out.println(j+"条数据受到影响"); }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.closeConnection(connection,ps,resultSet); } }}

输入

运行前

运行后

转账异常演示

在转出账户转出金额之后和转入账户收入金额之前模拟空指针异常
String str = null;
System.out.println(str.length());

package practice;import practice.jdbc.day03.DBUtil;import java.io.InputStream;import java.sql.*;import java.util.Objects;import java.util.Properties;import java.util.Scanner;/** * @Author 昊 * @Create 2020/5/19 21:29 * @Description * * 模拟银行一对一转账 */public class Test { public static void main(String[] args) { Scanner scanner=new Scanner(System.in); System.out.println("请输入出账账号:"); String fromAccount=scanner.nextLine(); System.out.println("请输入进账账号:"); String toAccount=scanner.nextLine(); System.out.println("请输入转账金额:"); double money=scanner.nextDouble(); oneToOne(fromAccount,toAccount,money); } public static void oneToOne(String fromAccount,String toAccount,double money){ if(fromAccount==null||fromAccount.length()==0){ System.out.println("输入不正确"); return; } if(toAccount==null||toAccount.length()==0){ System.out.println("输入不正确"); return; } if(money<0){ System.out.println("输入不正确"); return; } Connection connection=null; PreparedStatement ps=null; ResultSet resultSet=null; try{ connection= DBUtil.getConnection(); String sql="select * from bank_account where account_id=?"; ps=connection.prepareStatement(sql); ps.setString(1,fromAccount); resultSet = ps.executeQuery(); if(!resultSet.next()){ System.out.println("出账账号有误"); return; } double balance = resultSet.getDouble("account_balance"); ps=connection.prepareStatement(sql); ps.setString(1,toAccount); resultSet = ps.executeQuery(); if(!resultSet.next()){ System.out.println("进账账号有误"); return; } String sql1="update bank_account set account_balance=account_balance+? where account_id=?"; ps=connection.prepareStatement(sql1); ps.setDouble(1,-money); ps.setString(2,fromAccount); int i = ps.executeUpdate(); System.out.println(i+"条数据受到影响"); //在转出账户转出金额之后和转入账户收入金额之前模拟空指针异常 String str = null; System.out.println(str.length()); ps=connection.prepareStatement(sql1); ps.setDouble(1,money); ps.setString(2,toAccount); int j = ps.executeUpdate(); System.out.println(j+"条数据受到影响"); }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.closeConnection(connection,ps,resultSet); } }}

加入空指针异常前

加入异常后

解决方案

将手动提交事务改为自动提交，并设置回滚
setAutoCommit(false);
取消事务的自动提交操作，变成手动提交；默认为自动提交；true为自动提交，fals为手动提交
rollback();回滚到事务之初
releaseSavepoint(Savepoint savepoint);回滚到指定位置

事务

事务的特点：

• 默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。
• 如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。
  • 开启事务：start transaction；
  • 结束事务：commit或rollback；

jdbc的事务支持

Connection.setAutoCommit(boolean flag):此方法可以取消事务的自动提交功能，值为false。
Connection.commit()：进行事务提交 。
Connection.rollback():进行事务回滚。

public class Test { public static void main(String[] args) { Scanner scanner=new Scanner(System.in); System.out.println("请输入出账账号:"); String fromAccount=scanner.nextLine(); System.out.println("请输入进账账号:"); String toAccount=scanner.nextLine(); System.out.println("请输入转账金额:"); double money=scanner.nextDouble(); oneToOne(fromAccount,toAccount,money); } public static void oneToOne(String fromAccount,String toAccount,double money){ if(fromAccount==null||fromAccount.length()==0){ System.out.println("输入不正确"); return; } if(toAccount==null||toAccount.length()==0){ System.out.println("输入不正确"); return; } if(money<0){ System.out.println("输入不正确"); return; } Connection connection=null; PreparedStatement ps=null; ResultSet resultSet=null; try{ connection= DBUtil.getConnection(); //取消事务的自动提交操作，变成手动提交 //默认为自动提交 //true为自动提交，fals为手动提交 connection.setAutoCommit(false); String sql="select * from bank_account where account_id=?"; ps=connection.prepareStatement(sql); ps.setString(1,fromAccount); resultSet = ps.executeQuery(); if(!resultSet.next()){ System.out.println("出账账号有误"); return; } double balance = resultSet.getDouble("account_balance"); ps=connection.prepareStatement(sql); ps.setString(1,toAccount); resultSet = ps.executeQuery(); if(!resultSet.next()){ System.out.println("进账账号有误"); return; } String sql1="update bank_account set account_balance=account_balance+? where account_id=?"; ps=connection.prepareStatement(sql1); ps.setDouble(1,-money); ps.setString(2,fromAccount); ps.executeUpdate(); //在转出账户转出金额之后和转入账户收入金额之前模拟空指针异常 String str = null; System.out.println(str.length()); ps=connection.prepareStatement(sql1); ps.setDouble(1,money); ps.setString(2,toAccount); ps.executeUpdate(); }catch (Exception e){ e.printStackTrace(); //如果中途出现异常，会回滚到事务之初 try { connection.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } }finally { DBUtil.closeConnection(connection,ps,resultSet); } }}