JDBC的事务处理-----简单的说很.....滚

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionTest {
    //准备数据库连接的条件
    final static String URL = "jdbc:mysql://127.0.0.1:3306/gavin?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&cachePrepStmts=true&reWriteBatchedStatements=true&useServerPrepStmts=true";
    final static String USER = "gavin";
    final static String PASSWORD = "955945";
    static long start = 0;
    static long end = 0;

    public static void main(String[] args) {
TransactionTest();
    }


    public static void TransactionTest() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            String sql = "update acount set balance=balance+? where id=?;";

            start = System.currentTimeMillis();
//预编译
            preparedStatement = connection.prepareStatement(sql);
            //先不设置事务
            connection.setAutoCommit(true);

            preparedStatement.setInt(1, 500);
            preparedStatement.setInt(2, 2);
            int rows = preparedStatement.executeUpdate();
if(rows==1){
    System.out.println("mary已经转账了");
}else{
    System.out.println("mary转账失败");
}

            int i=100/0; //制造异常

            preparedStatement.setInt(1, -500);
            preparedStatement.setInt(2, 2);
            int row1 = preparedStatement.executeUpdate();


            end = System.currentTimeMillis();
            System.out.println(end - start);
            if(row1==1){
                System.out.println("lili已经收到账了");
            }else{
                System.out.println("转账失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("有异常发生了");
           /*try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }*/
        } finally {
          /*  try {
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != connection) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

数据库结果----

由于没有开启事务异常也没有回滚处理,mary的balance并没有减少,但是lili的却增加了;

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TransactionTest {
    //准备数据库连接的条件
    final static String URL = "jdbc:mysql://127.0.0.1:3306/gavin?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&cachePrepStmts=true&reWriteBatchedStatements=true&useServerPrepStmts=true";
    final static String USER = "gavin";
    final static String PASSWORD = "955945";
    static long start = 0;
    static long end = 0;
   static  int rows=0;
   static  int row=0;
    public static void main(String[] args) {
TransactionTest();
    }


    public static void TransactionTest() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            String sql = "update acount set balance=balance+? where id=?;";

            start = System.currentTimeMillis();
//预编译
            preparedStatement = connection.prepareStatement(sql);
            //先不设置事务
            connection.setAutoCommit(false);

            preparedStatement.setInt(1, 500);
            preparedStatement.setInt(2, 2);
            rows = preparedStatement.executeUpdate();


            int i=100/0; //制造异常

            preparedStatement.setInt(1, -500);
            preparedStatement.setInt(2, 2);
            row = preparedStatement.executeUpdate();


            end = System.currentTimeMillis();
            System.out.println(end - start);


        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("有异常发生了");
           try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if(rows==1&&row==1){
                System.out.println("转账成功");
            }else{
                System.out.println("转账失败");
            }
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != connection) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

这就是日常转账时候的操作,答题步骤是—

连接数据库,
设置为不自动提交即 connection.setAutoCommit(false);
向数据库发送sql,
异常处理处执行事务回滚
finnaly处选择执行;

但是回滚的话,上面的是全部回滚的,如果发生异常数据并不是全部要回滚,比如插入数据的时候,
下面的一个案例就是这样,向表中插入10666条数据,在10005条数据的时候出现了异常,那么之前插入的数据如果全部撤回,不太合理,那怎么办呢?

设置回滚点---- 源码中很清楚了,不在赘述;

package com.test.curd;

import java.sql.*;
import java.util.LinkedList;

public class TestBatch {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    final static String URL = "jdbc:mysql://127.0.0.1:3306/gavin?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&cachePrepStmts=true&reWriteBatchedStatements=true&useServerPrepStmts=true";
    final static String USER = "gavin";
    final static String PASSWORD = "955945";
    static long start =0;
    static long end =0;
    static LinkedList<Savepoint>savepoints= new LinkedList<>();
    public static void main(String[] args) {
        testAddBatch();
    }
    // 定义一个方法,向部门表增加1000条数据
    public static void testAddBatch(){
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(URL, USER,PASSWORD);
            String sql = "insert into accounting1 values (DEFAULT,?,?,?,now());";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置事务不自动提交
            connection.setAutoCommit(false);

          start = System.currentTimeMillis();
            for (int i = 1; i <= 10666; i++) {
                preparedStatement.setString(1, "Mary");
                preparedStatement.setString(2, "123456");
                preparedStatement.setDouble(3, 200.00d);
                preparedStatement.addBatch();// 将修改放入一个批次中
                if(i%1000==0){
                    preparedStatement.executeBatch();//1000条一提交
                    //设置回滚点
                    Savepoint savepoint = connection.setSavepoint();
                    //将该混滚点加入到最 后一个节点
                    savepoints.addLast(savepoint);
                    preparedStatement.clearBatch();// 清除批处理中的数据

                }
                //制造异常以用于回滚
                if(i==10005){
                    throw  new RuntimeException();
                }
            }

            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            end=System.currentTimeMillis();
        }catch (Exception e){//捕捉到异常
            e.printStackTrace();
            //得到回滚点
            Savepoint last = savepoints.getLast();
            try {
                //在该回滚点处
                connection.rollback(last);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            if(null!=connection) {
                try {
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null!=preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            System.out.println(end-start);
        }
    }
}