分析:通过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分多钟
数据库结果: