java导出20w条数据

---

前言

在上次写的java导出excel的后，在导出20w条数据时出现了请求超时的情况，而且一次查询超多的数据会很慢，所以修改了一下，
针对数据量一旦过大前端请求需要一直等待超时的情况将前端请求改为异步请求，请求完之后让后台自己跑，跑完直接下载；
在查询数据时改为分页查询；
之前的放回excel，base64字符串前端模拟点击下载，改为先生成excel，返回生成的文件名，直接访问地址下载

一、前端代码

  async doExport() {
                let that = this;
                if(that.expflag){
                    eframe.showToast("正在导出中");
                    return;
                }
                that.expflag = true;
                let rqurl = constData.baseUrl + "/export";
                try {
                    const response = await axios.get(rqurl, {
                        params: {
                            aab004:that.form.aab301,
                            bab001:that.form.bab001,
                            aab998:that.form.aab998,
                            ddz128:that.form.ddz128,
                            ddz183:that.form.ddz183,
                            c_date:that.form.c_date
                        }
                    });
                    var fileName = response.fileName;
                    if (fileName != null && fileName != "") {
                        var url = constData.baseUrl + "/download/"+fileName;
                        that.expflag = false;
                        window.location.href = url;
                    }
                } catch (error) {
                    that.expflag = false;
                    console.error('Error:', error);
                }
            }

二、后端代码

 @ResponseBody
    @RequestMapping(value = "/export",)
    public void export(HttpServletRequest request, HttpServletResponse response) throws BusinessException, UnsupportedEncodingException {
        String expFile;
        // 获取当前日期时间
        Date currentDate = new Date();
        // 定义日期时间格式
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        // 格式化当前日期时间
        String formattedDateTime = dateFormat.format(currentDate);
        // 构建新的文件名
        File file = null;
        expFile = "";
        FileOutputStream out = null;
        expFile = expFile + "excel" + formattedDateTime + "file.xlsx";
        file = this.creatDownloadFile(request, expFile);
        String aab004 = request.getParameter("aab004");
        String bab001 = request.getParameter("bab001");
        String aab998 = request.getParameter("aab998");
        String ddz128 = request.getParameter("ddz128");
        String ddz183 = request.getParameter("ddz183");
        String c_date = request.getParameter("c_date");
        file = ecoTownEnterpriseExportForm.getFile();
        System.out.println("==========开始导出===========");
        try {
            // 创建工作簿
            Workbook book = null;
            Sheet ws = null;
            Cell labelC = null;
            InputStream base = null;
            int sheetIndex = 0;
            if (file.isFile()) {
                base = new FileInputStream(file.getPath());
            } else {
                String path = request.getSession().getServletContext().getRealPath("") + "/resources/file/template/" + "base.xlsx";
                //System.out.println("文件是否存在：" + new File(path).exists());
                base = new FileInputStream(path);
            }
            book = new XSSFWorkbook(base);
            ((InputStream) base).close();
            try {
                ws = book.getSheetAt(sheetIndex);
            } catch (Exception var32) {
                book.createSheet("sheet" + sheetIndex);
                ws = book.getSheetAt(sheetIndex);
            }
            int x = 1;
            int i = 0;
            Row row = ws.createRow(0);
            int xx;
            List<String> headRow = new ArrayList<>();
            headRow.add("单位社保编号");
            headRow.add("统一社会信用代码");
            headRow.add("单位名称");
            headRow.add("法定代表人");
            headRow.add("电话");
            headRow.add("身份证号");
            headRow.add("联系人");
            headRow.add("联系电话");
            headRow.add("单位地址");
            headRow.add("经营地址");
            headRow.add("月份");
            for (xx = 0; xx < headRow.size(); xx++) {
                labelC = row.createCell(xx, 1);
                String title = (headRow.get(xx));
                labelC.setCellValue(title);
                ws.setColumnWidth(xx, 4000);
            }
	    EcoDTO ecoDTO = new EcoDTO();
            ecoDTO rowData = null;
            ecoDTO.setPageSize(new BigDecimal(10000));//分页查询每页数量
            List<EcoDTO> data = null;
            label240:
            while (true) {
                ecoDTO.setPageIndex(new BigDecimal(x));//分页查询 第几页
                data = DZ42.getEcoDtoList(ecoDTO);
                System.out.println("查第" + x + "次");
                ++x;
                if (data != null && !data.isEmpty()) {
                    xx = 0;
                    while (true) {
                        if (xx >= data.size()) {
                            continue label240;
                        }
                        rowData = data.get(xx);
                        row = ws.createRow(i + 1);
                        // 设置数据行内容，这里需要根据实际情况设置每一列的数据
                        row.createCell(0).setCellValue(rowData.getDdz128());
                        row.createCell(1).setCellValue(rowData.getDdz183());
                        row.createCell(2).setCellValue(rowData.getBab001());
                        row.createCell(3).setCellValue(rowData.getAab998());
                        row.createCell(4).setCellValue(rowData.getAab004());
                        row.createCell(5).setCellValue(rowData.getAab013());
                        row.createCell(6).setCellValue(rowData.getAae005());
                        row.createCell(7).setCellValue(rowData.getAac147());
                        row.createCell(8).setCellValue(rowData.getLxr());
                        row.createCell(9).setCellValue(rowData.getLxdh());
                        row.createCell(10).setCellValue(rowData.getDwdz());
                        row.createCell(11).setCellValue(rowData.getJydz());
                        row.createCell(12).setCellValue(rowData.getC_date());
                       ++i;
                        ++xx;
                    }
                }
                book.setActiveSheet(sheetIndex);
                out = new FileOutputStream(file);
                book.write(out);
                break;
            }
        } catch (Exception var36) {
            var36.printStackTrace();
            throw new BusinessException(var36);
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (Exception var34) {
                    throw new BusinessException(var34);
                }
            }

            System.out.println("==========结束导出===========");

        }
        JSONObject responseJson = new JSONObject();
        response.setHeader("Service-Type", "Servlet");
        response.setContentType("application/json;charset=UTF-8");
        // 直接将responseJsonStr发送回客户端
        responseJson.put("fileName",expFile);
        try {
            response.getWriter().write(responseJson.toJSONString());
            response.getWriter().flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

总结

虽然成功的导出了20w条数据，写完之后我测试了一下没有问题，又用了30w条也没问题，但是当导出40w条数据时出现了

org.springframework.web.util.NestedServletException: Handler dispatch
failed; nested exception is java.lang.OutOfMemoryError: Java heap
space

Java 堆空间不足，暂时还没解决，不知道怎么整。生产环境暂时数据量还没到所有不会报错，有没有大神提点建议，感激不尽。