使用场景:
批量导入一大堆的excel文件,插入数据时候有点慢,所以要批量插入。插入中跳过主键重复报错
mysql 批量插入,批量插入跳过主键重复,简化批量插入
package com.chenfan.finance.utils;
import cn.hutool.core.util.ReflectUtil;
import com.chenfan.finance.producer.U8Produce;
import java.util.List;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.ForkJoinTask;
import java.util.concurrent.RecursiveTask;
/**
* @author liran
*/
public class BatchInsertUtil {
public static int batchInsert(List<?> tables, Class<?> mapperClass, String methodName) {
int insert = 5000;
if (tables.size() < insert) {
insert = tables.size();
}
int loop = tables.size() / insert;
Object mapper = U8Produce.applicationContext.getBean(mapperClass);
int result = 0;
for (int i = 0; i < loop; i++) {
int start = i * insert;
int end = (i + 1) * insert;
if (loop - 1 == i) {
end = tables.size();
}
Object invoke = ReflectUtil.invoke(mapper, methodName, tables.subList(start, end));
int sum = Integer.parseInt(String.valueOf(invoke));
result = sum + result;
}
return result;
}
public static int batchInsertTask(List<?> tables, Class<?> mapperClass, String methodName) {
ForkJoinPool fjp = new ForkJoinPool(8);
Object mapper = U8Produce.applicationContext.getBean(mapperClass);
ForkJoinTask<Integer> task = new SaveTask(tables, 0, tables.size(), mapper, methodName);
return fjp.invoke(task);
}
public static class SaveTask extends RecursiveTask<Integer> {
static final int THRESHOLD = 5000;
List<?> array;
int start;
int end;
Object mapper;
String method;
SaveTask(List<?> array, int start, int end, Object mapper, String method) {
this.array = array;
this.start = start;
this.end = end;
this.mapper = mapper;
this.method = method;
}
@Override
protected Integer compute() {
if (end - start <= THRESHOLD) {
Object invoke = ReflectUtil.invoke(mapper, method, array.subList(start, end));
return Integer.parseInt(String.valueOf(invoke));
}
int middle = (end + start) / 2;
System.out.println(String.format("split %d~%d ==> %d~%d, %d~%d", start, end, start, middle, middle, end));
SaveTask task1 = new SaveTask(this.array, start, middle, mapper, method);
SaveTask task2 = new SaveTask(this.array, middle, end, mapper, method);
invokeAll(task1, task2);
int subresult1 = task1.join();
int subresult2 = task2.join();
int result = subresult1 + subresult2;
System.out.println("result = " + subresult1 + " + " + subresult2 + " ==> " + result);
return result;
}
}
}
说明:SaveTask 是用了fork join 这里一般要根据cpu 核数来确定 “ForkJoinPool fjp = new ForkJoinPool(8)”
aplication 就是普通的spring bean注入
mapper
插入时候如果要跳过主键重复或者唯一索引的校验, insert ignore
<insert id="insertList">
insert ignore into reference_table (
id, tid, task_year_month, table_type
)
VALUES
<foreach collection="subList" item="item" separator=",">
(#{item.id,jdbcType=VARCHAR},
#{item.tid,jdbcType=VARCHAR},
#{item.taskYearMonth,jdbcType=TIMESTAMP},
#{item.tableType,jdbcType=TINYINT})
</foreach>
</insert>
直接调用
BatchInsertUtil.batchInsert(tables, ReferenceTableMapper.class, "insertList");