远程连接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
