/**
*
*/
package com.cv.ftp.read;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.SocketException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;
import org.apache.commons.io.FileUtils;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPClientConfig;
import org.apache.commons.net.ftp.FTPReply;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.cv.ftp.read.model.Customer;
/**
* @author Chandra Vardhan
*
*/
public class FTPService {
static Properties readPropertiesFile = new Properties();
static {
readPropertiesFile = readPropertiesFile();
}
private static final Logger LOGGER = Logger.getLogger(FTPService.class);
private FTPClient ftpClient = null;
private static String sourceDirectory = null;
private static String destinationFtpAddress = null;
private static String destinationFtpUserId = null;
private static String destinationFtpPassword = null;
private static String destinationDirectory = null;
private static String dbUsername = null;
private static String dbPassword = null;
private static String dbUrl = null;
private static String dbDriverClass = null;
/*
* (non-Javadoc)
*
* @see com.cv.ftp.read.FTPService#sendFileViaFTP(java.lang.String)
*/
public void sendFileViaFTP(String fileName) {
setValues(readPropertiesFile);
LOGGER.info("uploading file to FTP...");
ftpClient = getFtpClient();
InputStream file = null;
try {
file = new FileInputStream(fileName);
String formatedFileName = new File(fileName).getName();
boolean isFileStored = ftpClient.storeFile(formatedFileName, file);
LOGGER.info(String.format("store file to ftp %s:%s", formatedFileName, isFileStored));
LOGGER.info("file upload success...");
} catch (IOException e) {
LOGGER.error("An IOException has occured..." + e);
e.printStackTrace();
} finally {
if (ftpClient.isConnected()) {
try {
ftpClient.logout();
ftpClient.disconnect();
file.close();
} catch (IOException ioe) {
LOGGER.error("An IOException has occured..." + ioe);
ioe.printStackTrace();
}
}
}
}
private static void setValues(Properties readPropertiesFile2) {
if (readPropertiesFile2 != null) {
sourceDirectory = readPropertiesFile2.getProperty("source.fileLocation");
destinationFtpAddress = readPropertiesFile2.getProperty("destination.ftp.address");
destinationFtpUserId = readPropertiesFile2.getProperty("destination.ftp.user");
destinationFtpPassword = readPropertiesFile2.getProperty("destination.ftp.pass");
destinationDirectory = readPropertiesFile2.getProperty("backup.fileLocation");
dbUsername = readPropertiesFile2.getProperty("database.connection.username");
dbPassword = readPropertiesFile2.getProperty("database.connection.password");
dbUrl = readPropertiesFile2.getProperty("database.connection.url");
dbDriverClass = readPropertiesFile2.getProperty("database.connection.driver_class");
}
}
/*
* (non-Javadoc)
*
* @see com.cv.ftp.read.FTPService#getFileViaFTP()
*/
public String getFileViaFTP() {
String name = null;
setValues(readPropertiesFile);
ftpClient = getFtpClient();
OutputStream outputFile = null;
try {
boolean isDirChanged = ftpClient.changeWorkingDirectory(sourceDirectory);
String[] fileNames = ftpClient.listNames();
if (fileNames != null) {
for (int i = 0; i < fileNames.length; i++) {
name = fileNames[i];
if (name != null) {
outputFile = new FileOutputStream(destinationDirectory + name);
ftpClient.setFileType(FTP.BINARY_FILE_TYPE);
ftpClient.retrieveFile(name, outputFile);
outputFile.flush();
outputFile.close();
Connection connection = getDatabaseConnection();
List<Customer> customerList = readTerminatedMemberFile(destinationDirectory + name);
boolean dataInsertedFailed = writeContentToDatabase(customerList, connection);
if (dataInsertedFailed) {
System.out.println("There is a problem while inserting into the Customer table!!!");
}
ftpClient.deleteFile(name);
}
}
}
} catch (IOException e) {
LOGGER.error("An IOException has occured..." + e);
e.printStackTrace();
} finally {
if (ftpClient.isConnected()) {
try {
ftpClient.logout();
ftpClient.disconnect();
if (outputFile != null) {
outputFile.close();
}
} catch (IOException ioe) {
LOGGER.error("An IOException has occured..." + ioe);
ioe.printStackTrace();
}
}
}
return null;
}
private Connection getDatabaseConnection() {
Connection connection = null;
try {
Class.forName(dbDriverClass);
connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* @return the ftpClient
*/
public FTPClient getFtpClient() {
ftpClient = new FTPClient();
FTPClientConfig config = new FTPClientConfig();
boolean error = false;
int reply;
try {
ftpClient.connect(destinationFtpAddress);
ftpClient.user(destinationFtpUserId);
ftpClient.pass(destinationFtpPassword);
reply = ftpClient.getReplyCode();
if (!FTPReply.isPositiveCompletion(reply)) {
ftpClient.disconnect();
LOGGER.error("FTP server refused connection.");
}
ftpClient.setFileType(FTP.ASCII_FILE_TYPE);
// Use passive mode as default because most of us are
// behind firewalls these days.
// ftpClient.enterLocalPassiveMode();
} catch (SocketException e) {
LOGGER.error("An SocketException has occured..." + e);
e.printStackTrace();
} catch (IOException e) {
LOGGER.error("An IOException has occured..." + e);
e.printStackTrace();
}
return ftpClient;
}
/**
* @param ftpClient
* the ftpClient to set
*/
public void setFtpClient(FTPClient ftpClient) {
this.ftpClient = ftpClient;
}
public String getDestinationFtpAddress() {
return destinationFtpAddress;
}
public void setDestinationFtpAddress(String destinationFtpAddress) {
FTPService.destinationFtpAddress = destinationFtpAddress;
}
public String getDestinationFtpUserId() {
return destinationFtpUserId;
}
public void setDestinationFtpUserId(String destinationFtpUserId) {
FTPService.destinationFtpUserId = destinationFtpUserId;
}
public String getDestinationFtpPassword() {
return destinationFtpPassword;
}
public void setDestinationFtpPassword(String destinationFtpPassword) {
FTPService.destinationFtpPassword = destinationFtpPassword;
}
public String getSourceDirectory() {
return sourceDirectory;
}
public void setSourceDirectory(String sourceDirectory) {
FTPService.sourceDirectory = sourceDirectory;
}
public String getDestinationDirectory() {
return destinationDirectory;
}
public void setDestinationDirectory(String destinationDirectory) {
FTPService.destinationDirectory = destinationDirectory;
}
public static String getDbUsername() {
return dbUsername;
}
public static void setDbUsername(String dbUsername) {
FTPService.dbUsername = dbUsername;
}
public static String getDbPassword() {
return dbPassword;
}
public static void setDbPassword(String dbPassword) {
FTPService.dbPassword = dbPassword;
}
public static String getDbUrl() {
return dbUrl;
}
public static void setDbUrl(String dbUrl) {
FTPService.dbUrl = dbUrl;
}
public static String getDbDriverClass() {
return dbDriverClass;
}
public static void setDbDriverClass(String dbDriverClass) {
FTPService.dbDriverClass = dbDriverClass;
}
private static Properties readPropertiesFile() {
Properties props = new Properties();
try {
props.load(FTPService.class.getClassLoader().getResourceAsStream("input.properties"));
} catch (Exception e) {
System.err.println("Problem while reading properties file!!!");
}
return props;
}
public static List<Customer> readTerminatedMemberFile(String filePath) throws IOException {
List<Customer> list = new ArrayList<Customer>();
if (filePath == null || filePath.endsWith("properties")) {
return null;
}
FileInputStream inputStream = new FileInputStream(new File(filePath));
Workbook workbook = getWorkbook(inputStream, filePath);
Sheet sheet = null;
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheet = workbook.getSheetAt(i);
System.out.println(sheet.getSheetName());
Iterator<Row> iterator = sheet.iterator();
for (int j = 0; iterator.hasNext(); ++j) {
Row row = iterator.next();
if (j < 6) {
continue;
}
if (row != null) {
// For each row, iterate through each columns
Cell cell = row.getCell(1);
Cell cell2 = row.getCell(2);
if (cell != null && cell2 != null) {
String customerId = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
customerId = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
customerId = cell.getNumericCellValue() + "";
break;
default:
}
Date date = new Date();
Customer customer = new Customer(customerId, date);
list.add(customer);
}
}
}
}
inputStream.close();
return list;
}
private static Workbook getWorkbook(FileInputStream inputStream, String excelFilePath) throws IOException {
Workbook workbook = null;
if (excelFilePath.endsWith("xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else if (excelFilePath.endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
throw new IllegalArgumentException("The specified file is not Excel file");
}
return workbook;
}
private boolean writeContentToDatabase(List<Customer> contentList, Connection connection) {
boolean flag = false;
try {
if (contentList != null) {
if (connection != null) {
for (Customer customer : contentList) {
if (customer != null) {
String selectMax = "SELECT max(ID) FROM CUSTOMER";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(selectMax);
int maxId = 0;
if (rs != null && rs.next()) {
int int1 = rs.getInt(1);
maxId = int1 + 1;
}
SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
String dateStr = format.format(customer.getDate());
String sql = "INSERT INTO CUSTOMER(ID, CUSTOMER_ID,CREATED_DATE) VALUES (" + maxId + ","
+ Integer.parseInt(customer.getCustomerId()) + "," + dateStr + "')";
System.out.println("sql : " + sql);
Statement statement2 = connection.createStatement();
int executeUpdate = statement2.executeUpdate(sql);
if (executeUpdate < 1) {
flag = true;
System.out.println("CUSTOMER_ID Failed to insert : " + customer.getCustomerId());
}
statement2.close();
}
}
connection.close();
}
}
} catch (SQLException e) {
flag = true;
e.printStackTrace();
} finally {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
public static void main(String[] args) {
Date date = new Date();
Timer timer = new Timer();
timer.schedule(new TimerTask() {
public void run() {
FTPService ftpService = new FTPService();
ftpService.getFileViaFTP();
}
}, date, 24 * 60 * 60 * 1000);// 24*60*60*1000 add 24 hours delay
// between job executions.
}
public static void moveFile(String srcDir, String destDir) {
File sourceFile = new File(srcDir);
File destFile = new File(destDir);
try {
FileUtils.copyFile(sourceFile, destFile);
FileUtils.forceDelete(sourceFile);
} catch (IOException e) {
e.printStackTrace();
}
}
} | 
No comments:
Post a Comment