I have a requirement where I have to generate some reports read from a database and write the data to Excel.
I want your help in reviewing the following things in my code:
- Database connection
- Multithreaded programming
- Is my data structure
Map<Integer, TreeMap<String,String>>
proper? - What design pattern can I apply?
DB connection
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.Vector;
import javax.sql.DataSource;
public class RConnection {
private Connection mConnection;
private Statement mStatement;
private static Object lockObject = new Object();
private static int openCount = 0;
private static int closeCount = 0;
private static String dataSourceName = "jdbc/dtcuae";
private static DataSource dataSource = null;
private Vector statementVector= new Vector();
private Vector resultSetVector= new Vector();
public CallableStatement createCallableStatement(String sql) throws SQLException
{
CallableStatement cstmt = null;
try
{
cstmt = this.getDBConnection().prepareCall(sql);
statementVector.add(cstmt);
}
catch(SQLException e)
{
e.printStackTrace();
throw new SQLException("Can not create CallableStatement for sql "+e.getMessage());
}
return cstmt;
}
public ResultSet getResultSet(CallableStatement cstmt, int paramNo) throws SQLException
{
ResultSet rs=null;
try
{
rs=(ResultSet) cstmt.getObject(paramNo);
resultSetVector.add(rs);
}
catch(SQLException e)
{
e.printStackTrace();
throw new SQLException("Can not retrieve ResultSet for this CallableStatement "+e.getMessage());
}
return rs;
}
public static int getOpenConnections()
{
return openCount;
}
public static int getCloseConnections()
{
return closeCount;
}
/**
/**
* Constructor being made Private, Singleton implementation
*/
public RConnection()
{
this.connect();
}
/**
* This is the function that is used to connect to the
* database using jdbc
*/
public void connect()
{
try
{
String errorString = "Error obtaining database connection.";
final ResourceBundle BUNDLE = ResourceBundle.getBundle(AppConstants.CONFIG_PATH);
try {
/*
System.out.println("-------------------Oracle Connection Properties Start-----------------------------------");
System.out.println("oracle.jdbc.url"));
System.out.println("oracle.jdbc.usr"));
System.out.println("-------------------Oracle Connection Properties End-----------------------------------");
*/
String DB_USERNAME = BUNDLE.getString(AppConstants.DB_USERNAME);
//String DB_PASSWORD = BUNDLE.getString(AppConstants.TR_DB_PASSWORD);
String DB_PASSWORD = AES.decryptString(BUNDLE.getString(AppConstants.DB_PASSWORD),
AppConstants.ENCRYPTION_KEY);
String DB_URL = BUNDLE.getString(AppConstants.DB_URL);
Class.forName(AppConstants.DRIVER_CLASS_NAME);
mConnection = DriverManager.getConnection(DB_URL, DB_USERNAME,
DB_PASSWORD);
mConnection.setAutoCommit(false);
}catch (NullPointerException e) {
throw new Exception(errorString+":"+e.getMessage());
}
catch(SQLException sqle) {
throw new SQLException(errorString+":"+sqle.toString());
}
if (mConnection == null) {
throw new SQLException(errorString);
} else {
synchronized (lockObject) {
openCount++;
}
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
/** Return Connection **/
public Connection getDBConnection()
{
return mConnection;
}
/**
* Execute any plain SQL query and returns the ResultSet
* @throws SQLException
*/
public ResultSet executeSQL(String query) throws SQLException
{
ResultSet lRSet = null;
try
{
// close the statement if already open
if(mStatement != null) mStatement.close();
mStatement = mConnection.createStatement();
lRSet = mStatement.executeQuery(query);
}
catch(SQLException ex)
{
System.err.println("Error Query : " + query);
ex.printStackTrace();
throw new SQLException(ex.getMessage());
}
return lRSet;
}
public void commit()
{
try
{
if(mStatement != null) mStatement.close();
if(mConnection != null && !mConnection.getAutoCommit()) mConnection.commit();
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
public void close()
{
try
{
if ((mConnection != null) && !mConnection.isClosed()) {
if(!resultSetVector.isEmpty())
{
//ok close all result sets once more
int rsSize = resultSetVector.size();
for(int i=0;i<rsSize;i++)
{
ResultSet rset=(ResultSet)resultSetVector.get(i);
try
{
rset.close();
}
catch(SQLException e)
{
throw new SQLException(e.getMessage());
}
}
}
if(!statementVector.isEmpty())
{
//ok close all statements once more
int stmtSize = statementVector.size();
for(int i=0;i<stmtSize;i++)
{
CallableStatement cstmt=(CallableStatement)statementVector.get(i);
try
{
cstmt.close();
}
catch(SQLException e)
{
throw new SQLException(e.getMessage());
}
}
}
mConnection.close();
synchronized (lockObject) {
closeCount++;
}
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
public void callDummyArgumentProcedure(String procedureName) {
CallableStatement cstmt =null;
try {
cstmt = getDBConnection().prepareCall("{call " + procedureName + " (?)}");
cstmt.setInt(1,0);
cstmt.execute();
}
catch(Throwable e) {
}
finally {
if(cstmt !=null) try {cstmt.close();}catch (SQLException e) {}
}
}
}
Thread-based report generation
import java.util.Map;
import java.util.TreeMap;
public class ReportGenerator extends Thread {
String countryName;
public ReportGenerator(String countryName) {
super();
this.countryName = countryName;
}
public String getCountryName() {
return countryName;
}
public void setCountryName(String countryName) {
this.countryName = countryName;
}
public void run() {
try {
ReportDao dao = new ReportDao();
System.out.println("Current Thread"
+ Thread.currentThread().getName());
Map<Integer, TreeMap<String, String>> data = dao.read(countryName);
ExcelModifier.readExcel(data, countryName);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
DAO
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
public class ReportDao {
public RConnection connection = new RConnection();
public static int getOracleParamReturnType(String paramName) {
if (paramName == null)
return -1;
Field cursorField;
try {
Class c = Class.forName("oracle.jdbc.driver.OracleTypes");
cursorField = c.getField(paramName);
return cursorField.getInt(c);
} catch (Throwable th) {
th.printStackTrace();
return -1;
}
}
public Map<Integer, TreeMap<String,String>> read(String countryName) throws SQLException {
System.out.println(" read");
System.out.println("Country name data to be fetched:"+countryName);
ResultSet rs = null;
CallableStatement cstmt = null;
try {
String procedure = "{call USP_GET_KE_REPORT (?,?)}";
connection.getDBConnection();
cstmt = connection.createCallableStatement(procedure);
cstmt.setString(1,countryName);
cstmt.registerOutParameter(2, getOracleParamReturnType("CURSOR"));
cstmt.execute();
rs = connection.getResultSet(cstmt, 2);
return populateData(rs);
} finally {
if (rs != null)
rs.close();
connection.close();
System.out.println(" end ");
}
}
public Map<Integer, TreeMap<String,String>> readPH(String countryName,String product) throws SQLException {
System.out.println(" read");
System.out.println("Country name data to be fetched:"+countryName);
ResultSet rs = null;
CallableStatement cstmt = null;
try {
String procedure = "{call GETPHCERTIFICATIONREPORT (?,?,?)}";
cstmt = connection.createCallableStatement(procedure);
cstmt.setString(1,product);
cstmt.setString(2,countryName);
cstmt.registerOutParameter(3, getOracleParamReturnType("CURSOR"));
cstmt.execute();
rs = connection.getResultSet(cstmt, 3);
return populateData(rs);
} finally {
if (rs != null)
rs.close();
connection.close();
System.out.println(" end ");
}
}
private Map<Integer, TreeMap<String,String>> populateData(ResultSet rs) throws SQLException{
Map<Integer, TreeMap<String,String>> data = new TreeMap<Integer, TreeMap<String,String>>();
List<String> coulmnNames = new ArrayList<String>();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i < columnCount + 1; i++ ) {
coulmnNames.add(rsmd.getColumnName(i)) ;
}
int noOfRecords =0 ;
while (rs.next()) {
TreeMap<String,String> values = new TreeMap<String, String>();
for (String name : coulmnNames) {
values.put(name, rs.getString(name));
}
data.put(++noOfRecords, values);
}
System.out.println("---------No of records--"+noOfRecords);
return data;
}
}
Write data to Excel
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
public class ExcelModifier {
public static void readExcel(Map<Integer, TreeMap<String, String>> data,
String countryName) throws IOException {
String fileName = "c:\\AIG_KX_Certification Report_KE_Afghanistan_2003a5.xls";
String destFileName = "C:\\AIG_KX_Certification Report_KE_"
+ countryName + "_2003a5.xls";
FileInputStream file2 = new FileInputStream(new File(fileName));
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file2);
// Get first sheet from the workbook
HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(1);
System.out.println("Sheet name before:" + workbook.getSheetName(1));
workbook.setSheetName(1, countryName);
System.out.println("Sheet name after:" + workbook.getSheetName(1));
// if there is an validation error then write the error messages to
// excel and quit
// Get iterator to all the rows in current sheet
HSSFCellStyle unlockedCellStyle = unLockCellStyle(workbook);
HSSFCellStyle cs = greyCellStyle(workbook);
int rowNumber = 0;
int recordCount = data.size();
System.out.println(" recordcount " + recordCount);
System.out.println(" writin data to excel "
+ Thread.currentThread().getName());
int currentRecord = 0;
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
Cell keCell = row.getCell(2);
RichTextString excelValue = null;
if (keCell != null) {
excelValue = keCell.getRichStringCellValue();
}
++rowNumber;
if (rowNumber <= 8) {
continue;
}
++currentRecord;
Map<String, String> map = data.get(currentRecord);
boolean dummyData = false;
if (map == null) {
dummyData = true;
System.err.println(" NO RECORD " + countryName + " rowNumber "
+ currentRecord);
continue;
}
keData(dummyData, map, row, unlockedCellStyle, cs);
}
if (recordCount > (rowNumber - 8)) {
createRow(rowNumber++, currentRecord++, sheet, data,
unlockedCellStyle, cs);
}
sheet.setColumnHidden(0, true);
sheet.setColumnHidden(1, true);
sheet.setColumnHidden(4, true);
sheet.setColumnHidden(6, true);
sheet.setColumnHidden(8, true);
sheet.setColumnHidden(10, true);
sheet.setColumnHidden(12, true);
sheet.setColumnHidden(14, true);
sheet.setColumnHidden(18, true);
FileOutputStream fileOut = new FileOutputStream(destFileName);
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
}
public static void createPHExcel(
Map<Integer, TreeMap<String, String>> data, String countryName,
String product) throws IOException {
String fileName = "c:\\AIG_KX_Certification Report-PH v2.4.3 - 08-27-2014a.xls";
String destFileName = "C:\\AIG_KX_Certification Report-PH-GL-"
+ countryName + ".xls";
FileInputStream file2 = new FileInputStream(new File(fileName));
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file2);
// Get first sheet from the workbook
HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(1);
System.out.println("Sheet name before:" + workbook.getSheetName(1));
workbook.setSheetName(1, countryName);
System.out.println("Sheet name after:" + workbook.getSheetName(1));
Map<String, String> headermap = data.get(1);
System.out.println(" header map " + headermap);
Row countryRow = sheet.getRow(0);
Cell countryCell = countryRow.getCell(2);
countryCell.setCellValue(countryName);
Row productRow = sheet.getRow(3);
Cell productCell = productRow.getCell(2);
productCell.setCellValue(product);
Row providertRow = sheet.getRow(4);
Cell providerCell = providertRow.getCell(2);
providerCell.setCellValue(headermap.get("PROVIDED_BY"));
Row providertTitleRow = sheet.getRow(5);
Cell providertTitleCell = providertTitleRow.getCell(2);
providertTitleCell.setCellValue(headermap.get("ROVIDER_TITLE"));
Row approverRow = sheet.getRow(6);
Cell approverCell = approverRow.getCell(2);
approverCell.setCellValue(headermap.get("APPROVED_BY"));
Row approverTitleRow = sheet.getRow(7);
Cell approverTitleCell = approverTitleRow.getCell(2);
approverTitleCell.setCellValue(headermap.get("APPROVED_BY_TITLE"));
Row effectiveStartDateRow = sheet.getRow(8);
Cell effectiveStartDateCell = effectiveStartDateRow.getCell(2);
effectiveStartDateCell.setCellValue(convertStringToDate(headermap
.get("EFFECTIVE_START_DT")));
// if there is an validation error then write the error messages to
// excel and quit
// Get iterator to all the rows in current sheet
HSSFCellStyle unlockedCellStyle = unLockCellStyle(workbook);
int rowNumber = 0;
int recordCount = data.size();
System.out.println(" recordcount " + recordCount);
System.out.println(" writin data to excel "
+ Thread.currentThread().getName());
int currentRecord = 0;
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
Cell keCell = row.getCell(2);
// System.out.println(" KE Name "+excelValue);
++rowNumber;
System.out.println("rowNumber " + rowNumber);
if (rowNumber < 12) {
continue;
}
++currentRecord;
Map<String, String> map = data.get(currentRecord);
boolean setDummyValue = false;
if (recordCount < rowNumber) {
setDummyValue = true;
}
int cellNo = 1;
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
Cell cell = (Cell) cells.next();
switch (cellNo) {
case 1:
setStringCellValue(setDummyValue ? "" : map
.get("PH_CATEGORY_NM"), cell, unlockedCellStyle);
break;
case 2:
setStringCellValue(setDummyValue ? "" : map
.get("CATEGORY_DISPLAY_ORDER"), cell,
unlockedCellStyle);
break;
case 3:
setStringCellValue(setDummyValue ? "" : map
.get("SUB_CATEGORY_NM"), cell, unlockedCellStyle);
break;
case 4:
setStringCellValue(setDummyValue ? "" : map
.get("SUB_CATEGORY_DISPLAY_ORDER"), cell,
unlockedCellStyle);
break;
case 5:
setStringCellValue(setDummyValue ? "" : map
.get("QUESTION_NM"), cell, unlockedCellStyle);
break;
case 6:
setStringCellValue(setDummyValue ? "" : map
.get("QUESTION_PRINT_ORDER"), cell,
unlockedCellStyle);
break;
case 7:
setStringCellValue(setDummyValue ? "" : map
.get("ANSWER_VALUE"), cell, unlockedCellStyle);
break;
case 8:
setStringCellValue(setDummyValue ? "" : map
.get("COMMENT_VALUE"), cell, unlockedCellStyle);
break;
case 9:
setStringCellValue(setDummyValue ? "" : map
.get("ANSWER_VALUE1"), cell, unlockedCellStyle);
break;
case 10:
setStringCellValue(setDummyValue ? "" : map
.get("COMMENT_VALUE1"), cell, unlockedCellStyle);
break;
default:
break;
}
cellNo++;
}
}
if (recordCount > rowNumber) {
System.err.println(" More Rows required");
}
/*
* sheet.setColumnHidden(0, true); sheet.setColumnHidden(1, true);
* sheet.setColumnHidden(4, true); sheet.setColumnHidden(6, true);
* sheet.setColumnHidden(8, true); sheet.setColumnHidden(10, true);
* sheet.setColumnHidden(12, true); sheet.setColumnHidden(14, true);
* sheet.setColumnHidden(18, true);
*/
FileOutputStream fileOut = new FileOutputStream(destFileName);
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
}
private static String replcaseHtmlTags(String value) {
if (value != null) {
value = value.replaceAll("<br>", "\n");
} else {
value = "";
}
return value;
}
public static String convertDateToString(Date date, String formatToConvert)
throws SystemException {
SimpleDateFormat dateFormat = null;
String formatedStr = "";
try {
dateFormat = new SimpleDateFormat(formatToConvert);
formatedStr = dateFormat.format(date);
} catch (NullPointerException ne) {
ne.printStackTrace();
} catch (IllegalArgumentException ire) {
ire.printStackTrace();
}
return formatedStr;
}
public static String convertStringToDate(String dateAsString) {
String start_dt = dateAsString;
DateFormat formatter = new SimpleDateFormat("yyyy-MM-DD");
Date date = null;
try {
date = (Date) formatter.parse(start_dt);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
SimpleDateFormat newFormat = new SimpleDateFormat("MM-dd-yyyy");
String finalString = newFormat.format(date);
return finalString;
}
private static HSSFCellStyle unLockCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle unlockedCellStyle = workbook.createCellStyle();
unlockedCellStyle.setWrapText(true);
unlockedCellStyle.setLocked(false);
unlockedCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
unlockedCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
unlockedCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
unlockedCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
return unlockedCellStyle;
}
private static HSSFCellStyle greyCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle cs = workbook.createCellStyle();
cs.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cs.setLocked(false);
cs.setWrapText(true);
cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
return cs;
}
private static void setStringCellValue(String value, Cell cell,
HSSFCellStyle unlockedCellStyle) {
cell.setCellValue(replcaseHtmlTags(value));
cell.setCellStyle(unlockedCellStyle);
}
private static void createRow(int rowNumber, int recordNumber,
HSSFSheet sheet, Map<Integer, TreeMap<String, String>> data,
HSSFCellStyle unlockedCellStyle, HSSFCellStyle cs) {
int nextRow = 12;
System.out.println(" creating row");
Row r = sheet.getRow(rowNumber);
if (r == null) {
r = sheet.createRow(nextRow);
}
Map<String, String> map = data.get(recordNumber);
keData(false, map, r, unlockedCellStyle, cs);
}
private static void keData(boolean dummyData, Map<String, String> map,
Row row, HSSFCellStyle unlockedCellStyle, HSSFCellStyle cs) {
int cellNo = 1;
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
Cell cell = (Cell) cells.next();
switch (cellNo) {
case 1:
cell.setCellValue(dummyData ? "" : map.get("ID"));
cell.setCellStyle(unlockedCellStyle);
break;
case 2:
cell.setCellValue(dummyData ? "" : map.get("SEQ_NO"));
cell.setCellStyle(unlockedCellStyle);
break;
case 3:
cell.setCellValue(dummyData ? "" : map.get("KE_NM"));
cell.setCellStyle(cs);
break;
case 4:
cell.setCellValue(dummyData ? "" : map.get("KE_DS"));
cell.setCellStyle(cs);
break;
case 5:
cell.setCellValue(dummyData ? "" : map.get("PRODUCT1"));
cell.setCellStyle(unlockedCellStyle);
break;
case 6:
cell.setCellValue(dummyData ? "" : map.get("PRODUCT2"));
cell.setCellStyle(unlockedCellStyle);
break;
case 7:
cell.setCellValue(dummyData ? "" : map.get("STATE1"));
cell.setCellStyle(unlockedCellStyle);
break;
case 8:
cell.setCellValue(dummyData ? "" : map.get("STATE2"));
cell.setCellStyle(unlockedCellStyle);
break;
case 9:
cell.setCellValue(dummyData ? "" : map.get("ENTITY_ORIGINAL"));
cell.setCellStyle(unlockedCellStyle);
break;
case 10:
cell.setCellValue(dummyData ? "" : map.get("ENTITY"));
cell.setCellStyle(unlockedCellStyle);
break;
case 11:
String shortAswere1 = map.get("SHORT ANSWER VALUE1");
shortAswere1 = shortAswere1 == null ? "" : shortAswere1
.replaceAll("<br>", "\n");
cell.setCellValue(dummyData ? "" : shortAswere1);
cell.setCellStyle(unlockedCellStyle);
break;
case 12:
String shortAswere2 = map.get("SHORT ANSWER VALUE2");
shortAswere2 = shortAswere2 == null ? "" : shortAswere2
.replaceAll("<br>", "\n");
cell.setCellValue(dummyData ? "" : shortAswere2);
cell.setCellStyle(unlockedCellStyle);
break;
case 13:
String longAswere1 = map.get("LONG ANSWER VALUE1");
longAswere1 = longAswere1 == null ? "" : longAswere1
.replaceAll("<br>", "\n");
cell.setCellValue(dummyData ? "" : longAswere1);
cell.setCellStyle(unlockedCellStyle);
break;
case 14:
String longAswere2 = map.get("LONG ANSWER VALUE2");
longAswere2 = longAswere2 == null ? "" : longAswere2
.replaceAll("<br>", "\n");
cell.setCellValue(dummyData ? "" : longAswere2);
cell.setCellStyle(unlockedCellStyle);
break;
default:
break;
}
cellNo++;
}
}
}