Mybatis的批量插入确实很好用,但是当数据量特别大的时候可能一次批量插入2000条数据就死翘翘了。怎么办呢?当然是原生sql是最快的啦,10000条数据秒入无压力。下面是将对象集合拼接成批量插入MySQL的简单工具类。
package com.aijiao.util;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;
import java.lang.reflect.Field;
import java.util.List;
public class SqlJointUtils {
private static final String UNDERLINE = "_";
private static final String SINGLR_QUOTES = "'";
private static final String COMMA = ",";
private static final String LEFT_BRACKET = "(";
private static final String RIGHT_BRACKET = ")";
/**
* 批量插入
*
* @param list 对象集合
* @param tableName 表名
* @param 实体类类型
* @return java.lang.String
*/
public static String jointBatchInsert(List list, String tableName) {
if (CollectionUtils.isEmpty(list)) {
return null;
}
T t = list.get(0);
String jointSqlColumnName = jointSqlColumnName(t.getClass(), tableName);
StringBuffer sb = new StringBuffer(jointSqlColumnName);
for (int i = 0; i < list.size(); i++) {
sb.append(LEFT_BRACKET);
Object o = list.get(i);
Class> aClass = o.getClass();
Field[] fields = aClass.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
try {
field.setAccessible(true);
Object o1 = field.get(o);
Class> type = field.getType();
String name = type.getSimpleName();
if (name.equals("String")) {
String s = (String) o1;
if (StringUtils.isBlank(s)) {
s = null;
sb.append(s + COMMA);
} else {
sb.append(SINGLR_QUOTES + s + SINGLR_QUOTES + COMMA);
}
} else if (name.equals("Integer")) {
Integer integer = (Integer) o1;
if (integer == null) {
integer = 0;
}
sb.append(integer + COMMA);
} else if (name.equals("date")) {
sb.append("now(),");
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
//将最后一个“,”暨倒数第一个字符替换为“)”
sb.replace(sb.length() - 1, sb.length() - 1, RIGHT_BRACKET);
}
//去掉会后一个“,”
return sb.substring(0, sb.length() - 1);
}
/**
* 拼接sql插入字段名
*
* @param c 实体类类型
* @param tableName 表名
* @return java.lang.String
*/
public static String jointSqlColumnName(Class c, String tableName) {
StringBuffer sb = new StringBuffer();
sb.append("insert into " + tableName + LEFT_BRACKET);
Field[] fields = c.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
String name = field.getName();
String s = fieldConvertColumn(name);
sb.append(s + COMMA);
}
return sb.substring(0, sb.length() - 1) + ") values";
}
/**
* java对象属性名转数据库字段名
*
* @param s 属性名
* @return java.lang.String
*/
static String fieldConvertColumn(String s) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < s.length(); i++) {
char c = s.charAt(i);
if (Character.isUpperCase(c)) {
sb.append(UNDERLINE);
}
sb.append(c);
}
return sb.toString().toLowerCase();
}
}
目前只对字符串类型、时间和整型类型做了处理,后续将优化。
主键未处理,后续将优化