04/27/2004: Java, JDBC - How to Handle Exceptions and Errors When Closing, or Freeing, Database Resources
When closing database resources it is possible to get multiple exceptions and it is important not to lose any of them.
All of the tutorials that I have read only show the use of printStackTrace() during exception handling.
However, that approach is not sophisticated enough for Enterprise applications. I use the following technique:
Exceptions (and Errors if applicable) are held in an ArrayList until all resources are closed. Then the list is
examined. If exceptions are present, they are processed as needed.
// This code is part of my DataAccess class. Other code in the class is responsible for
// opening the database connection and creating a prepared statement - both are
// static objects.
public synchronized static void free() {
// This list holds any exception objects that are caught.
List caughtExceptions = new ArrayList();
if (ps != null) {
try {
ps.close();
logger.debug("free; closed prepared statement.");
} catch (SQLException e) {
caughtExceptions.add(e);
}
}
if (con != null) {
try {
con.close();
logger.debuf("free; closed database connection.");
} catch (SQLException e) {
caughtExceptions.add(e);
}
}
if (caughtExceptions.size() > 0) {
LogConfiguration.message(caughtExceptions, "Problem closing database resources.");
}
}
// Here is the LogConfiguration.message() method which resides in a different class
// from the above code.
public static void message(final List exceptions, final String message) {
logger.fatal(message);
int throwableIndex = 1;
int throwableCount = exceptions.size();
for (Iterator iter = exceptions.iterator(); iter.hasNext(); ) {
Throwable t = (Throwable) iter.next();
logger.fatal("Exception [" + throwableIndex + "] of [" + throwableCount + "]", t);
throwableIndex++;
}
}
04/26/2004: POI Optimization - eliminating trailing empty rows from an HSSFSheet.
While my spreadsheet has only 7 rows with data, POI creates over 65,000 rows in the HSSFSheet object. This leads to a
large amount of essentially unused memory. In order to free that memory, the following code snippet from my version
of the public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes) method works from bottom towards
the top of the sheet removing empty rows. The code in bold performs the optimization.
HSSFSheet hsheet = new HSSFSheet(workbook, sheet);
boolean stop = false;
boolean nonBlankRowFound;
short c;
HSSFRow lastRow = null;
HSSFCell cell = null;
while (stop == false) {
nonBlankRowFound = false;
lastRow = hsheet.getRow(hsheet.getLastRowNum());
for (c = lastRow.getFirstCellNum(); c <= lastRow.getLastCellNum(); c++) {
cell = lastRow.getCell(c);
if (cell != null && lastRow.getCell(c).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
nonBlankRowFound = true;
}
}
if (nonBlankRowFound == true) {
stop = true;
} else {
hsheet.removeRow(lastRow);
}
}
sheets.add(hsheet);