平时项目中,有时候可能会遇到需要大批量写入数据到mysql数据库中的场景,这时候我们可以利用mysql自带的load data语法,先将数据写入文本文件,再将文件读入数据库,具体用法看下面!
1、表结构如下:CREATE TABLE `wework_receive_messages` ( `msg_id` varchar(64) NOT NULL COMMENT 'ID', `action` varchar(12) DEFAULT NULL COMMENT '动作', `from_user` varchar(64) DEFAULT NULL COMMENT '发出用户', `to_list` varchar(2056) DEFAULT NULL, `room_id` varchar(32) DEFAULT NULL COMMENT '房间id', `msg_time` bigint(20) DEFAULT NULL COMMENT '消息时间', `msg_type` varchar(12) DEFAULT NULL COMMENT '消息类型', `content` varchar(1024) DEFAULT NULL COMMENT '消息内容', `seq` bigint(20) DEFAULT NULL COMMENT '消息计数', `msg_flag` tinyint(1) DEFAULT NULL COMMENT '消息状态(0未拉取 1已拉取)', PRIMARY KEY (`msg_id`), KEY `ix_wework_receive_messages_from_user` (`from_user`), KEY `ix_wework_receive_messages_msg_time` (`msg_time`), KEY `ix_wework_receive_messages_room_id` (`room_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;2、原生sql用法如下:
load data concurrent local infile '路径' replace into table '表名' character set utf8 fields terminated by '字段分隔符' lines terminated by '行分隔符' (字段1, 字段2, 字段3);3、封装工具代码如下:
LoadUtil.java load data主要工具
import lombok.extern.log4j.Log4j2; import org.springframework.jdbc.core.JdbcTemplate; import java.lang.reflect.AnnotatedType; import java.lang.reflect.Field; import java.util.List; @Log4j2 public class LoadUtil{ private String tableName; private String[] columns; private List data; // 字段分隔符 private String terminated; // 行数据分隔符 private String linesTerminated; private ModeEnum mode; public LoadUtil(String tableName, String[] columns, List data, String terminated, String linesTerminated, ModeEnum mode) { this.tableName = tableName; this.columns = columns; this.data = data; this.terminated = terminated; this.linesTerminated = linesTerminated; this.mode = mode; } public LoadUtil(String tableName, String[] columns, List data, ModeEnum mode) { this(tableName, columns, data, "$$", "n", mode); } public LoadUtil(String tableName, String[] columns, List data) { this(tableName, columns, data, ModeEnum.replace); } public void loadData() { String filePath = ""; try { long startAt = System.currentTimeMillis(); log.info("数据大小: {}", this.data.size()); String str = concatData(); filePath = writeFile(str); String sql = sqlGenerator(filePath); log.info("load data sql: {}", sql); long endAt = System.currentTimeMillis(); log.info("耗时: {}s", (endAt - startAt) / 1000); log.info("load data写入完成"); sqlExecutor(sql); } catch (NoSuchFieldException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } finally { deleteFile(filePath); } } private void deleteFile(String filePath) { if(filePath != null && filePath != "") { IOUtil.delete(filePath); } } private String sqlGenerator(String filePath) { String sql = String.format("load data concurrent local infile '%s' %s into table %s character set utf8 fields terminated by '%s' lines terminated by '%s' (%s)", filePath, mode.name(), tableName, terminated, linesTerminated, StringUtils.join(columnsConvert(), ",")); return sql; } private void sqlExecutor(String sql) { JdbcTemplate jdbcTemplate = (JdbcTemplate) ApplicationContextUtils.getBean("jdbcTemplate"); jdbcTemplate.execute(sql); } private String[] columnsConvert() { String[] _columns = new String[columns.length]; for(int i=0,len=columns.length; i 依赖的方法如下:
ApplicationContextUtils.java spring bean对象获取工具,此处主要用来获取jdbcTemplate对象import lombok.extern.log4j.Log4j2; import org.springframework.beans.BeansException; import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.stereotype.Component; @Component @Log4j2 public class ApplicationContextUtils implements ApplicationContextAware { private static ApplicationContext context; @Override public void setApplicationContext(ApplicationContext applicationContext) throws BeansException { if (context == null) { context = applicationContext; } } public static ApplicationContext getApplicationContext() { return context; } public static Object getBean(String name) { log.info("application_context: {}", context); return context.getBean(name); } public staticT getBean(Class classz) { return context.getBean(classz); } } IOUtil.java 用来写入临时文本文件
package com.mw.web.common.utils; import java.io.*; public class IOUtil { // 读取指定路径文本文件 public static String read(String filePath) { StringBuilder str = new StringBuilder(); BufferedReader in = null; try { in = new BufferedReader(new FileReader(filePath)); String s; try { while ((s = in.readLine()) != null) str.append(s + 'n'); } finally { in.close(); } } catch (IOException e) { e.printStackTrace(); } return str.toString(); } // 读取指定路径文本文件 public static String readWeb(String filePath) { StringBuilder str = new StringBuilder(); BufferedReader in = null; try { InputStream is = IOUtil.class.getClassLoader().getResourceAsStream(filePath); InputStreamReader isr = new InputStreamReader(is); in = new BufferedReader(isr); String s; try { while ((s = in.readLine()) != null) str.append(s + 'n'); } finally { in.close(); } } catch (IOException e) { e.printStackTrace(); } return str.toString(); } // 写入指定的文本文件,append为true表示追加,false表示重头开始写, //text是要写入的文本字符串,text为null时直接返回 public static void write(String filePath, boolean append, String text) { if (text == null) return; try { BufferedWriter out = new BufferedWriter(new FileWriter(filePath, append)); try { out.write(text); } finally { out.close(); } } catch (IOException e) { e.printStackTrace(); } } public static String getAbsolutePath(String path) { if(null!=path && ""!=path){ String curPath = System.getProperty("user.dir").toString(); if(path.substring(0, 1)=="/"){ return curPath + path; }else{ return curPath + "/" + path; } } return null; } public static void delete(String filePath) { File file = new File(filePath); if(file.exists()) { file.delete(); } } }StringUtils.java 字符串处理工具,例如将字段名驼峰与蛇形命名风格相互转换!
package com.mw.web.common.utils; import java.io.IOException; import java.security.InvalidParameterException; import java.util.Map; import java.util.UUID; import java.util.regex.Matcher; import java.util.regex.Pattern; public class StringUtils { public static String uuid() { return UUID.randomUUID().toString(); } public static String shortUuid() { return uuid().replaceAll("-", ""); } public static String join(String[] arrays, String terminated) { StringBuilder sb = new StringBuilder(); for(String str : arrays) { sb.append(str).append(terminated); } String result = sb.toString(); result = result.substring(0, result.length() - terminated.length()); return result; } public static String underlineToCamelhump(String inputString) { Pattern linePattern = Pattern.compile("_(\w)"); inputString = inputString.toLowerCase(); Matcher matcher = linePattern.matcher(inputString); StringBuffer sb = new StringBuffer(); while (matcher.find()) { matcher.appendReplacement(sb, matcher.group(1).toUpperCase()); } matcher.appendTail(sb); return sb.toString(); } public static String camelhumpToUnderline(String inputString) { Pattern humpPattern = Pattern.compile("[A-Z]"); Matcher matcher = humpPattern.matcher(inputString); StringBuffer sb = new StringBuffer(); while (matcher.find()) { matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase()); } matcher.appendTail(sb); return sb.toString(); } }3、测试方法如下:
MessageReceiveParam.java 对象实体类
package com.mw.web.param; import lombok.Builder; import lombok.Data; import java.io.Serializable; @Data @Builder public class MessageReceiveParam implements Serializable { private String msgId; private String action; private String fromUser; private String toList; private String roomId; private String msgType; private long msgTime; private String content; private Long seq; private Integer msgFlag; }test.java 测试方法
public void test() { Listlist = new ArrayList<>(); for(int i=0, len=100000;i < len; i++) { list.add(MessageReceiveParam.builder().msgId("test" + i).content("测试" + i).build()); } // 获取实体类的所有字段 String[] fieldNames = EntityUtil.getFieldNames(MessageReceiveParam.class); // wework_receive_messages 为数据库表名 LoadUtil loadUtil = new LoadUtil<>("wework_receive_messages", fieldNames, list); loadUtil.loadData(); } 总结:以上就是整个load data的使用方法和封装工具,注意使用前请在数据库连接里面先配置开启允许load data命令 allowLoadLocalInfile=true



