事务提交、回滚、保存点

/**
 * 事务四个特性：
 * 1.原子性
 * 2.一致性
 * 3.隔离性
 * 4.持续性
 * @author John
 *
 */
public class TransactionDemo {
    //事务的提交和回滚
    @Test
    public void transaction1() throws SQLException{
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        JDBCUtils utils = JDBCUtils.getInstance();
        String sql1 = "update user set age=age-1 where id = 6";
        String sql2 = "select age from user where id = 7";
        try {
            conn = utils.getConnection();
            //设置事务自动提交为false，这样就不会自动提交事务了
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();

            ps = conn.prepareStatement(sql2);
            rs = ps.executeQuery();
            while(rs.next()){
                int age = rs.getInt("age");
                if(age>11){
                    throw new RuntimeException("大于11");
                }
            }
            //这个过程是一个事务
            conn.commit();
        } catch (SQLException e) {
            if(conn!=null){
                //如果事务提交失败 ，回滚事务
                conn.rollback();
            }
            throw e;
        }finally{
            utils.free(conn, ps, rs);
        }

    }
    //保存点
    @Test
    public void savePoint1() throws SQLException{
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        JDBCUtils utils = JDBCUtils.getInstance();
        String sql1 = "update user set age=age-1 where id = 6";
        String sql2 = "select age from user where id = 7";
        String sql3 = "update user set age=age-1 where id = 5";
        Savepoint sp=null;
        try {
            conn = utils.getConnection();
            //设置事务自动提交为false，这样就不会自动提交事务了
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            //设置保存点
            sp=conn.setSavepoint();

            ps = conn.prepareStatement(sql3);
            ps.executeUpdate();
            //
            ps = conn.prepareStatement(sql2);
            rs = ps.executeQuery();
            while(rs.next()){
                int age = rs.getInt("age");
                if(age>11){
                    throw new RuntimeException("不得大于11");
                }
            }
            conn.commit();
        } catch (RuntimeException e) {
            if(conn!=null && sp!=null){
                //如果事务提交失败 ，回滚到保存点位置
                conn.rollback(sp);
                //提交保存点之前执行结果
                conn.commit();
            }
            throw e;
        }finally{
            utils.free(conn, ps, rs);
        }

    }
}