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

CodeMartain

于 2021-09-11 15:47:42 发布

阅读量117

点赞数

分类专栏： Database_All 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_54061333/article/details/120238445

版权

Database_All 专栏收录该内容

62 篇文章 1 订阅

订阅专栏

这篇博客探讨了数据库事务的重要性和使用，通过示例展示了如何在Java中进行数据库连接、事务设置、预编译SQL以及异常处理。在异常情况下，未开启事务导致数据未按预期回滚，而设置了事务并利用保存点可以实现部分数据的精确回滚，避免了全量回滚的不合理性。

摘要由CSDN通过智能技术生成

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