2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2020

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);