数据库导出到Excel, 使用mybatis分批导出，防止oom

DAO层：

@Select("<script>" +
           " select * from legal_contract_tb where 1=1 " +
           " <if test='legalContractBean.contractName!=null and legalContractBean.contractName !=\"\"'> " +
           " and contract_name like CONCAT('%',#{legalContractBean.contractName},'%') " +
           " </if> " +
           " <if test='legalContractBean.handleStaffCode!=null and legalContractBean.handleStaffCode >0'> " +
           " and handle_staff_code =#{legalContractBean.handleStaffCode}" +
           " </if> " +
           " <if test='legalContractBean.applyStaffCode!=null and legalContractBean.applyStaffCode >0'> " +
           " and apply_staff_code =#{legalContractBean.applyStaffCode}" +
           " </if> " +
           " <if test='legalContractBean.applyAgencyCode!=null and legalContractBean.applyAgencyCode >0'> " +
           " and apply_agency_code =#{legalContractBean.applyAgencyCode}" +
           " </if> " +
           " <if test='legalContractBean.contractNumber!=null and legalContractBean.contractNumber !=\"\"'> " +
           " and contract_number like CONCAT('%',#{legalContractBean.contractNumber},'%') " +
           " </if> " +
           " <if test='legalContractBean.contractTypeCode!=null and legalContractBean.contractTypeCode>0'> " +
           " and contract_type_code =#{legalContractBean.contractTypeCode} " +
           " </if> " +
           " <if test='legalContractBean.contractSecondTypeCode!=null and legalContractBean.contractSecondTypeCode>0'> " +
           " and contract_second_type_code =#{legalContractBean.contractSecondTypeCode} " +
           " </if> " +
           " <if test='legalContractBean.operateType!=null and legalContractBean.operateType!=\"\"'> " +
           " and operate_type =#{legalContractBean.operateType} " +
           " </if> " +
           " <if test='legalContractBean.otherCompany!=null and legalContractBean.otherCompany!=\"\"'> " +
           " and other_company like CONCAT('%',#{legalContractBean.otherCompany},'%') " +
           " </if> " +
           " <if test='legalContractBean.fullOurCompany!=null and legalContractBean.fullOurCompany!=\"\"'> " +
           " and full_our_company like CONCAT('%',#{legalContractBean.fullOurCompany},'%') " +
           " </if> " +
           " <if test='legalContractBean.contractStatus!=null and legalContractBean.contractStatus!=\"\"'>" +
           "      and contract_status =#{legalContractBean.contractStatus} " +
           " </if> " +
           " <if test='legalContractBean.workflowStatus!=null and legalContractBean.workflowStatus!=\"\"'>" +
           "      and workflow_status =#{legalContractBean.workflowStatus} " +
           " </if> " +

           " <if test='startDay!=null and startDay!=\"\"'>" +
           "      and DATE_FORMAT(create_time, '%Y-%m-%d') >=#{startDay} " +
           " </if> " +
           " <if test='endDay!=null and endDay!=\"\"'>" +
           "      and DATE_FORMAT(create_time, '%Y-%m-%d') <=#{endDay} " +
           " </if> " +
           " <if test='legalContractBean.archiveStatus!=null and legalContractBean.archiveStatus!=\"\" '>" +
           "      and archive_status =#{legalContractBean.archiveStatus} " +
           " </if>" +
           " <if test='legalContractBean.description!=null and legalContractBean.description!=\"\" '>" +
           "      and description like CONCAT('%',#{legalContractBean.description},'%') " +
           " </if>" +
           " order by field (contract_status,'inProcess','expiringSoon','expire','cancel'),create_time desc" +
           "</script>")
   @Options(fetchSize = 5000, resultSetType = ResultSetType.FORWARD_ONLY)
   @ResultType(LegalContractBean.class)
   public void findAll(@Param("legalContractBean") LegalContractBean legalContractBean,
                       @Param("startDay") String startDay, @Param("endDay") String endDay,
                       ResultHandler<LegalContractBean> handler);

SERVICE层:

public SXSSFWorkbook exportList(LegalContractBean legalContractBean, String startDay,String endDay){
        List<LegalContractBean> list = new ArrayList<>();
        legalContractDao.findAll(legalContractBean,startDay,endDay, new ResultHandler<LegalContractBean>() {
            @Override
            public void handleResult(ResultContext<? extends LegalContractBean> resultContext) {
                LegalContractBean resultObject = resultContext.getResultObject();
                list.add(resultObject);
            }
        });
        for(LegalContractBean excelBean : list){
            List<LegalContractSigningBean> signingList = legalContractSigningDao.findByContractCode(excelBean.getCode(), null);
            //计算签约信息金额总和
            BigDecimal totalAmount = signingList.stream().filter(signing->signing.getPayTotal()!=null)
                    .map(LegalContractSigningBean::getPayTotal)
                    .reduce(BigDecimal.ZERO, BigDecimal::add);;
            excelBean.setTotalAmount(totalAmount.toString());
        }
        return assemblyData(list);
    }

public SXSSFWorkbook assemblyData(List<LegalContractBean> list) {
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        SXSSFSheet sheet = wb.createSheet("Sheet1");
        SXSSFRow row0 = sheet.createRow(0);
        row0.createCell(0).setCellValue("合同子分类");
        row0.createCell(1).setCellValue("合同名称");
        row0.createCell(2).setCellValue("合同分类");
        row0.createCell(3).setCellValue("经办人");
        row0.createCell(4).setCellValue("申请人");
        row0.createCell(5).setCellValue("申请部门");
        row0.createCell(6).setCellValue("创建时间");
        row0.createCell(7).setCellValue("我方公司");
        row0.createCell(8).setCellValue("对方公司");
        row0.createCell(9).setCellValue("有效期");
        row0.createCell(10).setCellValue("合同编号");
        row0.createCell(11).setCellValue("合同金额");
        row0.createCell(12).setCellValue("归属项目");
        row0.createCell(13).setCellValue("流程状态");
        for (int i = 0; i < list.size(); i++) {
            LegalContractBean rowData = list.get(i);
            SXSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(rowData.getContractSecondType());
            row.createCell(1).setCellValue(rowData.getContractName());
            row.createCell(2).setCellValue(rowData.getContractType());
            row.createCell(3).setCellValue(rowData.getHandleStaffName());
            row.createCell(4).setCellValue(rowData.getApplyStaffName());
            row.createCell(5).setCellValue(rowData.getApplyAgencyName());
            String createTime = DateUtils.dateToString(rowData.getCreateTime());
            row.createCell(6).setCellValue(createTime);
            row.createCell(7).setCellValue(rowData.getOurCompany());
            row.createCell(8).setCellValue(rowData.getOtherCompany());
            row.createCell(9).setCellValue(getValidityTerm(rowData));
            row.createCell(10).setCellValue(rowData.getContractNumber());
            row.createCell(11).setCellValue(rowData.getTotalAmount());
            String belongProject = getBelongProject(rowData.getBelongProject());
            row.createCell(12).setCellValue(belongProject);
            String workflowStatusCh = ContractEnums.WorkFlowStatus.description(rowData.getWorkflowStatus());
            row.createCell(13).setCellValue(workflowStatusCh);
        }
        return wb;
    }

CONTROLLER层：

    @PassOaAuth
    @RequestMapping("/export/list")
    public ResponseEntity<Object> exportList(LegalContractBean legalContractBean, String startDay, String endDay){
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try(SXSSFWorkbook workbook = legalContractService.exportList(legalContractBean, startDay, endDay)) {
            workbook.write(out);
            String exportFileName ="合同列表导出.xlsx";
            String fileNameUrl = URLEncoder.encode(exportFileName, "UTF-8");
            return ResponseEntity.ok().header("Content-disposition", "attachment;filename="+fileNameUrl+";"+"filename*=utf-8''"+fileNameUrl).
                    contentType(MediaType.parseMediaType("application/vnd.ms-excel")).
                    contentLength(out.size()).
                    body(out.toByteArray());
        } catch (Exception e) {
            log.info("导出数据异常,", e);
            return ResponseEntity.ok().contentType(MediaType.APPLICATION_JSON).body(new ResultBean<Object>(false,"导出异常",null));
        }
    }