JDBC代码实现之第六版

前言

由JDBC代码实现之第五版：利用PreparedStatement替代Statement执行SQL 对JDBC访问数据库做了更近一步的改善，至此，通过代码实现1-5版就基本括了JDBC技术的原理与使用方式。此处再通过模拟实际业务案例，对JDBC技术进行具体的应用，并阐明在应用过程中的有关事务的处理。

1.模拟转账业务功能

业务说明与设计

• 在解决正式项目中的一些需求，或者叫业务的时候，往往一个业务，它不是说只访问一次数据库，有可能一次业务，需要频繁的访问好多次数据库，比如，先从a表里取出一大堆数据，再对数据进行合并，运算和整理，然后还要从b表里取到一大堆数据，两者再合在一起，再运算，运算完以后，可能把这数据写到c表里，然后，把c表的结果再取出来，再折腾一下，再怎么样，可能是这样的。就可能一个业务，我们需要访问好几张表，访问好几次数据库，未必就是一次。所以，这里利用JDBC演示一个业务要访问多次数据库的场景，即同行转账业务，简单一点不做跨行，跨行更麻烦。About Explain
• 要模拟转账业务的方法，因为没有网页，所以只是模拟。模拟的前提条件是假设用户已经登录了网银(他是用网银转的，不是用支付宝，微信转的，用网银的话，很多人也用过，网银就是个网上银行，它就是一个网页，是个软件)，并且输入了收款方的账号，以及要转帐的金额，假设他把这个网页也登录了，都输入好了这些内容，然后他点了确定，或者说点了转账，当他点确认的那一刻，我们就实现转账，那要实现转账的话，首先得看他卡里有没有钱(如果我输入转1万，卡里就1块钱，就转不了)。查询一下付款账号的余额，如果余额足够，那没问题；第二步，若付款帐号没问题，余额也够，还得看看收款账号对不对，那查询收款账号，看他的账号对不对。如果付款账号没问题，余额也够，收款账号也对，两边都对就可以转了(真正实现网银转账的话，是非常严谨的，非常麻烦的，而且转账步骤，也不是由程序员简单的抓，就这么做，就完了，而是由专业的需求去调研，去设计的，需要有专业的业务人员去设计这个业务，怎么才能严谨，不是说我们说需求怎么说，就是了，但这里演示的话，就是粗糙一点，没必要那么细)。
• 转账的双方账号，转账金额都输入完成，那转是一个动作，实现这个动作在数据库层面，就是增删改查，显然转账应该是由update实现，那我转给你1000块钱，你得把我的钱update少一点，把你的钱update多一点，两次update。那么第三步修改付款余额，金额-N，修改收款余额，金额+N。以上三步是一个业务里完成的，实现这个业务需要访问数据库四次，查询付款账号余额，查询收款账户账号，改变付款账户余额，改变收款账户余额。这是一个完整的业务，是一个人的操作，一个人干的活，所以需要保证它属于一个事务之内，而要保证在一个事务之内，就一个原则，保证所有的操作在一个Connection之内完成，一定是一个连接。即转账是一个完整的业务，要使用一个连接来保障它只有一个事务。

准备工作

建表：想实现转账的话，还得有转账的账户表，如果是真实的银行的系统，账号表是比较复杂的，这里只是模拟，设计的简单些，尽量的简化。为了演示这个案例，没必要搞得那么复杂，只有三列，第一列id卡号，第二列name姓名，第三列money金额，然后注意这个ID，因为卡号是唯一的，所以可以以卡号为ID，一般地，ID都是整数，但这里这个ID要用 varchar2，因为银行卡的卡号是一个字符串，所以这里的ID不用整数，它就是银行卡的卡号，是一个字符串。默认往这个表里，插入两条数据，一个是张三，一个是李四，都会有一定的余额，用来实现转账业务。

create table accounts (
id varchar2(20),
name varchar2(30),
money number(11,2)
);
insert into accounts values(‘00001’,‘张三’,9000.0);
insert into accounts values(‘00002’,‘李四’,4000.0);
commit;

建好表以后，数据也插入完成，这个表中就两个用户，张三和李四，在这两者之间实现转账，张三卡号是00001，李四的卡号是00002。转账业务需要4个步骤来实现，查询付款账号余额，假设用户张三已经登录了网银，输入了付款账号，String payId = “00001”;，收款账号，String recId = “00002”;，和转账金额，double mny = “1000.0”;，这些假设内容都源于用户在网页端的输入的数据，假设服务器已经从浏览器得到了这些内容，就利用这些内容来实现转账。

实现解析：

• 第一步，以付款账号ID为条件，查询付款账号余额是否足够：String sql = "select * from accounts where id=?";创建PreparedStatement对象执行这个SQL，因为是查询付款方账号余额，所以问号传入的是payId，这个付款方是用户网银登录的账户，无论转入账，还是转出账，一般都会被系统默认选择，而无需再次输入账号ID，因此如果用户已登录了网银，这个付款方账号一定是对的，在服务器端一定不会传入错误，是一定可以查到数据的，且ResultSet中有且仅有一条数据，因为是通过账号ID查询的，而账号ID是唯一的，也无需通过while循环获取数据，在得到账号余额后与转账金额进行比较，余额是否充足。
• 第二步，如果付款方账号余额足够，就查询收款账号是否存在，正确与否。此处的查询收款方账号的sql与查询付款方账号的sql是一致的，以及执行付款方sql的语句对象PreparedStatement，结果集ResultSet都可以复用，但为了有所区别，这里重写一个全新的sql2：String sql2 = "select * from accounts where id=?";，也重新创建一个新的PreparedStatement对象ps2来执行这个sql2，和新的ResultSet对象rs2去接收执行后的返回值，如果没有查到数据，则说明可能是转账时的收款账号或转账的密码写错了，如果有数据，因为账号是唯一的，只会有一条数据，所以也无需while遍历，有数据则表明收款账号正确，在if语句中判断是否查到数据即可，如果无数据则账号或密码有误，就直接return;，跳出if语句块。
• 第三步，如果收款账号没问题，就可以开始转账了，先修改付款账号余额，金额-N，修改操作一定是写一个update语句sql3：String sql3 = "update accounts set money=? where id=?";，根据付款方账号ID为条件，修改后的余额money为 付款账号原来的钱减掉转账的钱，然后创建一个新的PreparedStatement对象ps3执行这个sql3。
• 第4步，修改收款方账号的余额，金额+N，修改操作写update语句sql4：String sql4 = "update accounts set money=? where id=?";与sql3是完全相同的sql，只是问号的赋值条件不同，根据收款方账号ID为条件，修改金额money为收款方原来的钱加上转账的钱，然后创建一个新的PreparedStatement对象ps4来执行sql4(其中收款方余额由查询收款方信息时声明同级变量，记录保存得到)。

代码示例：

/**
 * 模拟转账业务
 * 
 * 前提：
 * 		假设用户已经登录了网银，并且输入了收款方的账号，以及要转账的金额。
 * 
 * 步骤：
 * 		1.查询付款账号，看余额够不够
 * 		2.查询收款账号，看账号对不对
 * 		3.修改付款余额，金额-N
 * 		4.修改收款余额，金额+N
 * 
 * 注意，转账是一个完整的业务，要使用一个连接，保障只有一个事务。
 * 
 */
@Test
public void test6() {
	//假设用户登录的账号是
	String payId = "00001";
	//假设他输入的收款账号是
	String recId = "00002";
	//假设他输入的转账金额是
	double mny = 1000.0;
	
	Connection conn = null;
	try {
		conn = DBUtil.getConnection();
		//1.查询付款账号，看余额够不够
		String sql = "select * from accounts where id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, payId);
		ResultSet rs = ps.executeQuery();
		rs.next();
		double payMoney = rs.getDouble("money");
		if(payMoney<mny) {
			System.out.println("余额不足");
			return;
		}
		//2.查询收款账号，看帐号对不对
		String sql2 = "select * from accounts where id=?";
		PreparedStatement ps2 = conn.prepareStatement(sql2);
		ps2.setString(1, recId);
		ResultSet rs2 = ps2.executeQuery();
		double recMoney = 0;
		if(!rs2.next()) {
			System.out.println("收款账号错误");
			return;
		} else {
			recMoney = rs2.getDouble("money");
		}
		 //3.修改付款余额，金额-N
		String sql3 = "update accounts set money=? where id=?";
		PreparedStatement ps3 = conn.prepareStatement(sql3);
		ps3.setDouble(1, payMoney-mny);
		ps3.setString(2, payId);
		ps3.executeUpdate();
		//4.修改收款余额，金额+N
		String sql4 = "update accounts set money=? where id=?";
		PreparedStatement ps4 = conn.prepareStatement(sql4);
		ps4.setDouble(1, recMoney+mny);
		ps4.setString(2, recId);
		ps4.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtil.close(conn);
	}
}

2.JDBC对事物的支持

在以上模拟转账业务时一共执行了4次sql，那么在程序执行的过程中，有没有这样的一种可能，第一个sql执行完毕，第二个sql也执行完毕，第三个也执行完毕，当程序执行到这儿，就三和四之间的时候，突然就断电，突然就断网了，答案是肯定的，因为我们访问数据库是通过网络远程访问数据库，你访问数据库不在你本机上，是在远程那个机房里，这个数据库可以在上海，可以在广州，只要你有它的IP，有网络，都可以访问，那这个网络有可能会中断，那假设在第三步完成以后，在第四部执行这个时候，比如说出现了一些问题，网络中断了，或者是断电了，假设出现这种情况，会不会对我们的数据产生影响呢，那当然了，那模拟断网，断电，我也不能说拔个网线，那也太那啥了，不太合适。这里抛个异常演示，假设这个异常，就是断网了，断电了，随便写，弄个异常Integer.valueOf("断网了")，这句话是将一个字符串转为整数，这个字符串儿显然是不能转的，它肯定会报异常，假设这句话就代表断网了，当然，它抛了异常是NumberFormatException，那之前默认catch的是SQLException，为了能同时catch这两个异常，可以干脆这样改，就我catch(Exception)，catch所有异常。希望在异常发生时，能catch到它，能够有日志能看到。

JDBC默认管理事务方式

• 为了通过转账模拟案例演示JDBC默认管理事务方式，首先得看一下数据库当前的数据，张三8000，李四5000，然后通过JUnit执行包含这段Integer.valueOf(“断网了”);的代码，会发现JUnit显示绿色，它认为执行成功了，但控制台存在异常(因为我们程序发生异常，我主动catch到它了，但catch到它以后，我没有往上抛，JUnit就不知道，我的调用者也并不知道有了问题。JUnit没有变红提醒，JUnit会认为这程序就正常执行了，当然，这是测试，为了省点事，而没有往上抛，但如果是写正式代码，你catch到异常，没往上抛，那你的调用者会认为，这个程序执行成功了，他会得到一个错误的判断，这是有影响的，所以正常来说，一定是按照异常处理原则，你处理不了就一定要抛，否则你的调用者不知道这个问题，他也不处理，那就没人处理的话，这个问题就这样了，就耽误了)。
• 在控制台catch到异常NumberFormatException，这个异常是因为我传入的是字符串，转换不了，但别管是报什么错，反之是报错。然后，看一下刚才从数据库里查到的，张三8000，李四5000，这个库表里的数据有没有受影响，查询对比如下：
  
  异常后的代码执行结果是，张三7000，李四5000， 张三的钱转出了，李四却没有收到，那这个钱哪去了，被银行吃掉了。那这种bug是很龌龊的，很不要脸的， 一定要避免。
• 那么这种问题的原因根本是，整个转账这件事，它没有保证在一个事务之内，而一个事务就是整个转账过程是一个整体，它要么整个都成功，要么整个都失败，绝不能成功一半，失败一半。现在的就是成功一半，失败一般，第三步成功，第4步失败。要解决这个问题就需要对事务管理，将1234这4步放到一个事务之内。而JDBC默认管理事务的方式，显然没有达到这个要求。
• JDBC是支持事务的，并且有一种默认的方式自动管理事务，如果不喜欢这种默认的方式，也可以自己手动管理事务。默认管理事务方式，会在每次调用executeUpdate()方法时，它会自动commit。我们在利用JDBC技术执行sql语句时，并没有执行commit语句，而是它自动commit，他每次update都会commit一下，在转账案例中，第3步，第4步都是update，第3步一执行update，它就commit，这个数据就立刻生效了，如果第4步失败，那第3步也不会回滚，这是两个事务，这就有问题了。
• JDBC默认管理事务的方式是每次update都会自动commit，这种方式只适合于一项业务当中，只需要执行一次DML语句的场合。如果说一个业务只包含一次DML语句，就可以自动commit，因为就一次insert，delete，或者update，commit也没有关系，但是，如果类似于转账功能，一个业务包含了多次DML语句，有两个update，就不太适合，这时就需要用手动管理事务的方式。

JDBC手动管理事务方式

JDBC提供了如下三个方法支持手动管理事务：

1. conn.setAutoCommit(false);，这个方法是取消自动提交，要想手动管理，首先得把自动提交取消了，这样它就不会自动commit了。
2. conn.commit();，这个方法是手动提交，可以在任意需要的时候，进行手动commit。在一个业务所有的DML都执行完，一次性提交。
3. conn.rollback();，这个方法是手动回滚，当然如果是自动管理事务的话，程序抛异常也会自动回滚，但只回滚到本次提交的内容。

我们通过这3个方法对转账案例加以修改，去手动管理事务。现在想手动管理事务，那么就需要在程序的一开始就取消自动提交，即在创建连接之后，立刻对连接执行代码：conn.setAutoCommit(false);，这时它就不会自动commit了，要在这个业务中所有的DML都执行完了，转账案例的第四步以后，手动提交一次事务，就commit一次，一次性提交，要么都成功，要么都失败，执行代码：conn.commit();，如果程序正常执行完成以后，就会commit，如果程序在中间发生了问题，抛了异常报错，比如断网了，就不能提交了。这时我们catch异常就需要对异常进行处理，而处理方式就是手动回滚事务，让数据回到原始状态，执行代码：conn.rollback();但此处因为rollback()方法声明抛了异常，为了代码复用和代码的简洁性，将其封装到DBUtil工具类中，执行代码：DBUtil.rollback();。

完整代码示例：

/**
 * 模拟转账业务
 * 
 * 前提：
 * 		假设用户已经登录了网银，并且输入了收款方的账号，以及要转账的金额。
 * 
 * 步骤：
 * 		1.查询付款账号，看余额够不够
 * 		2.查询收款账号，看账号对不对
 * 		3.修改付款余额，金额-N
 * 		4.修改收款余额，金额+N
 * 
 * 注意，转账是一个完整的业务，要使用一个连接，保障只有一个事务。
 * 
 */
@Test
public void test6() {
	//假设用户登录的账号是
	String payId = "00001";
	//假设他输入的收款账号是
	String recId = "00002";
	//假设他输入的转账金额是
	double mny = 1000.0;
	
	Connection conn = null;
	try {
		conn = DBUtil.getConnection();
		
		//取消自动提交事务
		conn.setAutoCommit(false);
		
		//1.查询付款账号，看余额够不够
		String sql = "select * from where id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, payId);
		ResultSet rs = ps.executeQuery();
		rs.next();
		double payMoney = rs.getDouble("money");
		if(payMoney<mny) {
			System.out.println("余额不足");
			return;
		}
		//2.查询收款账号，看帐号对不对
		String sql2 = "select * from accounts where id=?";
		PreparedStatement ps2 = conn.prepareStatement(sql2);
		ps2.setString(1, recId);
		ResultSet rs2 = ps2.executeQuery();
		double recMoney = 0;
		if(!rs2.next()) {
			System.out.println("收款账号错误");
			return;
		} else {
			recMoney = rs2.getDouble("money");
		}
		 //3.修改付款余额，金额-N
		String sql3 = "update accounts set money=? where id=?";
		PreparedStatement ps3 = conn.prepareStatement(sql3);
		ps3.setDouble(1, payMoney-mny);
		ps3.setString(2, payId);
		ps3.executeUpdate();
		
		//"断网了"将这个字符串转为整数时会报异常，假设这句话就代表断网了。
		Integer.valueOf("断网了");
		
		//4.修改收款余额，金额+N
		String sql4 = "update accounts set money=? where id=?";
		PreparedStatement ps4 = conn.prepareStatement(sql4);
		ps4.setDouble(1, recMoney+mny);
		ps4.setString(2, recId);
		ps4.executeUpdate();
		
		//提交事务
		conn.commit();
		
		//为了catch到"断网了"的报错异常，将SQLException异常改为Exception，catch到所有异常。
		//} catch (SQLException e) {
		} catch (Exception e) {
		e.printStackTrace();
		//回滚事务：将rollback封装到DBUtil中，这样就可以方便调用者，不用直接处理这个方法的异常。
		//conn.rollback();
		DBUtil.rollback(conn);
	} finally {
		DBUtil.close(conn);
	}
}

重点解析：

1. 数据锁死现象

• 在一个事务中，如果程序在执行过程中间抛了异常，事务没有被提交，那就必须得事务回滚，虽然已经执行的DML的这个数据也不会有效，但是，因为你是对某一条数据加以的修改，那么当你在改的时候，这条数据其实是被你锁死了，别人是同时改不了的，你改的时候我就不能改，直到你commit以后，我才能去读，才能去改它，但是如果说你最终程序报错了，你没有提交，这个数据最终还是处于锁的状态，你又没有回滚，这个锁没有解除，那我是无论如何都读取不了，也修改不了，这个数据就费掉了，被锁死了，而这个锁必须强制解除才行，所以，我们对于一份数据的修改，一定是要有始有终的。
• 如果程序正常的话，它最后commit提交了数据，commit以后数据生效了，锁也解除了，别人就可以读可以改。那么当程序发生异常时，我回滚，就让程序回到原始状态，原来的状态，也是把这个锁解除了，别人照样也可以读可以改，但一定不能是，我既不提交也不回滚，那这个数据就被锁死了，谁都用不了。同理，如果你在SQLDeveloper里面，比如执行一个insert，执行个delete，或执行个update，执行完以后，没有commit，那么这份数据就被你锁死了，回过头来，你再使用jdbc去访问这份数据，你会发现，我怎么改不了，怎么处理不了，读不到了，因为被锁死了，所以在执行sql时，尤其是insert等等的时候，一定要写commit，否则的话就锁死了。虽然在自己这里能看到，因为你是在这里改的，你能看到，但别人看不到，另外的线程是看不到的，所以我们发生异常时，一定要回滚。

2. 添加事务回滚方法，重构DBUtil工具类：

• 在调用回滚方法conn.rollback()时，这个方法声明抛了异常，需要try-catch处理异常，不过类似这样的try-catch都被封装到了DBUtil工具类中了，这里也一样，也把这个rollback()方法封装到DBUtil里，而调用者在使用时，在业务代码中就写一句话即可，避免到处try-catch，让代码更加简洁。
• 在DBUtil工具类了再加一个方法，帮助调用者去回滚事务，那方法名就叫做rollback()。想要回滚事务，首先需要调用者把他那个事务的连接给我，利用连接中的rollback()方法进行回滚，所以这个方法需要传入一个Connection类型的参数，严谨起见，以免调用者传入的参数不靠谱，比如传入个空值进来，所以首先对传入的连接加以判断，如果连接非空，我就调用Connection的rollback()方法去回滚，这个方法声明抛了异常，然后对这个方法进行异常处理try-catch，如果catch到异常，其实这里也处理不了，处理不了就往上抛，但是为了调用者方便，我们抛一个Runtime异常，调用者可以处理，也可以不处理，自己看着办，那如果调用者不处理，以后也不用担心，最终的话，我们服务器可以统一处理一切异常，就大胆的抛就是了，就抛一个RuntimeException。这个rollback()就封装完成。代码如下：

  package util;
  … …
  public class DBUtil {
  … …
  public static void rollback(Connection conn) {
  if(conn != null) {
  try {
  conn.rollback();
  } catch (SQLException e) {
  e.printStackTrace();
  throw new RuntimeException(“回滚失败”, e);
  }
  }
  }
  }

3.总结：

在一个业务中包括多次DML语句时，比如模拟转账案例，要进行手动管理事务。在程序执行的时候，如果平时bug，没有断网，没有断电的情况，即便不进行手动管理事务也没有问题。就怕有问题的时候，那么这个事务的作用，事务的价值，才能凸显出来。它能保证我们数据的有效和一致，如果没有事务，事务就可能会成功一半，失败一半，会出现这样的问题。

参考文献(References)

文中如有侵权行为，请联系me。。。。。。。。。。。。。
文中的错误，理解不到位的地方在所难免，也请指教！在成长过程中，也将继续不断完善，不作为专业文章。不喜勿喷。