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

packagecom.examstack.common.util;import com.jcraft.jsch.*;import java.io.*;import java.util.*;public classSftpUtil {private ChannelSftp sftp = null;private Session sshSession = null;privateString host;privateString username;privateString password;private intport;privateString keyFilePath;privateString passphrase;public SftpUtil(String host, String username, String password, intport, String keyFilePath, String passphrase) {this.host =host;this.username =username;this.password =password;this.port =port;this.keyFilePath =keyFilePath;this.passphrase =passphrase;

}/*** 连接sftp服务器

* 密码连接

*

*@returnChannelSftp sftp连接实例*/

publicChannelSftp connect() {

JSch jsch= newJSch();try{

jsch.getSession(username, host, port);

sshSession=jsch.getSession(username, host, port);

sshSession.setPassword(password);

Properties properties= newProperties();

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);

}returnsftp;

}/*** 连接sftp服务器

* 密玥连接

*

*@returnChannelSftp sftp连接实例*/

publicChannelSftp connectKey() {

JSch jsch= newJSch();

Session session= null;

ChannelSftp channel= null;try{

jsch= newJSch();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= newProperties();

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);

}returnsftp;

}/*** 批量下载文件

*

*@paramremotePath：远程下载目录(以路径符号结束,可以为相对路径eg:/assess/sftp/jiesuan_2/2014/)

*@paramlocalPath：本地保存目录(以路径符号结束,D:\\Duansha\\sftp\\)

*@return

*/

public ListdownloadFile(String remotePath, String localPath) {

List filenames = new ArrayList();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{

}returnfilenames;

}/*** 列出目录下的文件

*

*@paramdirectory：要列出的目录

*@return*@throwsSftpException*/

public Vector> listFiles(String directory) throwsSftpException {returnsftp.ls(directory);

}/*** 下载单个文件

*

*@paramremotePath：远程下载目录(以路径符号结束)

*@paramremoteFileName：下载文件名

*@paramlocalPath：本地保存目录(以路径符号结束)

*@paramlocalFileName：保存文件名

*@return

*/

public booleandownloadFile(String remotePath, String remoteFileName, String localPath, String localFileName) {

FileOutputStream fieloutput= null;try{

File file= new File(localPath + "/" +localFileName);

File f= newFile(localPath);if (!f.exists()) f.mkdirs();if(file.exists()) {return true;

}

fieloutput= newFileOutputStream(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;

}/*** 上传单个文件，如果指正下载文件名则使用，否则保留原有文件名

*

*@paramremoteFilePath 远程文件路径 /tmp/xxx.txt ||xxx.txt.zip

*@paramuploadFilePath 要上传的文件 如：D:\\test\\xxx.txt*/

public voiduploadFile(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= newFile(uploadFilePath);try{

in= newFileInputStream(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 voiddisconnect() {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 voidinfo(String msg) {

System.out.println("info: " +msg);

}private voiderror(String msg) {

error(msg,null);

}private voiderror(String msg, Throwable e) {

System.out.println("error: " +msg);if (e != null) {

e.printStackTrace();

}

}

}