static boolean getConnection(String username, String password, String host, int port) {
try {
JSch jsch = new JSch();
session = jsch.getSession(username, host, port);
UserInfo ui = new MyUserInfo();
session.setUserInfo(ui);
MyUserInfo temp = (MyUserInfo) ui;
temp.setPassword(password);
session.connect();
Channel channel = session.openChannel("sftp");
channel.connect();
SFTPFileLoader.sftpChannel = (ChannelSftp) channel;
return true;
} catch (Exception e) {
System.out.println(e);
errorMessage += e.toString();
}
return false;
}
static void downloadAllFiles(String fileType) {
try {
java.util.Vector fileList = sftpChannel.ls(".");
if (fileList != null) {
for (int ii = 0; ii < fileList.size(); ii++) {
Object obj = fileList.elementAt(ii);
LsEntry lsentry = (com.jcraft.jsch.ChannelSftp.LsEntry) obj;
if (obj instanceof com.jcraft.jsch.ChannelSftp.LsEntry) {
String fileName = ((com.jcraft.jsch.ChannelSftp.LsEntry) obj).getFilename();
if (fileType.equals(".*")) {
if (!(lsentry.getAttrs().isDir())) {
boolean valid = checkFileName(fileName);
if (valid) {
totalFileCount++;
downloadFileByName(fileName);
}
}
} else if (fileName.toLowerCase().endsWith(fileType)) {
boolean valid = checkFileName(fileName);
if (valid) {
totalFileCount++;
downloadFileByName(fileName);
}
}
}
}
}
} catch (Exception e) {
System.out.println("Exception in downloadAllFiles(): " + e.toString());
errorMessage += "Exception in downloadAllFiles(): " + e.toString() + "\n";
}
}
static void setProperties(
String serverDirectory,
String localDirectory,
String username,
String password,
String host,
int port) {
SFTPFileLoader.username = username;
SFTPFileLoader.password = password;
SFTPFileLoader.host = host;
SFTPFileLoader.port = port;
SFTPFileLoader.localDirectory = localDirectory;
SFTPFileLoader.serverDirectory = serverDirectory;
SFTPFileLoader.processedDirectory = "processed";
sftpChannel = null;
session = null;
totalFileCount = 0;
downloadedFileCount = 0;
logMessage = "";
errorMessage = "";
IVRSTrialList = new Vector();
try {
conn = DriverManager.getConnection("jdbc:default:connection:");
} catch (Exception e) {
System.out.println("Database Connection Error in setProperties() -> " + e.toString());
errorMessage += "Database Connection Error in setProperties() -> " + e.toString() + "\n";
}
}
static void disconnectFromSFTP() {
try {
session.disconnect();
} catch (Exception e) {
errorMessage +=
"Exception in loadFilesFromSFTP(exception during disconnecting): " + e.toString() + "\n";
}
}
/**
* Copy a file to specific destination with WinSCP command
*
* @param lfile file you want to transfer
* @param rfile destination file
*/
public synchronized void scpTo(String lfile, String rfile) {
if (!connected) {
throw new ActionFailedException("There is no session!");
}
try {
// exec 'scp -t rfile' remotely
String command = "scp -p -t " + rfile;
channel = session.openChannel("exec");
((ChannelExec) channel).setCommand(command);
// get I/O streams for remote scp
OutputStream out = channel.getOutputStream();
InputStream in = channel.getInputStream();
channel.connect();
// byte[] tmp = new byte[1];
checkAck(in);
// send "C0644 filesize filename", where filename should not include '/'
int filesize = (int) (new File(lfile)).length();
command = "C0644 " + filesize + " ";
if (lfile.lastIndexOf('/') > 0) {
command += lfile.substring(lfile.lastIndexOf('/') + 1);
} else {
command += lfile;
}
command += "\n";
out.write(command.getBytes());
out.flush();
checkAck(in);
// send a content of lfile
FileInputStream fis = new FileInputStream(lfile);
byte[] buf = new byte[1024];
while (true) {
int len = fis.read(buf, 0, buf.length);
if (len <= 0) break;
out.write(buf, 0, len);
out.flush();
}
fis.close();
// send '\0'
buf[0] = 0;
out.write(buf, 0, 1);
out.flush();
checkAck(in);
} catch (Exception e) {
throw new ItemNotFoundException("Failed to copy file: " + e.getMessage());
} finally {
if (channel != null) {
channel.disconnect();
}
}
}
private static void moveFileToProcessedFolder(String fileName) {
try {
String newPath = processedDirectory + "/" + fileName;
sftpChannel.rename(fileName, newPath);
System.out.println("File moved to processed folder");
} catch (Exception e) {
System.out.println("Exception in moveFile(" + fileName + "): " + e.toString());
errorMessage += "Exception in moveFile(" + fileName + "): " + e.toString() + "\n";
}
}
private static boolean goToServerDirectory() {
try {
System.out.println("Accessing ServerDirectory : " + serverDirectory);
sftpChannel.cd(serverDirectory);
System.out.println("Accessing ServerDirectory Completed.");
return true;
} catch (Exception e) {
System.out.println(
"Exception in goToServerDirectory(" + serverDirectory + "): " + e.toString());
errorMessage +=
"Exception in goToServerDirectory(" + serverDirectory + "): " + e.toString() + "\n";
}
return false;
}
static void downloadFileByName(String filename) {
try {
System.out.println("Downloading file : " + filename);
int mode = ChannelSftp.OVERWRITE;
sftpChannel.get(filename, localDirectory, null, mode);
downloadedFileCount++;
System.out.println("Download Completed.");
// file moving to processed folder
moveFileToProcessedFolder(filename);
} catch (SftpException e) {
System.out.println("Exception in downloadFileByName(" + filename + "): " + e.toString());
errorMessage += "Exception in downloadFileByName(" + filename + "): " + e.toString() + "\n";
} catch (Exception e) {
System.out.println("Exception in downloadFileByName(" + filename + "): " + e.toString());
errorMessage += "Exception in downloadFileByName(" + filename + "): " + e.toString() + "\n";
}
}
static boolean checkFileName(String fileName) {
int index = 0;
String trial = null;
try {
index = fileName.indexOf('_', 0);
if (index > 0) {
trial = fileName.substring(0, index);
}
if (IVRSTrialList.contains(trial)) return true;
} catch (Exception e) {
System.out.println("Exception in checkFileName(" + fileName + "): " + e.toString());
errorMessage += "Exception in checkFileName(" + fileName + "): " + e.toString() + "\n";
}
return false;
}
private static boolean isDirecotryExist(String processedDirectory) {
try {
java.util.Vector fileList = sftpChannel.ls(".");
if (fileList != null) {
for (int ii = 0; ii < fileList.size(); ii++) {
Object obj = fileList.elementAt(ii);
LsEntry lsentry = (com.jcraft.jsch.ChannelSftp.LsEntry) obj;
if (obj instanceof com.jcraft.jsch.ChannelSftp.LsEntry) {
String fileName = ((com.jcraft.jsch.ChannelSftp.LsEntry) obj).getFilename();
if (fileName.equals(processedDirectory)) {
return true;
}
}
}
}
} catch (Exception e) {
System.out.println("Exception in isDirecotryExist(): " + e.toString());
errorMessage += "Exception in isDirecotryExist(): " + e.toString() + "\n";
}
return false;
}
private static String[] splitHostNameAndDirectory(String serverPath) {
String[] str = new String[2];
try {
int index = serverPath.indexOf("/", 0);
if (index > 0) {
str[0] = serverPath.substring(0, index);
str[1] = serverPath.substring(index + 1, serverPath.length());
if (str[1].equals("")) str[1] = ".";
} else {
str[0] = serverPath;
str[1] = ".";
}
} catch (Exception e) {
System.out.println(
"Exception in splitHostNameAndDirectory(" + serverPath + "): " + e.toString());
errorMessage +=
"Exception in splitHostNameAndDirectory(" + serverPath + "): " + e.toString() + "\n";
}
return str;
}
private static void getIVRSTrialList() {
Statement stmt = null;
try {
// Class.forName("oracle.jdbc.driver.OracleDriver");
// conn = DriverManager.getConnection("jdbc:oracle:thin:@162.44.191.17:1521:odt01",
// "impact_admin", "august");
stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery(
"select t.trial_alias_code from ip_tr_item ti, trial t where t.trial_no=ti.trial_no and ti.item_code='IVRS_DOWNLOAD' and ti.item_value='Y'");
while (rset.next()) {
IVRSTrialList.add(rset.getString(1));
System.out.println(rset.getString(1));
}
} catch (Exception e) {
System.out.println("Exception in getIVRSTrialList(): " + e.toString());
errorMessage += "Exception in getIVRSTrialList(): " + e.toString() + "\n";
} finally {
try {
stmt.close();
} catch (Exception e) {
System.out.println("Statement close error in getIVRSTrialList() -> " + e.toString());
errorMessage += "Statement close error in getIVRSTrialList() -> " + e.toString() + "\n";
}
// try{
// conn.close();
// } catch (SQLException ex) {
// java.util.logging.Logger.getLogger(SFTPFileLoader.class.getName()).log(Level.SEVERE,
// null, ex);
// }
}
}
/**
* execute the given command in the remote host
*
* @param command
* @return - command output in the remote host
*/
public String exec(String command) {
if (!connected) {
throw new ActionFailedException("There is no session!");
}
StringBuffer data = new StringBuffer();
OutputStream out = null;
InputStream in = null;
try {
Channel channel;
boolean channel_connected = false;
int count = 0;
while (!channel_connected) {
try {
channel = session.openChannel("exec");
((ChannelExec) channel).setCommand(command);
out = channel.getOutputStream();
in = channel.getInputStream();
channel.connect();
channel_connected = true;
} catch (Exception e) {
count++;
String msg = e.getMessage();
if (count < 5) {
AutomationLogger.getInstance()
.warn(
"Failed to connect to SSH server due to "
+ msg
+ ". Will try again in 1 second");
if (msg.startsWith("session is down")) {
AutomationLogger.getInstance().info("Try to re-connect session");
connect();
}
Timer.sleep(1000);
} else {
throw new ActionFailedException("Failed to connect to SSH server due to " + e);
}
}
}
byte[] buf = new byte[1024];
// read
count = 0;
while ((count = in.read(buf)) > 0) {
data.append(new String(buf, 0, count));
}
} catch (Exception e) {
AutomationLogger.getInstance().warn(e);
} finally {
try {
in.close();
} catch (Exception e) {
}
try {
out.close();
} catch (Exception e) {
}
if (channel != null) {
channel.disconnect();
}
}
return data.toString();
}
public static String loadFilesFromSFTP(
String fileName,
String localDirectory,
String username,
String password,
String serverPath,
int port)
throws Exception {
int i = 1;
if (i == 1) throw new Exception();
try {
String[] strArray = splitHostNameAndDirectory(serverPath);
if (!((strArray[0].length() > 0) && (strArray[1].length() > 0))) {
return "F+Invalid server path.+" + "Error Occurred in loadFilesFromSFTP(): " + errorMessage;
}
String strHost = strArray[0];
String strServerDirectory = strArray[1];
setProperties(strServerDirectory, localDirectory, username, password, strHost, port);
boolean validConnection = getConnection(username, password, strHost, port);
if (validConnection) {
boolean isAccessible = goToServerDirectory();
if (isAccessible) {
if (!isDirecotryExist(processedDirectory)) sftpChannel.mkdir(processedDirectory);
getIVRSTrialList();
if (fileName.matches(
"\\x2A\\x2E(([\\p{Alnum}]{3,4})|(\\x2A))")) // pattern for [*.txt] or [*.html] //
// [*->42(Ox2A) and .->46(Ox2E)]
{
int dotPos = fileName.toLowerCase().lastIndexOf('.', fileName.length());
String fileType = fileName.toLowerCase().substring(dotPos, fileName.length());
downloadAllFiles(fileType);
} else {
totalFileCount = 1;
downloadFileByName(fileName);
}
}
disconnectFromSFTP();
errorMessageFurnishing();
if ((totalFileCount == 0) && (downloadedFileCount == 0)) {
logMessage = "T+" + "Server has no files to download.+" + errorMessage;
} else if (totalFileCount != downloadedFileCount) {
logMessage =
"T+"
+ downloadedFileCount
+ " file(s) downloaded out of "
+ totalFileCount
+ " files.+"
+ errorMessage;
} else if (totalFileCount == downloadedFileCount) {
logMessage =
"T+Successfully " + downloadedFileCount + " file(s) downloaded.+" + errorMessage;
}
} else {
logMessage = "F+Connection Error Occurred.+" + errorMessage;
}
return logMessage;
} catch (Exception e) {
disconnectFromSFTP();
System.out.println("Exception in loadFilesFromSFTP(): " + e.toString());
errorMessage += "Exception in loadFilesFromSFTP(): " + e.toString();
return "F+Error Occurred in loadFilesFromSFTP()+" + errorMessage;
}
}
private Connection getMySQLSSHConnection() {
Connection connection = null;
//
int assigned_port;
final int local_port = 3309;
// Remote host and port
final int remote_port = 3306;
final String remote_host = "remote.host.com";
try {
JSch jsch = new JSch();
// Create SSH session. Port 22 is your SSH port which
// is open in your firewall setup.
System.out.println("DEBUG: get session");
Session session = jsch.getSession("user", remote_host, 22);
System.out.println("DEBUG: set password");
session.setPassword("password");
// Additional SSH options. See your ssh_config manual for
// more options. Set options according to your requirements.
java.util.Properties config = new java.util.Properties();
config.put("StrictHostKeyChecking", "no");
config.put("Compression", "yes");
config.put("ConnectionAttempts", "2");
System.out.println("DEBUG: set configuration");
session.setConfig(config);
// Connect
System.out.println("DEBUG: SSH connect");
session.connect();
// Create the tunnel through port forwarding.
// This is basically instructing jsch session to send
// data received from local_port in the local machine to
// remote_port of the remote_host
// assigned_port is the port assigned by jsch for use,
// it may not always be the same as
// local_port.
System.out.println("DEBUG: get assigned port");
assigned_port = session.setPortForwardingL(local_port, remote_host, remote_port);
} catch (JSchException e) {
System.out.println("DEBUG: SSH exception: fail");
e.printStackTrace();
return null;
}
if (assigned_port == 0) {
System.out.println("Port forwarding failed !");
return null;
}
// Database access credintials. Make sure this user has
// "connect" access to this database;
// these may be initialized somewhere else in your code.
final String database_user = "******";
final String database_password = "******";
final String database = "db_name";
// Build the database connection URL.
StringBuilder url = new StringBuilder("jdbc:mysql://localhost:");
// use assigned_port to establish database connection
url.append(assigned_port)
.append("/")
.append(database)
.append("?user="******"&password="******"DEBUG: load mysql driver");
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println("DEBUG: get connection");
connection = DriverManager.getConnection(url.toString());
} catch (Exception e) {
System.out.println("DEBUG get connection failed");
e.printStackTrace();
}
return connection;
}
