存储过程-增量同步ftp文本文件到数据库的过程

一、编写ftp文本文件的下载导入功能

1.编写开启线程，一张表可开一个线程，可以提高效率

//@Scheduled(cron = "0 0 0 * * ?") public void downloadFile(String rundate) throws IOException, SQLException, ClassNotFoundException { logger.info("**********进入定时任务****************"); Thread t1 = new DataThread("ftp_file1_names"); // Thread t2 = new DataThread("ftp_file2_names"); // Thread t3 = new DataThread("ftp_file3_names"); t1.start(); // t2.start(); // t3.start(); //阻塞Main线程，完毕后继续执行后续的逻辑 try { t1.join(); // t2.join(); // t3.join(); } catch (Exception e) { e.printStackTrace(); } tableTotable(); deleteFile(); }

2.编写线程类

package com.yihuisoft.job.service;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.yihuisoft.job.service.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author :huxinkuo
 * @version :V1.0
 * @Description:线程启动定时任务（担心线程锁影响效率，将所有方法均放入run()中）
 * @date :2017年11月27日 下午8:23:03
 */

public class DataThread extends Thread {

    private static final Logger logger = LoggerFactory.getLogger(DataThread.class);


    // 通过CommonUtil.getTftpDownloadPath() 方法获取到项目中的WebContent/download/.
    private static final String localPath = "E:/ftpDownload";


    //private  String fileTempTransPath;


    private PreparedStatement insertAttrPst = null;

    public DataThread(String name) {
        super(name);
    }

    public void run() {
        String job = "pwms.LZDataImport";//任务名称
        //String trg = "DataImportTrg";//触发器
        //记录线程开始时间
        String beginTime = DateUtil.format(new Date());
        //ftp相对路径
        String fileTempTransPath = ParamProvider.getParamValue("import_file_path");
        //数据库jdbc路径
        String jdbcUrl = "jdbc:oracle:thin:@" + ParamProvider.getParamValue("import_jdbcUrl");
        //String jdbcUrl = "jdbc:oracle:thin:@98.11.51.32:1521/uwp";
        //数据库用户名
        String jdbcUser = ParamProvider.getParamValue("import_jdbcUser");
        //String jdbcUser = "fdms_lanzhou";
        //数据库密码
        String jdbcPassword = ParamProvider.getParamValue("import_jdbcPassword");
        //ftp ip路径
        String url = ParamProvider.getParamValue("import_ftp_server");
        //ftp 端口
        int port = Integer.parseInt(ParamProvider.getParamValue("import_ftp_port"));
        //ftp用户名
        String username = ParamProvider.getParamValue("import_ftp_user");
        //ftp密码
        String password = ParamProvider.getParamValue("import_ftp_pwd");
        //文件下载路径
        String workPath = localPath + fileTempTransPath;

        //获取文件数组，通过|拆分表
        String fileNameStr = ParamProvider.getParamValue(this.getName());
        String fileNameIns = ParamProvider.getParamValue("import_uwp_fileNameIns");
        String[] fileNames = StringUtils.split(fileNameStr, "|");
        for (String checkName : fileNames) {
            String table = checkName.substring(0, checkName.length() - 4);

            if (checkName.indexOf(".dat") != -1) {
                int deleteNum = 0;
                try {
                    String sql = "truncate table " + table;
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection con = (Connection) DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
                    // 关闭事务自动提交
                    con.setAutoCommit(false);

                    Statement st = con.createStatement();
                    deleteNum = st.executeUpdate(sql);
                    st.close();
                    con.close();
                    logger.info("删除 " + checkName + " 数据成功！数据量： " + deleteNum);
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            List<String> dirList = new ArrayList<String>();
            //计算前一天的时间格式，作为文件名，
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.DAY_OF_MONTH, -2);
            String startTime = DateUtil.format(new Date());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
            String dir = simpleDateFormat.format(calendar.getTime());

            //TODO 此处写死 测试  上线时注释掉  
            //dirName = "20171101";
            //根据文件名称查询文件夹名称
            if (fileNameIns.indexOf(checkName) != -1) {
                try {
                    String sql = "SELECT DIR_NAME FROM TABLE_DEFAULT WHERE FILE_NAME = '" + checkName + "' ORDER BY DIR_NAME";

                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection con = (Connection) DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
                    // 关闭事务自动提交
                    con.setAutoCommit(false);
                    PreparedStatement pstmt = con.prepareStatement(sql);
                    ResultSet rs = pstmt.executeQuery();
                    while (rs.next()) {
                        dirList.add(rs.getString(1));
                    }
                    pstmt.close();
                    con.close();