Java实战：多线程批量插入20W数据【未完成 】

业务需求：

新建一张表label表，把数据从A表查询出、再经过处理后，插入label表，预计查询出十万条数据，处理后为20万条数据插入MySQL数据库，可重复执行！如果存在做更新，如果不存在就插入。

---

设计方案：

springboot + mybatis + mysql

多线程 + 分页查询 +  批量插入

1、多线程：

2、分页查询：

3、批量插入：

---

问题分析：

提示：这里填写问题的分析：

---

示例代码：

1、springboot使用线程池

@Configuration
@EnableAsync
public class ThreadConfig {
    @Bean
    public ThreadPoolTaskExecutor executor(){
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        //配置核心线程数
        executor.setCorePoolSize(15);
        //配置最大线程数
        executor.setMaxPoolSize(30);
        //配置队列大小
        executor.setQueueCapacity(1000);
        //线程的名称前缀
        executor.setThreadNamePrefix("Executor-Label-");
        //等待所有任务结束后再关闭线程池
        executor.setWaitForTasksToCompleteOnShutdown(true);
        //执行初始化
        executor.initialize();
        return executor;
    }
}

2、future多线程查询

@Service
public class SelectTask {
    @Autowired
    private ThreadPoolTaskExecutor executor;

    @Autowired
    private LabelDao labelDao;

    public List<LabelPojo> queryAllThreadPool(int limit) {

        //还是获取到总记录数，本机是600多W测试数据
        Long count = labelDao.getCount();

        List<FutureTask<List<LabelPojo>>> resultList = new ArrayList<>();

        //分段次数
        long cycles = count / limit;
        for (int i = 0; i < cycles; i++) {
            //每一段的起始坐标
            long idx =  i * limit;

            //具体的查询任务
            FutureTask<List<LabelPojo>> futureTask = new FutureTask<>(() -> labelDao.queryAllByLimit(idx,limit));

            //把任务丢给线程池调度执行
            executor.execute(futureTask);
            //future异步模式，把任务放进去先，先不取结果
            resultList.add(futureTask);
        }

        List<LabelPojo> result = new ArrayList<LabelPojo>();
        while (resultList.size() > 0) {
            Iterator<FutureTask<List<LabelPojo>>> iterator = resultList.iterator();
            while (iterator.hasNext()) {
                try {
                    result.addAll(iterator.next().get());
                    //获取一个就删除一个任务
                    iterator.remove();
                } catch (InterruptedException  | ExecutionException e) {
                    //log.error("多线程查询出现异常：{}", e.getMessage());
                }
            }
        }

        //最后一次数据可能不为整，需要额外操作
        if (result.size() != count) {
            result.addAll(labelDao.queryAllByLimit(result.size(),Math.toIntExact(count)));
        }
        return result;
    }

}

3、JDBC方式批量插入数据

package com.example.demo.threadTest.ForkJoinTest.main.task;

import com.example.demo.threadTest.ForkJoinTest.pojo.DeptPojo;
import com.example.demo.threadTest.ForkJoinTest.utils.LabelEnum;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

@Service
public class JDBCTask {
    // 从spring管理的数据源中直接拿
    @Resource(name = "dataSource")
    private DataSource dataSource;


    public void jdbcTest(ArrayList<DeptPojo> deptPojoArrayList) throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        String sql = "REPLACE INTO lixiunan_test_label (id,kind,`value`,index_no,label_no,label_name) VALUES(?,?,?,?,?,?) ";
        PreparedStatement statement = connection.prepareStatement(sql);
        for (int i = 0; i < deptPojoArrayList.size(); i++) {
            DeptPojo deptPojo = deptPojoArrayList.get(i);
            String deptChiefNo = deptPojo.getDeptChiefNo();
            String deptNo = deptPojo.getDeptNo();
            statement.setString(2, "dept");
            statement.setString(4, deptNo);
            if("A".equals(deptChiefNo)){
                statement.setString(1, String.valueOf(deptNo+"D"+(LabelEnum.DEPT_NO3_ST.getLabelNo())));
                statement.setString(3, "Y");
                statement.setString(5, LabelEnum.DEPT_NO3_ST.getLabelNo());
                statement.setString(6, LabelEnum.DEPT_NO3_ST.getLabelName());
                statement.addBatch();
                statement.setString(1, String.valueOf(deptNo+"D"+(LabelEnum.DEPT_NO2_XN.getLabelNo())));
                statement.setString(3, "N");
                statement.setString(5, LabelEnum.DEPT_NO2_XN.getLabelNo());
                statement.setString(6, LabelEnum.DEPT_NO2_XN.getLabelName());
                statement.addBatch();
            }else{
                statement.setString(1, String.valueOf(deptNo+"D"+(LabelEnum.DEPT_NO3_ST.getLabelNo())));
                statement.setString(2, "dept");
                statement.setString(3, "N");
                statement.setString(5, LabelEnum.DEPT_NO3_ST.getLabelNo());
                statement.setString(6, LabelEnum.DEPT_NO3_ST.getLabelName());
                statement.addBatch();
                statement.setString(1, String.valueOf(deptNo+"D"+(LabelEnum.DEPT_NO2_XN.getLabelNo())));
                statement.setString(3, "Y");
                statement.setString(5, LabelEnum.DEPT_NO2_XN.getLabelNo());
                statement.setString(6, LabelEnum.DEPT_NO2_XN.getLabelName());
                statement.addBatch();
            }
        }
        long start = System.currentTimeMillis();
        statement.executeBatch();
        connection.commit();

        statement.close();
        connection.close();
        System.out.print("耗时：");
        System.out.println(System.currentTimeMillis() - start);
    }



}

 4、枚举类型

public enum LabelEnum {
    DEPT_NO1_WT("1","无头部门"),
    DEPT_NO2_XN("2","虚拟部门"),
    DEPT_NO3_ST("3","实体部门");
    private String labelNo;
    private String labelName;

    public String getLabelNo() {
        return labelNo;
    }

    public void setLabelNo(String labelNo) {
        this.labelNo = labelNo;
    }

    public String getLabelName() {
        return labelName;
    }

    public void setLabelName(String labelName) {
        this.labelName = labelName;
    }

    LabelEnum(String labelNo, String labelName) {
        this.labelNo = labelNo;
        this.labelName = labelName;
    }
}

5、SQL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.threadTest.ForkJoinTest.dao.LabelDao">

    <update id="insert" parameterType="com.example.demo.threadTest.ForkJoinTest.pojo.LabelPojo">

    </update>
    <select id="select" resultType="com.example.demo.threadTest.ForkJoinTest.pojo.LabelPojo">
        select id,kind as kind,`value` as `value`,index_no as indexNo,label_no as labelNo,label_name as labelName
        from lixiunan_test_label
    </select>
    <insert id="insertForeach" >
        insert into lixiunan_test_label (kind,`value`,index_no,label_no,label_name)
        values
        <foreach item="data" collection="data" separator=",">
        (#{data.kind},#{data.value},#{data.indexNo},#{data.labelNo},#{labelName})
        </foreach>

    </insert>
    <select id="getCount" resultType="java.lang.Long">
        select count(1) from lixiunan_test_label
    </select>
    <select id="queryAllByLimit" resultType="com.example.demo.threadTest.ForkJoinTest.pojo.LabelPojo">
        select id,kind as kind,`value` as `value`,index_no as indexNo,label_no as labelNo,label_name as labelName
        from lixiunan_test_label limit #{from},#{to}
    </select>
    <select id="queryAll" resultType="com.example.demo.threadTest.ForkJoinTest.pojo.LabelPojo">
        select id,kind as kind,`value` as `value`,index_no as indexNo,label_no as labelNo,label_name as labelName
        from lixiunan_test_label
    </select>
</mapper>

6、建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : local-mysql
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : localhost:3306
 Source Schema         : lxn_test

 Target Server Type    : MySQL
 Target Server Version : 80023
 File Encoding         : 65001

 Date: 26/03/2023 22:07:54
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for lixiunan_test_label
-- ----------------------------
DROP TABLE IF EXISTS `lixiunan_test_label`;
CREATE TABLE `lixiunan_test_label` (
  `id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `kind` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `label_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `index_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `value` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `label_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  UNIQUE KEY `main_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;