java数据库(mysql)事务回滚

留个记录

import java.sql.*;

public class TransactionDemo {

    static Connection conn=null;
    static PreparedStatement pstmt = null;
    static Savepoint savePoint = null;

    public static void main(String args[]) {
        try {
            //本地mysql版本是8.0.11，某个版本之后需要加上.cj
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false","root","root");
            //设置不自动提交
            conn.setAutoCommit(false);
            //设置了回滚位置之后会生效，未设置则会回滚
            pstmt = conn.prepareStatement("update test_table set id=11 WHERE id=1");
            pstmt.executeUpdate();
            //设置回滚位置，如果不设置，默认回滚全部
            //可以注释此行测试
            savePoint = conn.setSavepoint();
            //回滚，不会生效
            pstmt = conn.prepareStatement("update test_table set id=22 WHERE id=2");
            pstmt.executeUpdate();
            //报错，在catch进行事务回滚
            pstmt = conn.prepareStatement("update test_table set id1=22 WHERE id=2");
            pstmt.executeUpdate();
            conn.commit();
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                //事务回滚，无参数情况下，默认回滚到事务开始之前的状态
                //有参数情况下，会回滚到指定位置
                conn.rollback(savePoint);
                conn.commit();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            //关闭
            try {
                if(pstmt!=null)
                    pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn!=null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}