mysql事务demo_mysql--事务demo1----

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);

}

}

}