Code:
package org.dev.excel.util;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ResourceBundle;
public class Int2TextUtility
{
private static final String COMMA_DELIMITER = ",";
private static final String NEW_LINE_SEPARATOR = "\n";
private static final String FILE_HEADER = "id,firstName,lastName,gender,age";
public Int2TextUtility()
{
}
public static void main(String args[])
{
if(args.length < 2)
{
System.err.println("Usage: <java class name> <report path> <file name>");
System.exit(1);
}
SimpleDateFormat dateFormat = new SimpleDateFormat("MMddyyyy");
String date = dateFormat.format(new Date());
String reportPath = args[0];
String fileName = (new StringBuilder(String.valueOf(args[1]))).append(date).append(".csv").toString();
System.out.println((new StringBuilder("report file name ")).append(fileName).toString());
String query = prepareQuery();
System.out.println("Preparing connection..");
Connection conn = getConnection();
System.out.println((new StringBuilder("Got connection..")).append(conn).toString());
try
{
writeCsvFile(conn, query, reportPath, fileName);
System.out.println("Report is generated..");
}
catch(Exception e)
{
e.printStackTrace();
System.err.println("There is some issue in report generation..");
}
System.out.println("Database connection is being closed..");
closeConnection(conn);
System.out.println("Database connection is closed..");
}
private static String prepareQuery()
{
StringBuilder builder = new StringBuilder();
builder.append("select NVL( TO_CHAR(s.name), ' ' ) name ,NVL( TO_CHAR(s.ql), ' ' ) ql, NVL( TO_C" +
"HAR(s.job_code), ' ' ) job_code,"
);
builder.append(" NVL( TO_CHAR(s.bu), ' ' ) bu ,NVL( TO_CHAR(s.resource_flag), ' ' ) resource_fla" +
"g,NVL( TO_CHAR(s.manager), ' ' ) manager ,"
);
builder.append(" NVL( TO_CHAR(s.manager_ql), ' ' ) manager_ql,NVL( TO_CHAR(s.date_job_code_enter" +
"ed), ' ' ) date_job_code_entered, "
);
builder.append(" NVL( TO_CHAR(s.country), ' ' ) country,NVL( TO_CHAR(pp.region), ' ' ) region,");
builder.append(" NVL( TO_CHAR(j.operational), ' ' ) operational,NVL( TO_CHAR(j.operational_speci" +
"al), ' ' ) operational_special,NVL( TO_CHAR(j.inv_supp), ' ' ) inv_supp,"
);
builder.append(" NVL( TO_CHAR(j.inv_not_supp), ' ' ) inv_not_supp,NVL( TO_CHAR(j.contigent_liabi" +
"lities), ' ' ) contigent_liabilities,NVL( TO_CHAR(j.financing_comm), ' ') financ" +
"ing_comm, "
);
builder.append(" NVL( TO_CHAR(s.Bus_Subunit_Code), ' ' ) Bus_Subunit_Code,NVL( TO_CHAR(s.Bus_Uni" +
"t_Name), ' ' ) Bus_Unit_Name"
);
builder.append(" from NCR_JOB_CODE_LEVELS j,NCR_SPP_SAR_ALL s,ncr_po_prefix pp");
builder.append(" where j.job_code = s.job_code and s.country = pp.code(+) ");
builder.append(" order by s.name");
System.out.println((new StringBuilder("Prepared Query :")).append(builder.toString()).toString());
return builder.toString();
}
private static void closeConnection(Connection conn)
{
try
{
conn.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
private static Connection getConnection()
{
Connection conn = null;
try
{
ResourceBundle bundle = ResourceBundle.getBundle("database");
Class.forName(bundle.getString("DRIVER_NAME"));
conn = DriverManager.getConnection(bundle.getString("DB_URL"), bundle.getString("USER_NAME"), bundle.getString("PASS"));
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
return conn;
}
public static void writeCsvFile(Connection conn, String query, String reportPath, String fileName)
throws SQLException
{
ResultSet rs;
FileWriter fileWriter;
PreparedStatement ps = conn.prepareStatement(query);
rs = ps.executeQuery();
String file = (new StringBuilder(String.valueOf(reportPath))).append("/").append(fileName).toString();
System.out.println((new StringBuilder("Report is going to be written in :")).append(file).toString());
fileWriter = null;
try
{
fileWriter = new FileWriter(fileName);
fileWriter.append("id,firstName,lastName,gender,age".toString());
fileWriter.append("\n");
for(; rs.next(); fileWriter.append("\n"))
{
fileWriter.append(String.valueOf(rs.getString("NAME")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("QL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("JOB_CODE")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("BU")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("RESOURCE FLAG")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("MANAGER")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("MANAGER_QL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("DATE_JOB_CREATED")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("COUNTRY")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("REGION")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("OPERATIONAL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("OPERATIONAL SPECIAL")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("INVENTORY(SUPPORTED")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("INVENTORY(Not SUPPORTED)")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("CONTINGENT LIABILITIES")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("FINANCING COMMITMENTS")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("COST CENTER")));
fileWriter.append(",");
fileWriter.append(String.valueOf(rs.getString("COST CENTER")));
fileWriter.append(",");
}
System.out.println("CSV file was created successfully !!!");
break MISSING_BLOCK_LABEL_684;
}
catch(Exception e)
{
System.out.println("Error in CsvFileWriter !!!");
e.printStackTrace();
}
try
{
fileWriter.flush();
fileWriter.close();
}
catch(IOException e)
{
System.out.println("Error while flushing/closing fileWriter !!!");
e.printStackTrace();
}
break MISSING_BLOCK_LABEL_713;
Exception exception;
exception;
try
{
fileWriter.flush();
fileWriter.close();
}
catch(IOException e)
{
System.out.println("Error while flushing/closing fileWriter !!!");
e.printStackTrace();
}
throw exception;
try
{
fileWriter.flush();
fileWriter.close();
}
catch(IOException e)
{
System.out.println("Error while flushing/closing fileWriter !!!");
e.printStackTrace();
}
}
}
07-05-2015
