mysql插入数据集合可以循环插入，可以使用jdbc的批处理executeBatch语句，可以使用load data local infile语句插入。
目录
一、循环遍历插入
二、批处理executeBatch语句
三、load data local infile语句
1.生成文件插入
2.采用setLocalInfileInputStream方法，这个方法可以不用生成文件，减少生成文件，往文件写入数据的IO操作
报错问题
反射获取表的所有数据库字段名称
一、循环遍历插入
List<BimXmlLevel> levels=new ArrayList<>();
for (BimXmlLevel level : levels) {
(level);
}每个对象每次都会生成一条插入语句 insert into tableName values ()
这种方法只要数据量一多速度会很慢。
二、批处理executeBatch语句
使用prepareStatement预编译语句
使用addBatch，executeBatch批量插入语句
private static JdbcTemplate jdbcTemplate = ();
/**
* 使用prepareStatement预编译语句
* 使用addBatch，executeBatch批量插入语句
* 需要在数据库url连接添加参数rewriteBatchedStatements=true
* 实现原理就是把多条插入语句变成一条 INSERT INTO tableName(xx) values(xx),(xx),(xx)
* 提示:sql后面不要加; 不然底层会拼接sql报错
*
* @param list
* @throws Exception
*/
public static void saveList(List list) throws Exception {
long start = ();
Connection connection = ().getConnection();
(false);//不自动commit，一定要设置，不然速度也会很慢
//通过反射获取表的所有数据库字段名称
List<String> names = getNames((0));
//多少个字段就有多少个?号
List<String> vs = new ArrayList<>();
for (int i = 0; i < (); i++) {
("?");
}
//表名
String tableName = table((0));
//语句
String sql = "INSERT INTO " + tableName + "(" + (names, ",") + ") VALUES (" + (vs, ",") + ")";
PreparedStatement stmt = (sql);
for (int j = 0; j < (); j++) {
Object obj = (j);
for (int i = 0; i < (); i++) {
String name = (i);
Object value = (obj, name);
(i + 1, value);//设置值
}
();
}
//批量执行
();
();
();
();//commit
();
long end = ();
("{}数据Batch入库成功：{}条,总耗时：{} ms", tableName, (), end - start);
}需要在数据库url连接添加参数rewriteBatchedStatements=true
实现原理就是把多条插入语句变成一条 INSERT INTO tableName(xx) values(xx),(xx),(xx)
提示:sql后面不要加; 不然底层会拼接sql报错
三、load data local infile语句
1.生成文件插入
private static JdbcTemplate jdbcTemplate = ();
/**
* 行分隔符
*/
public static final String LINE_TERMINATED = ";;";
/**
* 字段分隔符
*/
public static final String FIELDS_TERMINATED = "||";
public static void saveFile(List list) throws Exception {
long start = ();
Connection connection = ().getConnection();
(false);//不自动commit
//通过反射获取表的所有数据库字段名称
List<String> names = getNames((0));
//表名
String tableName = table((0));
//获取插入数据
StringBuilder data = new StringBuilder();
for (Object obj : list) {
for (int i = 0; i < (); i++) {
String name = (i);
Object value = (obj, name);
if (value == null) value = "";
if ("".equals(().getName()))
value = ((value));
if (i == () - 1) {
(value).append(LINE_TERMINATED);//最后一个参数，一条数据结束
} else {
(value).append(FIELDS_TERMINATED);
}
}
}
//生成数据文件
String path = () + + "BIM" + + "data" + + tableName + "-" + () + ".txt";
File file = new File(path);
//写入数据
(file, data, "utf-8");
//文件路径使用 / 符号
path = ("\\", "/");
//load data local infile语句
String sql = "load data local infile '"+path+"' into table " + tableName +
" fields terminated by '" + FIELDS_TERMINATED + "' lines terminated by '" + LINE_TERMINATED + "' " +
" (" + (names, ",") + ");";
Statement statement = ();
(sql);
();
();//commit
();
long end = ();
("{}数据file入库成功：{}条,总耗时：{} ms", tableName, (), end - start);2.采用setLocalInfileInputStream方法，这个方法可以不用生成文件，减少生成文件，往文件写入数据的IO操作
private static JdbcTemplate jdbcTemplate = ();
/**
* 行分隔符
*/
public static final String LINE_TERMINATED = ";;";
/**
* 字段分隔符
*/
public static final String FIELDS_TERMINATED = "||";
public static void saveFile(List list) throws Exception {
long start = ();
Connection connection = ().getConnection();
(false);//不自动commit
//通过反射获取表的所有数据库字段名称
List<String> names = getNames((0));
//表名
String tableName = table((0));
//获取插入数据
StringBuilder data = new StringBuilder();
for (Object obj : list) {
for (int i = 0; i < (); i++) {
String name = (i);
Object value = (obj, name);
if (value == null) value = "";
if ("".equals(().getName()))
value = ((value));
if (i == () - 1) {
(value).append(LINE_TERMINATED);//最后一个参数，一条数据结束
} else {
(value).append(FIELDS_TERMINATED);
}
}
}
byte[] bytes = ().getBytes();
InputStream dataStream = new ByteArrayInputStream(bytes);
//文件路径可以不写，采用文件流
String sql = "load data local infile 'filepath' into table " + tableName +
" fields terminated by '" + FIELDS_TERMINATED + "' lines terminated by '" + LINE_TERMINATED + "' " +
" (" + (names, ",") + ");";
PreparedStatement stmt = (sql);
ClientPreparedStatement statement = ();
(dataStream);//设置文件输入流
int size = ();
if (() != size)
throw new BusinessException(tableName + "导入条数：" + size + ",总数据条数：" + () + ",检查数据是否有非法字符" + FIELDS_TERMINATED + "," + LINE_TERMINATED);
();
();//commit
();
long end = ();
("{}数据file入库成功：{}条,总耗时：{} ms", tableName, (), end - start);
}报错问题
Loading local data is disabled; this must be enabled on both the client and server sides
1.在mysql配置文件修改：
[mysqld]下添加
local_infile = 1
[mysql]下添加
local_infile = 1
2.在数据库连接添加参数allowLoadLocalInfile=true
url=jdbc\:mysql\xxxxx?allowLoadLocalInfile=true
注意：文本文件中字段中的空值用\N表示
反射获取表的所有数据库字段名称
/**
* 获取所有数据库字段
*/
private static List<String> getNames(Object obj) {
List<String> ignore = ("parentIds", "createBy", "createTime", "updateTime", "companyId");//忽略插入字段
List<String> names = new ArrayList<>();
List<Field> fields = new ArrayList<>();
for (Class<?> superClass = (); superClass != ; superClass = ()) {
Field[] declaredFields = ();
((declaredFields));
}
for (Field field : fields) {
Boolean status = true;
Transient fieldTran = ();
if (fieldTran != null) status = false;
String methodName = "get" + (());
Method methodByName = (obj, methodName);
if (methodByName != null) {
Transient methodTran = ();
if (methodTran != null) status = false;
} else {
status = false;
}
if (status && !(())) {
(());
}
}
return names;
}
/**
* 获取数据库表名
*/
private static String table(Object obj) {
Table table = ().getAnnotation();
if (table != null && (())) {
return ();
} else {
return ().getSimpleName();
}
}