1查看是否自动提交:
show variables like’%commit%'
然后设置 set autocommit = off ;
关闭自动提交
这个时候对数据库操作,
最后不commit;
别人是无法看到操作的结果的,只有自己能看到。
commit之后
再次启动事务
start transaction;
设置保留点:
SAVEPOINT savepoint_name;
保存的是此次操作之前的状态。
因此 最好在操作数据之前先 savepoint start;把原数据设置一个savepoint
rollback to start;
并且rollback 一次后就失效了,但是可以back完前面的savepoint,再回到后面的savepoint;
演示:
我设置了三个savepoint:
start 初始
dele9
dele10
现在我先rollback to dele9;
再回rollback to dele10时就显示不存在了,这是因为回到dele9的状态你还没有设置dele10.
相反的我可以先rollback to dele10,再rollback to dele9;这是可以的。
代码演示:
1.Transaction 没有事务的时候 提交出现异常前面的会提交成功
package SpringWork.Transaction;
import SpringWork.Util.JDBCUtils;
import org.junit.Test;
import java.net.ConnectException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TransactionTest
{
@Test
public void transactionTest1(){
PreparedStatement pst= null;
Connection conn =null;
try {
//insert into transaction1(money) values(100);
// update transaction1 set money =1000 where id>0;
conn= JDBCUtils.getConn();
conn.setAutoCommit(true);
String sql ="update transaction1 set money= money - ? where id=?";
pst=conn.prepareStatement(sql);
//扣钱
pst.setInt(1,300);
pst.setInt(2,2);
pst.executeUpdate();
int a=10/0;
//加钱 2
pst.setInt(1,-500);
pst.setInt(2,1);
if ( pst.executeUpdate()!=0)
System.out.println("操作成功");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(pst,conn);
}
}
}
有了transaction 后异常前的也不会成功
2.JDBCUtils
package SpringWork.Util;
import org.springframework.test.context.jdbc.Sql;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
public class JDBCUtils {
static String driverClass="com.mysql.cj.jdbc.Driver";
static String url="jdbc:mysql://127.0.0.1/work?useSSL=false&serverTimezone=UTC";
static String username="root";
static String password="123456";
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driverClass);
conn= DriverManager.getConnection(url,username,password);
}catch (SQLException e){
e.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(ResultSet rs,Statement st,PreparedStatement pst,Connection conn){
closeRs(rs);
closeSt(st);
closePst(pst);
closeConn(conn);
}
public static void close(Connection conn,Statement st){
closeSt(st);
closeConn(conn);
}
public static void close(ResultSet rs,Statement st,Connection conn){
closeRs(rs);
closeSt(st);
closeConn(conn);
}
public static void close(PreparedStatement pst,Connection conn){
closePst(pst);
closeConn(conn);
}
public static void close(ResultSet rs,PreparedStatement pst,Connection conn){
closeRs(rs);
closePst(pst);
closeConn(conn);
}
private static void closeConn(Connection conn){
try{
if (conn!=null)
conn.close();
}catch (SQLException e){
e.printStackTrace();
}finally {
conn = null;
}
}
private static void closeRs(ResultSet rs){
try{
if (rs!=null)
rs.close();
}catch (SQLException e){
e.printStackTrace();
}finally {
rs = null;
}
}
private static void closeSt(Statement st){
try{
if (st!=null)
st.close();
}catch (SQLException e){
e.printStackTrace();
}finally {
st = null;
}
}
private static void closePst(PreparedStatement pst){
try{
if (pst!=null)
pst.close();
}catch (SQLException e){
e.printStackTrace();
}finally {
pst = null;
}
}
}