Spring Boot 定时通过sftp 从linux服务器下载.csv文件，读取文件，并写入数据库

IDE：IntelliJ IDEA 2018.3.1

项目结构

SftpConfig 配置类

package com.czx.scheduleddownloadcsv.entity;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "sftp")
@Data
public class SftpConfig {
    //ip
    private String host;
    //端口
    private int port;
    //连接sftp用户名
    private String username;
    //连接sftp密码
    private String password;
    //存放文件目录
    private String dir;
    //sftp 中文件名前缀
    private String fileNamePrefix;
    //从sftp 下载文件保存位置
    private String savePath;
}

user 用户实体类

package com.czx.scheduleddownloadcsv.entity;

import lombok.Data;

@Data
public class User {
    private int id;
    private String name;
    private int age;
    private String city;
}

UserMapper

package com.czx.scheduleddownloadcsv.mapper;

import com.czx.scheduleddownloadcsv.entity.User;
import org.apache.ibatis.annotations.Insert;

import java.util.List;

public interface UserMapper {

    @Insert({
            "<script>",
            "insert into t_user(name, age, city) values ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(#{item.name}, #{item.age}, #{item.city})",
            "</foreach>",
            "</script>"
    })
    void addUser(List<User> list);
}

DownLoadTask 定时下载.csv文件，读取.csv文件，并存入数据库

package com.czx.scheduleddownloadcsv.task;

import com.czx.scheduleddownloadcsv.entity.SftpConfig;
import com.czx.scheduleddownloadcsv.entity.User;
import com.czx.scheduleddownloadcsv.mapper.UserMapper;
import com.czx.scheduleddownloadcsv.utils.DataUtils;
import com.czx.scheduleddownloadcsv.utils.SftpUtils;
import com.jcraft.jsch.ChannelSftp;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.List;

@Component
public class DownLoadTask {

    @Resource
    private SftpConfig sftpConfig;

    @Resource
    private UserMapper userMapper;

    @Scheduled(cron ="${sftp.cron}")
    public void downLoadCsv(){
        //文件前缀(要保存到本地的文件)
        String prefix = DataUtils.getCurrentDate("yyyyMMddHHmmss");
        //文件名
        String saveFileName = prefix + ".csv";
        //文件存放路径
        String path = sftpConfig.getSavePath() + saveFileName;

        //从sftp下载文件
        boolean flag = this.downLoadFile(path);
        //下载成功后读取文件，失败不读取
        if(flag){
            //读取.csv文件
            List<String> list = this.readCsv(path);
            List<User> param = new ArrayList<User>();
            if (list != null && list.size() > 0) {
                for (int i = 1; i < list.size(); i++) {
                    User u = new User();
                    String columns = list.get(i);
                    String[] column = columns.split(",");
                    u.setName(column[1]);
                    u.setAge(Integer.parseInt(column[2]));
                    u.setCity(column[3]);
                    param.add(u);
                }
            }
            //批量插入数据
            userMapper.addUser(param);
            System.out.println(prefix+"下载成功");
        }else{
            System.out.println("下载失败");
        }

    }

    /**
     * 下载文件
     * @param path 路径+文件名 本地保存文件路径
     */
    public boolean downLoadFile(String path){
        //执行读取文件的标识，
        boolean flag = true;

        //String downLoadFileName = sftpConfig.getFileNamePrefix() + DataUtils.getPreDate("yyyyMM") + ".csv";
        //sftp 文件名
        String downLoadFileName = "t_user201904.csv";
        try {
            //连接stfp
            ChannelSftp sftp = SftpUtils.login(sftpConfig.getUsername(), sftpConfig.getPassword(), sftpConfig.getHost(), sftpConfig.getPort());
            sftp.cd(sftpConfig.getDir());
            //创建文件
            File file = new File(path);
            //文件存在就删除
            if (file.exists()) {
                file.delete();
            }
            //创建新文件
            file.createNewFile();
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            sftp.get(downLoadFileName, fileOutputStream);
            //关闭流
            fileOutputStream.close();
            //退出sftp
            SftpUtils.logout();
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
        }
        return flag;
    }

    /**
     * 读取.csv文件
     * @param path 文件路径
     * @return
     */
    public List<String> readCsv(String path) {
        File file = new File(path);
        file.setReadable(true);//设置可读
        file.setWritable(true);//设置可写
        BufferedReader br = null;
        String line = "";
        String everyLine = "";
        List<String> list = new ArrayList<String>();
        try {
            br = new BufferedReader(new FileReader(file));
            while ((line=br.readLine())!=null){
                everyLine = line;
                list.add(everyLine);
                System.out.println(everyLine);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


}

DataUtils 日期工具类

package com.czx.scheduleddownloadcsv.utils;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DataUtils {

    /**
     * 获取当前时间
     * @param format 格式 例如：yyyy-MM-dd HH:mm:ss
     * @return
     */
    public static String getCurrentDate(String format){
        SimpleDateFormat s = new SimpleDateFormat(format);
        return s.format(new Date());
    }

    /**
     * 获取上一个月
     * @param format 格式 例如：yyyy-MM
     */
    public static String getPreDate(String format){
        SimpleDateFormat ss = new SimpleDateFormat(format);
        Calendar c = Calendar.getInstance();
        c.setTime(new Date());
        c.add(Calendar.MONTH, -1);
        return ss.format(c.getTime());
    }

    public static void main (String[] args){
        System.out.println(getCurrentDate("yyyyMMddHHmmss"));
        System.out.println(getPreDate("yyyyMM"));
    }
}

SftpUtils sftp连接工具类

package com.czx.scheduleddownloadcsv.utils;
import com.jcraft.jsch.*;

import java.util.Properties;

/**
 * sftp连接工具类
 */
public class SftpUtils {

    private static ChannelSftp sftp = null;
    private static Session session = null;

    // 登录
    public static ChannelSftp login(String username,String password,String host,int port) throws JSchException, SftpException {
        JSch jSch = new JSch();
        // 设置用户名和主机，端口号一般都是22
        session = jSch.getSession(username, host, port);
        // 设置密码
        session.setPassword(password);
        Properties config = new Properties();
        //严格主机**检查
        config.put("StrictHostKeyChecking", "no");
        session.setConfig(config);
        //开启sshSession链接
        session.connect();
        //获取sftp通道
        Channel channel = session.openChannel("sftp");
        channel.connect();
        sftp = (ChannelSftp) channel;
        return sftp;
    }

    // 退出登录
    public static void logout() {
        if (sftp != null) {
            if (sftp.isConnected()) {
                sftp.disconnect();
            }
        }
        if (session != null) {
            if (session.isConnected()) {
                session.disconnect();
            }
        }
    }



}

application.yml

# 端口号
server:
  port: 8888
spring:
  datasource:
    # 连接数据库url
    url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
    # 数据库驱动
    driver-class-name: com.mysql.jdbc.Driver
    # 用户名
    username: root
    # 密码
    password: root
    # 连接池类型 ，springboot2.x 默认支持的连接池,所以pom文件不需要专门加依赖
    type: com.zaxxer.hikari.HikariDataSource
# 打印日志
logging:
  level:
    com:
      czx:
        scheduleddownloadcsv:
          mapper: debug
          task: debug

config.properties

# host
sftp.host=192.168.153.129
# port
sftp.port=22
# 用户名
sftp.username=root
# 密码
sftp.password=root
# 目录
sftp.dir=/usr/local
# 文件名前缀
sftp.fileNamePrefix=t_user
# 从sftp 下载文件保存位置
sftp.savePath=E:/stfpdownload/
# 每20秒执行一次
sftp.cron=0/20 * * *  * ?

项目github地址：https://github.com/BigBearGitHub/scheduled-download-csv.git