jeecg大数据导出Excel以及优化
控制层优化
版本1（初始版本）
@ApiOperation(value ="导出", notes ="导出") @GetMapping("/exportTruckNoReportList")public ModelAndView exportTruckNoReportList(@RequestParam(name ="sap_type", required =false) String sap_type, @RequestParam(name ="start_time", required =false) String start_time, @RequestParam(name ="end_time", required =false) String end_time, @RequestParam(name ="gascode", required =false) String gas_code, @RequestParam(name ="truck_no", required =false) String truck_no, @RequestParam(name ="is_all_org", defaultValue ="false") String isAllOrg, HttpServletRequest req){ UserInfo userInfo = BaseContextHandler.getUserInfo(); QueryWrapper<TruckNoReportExport> queryWrapper =newQueryWrapper<>();if(StringUtils.isNotBlank(start_time)&& StringUtils.isNotBlank(end_time)){ queryWrapper.between("trade_at", start_time, end_time);}elseif(StringUtils.isBlank(start_time)&& StringUtils.isNotBlank(end_time)){ queryWrapper.le("trade_at", end_time);}elseif(StringUtils.isNotBlank(start_time)&& StringUtils.isBlank(end_time)){ queryWrapper.ge("trade_at", start_time);} queryWrapper.like(StringUtils.isNotBlank(truck_no),"truck_no", truck_no); queryWrapper.ne("trade_type","1200");if("true".equals(isAllOrg)){ queryWrapper.eq((StringUtils.isNotBlank(gas_code)),"gas_code", gas_code);}else{ queryWrapper.like((StringUtils.isNotBlank(gas_code)),"gas_code", gas_code);} queryWrapper.groupBy("truck_no"); queryWrapper.groupBy("gas_code"); queryWrapper.orderByDesc("trade_at"); long startTime = System.currentTimeMillis(); List<TruckNoReportExport> list = tradesOrderPayService.exportTruckNoReportList(queryWrapper); long endTime1 = System.currentTimeMillis(); long elapsedTime1 = endTime1 - startTime; log.info("从数据库查询时间{}", String.valueOf(elapsedTime1)); long endTime2 = System.currentTimeMillis(); log.info("导出数据量:{}", list.size()); long elapsedTime = endTime2 - endTime1; log.info("循环处理时间：{}", String.valueOf(elapsedTime)); ModelAndView mv =newModelAndView(newJeecgEntityExcelView());//此处设置的filename无效 ,前端会重更新设置一下 mv.addObject(NormalExcelConstants.FILE_NAME,"统计"); mv.addObject(NormalExcelConstants.CLASS, TruckNoReportExport.class);//update-begin--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置-------------------- ExportParams exportParams =newExportParams("统计","导出人:"+ userInfo.getTrueName(),"统计");// exportParams.setImageBasePath(upLoadPath);//update-end--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置---------------------- mv.addObject(NormalExcelConstants.PARAMS, exportParams); mv.addObject(NormalExcelConstants.DATA_LIST, list);return mv;}版本2（通过分页查询，优化了查询时内存的开销）
@ApiOperation(value ="导出", notes ="导出") @GetMapping("/exportTruckNoReportList")public ModelAndView exportTruckNoReportList(@RequestParam(name ="sap_type", required =false) String sap_type, @RequestParam(name ="start_time", required =false) String start_time, @RequestParam(name ="end_time", required =false) String end_time, @RequestParam(name ="gascode", required =false) String gas_code, @RequestParam(name ="truck_no", required =false) String truck_no, @RequestParam(name ="is_all_org", defaultValue ="false") String isAllOrg, HttpServletRequest req){ UserInfo userInfo = BaseContextHandler.getUserInfo(); QueryWrapper<TruckNoReportExport> queryWrapper =newQueryWrapper<>();if(StringUtils.isNotBlank(start_time)&& StringUtils.isNotBlank(end_time)){ queryWrapper.between("trade_at", start_time, end_time);}elseif(StringUtils.isBlank(start_time)&& StringUtils.isNotBlank(end_time)){ queryWrapper.le("trade_at", end_time);}elseif(StringUtils.isNotBlank(start_time)&& StringUtils.isBlank(end_time)){ queryWrapper.ge("trade_at", start_time);} queryWrapper.like(StringUtils.isNotBlank(truck_no),"truck_no", truck_no); queryWrapper.ne("trade_type","1200");if("true".equals(isAllOrg)){ queryWrapper.eq((StringUtils.isNotBlank(gas_code)),"gas_code", gas_code);}else{ queryWrapper.like((StringUtils.isNotBlank(gas_code)),"gas_code", gas_code);} queryWrapper.groupBy("truck_no"); queryWrapper.groupBy("gas_code"); queryWrapper.orderByDesc("trade_at"); long startTime = System.currentTimeMillis(); Page<TruckNoReportExport> pages =newPage<>(1,3000); Page<TruckNoReportExport> pageList = tradesOrderPayService.exportTruckNoReportList(pages,queryWrapper); List<TruckNoReportExport> list =newArrayList<>();for(int pageNo =1; pageNo <= pageList.getPages(); pageNo++){ Page<TruckNoReportExport> page1 =newPage<>(pageNo,3000);// 设置不执行 count 查询，每次page查询都会先执行一遍count太花费时间了，这里已经知道有数据了所以不需要 page1.setSearchCount(false); Page<TruckNoReportExport> pageList1 = tradesOrderPayService.exportTruckNoReportList(page1,queryWrapper); list.addAll(pageList1.getRecords());} long endTime1 = System.currentTimeMillis(); long elapsedTime1 = endTime1 - startTime; log.info("从数据库查询时间{}", String.valueOf(elapsedTime1)); long endTime2 = System.currentTimeMillis(); log.info("导出数据量:{}", list.size()); long elapsedTime = endTime2 - endTime1; log.info("循环处理时间：{}", String.valueOf(elapsedTime)); ModelAndView mv =newModelAndView(newJeecgEntityExcelView());//此处设置的filename无效 ,前端会重更新设置一下 mv.addObject(NormalExcelConstants.FILE_NAME,"统计"); mv.addObject(NormalExcelConstants.CLASS, TruckNoReportExport.class);//update-begin--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置-------------------- ExportParams exportParams =newExportParams("统计","导出人:"+ userInfo.getTrueName(),"统计");// exportParams.setImageBasePath(upLoadPath);//update-end--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置---------------------- mv.addObject(NormalExcelConstants.PARAMS, exportParams); mv.addObject(NormalExcelConstants.DATA_LIST, list);return mv;}版本3（把数据存入OOS，用户通过OOS下载，节省内存开销）
**部分代码实例** （略）//oos存表-导出中 long startTime = System.currentTimeMillis(); ByteArrayOutputStream byteArrayOutputStream =null; ByteArrayInputStream byteArrayInputStream =null;try{ byteArrayOutputStream =newByteArrayOutputStream(); long count = tradesOrderPayService.selectCount(queryWrapper);// 总记录数 long pageSize =3000;// 每页记录数 int totalPages =(int) Math.ceil((double) count / pageSize);// 计算总页数 HSSFWorkbook workbook =null;for(int pageNo =1; pageNo <= totalPages; pageNo++){ List<TradesOrderPayExport> lists = tradesOrderPayService.exportOrderPayList((pageNo -1)* pageSize,pageSize, queryWrapper); log.info("获取当前{}", System.currentTimeMillis()- startTime); ExcelUtil<TradesOrderPayExport> util =newExcelUtil<>(TradesOrderPayExport.class); workbook = util.convert2WorkbookPage(lists, workbook,"导出",pageNo,3000);} long endTime1 = System.currentTimeMillis(); long elapsedTime1 = endTime1 - startTime; log.info("从数据库查询时间{}", String.valueOf(elapsedTime1)); long endTime2 = System.currentTimeMillis(); log.info("导出数据量:{}", count); workbook.write(byteArrayOutputStream); byteArrayInputStream =newByteArrayInputStream(byteArrayOutputStream.toByteArray()); String bizPath ="total/prod/"; String format = DateUtil.format(newDate(),"yyyy-MM-dd"); long l = System.currentTimeMillis(); bizPath = bizPath + format +"/"+ l +"导出.xls"; String upload = OssBootUtil.upload(byteArrayInputStream, bizPath); System.err.println(upload); fossUploadUrl1.setUrl(upload); fossUploadUrl1.setUpdateTime(newDate()); fossUploadUrl1.setUpdateName(userInfo.getTrueName()); fossUploadUrl1.setStatus("2");//oos存表-导出成功 fossApi.updateUploadUrl(fossUploadUrl1); long elapsedTime = endTime2 - endTime1; log.info("上传成功时间：{}", String.valueOf(elapsedTime));}catch(IOException e){ fossUploadUrl1.setUpdateTime(newDate()); fossUploadUrl1.setUpdateName(userInfo.getTrueName()); fossUploadUrl1.setStatus("3");//oos存表-导出失败 fossApi.updateUploadUrl(fossUploadUrl1);thrownewRuntimeException(e);}finally{if(byteArrayOutputStream !=null){try{ byteArrayOutputStream.close();}catch(IOException e){ e.printStackTrace();}}if(byteArrayInputStream !=null){try{ byteArrayInputStream.close();}catch(IOException e){ e.printStackTrace();}}}
总体优化思路： 1.建立基础的连表查询，要求达到需求 2.去除不必要的字段，尽量`减少连表`，将数据量比较少切不变动的表使用case替换 3.表里建议`索引优化`4.发现查询速度还是很慢，通过`建立视图`，优化连表查询速度，同时视图查询会导致索引失效 5.查询数据量比较大，几百名用户同时导出会造成内存溢出的问题，那么改成`分页查询`，测试到以数万条的数据量，每次分页以3000时间空间上都比较可以接受 6.page分页查询执行sql时，内部会自动执行一遍count()查询，这里页数已经查询出来了，可以`去掉count()`减少查询时间 7.数据量太大时还是会造成内存溢出的问题，所以将查询出来的数据写入excel里再`放入oos里`，建立个表存放下载地址，时间，用户，前端直接通过存放地址在oos下载，考虑到节省内存，可以分页的时候`循环写入excel`里，存入流，再上传到oos，修改`保留地址`8.查询速度还是慢了，发现sql可以继续优化，通过`将主表进行子查询嵌套`的方式，可以先查询主表，再添加其余的连表字段，那么就是说`查询条件可以也放入到主表`里通过子查询先查出来，再进行连表，优化了不少的查询时间，==但是这里发现排序放到主表失效了==9.发现mybatisplus的分页条查询，会把查询条件替换到${ew.customSqlSegment}位置，但是分页仍然会放到连表之后，那么把`分页也放到主表`里把。那么需要传入#{pageNum},#{pageSize}两个字段，增加查询一个`查询总个数的count`方法（通过`查询主表`个数就行了，这里我是直接查询视图，因为字段重命名了）， 10.最后把`排序`加到连表的最后面 **这里给出最终优化方案的查询sql实例**<select id="exportOrderPayList" resultType="com.bee.trades.api.vo.TradesOrderPayExport"> select v1.*,`info`.`oil_name`AS`oil_total_name`,round((`info`.`mac_price`/100),2)AS`mac_price`,`info`.`mileage`AS`mileage`,`info`.`remark`AS`remark`,`org1`.`org_name`AS`gas_name`,`org1`.`org_name`AS`org_name`,`org1`.`org_root_name`AS`org_root_name`,`station`.`station_name`AS`station_name`,(CASEWHEN(`station`.`source`=1)THEN'自营'WHEN(`station`.`source`=2)THEN'合作对接'ELSE''END)AS`station_source_name`,`supplier`.`supplier_name`AS`pname`FROM( select * from v_order_pay_view //这里是主表的视图，这里有两千行放入到mapper层会影响到控制台查看数据 ${ew.customSqlSegment}//mybatisplus取查询条件 limit #{pageNum},#{pageSize}//分页)AS v1 LEFTJOIN`is_trades`.`trades_order_pay_info``info`ON`v1`.`id`=`info`.`order_pay_id`LEFTJOIN`is_base`.`base_org``org1`ON`v1`.`gas_code`=`org1`.`org_code`LEFTJOIN`is_gas`.`gas_station``station`ON`v1`.`station_id`=`station`.`id`LEFTJOIN`is_base`.`base_supplier``supplier`ON`v1`.`pcode`=`supplier`.`scode`ORDERBY v1.trade_at DESC</select><select id="getCount" resultType="java.lang.Long">//查询总条数 select count(0) from v_order_pay_view ${ew.customSqlSegment}</select>
sql优化
1.表里建立索引优化
2.建立视图查询
3.连表获取所有的字段
4.尽量减少连表，数据量比较少的表使用case替换
5.去掉不必要的字段
注意：这里有一个问题
假如使用了SUM等统计字段，后面一定要接GROUP BY进行分组
如果是先根据条件查询，再分组那么就不能根据视图查询，下面代码实例
<select id="exportTruckNoReportList" resultType="com.bee.trades.api.vo.TruckNoReportExport">SELECT v1.*,`org1`.`org_name`AS gas_name,`org2`.`org_name`AS org_root_name FROM(SELECT`trades`.`trade_at`AS`trade_at`,`trades`.`trade_type`AS`trade_type`,(CASEWHEN(`trades`.`trade_type`LIKE'11%')THEN'加油'WHEN(`trades`.`trade_type`LIKE'12%')THEN'撤销'END)AS`trade_type_name`,round((sum(`trades`.`pay_money`)/100),2)AS`pay_money`,`trades`.`is_oil`AS`is_oil`,`trades`.`oil_num`AS`oil_num`,`trades`.`oil_name`AS`oil_name`,sum(IF((`trades`.`is_oil`=11),`trades`.`oil_num`,0))AS`oil_num_count`,round((sum(IF((`trades`.`is_oil`=11),`trades`.`pay_money`,0))/100),2)AS`oil_pay_money`,round((sum(IF((`trades`.`is_oil`=12),`trades`.`pay_money`,0))/100),2)AS`not_oil_pay_money`,round((sum(IF((`trades`.`oil_name`='e6d070d8-f95c-11e8-bfa7-02abba8d56d2'),`trades`.`pay_money`,0))/100),2)AS`diesel_oil_pay_money`,sum(IF((`trades`.`oil_name`='e6d070d8-f95c-11e8-bfa7-02abba8d56d2'),`trades`.`oil_num`,0))AS`diesel_oil_num`,round((sum(IF((`trades`.`oil_name`='e07bc2be-f95c-11e8-8f4b-02abba978494'),`trades`.`pay_money`,0))/100),2)AS`gasoline_pay_money`,sum(IF((`trades`.`oil_name`='e07bc2be-f95c-11e8-8f4b-02abba978494'),`trades`.`oil_num`,0))AS`gasoline_oil_num`,`trades`.`truck_no`AS`truck_no`,`trades`.`gas_code`AS`gas_code`,CASEWHENLENGTH(`trades`.`gas_code`)>0THENSUBSTRING(`trades`.`gas_code`,1,6)ELSENULLENDAS org_root FROM`is_trades`.`trades_order_pay``trades` ${ew.customSqlSegment})AS v1 LEFTJOIN v_base_org_name_view `org1`ON v1.gas_code = org1.org_code LEFTJOIN v_base_org_name_view `org2`ON v1.gas_code = org2.org_code </select>//这里通过连表查询了gas_code对应的机构名称gas_name,和对应的顶级机构名称org_root_name//通过查询之后再连表，优化了查询时间${ew.customSqlSegment} 是mybatisplus的参数接收,内部是使用ew接收的参数
mapper层
Page<TruckNoReportExport>exportTruckNoReportList(@Param(Constants.WRAPPER) QueryWrapper<TruckNoReportExport> queryWrapper, Page<TruckNoReportExport> pages);
entity实体类
package com.bee.trades.api.vo;import com.fasterxml.jackson.annotation.JsonFormat;import com.fasterxml.jackson.annotation.JsonProperty;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import org.jeecgframework.poi.excel.annotation.Excel;import org.springframework.format.annotation.DateTimeFormat;import java.io.Serializable;import java.math.BigDecimal;import java.util.Date;/** * 功能描述 * * @author: scott * @date: 2023年12月01日 20:23 */ @Data publicclassTruckNoReportExportimplementsSerializable{privatestatic final long serialVersionUID = 1L;/** * 车牌号 */ @Excel(name ="车牌号", width =15) @ApiModelProperty(value ="车牌号") @JsonProperty("truck_no")private String truckNo;/** * 所属机构 */ @ApiModelProperty(value ="所属机构") @JsonProperty("gas_code")private String gasCode; @Excel(name ="所属机构", width =25) @JsonProperty("gas_name")private String gasName;/** * 顶级机构编码 */ @ApiModelProperty(value ="顶级机构编码") @JsonProperty("org_root")private String orgRoot; @Excel(name ="顶级机构", width =25) @JsonProperty("org_root_name")private String orgRootName;/** * 消费总额 */ @Excel(name ="消费总额", width =15, type =4) @ApiModelProperty(value ="消费总额") @JsonProperty("pay_money")private BigDecimal payMoney;/** * 油品总量 */ @Excel(name ="油品总量", width =15, type =4) @ApiModelProperty(value ="油品总量") @JsonProperty("oil_num_count")private BigDecimal oilNumCount;/** * 油品总额 */ @Excel(name ="油品总额", width =15, type =4) @ApiModelProperty(value ="油品总额") @JsonProperty("oil_pay_money")private BigDecimal oilPayMoney;/** * 柴油总量 */ @Excel(name ="柴油总量", width =15, type =4) @ApiModelProperty(value ="柴油总量") @JsonProperty("diesel_oil_num")private BigDecimal dieselOilNum;/** * 柴油总额 */ @Excel(name ="柴油总额", width =15, type =4) @ApiModelProperty(value ="柴油总额") @JsonProperty("diesel_oil_pay_money")private BigDecimal dieselOilPayMoney;/** * 汽油总量 */ @Excel(name ="汽油总量", width =15, type =4) @ApiModelProperty(value ="汽油总量") @JsonProperty("gasoline_oil_num")private BigDecimal gasolineOilNum;/** * 汽油总额 */ @Excel(name ="汽油总额", width =15, type =4) @ApiModelProperty(value ="汽油总额") @JsonProperty("gasoline_pay_money")private BigDecimal gasolinePayMoney;/** * 非油总额 */ @Excel(name ="非油总额", width =15, type =4) @ApiModelProperty(value ="非油总额") @JsonProperty("not_oil_pay_money")private BigDecimal notOilPayMoney;/** * 交易时间 */ @Excel(name ="交易时间", width =25, format ="yyyy-MM-dd HH:mm:ss") @JsonFormat(timezone ="GMT+8",pattern ="yyyy-MM-dd HH:mm:ss") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @ApiModelProperty(value ="交易时间") @JsonProperty("trade_at")private Date tradeAt;/** * 交易类型 1开头代表加油，正常加油1111，修改后的1112，加油补录 1113 撤销 1200 撤销中 1201 */ @ApiModelProperty(value ="交易类型") @JsonProperty("trade_type")private String tradeType; @Excel(name ="交易类型", width =25) @JsonProperty("trade_type_name")private String tradeTypeName;}
本文转载自: https://blog.csdn.net/qq_39445235/article/details/135089676
版权归原作者 助助助助助手 所有， 如有侵权，请联系我们删除。
版权归原作者 助助助助助手 所有， 如有侵权，请联系我们删除。