13秒插入30万数据，这才是批量插入的正确姿势！

一、工具类封装

package com.example.demo.v2;

import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.LinkedBlockingDeque;

/**
 * @author yming wang
 * @date 2023/8/17 15:12
 * @desc jdbc工具类
 */
@Slf4j
public class JdbcUtils {

    /**
     *分片条数
     */
    private static int shardingCount = 100000;

    /**
     *批量插入条数
     */
    private static int batchAddCount = 500;

    static {
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @params:
     * @param url
     * @param username
     * @param password
     * @return java.sql.Connection
     * @author yming wang
     * @date 2023/8/17 17:05
     * @desc 获取连接
     * 参数描述
     * url = jdbc:mysql://127.0.0.1:3306/msyql?userUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true
     * userUnicode = true 是否使用字符集
     * characterEncoding = utf8 使用utf8编码格式
     * rewriteBatchedStatements = true 在调用 executeBatch() 批量执行 INSERT语句时(3.1.13以后的mysql连接驱动都支持该配置)
     * username = your username
     * password = your password
     */
    public static Connection getConnection(String url,String username,String password) {
        Connection conn= null;
        try {
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * @params:
     * @param connection
     * @param sql
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     * @author yming wang
     * @date 2023/8/17 17:04
     * @desc 查询
     */
    public static List<Map<String,Object>> query(Connection connection,String sql) {
        List<Map<String,Object>> elements = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        long start = System.currentTimeMillis();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //获取结果集
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnLen = metaData.getColumnCount();
            while (resultSet.next()) {
                //组装数据
                Map<String, Object> element = new HashMap<>(columnLen);
                for (int j = 0; j < columnLen; j++) {
                    String columnName = metaData.getColumnName(j + 1);
                    element.put(columnName, resultSet.getObject(columnName));
                }
                elements.add(element);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            release(null,preparedStatement,resultSet);
        }
        long end = System.currentTimeMillis();
        log.info("select方法执行sql = {} 查询完成，共查询出： {}条数据，共耗时：{}秒",sql,elements.size(),(end - start) / 1000);
        return elements;
    }

    /**
     * @params:
     * @param shardingList
     * @param connection
     * @param sql
     * @param keyArray
     * @return
     * @author yming wang
     * @date 2023/8/17 17:04
     * @desc 批量插入
     */
    public static void batchAdd(Connection connection, String sql,
                                LinkedBlockingDeque<List<Map<String, Object>>> shardingList,
                                String[] keyArray){
        PreparedStatement preparedStatement = null;
        try {
            //手动事务
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            for (List<Map<String, Object>> list : shardingList) {
                for (int i = 0; i < list.size(); i++) {
                    Map<String, Object> data = list.get(i);
                    try {
                        //动态绑定
                        dynamicColumn(preparedStatement,data,keyArray).addBatch();
                        //每500条批量提交一次
                        if ((i + 1) % batchAddCount == 0) {
                            //批量执行
                            preparedStatement.executeBatch();
                            preparedStatement.clearBatch();
                            //提交事务
                            connection.commit();
                            log.info("batchAdd方法执行批处理条数：{}",(i + 1));
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                try {
                    //执行剩余部分数据
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                    //提交事务
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            release(null,preparedStatement,null);
        }
    }

    /**
     * @params:
     * @param preparedStatement
     * @param map
     * @param keyArray
     * @return java.sql.PreparedStatement
     * @author yming wang
     * @date 2023/8/17 17:04
     * @desc 动态绑定
     */
    public static PreparedStatement dynamicColumn(PreparedStatement preparedStatement, Map<String, Object> map,String[] keyArray){
        try {
            for (int i = 1;i <= keyArray.length;i++){
                String key = keyArray[i - 1];
                String value = String.valueOf(map.get(key));
                //设置数据
                preparedStatement.setObject(i,value);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }

    /**
     * @params:
     * @param maps
     * @return java.util.concurrent.LinkedBlockingDeque<java.util.List<java.util.Map<java.lang.String,java.lang.Object>>>
     * @author yming wang
     * @date 2023/8/17 17:04
     * @desc 数据分片
     */
    public static LinkedBlockingDeque<List<Map<String, Object>>> sharding(List<Map<String, Object>> maps){
        LinkedBlockingDeque<List<Map<String, Object>>> blockingDeque = new LinkedBlockingDeque();
        if (!CollectionUtils.isEmpty(maps)){
            int size = maps.size();
            //数据分片
            int batch = maps.size() % shardingCount == 0 ? maps.size() / shardingCount : maps.size() / shardingCount + 1;
            for (int i = 0;i < batch;i++){
                List<Map<String, Object>> dataSharding;
                if (i == (batch - 1)){
                    dataSharding = maps.subList(i * shardingCount, size);
                }else{
                    dataSharding = maps.subList(i * shardingCount, (i + 1) * shardingCount);
                }
                blockingDeque.add(dataSharding);
            }
            log.info("sharding方法执行数据分片完成");
        }
        return blockingDeque;
    }

    /**
     * @params:
     * @param connection
     * @param createTableSql
     * @return
     * @author yming wang
     * @date 2023/8/17 17:03
     * @desc 创建表
     */
    public static void createTable(Connection connection,String createTableSql){
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(createTableSql);
            int i = preparedStatement.executeUpdate(createTableSql);
            log.info("renameTable方法执行sql = {} ,result = {}",createTableSql,i);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            release(null,preparedStatement,null);
        }
    }

    /**
     * @params:
     * @param connection
     * @param tableName
     * @param newTableName
     * @return
     * @author yming wang
     * @date 2023/8/17 17:03
     * @desc 重命名表
     */
    public static void renameTable(Connection connection,String tableName,String newTableName){
        PreparedStatement preparedStatement = null;
        try {
            String sql = "rename table " + tableName + " to " + newTableName;
            preparedStatement = connection.prepareStatement(sql);
            int i = preparedStatement.executeUpdate(sql);
            log.info("renameTable方法执行sql = {} ,result = {}",sql,i);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            release(null,preparedStatement,null);
        }
    }

    /**
     * @params:
     * @param connection
     * @param tableName
     * @return
     * @author yming wang
     * @date 2023/8/17 17:03
     * @desc 删除表
     */
    public static void dropTable(Connection connection,String tableName){
        PreparedStatement preparedStatement = null;
        try {
            String sql = "drop table if exists " + tableName;
            preparedStatement = connection.prepareStatement(sql);
            int i = preparedStatement.executeUpdate(sql);
            log.info("dropTable方法执行sql = {} ,result = {}",sql,i);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            release(null,preparedStatement,null);
        }
    }

    /**
     * @params:
     * @param connection
     * @param tableName
     * @return
     * @author yming wang
     * @date 2023/8/17 17:03
     * @desc 清空表
     */
    public static void truncateTable(Connection connection,String tableName){
        PreparedStatement preparedStatement = null;
        try {
            String sql = "truncate " + tableName;
            preparedStatement = connection.prepareStatement(sql);
            boolean execute = preparedStatement.execute();
            log.info("truncateTable方法执行sql = {} ,result = {}",sql,execute);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            release(null,preparedStatement,null);
        }
    }
    
    /**
     * @params: 
     * @param connection
     * @param preparedStatement
     * @param resultSet
     * @return 释放资源
     * @author yming wang
     * @date 2023/8/17 17:03
     * @desc 
     */
    public static void release(Connection connection, PreparedStatement preparedStatement,ResultSet resultSet) {
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
            if (connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

二、使用demo

	public static void main(String[] args) {
        String url = "jdbc:mysql://127.0.0.1:3306/mysql?userUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true";
        String user = "root";
        String password = "zte@Unicom20";
        Connection connection = JdbcUtils.getConnection(url, user, password);

        //查询30w数据
        String sql = "select * from table";
        List<Map<String, Object>> list = JdbcUtils.query(connection, sql);

        //数据分片
        LinkedBlockingDeque<List<Map<String, Object>>> shardingList = JdbcUtils.sharding(list);

        //使用批量插入13秒执行完成
        String insertSql = "insert into table(column1,column2,column1) values(value1,value2,value3)";
        JdbcUtils.batchAdd(connection,insertSql,shardingList,getInsertTableSqlColumns());

        //释放资源
        JdbcUtils.release(connection,null,null);
    }