package com.etc.entity;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Savepoint;

import org.junit.Test;

import com.etc.utils.JDBCUtil;

public class TransactionDemo1 {

//@Test

public void testTransaction1()

{

	Connection conn=null;

	PreparedStatement st=null;

	ResultSet rs=null;

	try {

		conn = JDBCUtil.getConnection();

		conn.setAutoCommit(false);//通知数据库开启事务

		String sql1 = "update account set money=money-100 where name='A'";

		st = conn.prepareStatement(sql1);

		st.executeUpdate();

		String sql2 = "update account set money=money+100 where name='B'";

		st = conn.prepareStatement(sql2);

		st.executeUpdate();

		conn.commit();//通知提交事务

		System.out.println("成功！");

	} catch (Exception e) {

		e.printStackTrace();

	}

	finally{

		JDBCUtil.close(rs, st, conn);

	}

}

//@Test

public void testTransaction2()

{

	Connection conn=null;

	PreparedStatement st=null;

	ResultSet rs=null;

	try {

		conn = JDBCUtil.getConnection();

		conn.setAutoCommit(false);//通知数据库开启事务

		String sql1 = "update account set money=money-100 where name='A'";

		st = conn.prepareStatement(sql1);

		st.executeUpdate();

		int i=1/0;//异常语句   事务不能提交   数据库自动回滚

		String sql2 = "update account set money=money+100 where name='B'";

		st = conn.prepareStatement(sql2);

		st.executeUpdate();

		conn.commit();//通知提交事务

		System.out.println("成功！");

	} catch (Exception e) {

		e.printStackTrace();

	}

	finally{

		JDBCUtil.close(rs, st, conn);

	}

}

//@Test

public void testTransaction3()

{

	Connection conn=null;

	PreparedStatement st=null;

	ResultSet rs=null;

	try {

		conn = JDBCUtil.getConnection();

		conn.setAutoCommit(false);//通知数据库开启事务

		String sql1 = "update account set money=money-100 where name='A'";

		st = conn.prepareStatement(sql1);

		st.executeUpdate();

		int i=1/0;//异常语句

		String sql2 = "update account set money=money+100 where name='B'";

		st = conn.prepareStatement(sql2);

		st.executeUpdate();

		conn.commit();//通知提交事务

		System.out.println("成功！");

	} catch (Exception e) {

		try {

			conn.rollback();//异常发生后   手动通知数据库事务回滚

		} catch (SQLException e1) {

			e1.printStackTrace();

		}

		e.printStackTrace();

	}

	finally{

		JDBCUtil.close(rs, st, conn);

	}

}

@Test  //sql1有效；sql2无效；sql3无效；

public void testTransaction4()

{

	Connection conn=null;

	PreparedStatement st=null;

	ResultSet rs=null;

	Savepoint sp=null;//事务回滚点

	try {

		conn = JDBCUtil.getConnection();

		conn.setAutoCommit(false);//通知数据库开启事务

		String sql1 = "update account set money=money-100 where name='A'";

		st = conn.prepareStatement(sql1);

		st.executeUpdate();

		sp=conn.setSavepoint();//设置事务回滚点

		String sql2 = "update account set money=money+100 where name='B'";

		st = conn.prepareStatement(sql2);

		st.executeUpdate();

		int i=1/0;//异常语句

		String sql3 = "update account set money=money+100 where name='C'";

		st = conn.prepareStatement(sql3);

		st.executeUpdate();

		conn.commit();//通知提交事务

	} catch (Exception e) {

		try {

			conn.rollback(sp);//回滚到事务回滚点

			conn.commit();//回滚之后提交

		} catch (SQLException e1) {

			e1.printStackTrace();

		}

		e.printStackTrace();

	}

	finally{

		JDBCUtil.close(rs, st, conn);

	}

}

}