Java调用SqlLoader将大文本导入数据库
业务场景:将一千万条数据,大约500M的文本文档的数据导入到数据库
分析:通过Java的IO流解析txt文本文档,拼接动态sql实现insert入库,可以实现,缺点如下
第一:IO流解析大文本文件对机器性能要求较高,测试大约消耗2G左右的内存
第二:拼接sql语句insert一千万条数据大约需要2小时时间,长时间insert会锁表,如果是核心业务表,例如订单表,会造成大量用户无法下单,影响数据库的性能
第三:这种操作可扩展性不强,每次只能针对指定的表,指定的列操作
针对以上缺点,现在通过接口调用数据库系统命令实现,通过可视化界面,选择要导入的表,要导入那些字段,上传指定的txt文本,会自动生成对应的模板文件,实现大批量数据高效率的导入到数据库,通过可配置化即可实现,相对前一种思路扩展性较强,
具体接口如下
1 package com.sun.sqlloader.api; 2 /** 3 * SqlLoader接口 4 * @ClassName: ISqlLoader 5 * @author sunt 6 * @date 2017年11月15日 7 * @version V1.0 8 */ 9 public interface ISqlLoader { 10 11 /** 12 * 自动生成控制文件 13 * @Title: ctlFileWriter 14 * @author sunt 15 * @date 2017年11月15日 16 * @param fileRoute 数据文件地址路径(文件所在磁盘目录) 17 * @param fileName 数据文件名 18 * @param tableName 表名 19 * @param fieldName 要写入表的字段 20 * @param ctlfileName 控制文件名 21 * @return void 22 */ 23 void ctlFileWriter(String fileRoute,String fileName,String tableName,String fieldName,String ctlfileName); 24 25 /** 26 * 执行系统dos命令 27 * @Title: Executive 28 * @author sunt 29 * @date 2017年11月15日 30 * @param user 数据库的用户名 31 * @param pwd 数据库的密码 32 * @param database 连接数据库的地址 33 * @param fileRoute 文件路径 34 * @param ctlfileName 控制文件名 35 * @param logfileName 日志文件名 36 * @return void 37 */ 38 void Executive(String user,String pwd,String database,String fileRoute,String ctlfileName,String logfileName); 39 }
1 package com.sun.sqlloader.api.impl; 2 3 4 import java.io.BufferedReader; 5 import java.io.FileWriter; 6 import java.io.IOException; 7 import java.io.InputStream; 8 import java.io.InputStreamReader; 9 import java.nio.charset.Charset; 10 import java.util.Date; 11 12 import org.apache.log4j.Logger; 13 import org.springframework.stereotype.Service; 14 15 import com.sun.sqlloader.api.ISqlLoader; 16 /** 17 * SqlLoader接口实现 18 * @ClassName: SqlLoaderImpl 19 * @author sunt 20 * @date 2017年11月15日 21 * @version V1.0 22 */ 23 @Service 24 public class SqlLoaderImpl implements ISqlLoader{ 25 26 private Logger logger = Logger.getLogger(SqlLoaderImpl.class); 27 28 @Override 29 public void ctlFileWriter(String fileRoute, String fileName, String tableName, String fieldName,String ctlfileName) { 30 FileWriter fw = null; 31 String strctl = "OPTIONS (skip=0)" + // 0是从第一行开始 1是 从第二行 32 " LOAD DATA CHARACTERSET AL32UTF8 INFILE '"+fileRoute+""+fileName+"'" + //设置字符集编码SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'; 33 " APPEND INTO TABLE "+tableName+"" + 覆盖写入 34 " FIELDS TERMINATED BY '\\|'" + //数据中每行记录用","分隔 ,TERMINATED用于控制字段的分隔符,可以为多个字符。|需要转译 35 " OPTIONALLY ENCLOSED BY \"'\"" + //源文件有引号 '',这里去掉 ''''" 36 " TRAILING NULLCOLS "+fieldName+""; //表的字段没有对应的值时允许为空 源数据没有对应,写入null 37 try { 38 fw = new FileWriter(fileRoute + "" + ctlfileName); 39 fw.write(strctl); 40 } catch (IOException e) { 41 e.printStackTrace(); 42 } finally { 43 try { 44 fw.flush(); 45 fw.close(); 46 } catch (IOException e) { 47 logger.error("生成控制器文件异常..."); 48 e.printStackTrace(); 49 } 50 } 51 } 52 53 @Override 54 public void Executive(String user, String pwd, String database, String fileRoute, String ctlfileName,String logfileName) { 55 InputStream ins = null; 56 //要执行的DOS命令 --数据库 用户名 密码 user/password@database 57 String dos="sqlldr "+user+"/"+pwd+"@"+database+" control="+fileRoute+""+ctlfileName+" log="+fileRoute+""+logfileName; 58 logger.info("执行的dos命令:" + dos); 59 String[] cmd = new String[] { "cmd.exe", "/C", dos }; // 命令cmd /c dir:是执行完dir命令后关闭命令窗口cmd /k dir:是执行完dir命令后不关闭命令窗口。 60 try { 61 Process process = Runtime.getRuntime().exec(cmd); 62 ins = process.getInputStream(); // 获取执行cmd命令后的信息 63 64 BufferedReader reader = new BufferedReader(new InputStreamReader(ins,Charset.forName("GBK")));//解决dos下中文输出乱码 65 String line = null; 66 long startTime = new Date().getTime(); 67 while ((line = reader.readLine()) != null) { 68 logger.info("调用dos执行的结果==========>" + line); // 输出 69 } 70 int exitValue = process.waitFor(); 71 if (exitValue == 0) { 72 logger.info("返回值:" + exitValue + "\n数据导入成功"); 73 logger.info("总共耗时:" + (new Date().getTime() - startTime) / 1000 + "秒"); 74 } else { 75 logger.info("返回值:" + exitValue + "\n数据导入失败"); 76 } 77 78 process.getOutputStream().close(); // 关闭 79 } catch (Exception e) { 80 e.printStackTrace(); 81 } 82 } 83 84 }
生成测试数据的代码
1 package com.sun.sqlloader; 2 3 import java.io.BufferedWriter; 4 import java.io.File; 5 import java.io.FileOutputStream; 6 import java.io.IOException; 7 import java.io.OutputStreamWriter; 8 9 /** 10 * 循环将数据按照指定的格式写入文本文件 11 * @ClassName: OperaFile 12 * @author sunt 13 * @date 2017年11月15日 14 * @version V1.0 15 */ 16 public class OperaFile { 17 18 /** 19 * 写数据到文件 20 * @Title: writeFile 21 * @author sunt 22 * @date 2017年11月15日 23 * @return void 24 */ 25 public static void writeFile(String filePath) throws IOException { 26 File fout = new File(filePath); 27 FileOutputStream fos = new FileOutputStream(fout); 28 BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(fos)); 29 for (Long i = 0L; i < 10000000; i++) { 30 bw.write(i + "|测试数据"+i+"|"); 31 bw.newLine(); 32 } 33 bw.close(); 34 } 35 }
前台展示效果
只需要输入:表名和字段名,上传大文本文件提交即可
一千万条数据测试结果如下:
执行结果:大约5分多钟
数据库结果: