手动提交事务和回滚的使用典例

package jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.junit.Test;

import util.DBUtil;

public class TestDay03 {

	/**
	 * 演示如何从ResultSetMetaData
	 * 中读取结果集相关的描述信息.
	 */
	@Test
	public void test1() {
		//假设页面传入的查询条件是
		int empno = 1;
		
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			String sql = 
				"select * from emps_lhh "
				+ "where empno=?";
			PreparedStatement ps = 
				conn.prepareStatement(sql);
			ps.setInt(1, empno);
			ResultSet rs = ps.executeQuery();
			//获取结果集元数据,它是一个对象,
			//内部封装了对结果集的描述信息.
			ResultSetMetaData md = rs.getMetaData();
			//多少列
			System.out.println(md.getColumnCount());
			//第1列的列名
			System.out.println(md.getColumnName(1));
			//第1列的类型的编号(常量)
			System.out.println(md.getColumnType(1));
			//第1列的类型的名称
			System.out.println(md.getColumnTypeName(1));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
	}

	/**
	 * 模拟转账业务.
	 * 
	 * 假设此时用户已经登录了网银,
	 * 并且已经输入了收款方账号和
	 * 转账的金额,点击了转账.
	 * 
	 * 转账的步骤:
	 * 1.验证收款方账号是否存在(查询)
	 * 2.验证付款方余额是否够用(查询)
	 * 3.将付款方余额-N元(修改)
	 * 4.将收款方余额+N元(修改)
	 */
	@Test
	public void test2() {
		//假设用户输入的信息如下
		//付款方账号
		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 accounts_lhh "
				+ "where id=?";
			PreparedStatement ps = 
				conn.prepareStatement(sql);
			ps.setString(1, recId);
			ResultSet rs = ps.executeQuery();
			if(!rs.next()) {
				throw new SQLException("收款方账号不存在");
			}
			double recMny = rs.getDouble("money");
			//2.查询付款方余额并验证
			String sql2 = 
				"select * from accounts_lhh "
				+ "where id=?";
			PreparedStatement ps2 = 
				conn.prepareStatement(sql2);
			ps2.setString(1, payId);
			ResultSet rs2 = ps2.executeQuery();
			double payMny = 0.0;
			if(rs2.next()) {
				payMny = rs2.getDouble("money");
				if(payMny<mny) {
					throw new SQLException("余额不足");
				}
			}
			//3.修改付款方余额
			String sql3 = 
				"update accounts_lhh set "
				+ "money=? where id=?";
			PreparedStatement ps3 = 
				conn.prepareStatement(sql3);
			ps3.setDouble(1, payMny-mny);
			ps3.setString(2, payId);
			ps3.executeUpdate();
			
			Integer.valueOf("断电了");
			
			//4.修改收款方余额
			String sql4 =
				"update accounts_lhh set "
				+ "money=? where id=?";
			PreparedStatement ps4 = 
				conn.prepareStatement(sql4);
			ps4.setDouble(1, recMny+mny);
			ps4.setString(2, recId);
			ps4.executeUpdate();
			
			//转账是一个完整的过程,只需要在
			//整个流程完成后,提交一次事务即可.
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DBUtil.close(conn);
		}
	}
	
	/**
	 *	批量添加员工(共108个,每批加50个)
	 */
	@Test
	public void test3() {
		//这是一个完整的业务,只创建
		//1个连接,提交1次事务
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
			String sql = 
				"insert into emps_lhh values("
				+ "emps_seq_lhh.nextval,"
				+ "?,?,?,?,?,?,?)";
			PreparedStatement ps = 
				conn.prepareStatement(sql);
			for(int i=1;i<=108;i++) {
				//每次循环都将数据暂存到ps上
				ps.setString(1, "好汉"+i);
				ps.setString(2, "打劫");
				ps.setInt(3, 0);
				ps.setDate(4, 
					Date.valueOf("2017-01-23"));
				ps.setDouble(5, 6000.0);
				ps.setDouble(6, 4000.0);
				ps.setInt(7, 9);
				ps.addBatch();
				//每循环50次发送一次数据
				if(i%50==0) {
					ps.executeBatch();
					//清空ps中的数据,以便于
					//暂存下一轮的数据
					ps.clearBatch();
				}
			}
			//循环结束后,为了避免有零头(8),
			//再单独批量发送一次数据.由于这
			//是最后一次发送,所以不用清空ps了
			ps.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DBUtil.close(conn);
		}
	}
	
	/**
	 * 添加部门及员工数据
	 * 添加员工时需要获取到部门的ID
	 */
	@Test
	public void test4() {
		//假设页面传入的数据是
		//部门
		String dname = "财务部";
		String loc = "杭州";
		//员工
		String ename = "郭嘉";
		String job = "谋士";
		int mgr = 0;
		Date hiredate = 
			Date.valueOf("2017-01-23");
		double sal = 6000.0;
		double comm = 2000.0;
		
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
			//增加部门
			String sql = 
				"insert into depts_lhh values("
				+ "depts_seq_lhh.nextval,?,?)";
			//参数2是一个数组,声明需要ps记住
			//的字段的名称,ps在执行SQL时会
			//记住这些字段的值.
			PreparedStatement ps = 
				conn.prepareStatement(
					sql, new String[]{"deptno"});
			ps.setString(1, dname);
			ps.setString(2, loc);
			ps.executeUpdate();
			//获取部门ID
			//返回的结果集中存储了一条数据,
			//该行数据包括我们让ps记录的所有字段.
			ResultSet rs = ps.getGeneratedKeys();
			rs.next();
			//获取ps记录的字段时必须使用序号
			int deptno = rs.getInt(1);
			//增加员工
			String sql2 = 
				"insert into emps_lhh values("
				+ "emps_seq_lhh.nextval,"
				+ "?,?,?,?,?,?,?)";
			PreparedStatement ps2 = 
				conn.prepareStatement(sql2);
			ps2.setString(1, ename);
			ps2.setString(2, job);
			ps2.setInt(3, mgr);
			ps2.setDate(4, hiredate);
			ps2.setDouble(5, sal);
			ps2.setDouble(6, comm);
			ps2.setInt(7, deptno);
			ps2.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DBUtil.close(conn);
		}
	}
}