BulkCopy插入要求字段的顺序和类型必须和数据库表完全一致，项目中有多个张表需要用到批量插入，每个表字段又不一样，每张表都需要写固定的代码，比较麻烦
通用方式：通过mybatis-plus插件，生成实体类，获取实体字段注解，设置每一个字段的值
版本：
com.baomidou mybatis-plus-boot-starter3.4.1 com.microsoft.sqlserver mssql-jdbc9.2.1.jre8
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;
import com.sun.rowset.CachedRowSetImpl;
import SpringBeanUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
@Component
public class PolicySqlBulkCopy {
public static Map getDeclaredFieldsInfo(Object instance) throws NoSuchFieldException {
Map map = new HashMap();
Class clazz = instance.getClass();
Field[] fields = clazz.getDeclaredFields();
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < fields.length; i++) {
String annotationName = "";
boolean annotationId = fields[i].isAnnotationPresent(TableId.class);
if (annotationId) {
// 获取注解值
annotationName = fields[i].getAnnotation(TableId.class).value();
}
boolean annotationPresent = fields[i].isAnnotationPresent(TableField.class);
if (annotationPresent) {
// 获取注解值
annotationName = fields[i].getAnnotation(TableField.class).value();
}
stringBuilder.append(annotationName);
stringBuilder.append(",");
// 字段名称
String attributesName = fields[i].getName();
map.put(attributesName, new String[]{annotationName, fields[i].getType().getName()});
}
return map;
}
public void saveBulkCopy(List objects) {
if (ObjectUtil.isEmpty(objects)) {
return;
}
T objClass = objects.get(0);
SqlSessionFactory policygndataSqlSessionFactory = (SqlSessionFactory) SpringBeanUtil.getBean("数据源名称");
SqlSession sqlSession = SqlSessionUtils.getSqlSession(policygndataSqlSessionFactory);
Connection connection = sqlSession.getConnection();
try {
// 数据库表名
String tableName = objClass.getClass().getAnnotation(TableName.class).value();
// 表字段属性名和类型@TableId和@TableField
Map declaredFieldsInfo = getDeclaredFieldsInfo(objClass);
ResultSet rs = executeSQL(connection, "select * from " + tableName + " where 1=0");
CachedRowSetImpl crs = new CachedRowSetImpl();
crs.populate(rs);
//循环插入数据
long startTime = System.currentTimeMillis();
//既然是批量插入肯定是需要循环
for (int i = 0, leg = objects.size(); i < leg; i++) {
//移动指针到“插入行”，插入行是一个虚拟行
crs.moveToInsertRow();
//更新虚拟行的数据（实体类要新增的字段，大家根据自己的实体类的字段来修改）
//数据库字段 ，填写的值
JSONObject jsonObject = JSONUtil.parseObj(objects.get(i), false, true);
for (Map.Entry map : jsonObject.entrySet()) {
// 属性和值
String key = map.getKey();
Object value = map.getValue();
// 过滤不是数据库的字段
if (!declaredFieldsInfo.containsKey(key)) {
continue;
}
// 字段名称，字段类型
String[] strings = declaredFieldsInfo.get(key);
String annotationName = strings[0];
String type = strings[1];
if (StrUtil.equals(type, String.class.getName())) {
if (ObjectUtil.isEmpty(value) || StrUtil.equals("null", value.toString())) {
crs.updateString(annotationName, null);
} else {
crs.updateString(annotationName, value.toString());
}
} else if (StrUtil.equals(type, Integer.class.getName())) {
if (ObjectUtil.isEmpty(value) || StrUtil.equals("null", value.toString())) {
crs.updateInt(annotationName, 0);
} else {
crs.updateInt(annotationName, Integer.parseInt(value.toString()));
}
} else if (StrUtil.equals(type, LocalDateTime.class.getName())) {
LocalDateTime localDateTime = (LocalDateTime) value;
crs.updateDate(annotationName, localTimeToDate(localDateTime));
} else if (StrUtil.equals(type, LocalDate.class.getName())) {
LocalDate localDate = (LocalDate) value;
crs.updateDate(annotationName, Date.valueOf(localDate));
} else if (StrUtil.equals(type, Long.class.getName())) {
crs.updateLong(annotationName, 0);
} else if (StrUtil.equals(type, Double.class.getName())) {
crs.updateDouble(annotationName, (Double) value);
} else if (StrUtil.equals(type, int.class.getName())) {
crs.updateInt(annotationName, (Integer) value);
} else if (StrUtil.equals(type, java.util.Date.class.getName())) {
java.util.Date date = (java.util.Date) value;
crs.updateDate(annotationName, new Date(date.getTime()));
} else if (StrUtil.equals(type, java.math.BigDecimal.class.getName())) {
if (ObjectUtil.isEmpty(value) || StrUtil.equals("null", value.toString())) {
crs.updateBigDecimal(annotationName, null);
} else {
BigDecimal bigDecimal = BigDecimal.valueOf(Integer.valueOf(value.toString()));
crs.updateBigDecimal(annotationName, bigDecimal);
}
} else {
log.info("未知的数据类型：{}", type);
}
}
//插入虚拟行
crs.insertRow();
//移动指针到当前行
crs.moveToCurrentRow();
}
//进行批量插入
SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setKeepIdentity(false);
copyOptions.setBatchSize(1000);
copyOptions.setUseInternalTransaction(false);
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connection);
bulkCopy.setBulkCopyOptions(copyOptions);
bulkCopy.setDestinationTableName(tableName);
bulkCopy.writeToServer(crs);
crs.close();
bulkCopy.close();
log.info("耗时：{},数量:{}", System.currentTimeMillis() - startTime, objects.size());
} catch (Exception e) {
e.printStackTrace();
}
}
// 数据查找，返回查找的内容，向上抛异常
public ResultSet executeSQL(Connection con, String sql, Object... object) throws SQLException {
PreparedStatement ps = con.prepareStatement(sql);
for (int i = 0; i < object.length; i++) {
//ps传入参数的下标是从1开始
ps.setObject(i + 1, object[i]);
}
//返回结果集
return ps.executeQuery();
}
public static java.sql.Date localTimeToDate(LocalDateTime lt) {
return new java.sql.Date(lt.atZone(ZoneId.systemDefault()).toInstant()
.toEpochMilli());
}
}
遇到的一些问题解决方法：
https://www.cnblogs.com/qydmw/p/13457641.html
https://www.shuzhiduo.com/A/kmzLLo3lzG/
