JavaSE JDBC事务控制之批处理设置回滚点

最新推荐文章于 2024-07-23 10:00:07 发布

MR_谢昊昊

最新推荐文章于 2024-07-23 10:00:07 发布

阅读量906

点赞数

CC 4.0 BY-SA版权

分类专栏： JavaSE，JavaEE 文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/qq_41587516/article/details/121845972

JavaSE，JavaEE 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了如何在Java中使用批处理操作数据库，通过`PreparedStatement`实现10000条数据插入，并设置了多个回滚点。遇到异常时，通过Savepoint回滚到特定点并进行处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

批处理设置回滚点：

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

public class TestTransaction2 {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useServerPrepStmts=true&cachePrepStmts=true&&rewriteBatchedStatements=true";
    private static String user="root";
    private static String password="root";
    public static void main(String[] args) {
        testAddBatch();
    }
    // 定义一个方法,向部门表增加1000条数据
    public static void testAddBatch(){
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        LinkedList<Savepoint> savepoints =new LinkedList<Savepoint>();
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            connection.setAutoCommit(false);
            String sql="insert into dept values (DEFAULT ,?,?)";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            for (int i = 1; i <= 10663; i++) {
                preparedStatement.setString(1, "name");
                preparedStatement.setString(2, "loc");
                preparedStatement.addBatch();// 将修改放入一个批次中
                if(i%1000==0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();// 清除批处理中的数据
                    // 设置回滚点
                    Savepoint savepoint = connection.setSavepoint();
                    savepoints.addLast(savepoint);
                }
                // 数据在 100001条插入的时候出现异常
                if(i ==10001){
                    int x =1/0;
                }
            }
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }catch (Exception e){
            if(null != connection){
                try {
                    //Savepoint sp = savepoints.getLast();
                    Savepoint sp = savepoints.get(4);
                    if(null != sp){
                        // 选择回滚点
                        connection.rollback(sp);// 回滚
                    }
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            if(null != connection){
                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();
                }
            }
        }
    }
}