Java调用SqlLoader将大文本导入数据库

Java调用SqlLoader将大文本导入数据库

业务场景:将一千万条数据，大约500M的文本文档的数据导入到数据库

　　分析:通过Java的IO流解析txt文本文档，拼接动态sql实现insert入库，可以实现，缺点如下

第一:IO流解析大文本文件对机器性能要求较高，测试大约消耗2G左右的内存

第二:拼接sql语句insert一千万条数据大约需要2小时时间，长时间insert会锁表，如果是核心业务表，例如订单表，会造成大量用户无法下单，影响数据库的性能

第三:这种操作可扩展性不强，每次只能针对指定的表，指定的列操作

针对以上缺点，现在通过接口调用数据库系统命令实现，通过可视化界面，选择要导入的表，要导入那些字段，上传指定的txt文本，会自动生成对应的模板文件，实现大批量数据高效率的导入到数据库，通过可配置化即可实现，相对前一种思路扩展性较强，

具体接口如下

package com.sun.sqlloader.api;
/**
 * SqlLoader接口
 * @ClassName: ISqlLoader  
 * @author sunt  
 * @date 2017年11月15日 
 * @version V1.0
 */
public interface ISqlLoader {

    /**
     * 自动生成控制文件
     * @Title: ctlFileWriter 
     * @author sunt  
     * @date 2017年11月15日
     * @param fileRoute 数据文件地址路径(文件所在磁盘目录)
     * @param fileName 数据文件名
     * @param tableName 表名
     * @param fieldName 要写入表的字段
     * @param ctlfileName 控制文件名
     * @return void
     */
    void ctlFileWriter(String fileRoute,String fileName,String tableName,String fieldName,String ctlfileName);
    
    /**
     * 执行系统dos命令
     * @Title: Executive 
     * @author sunt  
     * @date 2017年11月15日
     * @param user 数据库的用户名
     * @param pwd 数据库的密码
     * @param database 连接数据库的地址
     * @param fileRoute 文件路径
     * @param ctlfileName 控制文件名
     * @param logfileName 日志文件名
     * @return void
     */
    void Executive(String user,String pwd,String database,String fileRoute,String ctlfileName,String logfileName);
}

package com.sun.sqlloader.api.impl;


import java.io.BufferedReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.util.Date;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import com.sun.sqlloader.api.ISqlLoader;
/**
 * SqlLoader接口实现
 * @ClassName: SqlLoaderImpl  
 * @author sunt  
 * @date 2017年11月15日 
 * @version V1.0
 */
@Service
public class SqlLoaderImpl implements ISqlLoader{

    private Logger logger = Logger.getLogger(SqlLoaderImpl.class);
    
    @Override
    public void ctlFileWriter(String fileRoute, String fileName, String tableName, String fieldName,String ctlfileName) {
        FileWriter fw = null;
        String strctl = "OPTIONS (skip=0)" + // 0是从第一行开始  1是 从第二行
        " LOAD DATA CHARACTERSET AL32UTF8 INFILE '"+fileRoute+""+fileName+"'" + //设置字符集编码SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
        " APPEND INTO TABLE "+tableName+"" + ////覆盖写入
        " FIELDS TERMINATED BY '\\|'" + //数据中每行记录用","分隔 ,TERMINATED用于控制字段的分隔符，可以为多个字符。|需要转译
        " OPTIONALLY  ENCLOSED BY \"'\"" + //源文件有引号 ''，这里去掉    ''''"
        " TRAILING NULLCOLS "+fieldName+"";  //表的字段没有对应的值时允许为空  源数据没有对应，写入null
        try {
            fw = new FileWriter(fileRoute + "" + ctlfileName);
            fw.write(strctl);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                fw.flush();
                fw.close();
            } catch (IOException e) {
                logger.error("生成控制器文件异常...");
                e.printStackTrace();
            }
        }
    }

    @Override
    public void Executive(String user, String pwd, String database, String fileRoute, String ctlfileName,String logfileName) {
        InputStream ins = null;
        //要执行的DOS命令  --数据库  用户名  密码  user/password@database
        String dos="sqlldr "+user+"/"+pwd+"@"+database+" control="+fileRoute+""+ctlfileName+" log="+fileRoute+""+logfileName;
        logger.info("执行的dos命令:" + dos);
        String[] cmd = new String[] { "cmd.exe", "/C", dos }; // 命令cmd /c dir：是执行完dir命令后关闭命令窗口cmd /k dir：是执行完dir命令后不关闭命令窗口。
        try {
            Process process = Runtime.getRuntime().exec(cmd);
            ins = process.getInputStream(); // 获取执行cmd命令后的信息

            BufferedReader reader = new BufferedReader(new InputStreamReader(ins,Charset.forName("GBK")));//解决dos下中文输出乱码
            String line = null;
            long startTime = new Date().getTime();
            while ((line = reader.readLine()) != null) {
                logger.info("调用dos执行的结果==========>" + line); // 输出
            }
            int exitValue = process.waitFor();
            if (exitValue == 0) {
                logger.info("返回值:" + exitValue + "\n数据导入成功");
                logger.info("总共耗时:" + (new Date().getTime() - startTime) / 1000 + "秒");
            } else {
                logger.info("返回值:" + exitValue + "\n数据导入失败");
            }

            process.getOutputStream().close(); // 关闭
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
}

生成测试数据的代码

package com.sun.sqlloader;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;

/**
 * 循环将数据按照指定的格式写入文本文件
 * @ClassName: OperaFile  
 * @author sunt  
 * @date 2017年11月15日 
 * @version V1.0
 */
public class OperaFile {

    /**
     * 写数据到文件
     * @Title: writeFile 
     * @author sunt  
     * @date 2017年11月15日
     * @return void
     */
    public static void writeFile(String filePath) throws IOException {
        File fout = new File(filePath);
        FileOutputStream fos = new FileOutputStream(fout);
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(fos));
        for (Long i = 0L; i < 10000000; i++) {
            bw.write(i + "|测试数据"+i+"|");
            bw.newLine();
        }
        bw.close();
    }
}

前台展示效果

只需要输入:表名和字段名，上传大文本文件提交即可

一千万条数据测试结果如下:

执行结果:大约5分多钟

数据库结果: