论述市场营销对网站设计的影响,做网站好公司,做英语题的网站,直播推广引流的方式EasyExcel导出数据#xff0c;解决慢sql#xff0c;漏数据#xff0c;重复数据问题#xff08;一#xff09;
大家思考一下#xff0c;在导出excel时是否会出现如下几个常见问题
慢sql问题漏数据#xff0c;缺数据问题数据重复
那到底该如何解决呢#xff1f;下面我…EasyExcel导出数据解决慢sql漏数据重复数据问题一
大家思考一下在导出excel时是否会出现如下几个常见问题
慢sql问题漏数据缺数据问题数据重复
那到底该如何解决呢下面我们一起来看看我的实现吧
代码示例
controller入口 分页查询2000条数据分批次导出。 /***下载自动续费* author youlu* date 2022/11/21 14:32* param response* return com.smy.ucc.common.JsonMessage*/GetMapping(/downloadRenewalSign)public void downloadRenewalSign(HttpServletResponse response, RenewalSignAdminReq req) throws Exception {this.checkDownloadParam(req);response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName URLEncoder.encode(橡树会员自动续费, UTF-8);response.setHeader(Content-disposition, attachment;filename fileName .xlsx);ExcelWriter excelWriter EasyExcel.write(response.getOutputStream(), RenewalSignAdminResp.class).build();WriteSheet writeSheet EasyExcel.writerSheet(自动续费).build();req.setStartCreateTime(DateUtil.parseDate(req.getStartCreateTimeStr() 00:00:00, yyyy-MM-dd HH:mm:ss));req.setEndCreateTime(DateUtil.parseDate(req.getEndCreateTimeStr() 23:59:59, yyyy-MM-dd HH:mm:ss));int pageSize 2000;boolean firstFlag true;while (true) {ListRenewalSignAdminResp data cbsRenewalService.queryRenewalSignListForDownload(req, firstFlag, pageSize);if (CollectionUtils.isEmpty(data)) {break;}Date lastCreateTime data.get(data.size() - 1).getCreateTime();String startId data.get(data.size() - 1).getId();//补偿同一时间段内并发的数据 where create_time ? and id ? and 其他筛选条件ListRenewalSignAdminResp otherDatas cbsRenewalService.queryRenewalSignListByCreateTimeAndId(req, lastCreateTime, startId);data.addAll(otherDatas);excelWriter.write(data, writeSheet);req.setStartCreateTime(lastCreateTime);if (firstFlag) {firstFlag false;}}excelWriter.finish();}实体
package com.smy.cbs.dto.renewal;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.smy.cbs.easyexcel.write.RenewalStatusStringConverter;
import com.smy.cbs.easyexcel.write.VipTypeStringConverter;
import com.smy.cbs.enums.renewal.RenewalStatusEnum;
import com.smy.cbs.enums.vip.VipType;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
import java.util.Date;/***自动签约返回参数* author youlu* date 2022/9/22 10:19* return*/
Getter
Setter
public class RenewalSignAdminResp implements Serializable {private static final long serialVersionUID 472574503670404785L;//id:调用框架生成ExcelProperty(value 签约号, index 0)private String id;//客户号ExcelProperty(value 客户号, index 1)private String custNo;/*** 会员类型1橡树会员、2自营会员* see VipType*/ExcelProperty(value 会员类型, index 2, converter VipTypeStringConverter.class)private String vipType;//续费周期ExcelProperty(value 续费周期, index 3)private Integer renewalPeriod;//签约时间ExcelProperty(value 签约时间, index 4)DateTimeFormat(yyyy-MM-dd HH:mm:ss)JsonFormat(pattern yyyy-MM-dd HH:mm:ss,locale zh,timezoneGMT8)private Date signTime;//解约时间ExcelProperty(value 解约时间, index 5)DateTimeFormat(yyyy-MM-dd HH:mm:ss)JsonFormat(pattern yyyy-MM-dd HH:mm:ss,locale zh,timezoneGMT8)private Date terminateTime;/*** 签约状态1待签约、2签约失败、3签约中、4已解约* see RenewalStatusEnum*/ExcelProperty(value 签约状态, index 6, converter RenewalStatusStringConverter.class)private String renewalStatus;//下次代扣时间JsonFormat(pattern yyyy-MM-dd HH:mm:ss,locale zh,timezoneGMT8)DateTimeFormat(yyyy-MM-dd HH:mm:ss)ExcelProperty(value 下次代扣时间, index 7)private Date nextRenewalTime;//自动续费次数ExcelProperty(value 自动续费次数, index 8)private Integer renewalNum;//投放qdExcelProperty(value 投放qd, index 9)private String qd;//创建人ExcelProperty(value 创建人, index 10)private String createUser;//修改人ExcelProperty(value 修改人, index 11)private String updateUser;//创建日期ExcelProperty(value 创建日期, index 12)DateTimeFormat(yyyy-MM-dd HH:mm:ss)JsonFormat(pattern yyyy-MM-dd HH:mm:ss,locale zh,timezoneGMT8)private Date createTime;//修改日期ExcelProperty(value 修改日期, index 13)DateTimeFormat(yyyy-MM-dd HH:mm:ss)JsonFormat(pattern yyyy-MM-dd HH:mm:ss,locale zh,timezoneGMT8)private Date updateTime;//ExcelProperty(value 签约商户, index 14)private String signMerchant;}这里要注意converter 的使用例如vipType在数据库中设定的是枚举值“1”,“2”,“3”,“4” 但是我们导出数据期望是其代表的含义描述因此要转换关注其convertToExcelData方法转换如下
package com.smy.cbs.easyexcel.write;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.smy.cbs.enums.vip.VipType;/*** String and string converter** author youlu*/
public class VipTypeStringConverter implements ConverterString {Overridepublic Class supportJavaTypeKey() {return String.class;}Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 这里是读的时候会调用 不用管** param cellData* NotNull* param contentProperty* Nullable* param globalConfiguration* NotNull* return*/Overridepublic String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {return cellData.getStringValue();}/*** 这里是写的时候会调用 不用管** param value* NotNull* param contentProperty* Nullable* param globalConfiguration* NotNull* return*/Overridepublic CellData convertToExcelData(String value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {return new CellData(VipType.getVipTypeDescByCode(value));}
}service|Dao层实现 Overridepublic ListRenewalSignAdminResp queryRenewalSignListForDownload(RenewalSignAdminReq req, boolean firstFlag, int pageSize) {return cbsRenewalSignInnerService.queryRenewalSignListForDownload(req, firstFlag, pageSize);}Overridepublic ListRenewalSignAdminResp queryRenewalSignListByCreateTimeAndId(RenewalSignAdminReq req, Date createTime, String startId) {return cbsRenewalSignInnerService.queryRenewalSignListByCreateTimeAndId(req,createTime, startId);}sql层查询
select idqueryRenewalSignListForDownload resultMapsignAdminMapselectinclude refidBase_Column_List /from t_renewal_signwherechoosewhen testfirstFlagand create_time gt; #{req.startCreateTime}/whenotherwiseand create_time gt; #{req.startCreateTime}/otherwise/chooseand create_time lt; #{req.endCreateTime}and renewal_status #{req.renewalStatus}if testreq.custNo ! null and req.custNo ! and cust_no #{req.custNo}/ifif testreq.id ! null and req.id ! and id #{req.id}/ifif testreq.vipType ! null and req.vipType ! and vip_type #{req.vipType}/if/whereorder by create_time , idlimit #{pageSize}/selectfirstFlag 用于区分是否是第一次查询order by create_time , id 的排序可以用于解决慢sql问题。 这个查询会存在漏数据的问题因此引进下面这条sql专门查询在相同时间点内生成的数据 select idqueryRenewalSignListByCreateTimeAndId resultMapsignAdminMapselectinclude refidBase_Column_List /from t_renewal_signwhereand create_time #{createTime}and id gt; #{startId}and renewal_status #{req.renewalStatus}if testreq.custNo ! null and req.custNo ! and cust_no #{req.custNo}/ifif testreq.id ! null and req.id ! and id #{req.id}/ifif testreq.vipType ! null and req.vipType ! and vip_type #{req.vipType}/if/where这条sql的意义就是补偿查询在相同时间点内生成的数据
这里解决了我们开头提到的所有问题。为什么呢下节我们一起来分析下…
