引入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();
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 {
ChannelSftp sftp = SftpUtils.login();
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);
StringBuilder builder = new StringBuilder("insert into " + args[1] + " values(");
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(",");
for (int i=0;i<strings.length;i++){
if(i>=strings.length-1){
builder.append("?)");
}else{
builder.append("?,");
}
}
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++){
preparedStatement.setString(i,split[i-1]);
}
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
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();
}
}
}
}