java.lang.OutOfMemoryError in JXL

Question

If I have hundreds of thousands of rows arrived in result set, then it gives out of memory error.

I have generated XLS report using following code::

I am using IBM websphere Server V6.0.

public void generateExcel(Map parms) {

        Connection dbConnection = null;
        CallableStatement dbStatement = null;    
        PreparedStatement dbPreparedStatement = null;
        ResultSet rs = null;

        try {
                dbConnection = this.dbConnect();
                dbStatement = dbConnection.prepareCall("{ call generateReport()}");
                System.out.println("call riskControlSummaryReport("+startDate+","+endDate+")");
                rs = dbStatement.executeQuery();

                CachedRowSet crs = new CachedRowSet();
                crs.populate(rs);

                ws.setLocale(new Locale("en", "EN"));
                File xlsFile = new File("D:/report.xls");
                FileOutputStream fos = new FileOutputStream(xlsFile);
                ws.setGCDisabled(true);
                workbook = Workbook.createWorkbook(fos, ws);
                WritableSheet s1 = workbook.createSheet("riskControlSummary"+employeeId, 0);
                //Here write report in Excel File
                writeDetailReport(s1, crs);
                s1.setPageSetup(PageOrientation.LANDSCAPE, PaperSize.LETTER, 0.5, 0.25);
                SheetSettings sh = s1.getSettings();
                sh.setScaleFactor(69);

                workbook.write();
                workbook.close();
                fos.close();
        } catch (WriteException we) {
            we.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }

    }
public int writeDetailReport(WritableSheet s1, CachedRowSet rs) throws WriteException, SQLException {

        Label line = null;
        Label line = null;
        int row = 0;

        String labelNames[] = {"Function","Category","RiskTitle","Level",
                "Controls"};

        String dbColumnNames[] = {"Function","Category","Title","Level",
                "Controls"};

        //set cell width
        setCellWidth(s1,0,4000);
        setCellWidth(s1,1,5000);
        setCellWidth(s1,2,4000);
        setCellWidth(s1,3,3000);
        setCellWidth(s1,4,6000);

        int labelLength = labelNames.length;
        int dbLength = dbColumnNames.length;

        //label 
        row++;

        for(int i=0;i<labelLength;i++){
            line = new Label(i, row, labelNames[i], getArial8ptBold());
            s1.addCell(line);
        }

        row++;

        //data list
        while (rs.next()) {
                for(int j=0;j<dbLength;j++)
                {
                            line = new Label(j, row, RiskUtility.replaceBlankIfNull(rs.getString(dbColumnNames[j])).trim(), cellFormat);
                            s1.addCell(line);
                }
                row++;
            }//end while
        }
        return row;
    }

Answer 1

The usual solution: add -Xmx128m to your java command to increase the heap space. It is set to 64 MByte initially for standard Java application.

As your using an application server - the AS itself is started with an -Xmx parameter. Have a look at it's value and increase it to assign more heap space to the entire server. (it will be much more than 128 MByte)

Answer 2

1. Why cant you get Records of 10k in a batch and write to the file.

2. java -Xms512m -Xmx512m 

The ResultSet rs is not closed immediately after the cachedRowset is populated.

CachedRowSet crs = new CachedRowSet(CachedRowSet.TYPE_FORWARD_ONLY);
crs.populate(rs);
rs.close();