java 原生sql批量插入,Java对象集合转MySQL批量插入语句

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();

}

}

目前只对字符串类型、时间和整型类型做了处理，后续将优化。

主键未处理，后续将优化