JDBC(事务处理)

/*
	 * 事务(ACID)的性质 
	 * 1.原子性：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分(要么全做，要么全不做)
	 * 2.一致性：在事务处理执行前后，数据库是一致的(数据库完整性约束) 
	 * 3.隔离性：一个事务处理对另一个事务处理的影响
	 * 4.持续性：一个事务处理对另一个事务处理的影响
	 * 
	 * connection.setAutoCommit(false); //打开事务 connection.commit(); //提交事务
	 * connection.rollback(); //回滚事务
	 */

没有事务处理之前

public static void read() throws SQLException {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = jdbcUtils.getinstance().getConnection();

			st = conn.createStatement();
			String sql = "update user set money = money -100 where id = 1"; // id=1的人钱减少100
			st.executeUpdate(sql);

			sql = "select money from user where id = 2";
			rs = st.executeQuery(sql);

			float money = 0.0f;
			if (rs.next()) {
				money = rs.getFloat("money");
			}
			if (money > 300) {
				throw new RuntimeException("已经超出最大值");
			}
		} finally {
			// 6.释放资源
			jdbcUtils.free(rs, st, conn);
		}
	}

执行前

1	JiangLR	1998-01-02	333
2	HunagWB	1998-01-01	433
3	FanJH	1997-12-12	533
4	JiangJY	1997-01-02	633
6	ChenJM	2018-04-17	600

执行后

Exception in thread "main" java.lang.RuntimeException: 已经超出最大值
	at zucc.edu.cn.jdbc.TxTest.read(TxTest.java:43)
	at zucc.edu.cn.jdbc.TxTest.main(TxTest.java:12)

1	JiangLR	1998-01-02	233
2	HunagWB	1998-01-01	433
3	FanJH	1997-12-12	533
4	JiangJY	1997-01-02	633
6	ChenJM	2018-04-17	600

有事务处理之后

public static void read() throws SQLException {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = jdbcUtils.getinstance().getConnection();


			// *****************一个事务************************
			conn.setAutoCommit(false);// 关闭自动提交


			st = conn.createStatement();
			String sql = "update user set money = money -100 where id = 1"; // id=1的人钱减少100
			st.executeUpdate(sql);


			sql = "select money from user where id = 2";
			rs = st.executeQuery(sql);


			float money = 0.0f;
			if (rs.next()) {
				money = rs.getFloat("money");
			}
			if (money > 300) {
				throw new RuntimeException("已经超出最大值");
			}
			sql = "update user set money = money + 100 where id = 2";
			st.executeUpdate(sql);
			conn.commit();
			// ***********************************************
		} catch (SQLException e) {
			if (conn != null) {
				conn.rollback();// 回滚
			}
			throw e;
		} finally {
			// 6.释放资源
			jdbcUtils.free(rs, st, conn);
		}
	}

执行前：

1	JiangLR	1998-01-02	333
2	HunagWB	1998-01-01	433
3	FanJH	1997-12-12	533
4	JiangJY	1997-01-02	633
6	ChenJM	2018-04-17	600

执行后：

Exception in thread "main" java.lang.RuntimeException: 已经超出最大值
	at zucc.edu.cn.jdbc.TxTest.read(TxTest.java:43)
	at zucc.edu.cn.jdbc.TxTest.main(TxTest.java:12)

1	JiangLR	1998-01-02	333
2	HunagWB	1998-01-01	433
3	FanJH	1997-12-12	533
4	JiangJY	1997-01-02	633
6	ChenJM	2018-04-17	600

部分回滚

public static void read() throws SQLException {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		Savepoint sp = null; // 设置保存点
		try {
			conn = jdbcUtils.getinstance().getConnection();

			// *****************一个事务************************
			conn.setAutoCommit(false);// 关闭自动提交

			st = conn.createStatement();
			String sql = "update user set money = money -100 where id = 1"; // id=1的人钱减少100
			st.executeUpdate(sql);

			sp = conn.setSavepoint();
			/*
			 * 设置保存点之后，事务回滚只会回滚到目前的状态，然后通过conn.commit提交，也就是说之前的代码依然有效
			 * 当不设置保存点时，事务会回滚到最初申请事务答的那个状态
			 */

			sql = "update user set money = money - 100 whrer id = 3";
			st.executeQuery(sql);

			sql = "select money from user where id = 2";
			rs = st.executeQuery(sql);

			float money = 0.0f;
			if (rs.next()) {
				money = rs.getFloat("money");
			}
			if (money > 300) {
				throw new RuntimeException("已经超出最大值");
			}
			sql = "update user set money = money + 100 where id = 2";
			st.executeUpdate(sql);
			conn.commit();
			// ***********************************************
		} catch (SQLException e) {
			if (conn != null && sp != null) {
				conn.rollback();
				conn.commit();
			}
			throw e;
		} finally {
			// 6.释放资源
			jdbcUtils.free(rs, st, conn);
		}
	}

执行代码之前：

1	JiangLR	1998-01-02	333
2	HunagWB	1998-01-01	433
3	FanJH	1997-12-12	533
4	JiangJY	1997-01-02	633
6	ChenJM	2018-04-17	600

执行代码之后

Exception in thread "main" java.lang.RuntimeException: 已经超出最大值
	at zucc.edu.cn.jdbc.TxTest.read(TxTest.java:52)
	at zucc.edu.cn.jdbc.TxTest.main(TxTest.java:11)

1	JiangLR	1998-01-02	233
2	HunagWB	1998-01-01	433
3	FanJH	1997-12-12	533
4	JiangJY	1997-01-02	633
6	ChenJM	2018-04-17	600