读取sftp服务器上的文件内容到指定的数据库表内

引入sftp jar依赖

<dependency>
    <groupId>com.jcraft</groupId>
    <artifactId>jsch</artifactId>
    <version>0.1.27</version>
</dependency>

登录sftp的工具类

package com.chenfu;

import com.jcraft.jsch.*;

import java.util.Properties;

public class SftpUtils {

    private static ChannelSftp sftp = null;
    private static Session session = null;
	// 登录
    public static ChannelSftp login() 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);
        session.connect();
        Channel channel = session.openChannel("sftp");
        channel.connect();

        sftp = (ChannelSftp) channel;
        System.out.println("connect success....");
        return sftp;
    }
// 退出登录
    public static void logout() {
        if (sftp != null) {
            if (sftp.isConnected()) {
                sftp.disconnect();
            }
        }
        if (session != null) {
            if (session.isConnected()) {
                session.disconnect();
            }
        }
    }

}

使用JDBC方式插入数据到Oracle数据库内

package com.chenfu;

import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.SftpException;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

public class ReadData {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        BufferedReader reader = null;

        try {
	        // 登录sftp服务器
            ChannelSftp sftp = SftpUtils.login();
            // 构建文件输入流，读取文件内容，因为最终是要打成jar包，所以文件名从args上取
            reader = new BufferedReader(new InputStreamReader(sftp.get("/path/dir/"+args[0]), "utf-8"));
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url="jdbc:oracle:thin:@host:port:orcl";
            String username = "scott";
            String password = "root";

			// 获得数据库的连接
            connection = DriverManager.getConnection(url, username, password);
            // 表名也是从args上取
            StringBuilder builder = new StringBuilder("insert into " + args[1] + " values(");
            // 将所有数据存到一个list集合内
            ArrayList<String> list = new ArrayList<>();
            while(true){
                String line = reader.readLine();
                if(line == null){
                    break;
                }
                list.add(line);
            }

			// 通过其中的一行数据来看有多少列
            String line = list.get(0);
            // 规定的一行数据之间的分隔符是英文的逗号
            String[] strings = line.split(",");
            // 插入sql的占位符
            for (int i=0;i<strings.length;i++){
                if(i>=strings.length-1){
                    builder.append("?)");
                }else{
                    builder.append("?,");
                }
            }
            // 获取完整sql
            String sql = builder.toString();
            System.out.println("sql:"+sql);
            // 拿到预编译的对象
            preparedStatement = connection.prepareStatement(sql);

            for (String s:list) {
                String[] split = s.split(",");
                for (int i=1;i<=split.length;i++){
	                // 数据表内的所有字段都是varChar2类型的，所以都通过此种方式填充数据
                    preparedStatement.setString(i,split[i-1]);
                }
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            // 退出sftp服务器
            SftpUtils.logout();
            System.out.println("insert success.....");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (JSchException e) {
            e.printStackTrace();
        } catch (SftpException e) {
            e.printStackTrace();
        } finally {
            try {
                if (reader!=null){
                    reader.close();
                }
                if(preparedStatement!=null){
                    preparedStatement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}