石英多次执行后Java Excel POI停止 [英] Java Excel POI stops after multiple execution by quartz

问题描述

我想对此有一些见解.

我有一个从数据库读取和写入 Excel 文件的程序.它的执行基于使用 Quartz api 的计时器，并在每周的每个星期二触发.问题是，当我通过安排它每小时执行一次作业来测试它时，程序在写入 excel 文件的过程中执行几次后突然停止.这是我写的excel代码.

I have a program that reads and writes from a database to an excel file. It's execution is based on a timer using Quartz api and triggered every tuesday of the week. The problem is, when i tested it by scheduling it to execute the job every hour, the program suddenly stops after a few executions inside the process of writing the excel file. Here is my write to excel code.

try {
        FileInputStream file = new FileInputStream(excelFile);
        POIFSFileSystem myFileSystem = new POIFSFileSystem(file);
        HSSFWorkbook workbook = new HSSFWorkbook(myFileSystem);
        HSSFSheet worksheet = workbook.getSheetAt(0);
        this.cellStyle00 = workbook.createCellStyle();
        HSSFDataFormat df = workbook.createDataFormat();
        this.cellStyle00.setDataFormat(df.getFormat("00"));

for(int i = 0;i<Access.size();i++){
         AccessorMethods SetGet = (AccessorMethods)
                    InstlibAccessor.get(i);

    HSSFRow row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    HSSFCell cell = row.createCell(0);

    cell.setCellValue(new Double(SetGet.getOne()));
    cell.setCellStyle(cellStyle00);


  //other set value codes....

}
FileOutputStream fileOut = new FileOutputStream(fileName + ".xls");
workbook.write(fileOut);
 fileOut.flush();
 fileOut.close(); 

 //catch statements follow
 //end

命令行输出和 netbeans 输出没有指示任何错误，如内存不足等.程序并没有结束......它只是停止......就像 jvm 正在无限循环......对这个话题有更多的了解，这里是我的程序的简要流程.

The command line output and netbeans output does not indicate any error like out of memory, etc etc.. The program does not end.. it just, stops.. Like the jvm is working on a infinite loop... To shed more light on the topic, here is the brief flow of my program.

1. 用户执行调度程序
2. 调度程序在所需的时间执行程序(调度程序和程序"是两个不同的程序/jar 文件.调度程序只调用 jar)
3. 程序从创建excel文件开始
4. 然后读取数据库 1.数据库包含 80K 行
5. 对于每一行，如果满足某个条件，则读取数据库 2 和 3
6. 然后它一次将它存储在一个 arraylist 对象 1000 中(我试图避免任何内存问题，所以我批量存储它)
7. 然后我分批写，一次写 1000 条(这是它停止的部分)
8. 在它完成读写之后，它一直等到调度程序再次调用它......如果它到达这一步我就是一个快乐的程序员 =)

以下是我发现的一些观察结果；

Here are some observations I've found;

1. 程序通常在程序执行到第 4 到第 6 次时停止(即在不间断运行调度程序程序 4 或 6 小时后)
2. 它在 excel 中的一个随机写入点停止，例如第 34 千行或第 24 或第 15 行等等......
3. 当我在没有调度程序的情况下执行程序时不会发生此错误.我可以整天手动执行它(我做到了，这不是很有趣)没有任何错误.
4. 输出的 excel 文件显示大小为 0bytes
5. 例如，如果我安排它每小时运行一次，并且在这一小时内停止运行.它将在接下来的几个小时内继续运行，但会停止，并在与前一次运行相比的不同点停止.

可能导致此问题的原因.可能是内存泄漏或更简单的原因?

What could have caused this problem. A memory leak perhaps or something more simple?

其他信息

我通过导入其他程序的类并将其作为作业运行来实现 Quartz 调度程序.这是触发器的代码

I implemented the Quartz scheduler by importing the class of the other program and run it as a job. here is the code for the trigger

JobDetail job = newJob(ExtractorSchedulerJobUtilTester.class)
            .withIdentity("job1", "group1")
            .build();

    CronTrigger trigger = newTrigger()
            .withIdentity("trigger1", "group1")
            .withSchedule(cronSchedule("0 0/2 * 1/1 * ? *"))
            .build();

    Date ft = sched.scheduleJob(job, trigger);
        sched.start();

和工作

public class ExtractorSchedulerJobUtilTester implements Job {
    public void execute(JobExecutionContext context)
        throws JobExecutionException {
    theProgram program= new theProgram();

    program.main();

    JobKey jobKey = context.getJobDetail().getKey();
    }
}

有没有可能；

1. 应用程序占用了我的内存并崩溃
2. 我在我的 Quartz 作业中只使用了一个程序"实例，该实例在第一次运行作业时初始化，并且该作业的所有后续执行都从该实例中引用，从而最大限度地利用了内存.
3. 它与数据库 (AS400) 相关(我怀疑是因为它在编写 excel 时停止了).
4. 电脑太累了，决定休息一下.

更新 - 12/28/2012

小伙伴们/小伙伴们新年快乐！！

对不起，我花了一些时间才回到这个..(当世界将在 21 日结束时，为什么还要在这个上浪费时间.当它没有的时候是苦乐参半)

Sorry it took me some time to get back into this.. (Why bother wasting time on this when the world is gonna end on the 21st. It was bittersweet when it didn't)

我用 netbeans 分析器分析了我的程序，并用内存分析器得到了下图

I profiled my program with netbeans profiler and got the following figures with the memory analyzer

我在第一张图中注意到我的程序每次迭代消耗大约 75MB 的堆大小(如粉红色阴影所示).这是否意味着程序消耗的内存每次迭代增加 75mb?经过几次迭代后，这将消耗大量内存，从而影响程序的执行.我目前正在尝试进行堆转储..我会在设法让它运行后立即发布.

I noticed in the first graph that my program consumes around 75MB in heap size for every iteration (as indicated by the pink shade). Does this mean that the consumed memory of the program increases by 75mb per iteration? Which, after a few iterations, will consume so much memory that will effect the execution of the program. I'm currently trying to take a heap dump.. I'll post it as soon as I manage to get it running.

附加信息:我尝试在只运行 Quartz 的情况下使用探查器(不触发任何东西)，并且系统使用率相对较低，并且每次迭代都不会增加大小.

Additional Info: I tried using the profiler with only the Quartz running (does not trigger anything) and the system usage is relatively low and the size does not increase for each iteration.

我终于设法得到了一个堆转储.我进行了 2 次转储，第一次是第一次迭代发生时，第二次是下一次迭代.我注意到我的两个类的实例之间存在很大差异，如下所示

I finally managed to get a heap dump. I took 2 dumps, first is when the first iteration took place and the second is the next iteration. I noticed that there is a big difference between the instances of two of my classess as indicated below

谢谢！

推荐答案

经过大量的诅咒、祈祷和搜索，我想我已经找到了一个可能的解决方案.我所做的是在 Quartz 作业类的末尾添加 System.gc(); .因此，每次程序完成工作时调用垃圾收集.这只是一个可能的解决方案，而不是一个具体的答案，因为我仍然在吃掉大量的堆内存(我相信在我的代码混乱的某处仍然存在一些内存泄漏).但是，使用 System.gc(); 我的消耗要少得多.我只是不确定这是怎么发生的.从逻辑上讲，我认为 GC 只会影响程序的内存分配而不是内存性能.见下图；上图是有 GC 的图，下图是没有 GC 的图.

After a lot of cursing, praying and searching I think I have found a possible solution to this. What I did was add System.gc(); at the end of my Quartz job class. Thus, calling the garbage collection everytime the program finishes the job. It's only a possible solution and not a concrete answer because I'm still eating up a lot of heap memory (I believe there's still some memory leak somewhere in the chaos of my code). But, with the System.gc(); I'm consuming considerably less. I'm just not sure how this happens. Logically I would think that GC will only effect the memory allocation and not memory performance of the program. See image below; the top graph is the one with GC while the bottom is the one without.

正如你所看到的，有 GC 的比没有的消耗更少的堆内存.我假设内存使用量仍然与 GC 相同，但是一旦 GC 被调用，使用的堆空间将下降.我将暂时使用此解决方案，直到出现更好的答案.

As you can see the one with the GC is consuming less heap memory than the one without. I assume that the memory usage will still be the same with the GC, but once the GC is called the used heap space will decline. I'll be using this solution for now until a better answer appears.

这篇关于石英多次执行后Java Excel POI停止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！