远程连接sftp,下载csv文件，读取插入数据库

sftp工具类：

package com.examstack.portal.util;

import com.jcraft.jsch.*;

import java.io.*;
import java.util.*;

public class SftpUtil {
    private ChannelSftp sftp = null;
    private Session sshSession = null;
    private String host;
    private String username;
    private String password;
    private int port;
    private String keyFilePath;
    private String passphrase;

    public SftpUtil(String host, String username, String password, int port, String keyFilePath, String passphrase) {
        this.host = host;
        this.username = username;
        this.password = password;
        this.port = port;
        this.keyFilePath = keyFilePath;
        this.passphrase = passphrase;
    }

    /**
     * 连接sftp服务器
     * 密码连接
     *
     * @return ChannelSftp sftp连接实例
     */
    public ChannelSftp connect() {
        JSch jsch = new JSch();
        try {
            jsch.getSession(username, host, port);
            sshSession = jsch.getSession(username, host, port);
            sshSession.setPassword(password);
            Properties properties = new Properties();
            properties.put("StrictHostKeyChecking", "no");
            sshSession.setConfig(properties);
            sshSession.connect();
            Channel channel = sshSession.openChannel("sftp");
            channel.connect();
            sftp = (ChannelSftp) channel;
            info(" ftp Connected to " + host + ":" + port);
        } catch (JSchException e) {
            throw new RuntimeException("sftp连接失败", e);
        }
        return sftp;
    }

    /**
     * 连接sftp服务器
     * 密玥连接
     *
     * @return ChannelSftp sftp连接实例
     */
    public ChannelSftp connectKey() {
        JSch jsch = new JSch();
        Session session = null;
        ChannelSftp channel = null;
        try {
            jsch = new JSch();
            if (keyFilePath != null) {
                if (passphrase != null) {
                    jsch.addIdentity(keyFilePath, passphrase);// 设置私钥
                } else {
                    jsch.addIdentity(keyFilePath);// 设置私钥
                }
                System.out.println("连接sftp，私钥文件路径：" + keyFilePath);
            }
            System.out.println("SFTP Host: " + host + "; UserName:" + username);
            session = jsch.getSession(username, host, port);
            System.out.println("Session 已建立.");
            if (password != null) {
                session.setPassword(password);
            }
            Properties sshConfig = new Properties();
            sshConfig.put("StrictHostKeyChecking", "no");
            session.setConfig(sshConfig);
            session.setConfig("kex", "diffie-hellman-group1-sha1");
            session.connect();
            System.out.println("Session 已连接.");
            channel = (ChannelSftp) session.openChannel("sftp");
            channel.connect();
            sftp = (ChannelSftp) channel;
            System.out.println("连接到SFTP成功.Host: " + host);
        } catch (Exception e) {
            System.out.println("连接SFTP失败：" + e);
        }
        return sftp;
    }

    /**
     * 下载单个文件，如果指定文件名，则下载到文件名否则保持原有文件名
     *
     * @param remoteFilePath 远程文件路径 /tmp/xxx.txt || xxx.txt.zip
     * @param localFilePath  本地文件路径 如 D:\\xxx.txt
     * @return 下载的文件
     */
    public File downloadFile(String remoteFilePath, String localFilePath) {
        connectKey();
        String remoteFileName = "";
        // 远端目录确定以 / 作为目录格式
        String rFileSeparator = "/";
        int rDirNameSepIndex = remoteFilePath.lastIndexOf(rFileSeparator) + 1;
        String rDir = remoteFilePath.substring(0, rDirNameSepIndex);
        remoteFileName = remoteFilePath.substring(rDirNameSepIndex);
        /*if(localFilePath.endsWith(File.separator)) {
            localFilePath = localFilePath + (localFilePath.endsWith(File.separator) ? remoteFileName : "/" + remoteFileName);
        }*/
        File file = null;
        File fileLocalPath = null;
        OutputStream output = null;
        try {
            fileLocalPath = new File(localFilePath);
            if (!fileLocalPath.exists()) {
                fileLocalPath.mkdirs();
            }
            file = new File(localFilePath + "/" + remoteFileName);
            if (file.exists()) {
                file.delete();
            }
            file.createNewFile();
            sftp.cd(rDir);
            output = new FileOutputStream(file);
            sftp.get(remoteFileName, output);
            info("===DownloadFile:" + remoteFileName + " success from sftp.");
        } catch (SftpException e) {
            error("ftp下载文件失败", e);
        } catch (FileNotFoundException e) {
            error("本地目录异常，请检查" + file.getPath(), e);
        } catch (IOException e) {
            error("创建本地文件失败" + file.getPath(), e);
        } finally {
            if (output != null) {
                try {
                    output.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            disconnect();
        }

        info(">>>>>>>>>downloadFile--ftp下载文件结束>>>>>>>>>>>>>");
        return file;
    }

    /**
     * 上传单个文件，如果指正下载文件名则使用，否则保留原有文件名
     *
     * @param remoteFilePath 远程文件路径 /tmp/xxx.txt ||xxx.txt.zip
     * @param uploadFilePath 要上传的文件 如：D:\\test\\xxx.txt
     */
    public void uploadFile(String remoteFilePath, String uploadFilePath) {
        info(" begin uploadFile from:" + uploadFilePath +
                ", to: " + remoteFilePath);
        FileInputStream in = null;
        connect();
        String remoteFileName = "";
        String remoteDir = remoteFilePath;
        String localFileName = "";
        // 远端目录确定以 / 作为目录格式
        String rFileSeparator = "/";
        if (remoteFilePath.endsWith(rFileSeparator)) {
            localFileName = uploadFilePath.substring(uploadFilePath.lastIndexOf(File.separator) + 1);
            remoteFileName = localFileName;
        } else {
            int fileNameDirSep = remoteFilePath.lastIndexOf(rFileSeparator) + 1;
            remoteDir = remoteFilePath.substring(0, fileNameDirSep);
            remoteFileName = remoteFilePath.substring(fileNameDirSep);
        }
        try {
            sftp.cd(remoteDir);
        } catch (SftpException e) {
            try {
                sftp.mkdir(remoteDir);
                sftp.cd(remoteDir);
            } catch (SftpException e1) {
                error("ftp创建文件路径失败，路径为" + remoteDir);
                throw new RuntimeException("ftp创建文件路径失败" + remoteDir);
            }
        }
        File file = new File(uploadFilePath);
        try {
            in = new FileInputStream(file);
            sftp.put(in, remoteFileName);
        } catch (FileNotFoundException e) {
            error("文件不存在-->" + uploadFilePath);
        } catch (SftpException e) {
            error("sftp异常-->", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    info("Close stream error." + e.getMessage());
                }
            }
            disconnect();
        }
        info(">>>>>>>>>uploadFile--ftp上传文件结束>>>>>>>>>>>>>");
    }

    /**
     * 关闭连接
     */
    public void disconnect() {
        if (this.sftp != null) {
            if (this.sftp.isConnected()) {
                this.sftp.disconnect();
                this.sftp = null;
                info("sftp 连接已关闭！");
            }
        }
        if (this.sshSession != null) {
            if (this.sshSession.isConnected()) {
                this.sshSession.disconnect();
                this.sshSession = null;
                info("sshSession 连接已关闭！");
            }
        }
    }

    private void info(String msg) {
        System.out.println("info: " + msg);
    }

    private void error(String msg) {
        error(msg, null);
    }

    private void error(String msg, Throwable e) {
        System.out.println("error: " + msg);
        if (e != null) {
            e.printStackTrace();
        }
    }

}

 

package com.examstack.common.util;

import com.jcraft.jsch.*;

import java.io.*;
import java.util.*;

public class SftpUtil {
    private ChannelSftp sftp = null;
    private Session sshSession = null;
    private String host;
    private String username;
    private String password;
    private int port;
    private String keyFilePath;
    private String passphrase;

    public SftpUtil(String host, String username, String password, int port, String keyFilePath, String passphrase) {
        this.host = host;
        this.username = username;
        this.password = password;
        this.port = port;
        this.keyFilePath = keyFilePath;
        this.passphrase = passphrase;
    }

    /**
     * 连接sftp服务器
     * 密码连接
     *
     * @return ChannelSftp sftp连接实例
     */
    public ChannelSftp connect() {
        JSch jsch = new JSch();
        try {
            jsch.getSession(username, host, port);
            sshSession = jsch.getSession(username, host, port);
            sshSession.setPassword(password);
            Properties properties = new Properties();
            properties.put("StrictHostKeyChecking", "no");
            sshSession.setConfig(properties);
            sshSession.connect();
            Channel channel = sshSession.openChannel("sftp");
            channel.connect();
            sftp = (ChannelSftp) channel;
            info(" ftp Connected to " + host + ":" + port);
        } catch (JSchException e) {
            throw new RuntimeException("sftp连接失败", e);
        }
        return sftp;
    }

    /**
     * 连接sftp服务器
     * 密玥连接
     *
     * @return ChannelSftp sftp连接实例
     */
    public ChannelSftp connectKey() {
        JSch jsch = new JSch();
        Session session = null;
        ChannelSftp channel = null;
        try {
            jsch = new JSch();
            if (keyFilePath != null) {
                if (passphrase != null) {
                    jsch.addIdentity(keyFilePath, passphrase);// 设置私钥
                } else {
                    jsch.addIdentity(keyFilePath);// 设置私钥
                }
                System.out.println("连接sftp，私钥文件路径：" + keyFilePath);
            }
            System.out.println("SFTP Host: " + host + "; UserName:" + username);
            session = jsch.getSession(username, host, port);
            System.out.println("Session 已建立.");
            if (password != null) {
                session.setPassword(password);
            }
            Properties sshConfig = new Properties();
            sshConfig.put("StrictHostKeyChecking", "no");
            session.setConfig(sshConfig);
            session.setConfig("kex", "diffie-hellman-group1-sha1");
            session.connect();
            System.out.println("Session 已连接.");
            channel = (ChannelSftp) session.openChannel("sftp");
            channel.connect();
            sftp = (ChannelSftp) channel;
            System.out.println("连接到SFTP成功.Host: " + host);
        } catch (Exception e) {
            System.out.println("连接SFTP失败：" + e);
        }
        return sftp;
    }


    /**
     * 批量下载文件
     *
     * @param remotePath：远程下载目录(以路径符号结束,可以为相对路径eg:/assess/sftp/jiesuan_2/2014/)
     * @param localPath：本地保存目录(以路径符号结束,D:\\Duansha\\sftp\\)
     * @return
     */
    public List<String> downloadFile(String remotePath, String localPath) {
        List<String> filenames = new ArrayList<String>();
        try {
            connectKey();
            Vector v = listFiles(remotePath);
            if (v.size() > 0) {
                System.out.println("本次处理文件个数不为零,开始下载...fileSize=" + (v.size() - 2));
                Iterator it = v.iterator();
                while (it.hasNext()) {
                    ChannelSftp.LsEntry entry = (ChannelSftp.LsEntry) it.next();
                    String filename = entry.getFilename();
                    SftpATTRS attrs = entry.getAttrs();
                    if (".".equals(filename) || "..".equals(filename)) {
                        continue;
                    }
                    if (!attrs.isDir()) {
                        boolean flag = false;
                        String localFileName = localPath + filename;
                        flag = downloadFile(remotePath, filename, localPath, filename);
                        if (flag) {
                            filenames.add(localFileName);
                        }
                    }
                }
            }
        } catch (SftpException e) {
            e.printStackTrace();
        } finally {
        }
        return filenames;
    }

    /**
     * 列出目录下的文件
     *
     * @param directory：要列出的目录
     * @return
     * @throws SftpException
     */
    public Vector<?> listFiles(String directory) throws SftpException {
        return sftp.ls(directory);
    }

    /**
     * 下载单个文件
     *
     * @param remotePath：远程下载目录(以路径符号结束)
     * @param remoteFileName：下载文件名
     * @param localPath：本地保存目录(以路径符号结束)
     * @param localFileName：保存文件名
     * @return
     */
    public boolean downloadFile(String remotePath, String remoteFileName, String localPath, String localFileName) {
        FileOutputStream fieloutput = null;
        try {
            File file = new File(localPath + "/" + localFileName);
            File f = new File(localPath);
            if (!f.exists()) f.mkdirs();
            if (file.exists()) {
                return true;
            }
            fieloutput = new FileOutputStream(file);
            sftp.get(remotePath + "/" + remoteFileName, fieloutput);
            return true;
        } catch (FileNotFoundException e) {
            error(e.getMessage());
            e.printStackTrace();
        } catch (SftpException e) {
            error(e.getMessage());
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != fieloutput) {
                try {
                    fieloutput.close();
                } catch (IOException e) {
                    error(e.getMessage());
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    /**
     * 上传单个文件，如果指正下载文件名则使用，否则保留原有文件名
     *
     * @param remoteFilePath 远程文件路径 /tmp/xxx.txt ||xxx.txt.zip
     * @param uploadFilePath 要上传的文件 如：D:\\test\\xxx.txt
     */
    public void uploadFile(String remoteFilePath, String uploadFilePath) {
        info(" begin uploadFile from:" + uploadFilePath +
                ", to: " + remoteFilePath);
        FileInputStream in = null;
        connect();
        String remoteFileName = "";
        String remoteDir = remoteFilePath;
        String localFileName = "";
        // 远端目录确定以 / 作为目录格式
        String rFileSeparator = "/";
        if (remoteFilePath.endsWith(rFileSeparator)) {
            localFileName = uploadFilePath.substring(uploadFilePath.lastIndexOf(File.separator) + 1);
            remoteFileName = localFileName;
        } else {
            int fileNameDirSep = remoteFilePath.lastIndexOf(rFileSeparator) + 1;
            remoteDir = remoteFilePath.substring(0, fileNameDirSep);
            remoteFileName = remoteFilePath.substring(fileNameDirSep);
        }
        try {
            sftp.cd(remoteDir);
        } catch (SftpException e) {
            try {
                sftp.mkdir(remoteDir);
                sftp.cd(remoteDir);
            } catch (SftpException e1) {
                error("ftp创建文件路径失败，路径为" + remoteDir);
                throw new RuntimeException("ftp创建文件路径失败" + remoteDir);
            }
        }
        File file = new File(uploadFilePath);
        try {
            in = new FileInputStream(file);
            sftp.put(in, remoteFileName);
        } catch (FileNotFoundException e) {
            error("文件不存在-->" + uploadFilePath);
        } catch (SftpException e) {
            error("sftp异常-->", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    info("Close stream error." + e.getMessage());
                }
            }
            disconnect();
        }
        info(">>>>>>>>>uploadFile--ftp上传文件结束>>>>>>>>>>>>>");
    }

    /**
     * 关闭连接
     */
    public void disconnect() {
        if (this.sftp != null) {
            if (this.sftp.isConnected()) {
                this.sftp.disconnect();
                this.sftp = null;
                info("sftp 连接已关闭！");
            }
        }
        if (this.sshSession != null) {
            if (this.sshSession.isConnected()) {
                this.sshSession.disconnect();
                this.sshSession = null;
                info("sshSession 连接已关闭！");
            }
        }
    }

    private void info(String msg) {
        System.out.println("info: " + msg);
    }

    private void error(String msg) {
        error(msg, null);
    }

    private void error(String msg, Throwable e) {
        System.out.println("error: " + msg);
        if (e != null) {
            e.printStackTrace();
        }
    }

}

 

controller：

SftpUtil uploadTest = new SftpUtil("ip", "name", "password", port, "密钥地址", null);
 uploadTest.downloadFile("sftp文件路径", "下载保存路径");

读取csv文件：

public List<OrderPayCSV> readOrderCSV(String path) {
        List<OrderPayCSV> csvList = new ArrayList<OrderPayCSV>();
        try {
            //这里要统一编码
            InputStreamReader read = new InputStreamReader(new FileInputStream(path), "UTF-8");
            BufferedReader reader = new BufferedReader(read);
            String line;
            int num = 0;
            while ((line = reader.readLine()) != null) {
                num++;
                if (num == 1) {
                    continue;
                }
                String info[] = line.split(",");
                OrderPayCSV orderPayCSV = new OrderPayCSV();
                orderPayCSV.setOrderNo(info[0]);
                orderPayCSV.setSerialNumber(info[1]);
                csvList.add(orderPayCSV);
            }
        } catch (FileNotFoundException ex) {
            System.out.println("没找到文件！");
        } catch (IOException ex) {
            System.out.println("读写文件出错！");
        }
        return csvList;
    }

controller

public List<OrderPayCSV> readOrderCSV(String path) {
        List<OrderPayCSV> csvList = new ArrayList<OrderPayCSV>();
        try {
            //这里要统一编码
            InputStreamReader read = new InputStreamReader(new FileInputStream(path), "UTF-8");
            BufferedReader reader = new BufferedReader(read);
            String line;
            int num = 0;
            while ((line = reader.readLine()) != null) {
                num++;
                if (num == 1) {
                    continue;
                }
                String info[] = line.split(",");
                OrderPayCSV orderPayCSV = new OrderPayCSV();
                orderPayCSV.setOrderNo(info[0]);
                orderPayCSV.setSerialNumber(info[1]);
                csvList.add(orderPayCSV);
            }
        } catch (FileNotFoundException ex) {
            System.out.println("没找到文件！");
        } catch (IOException ex) {
            System.out.println("读写文件出错！");
        }
        return csvList;
    }

 

<insert id="insertList" parameterType="java.util.List">
        INSERT INTO examstack.et_order_pay_csv
        (order_no,serial_number,order_amount,order_currency,create_time)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.orderNo},#{item.serialNumber},#{item.orderAmount},#{item.orderCurrency},now())
        </foreach>
    </insert>

对账：获取在对账表tableA中，tableB不存在的数据，批量插入b表

 SELECT *
from tableA a
WHERE NOT EXISTS (SELECT * FROM tableB b WHERE b.id = a.id)

对比更新：

    UPDATE tableA a,tableB b
        SET b.order_status = a.order_status,
            b.order_amount = a.order_amount,
            b.real_bank_serial_number = a.refund_number,
        WHERE b.order_no = a.order_no