背景:
es使用page查询时，会有10000条数据限制，超过10000条就报错，查不出来了，不过可以使用scroll滚动分页来做查询。
实战
本例使用es的scroll查询，然后获取每一个scrollid对应的数据，进行自己的业务逻辑处理，最后将业务数据导出。
测试主方法
/** * 滚动导出每日行程 * * @param startTime * @param endTime * @param response * @throws IOException */ @GetMapping("/test10") public void test10(String startTime, String endTime, HttpServletResponse response) throws IOException { //最终导出的数据集合 List<VehicleRechargeVo> resultList = new ArrayList<>(); int pageNo = 0; //每次获取9000条数据 int pageSize = 9000; //查询条件构建 SearchQuery searchQuery = getSearchQuery2(startTime, endTime, pageNo, pageSize); //组装数据 List<VehicleRechargeVo> pageList = new ArrayList<>(); List<BusinessVehicleRecharge> pageContent = null; // 滚动查询 ScrolledPage<BusinessVehicleRecharge> scroll = elasticsearchTemplate.startScroll(5000, searchQuery, BusinessVehicleRecharge.class); // 判断是否有内容 while (scroll.hasContent()) { pageContent = scroll.getContent(); //拿出每一scrollId对应的数数据并处理自己的业务 pageList = combineData2(pageContent); //将上面的数据加入最终集合中 resultList.addAll(pageList); //取下一页，scrollId在es服务器上可能会发生变化，需要用最新的。发起continueScroll请求会重新刷新快照保留时间 scroll = elasticsearchTemplate.continueScroll(scroll.getScrollId(), 5000, BusinessVehicleRecharge.class); } // 最后释放查询，必须释放 elasticsearchTemplate.clearScroll(scroll.getScrollId()); // 下面是使用easyExcel做导出 response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("每日充电明细统计", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), VehicleRechargeVo.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet("每日充电明细统计") .doWrite(resultList); }
查询构造器
private SearchQuery getSearchQuery2(String startTime, String endTime, int pageNo, int pageSize) { BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); SearchQuery searchQuery = new NativeSearchQueryBuilder() .withQuery(boolQueryBuilder) .withIndices("business_vehicle_XXX").withTypes("_doc") .withSort(SortBuilders.fieldSort("dateStr").order(SortOrder.ASC)) .withPageable(PageRequest.of(pageNo, pageSize)) .withSourceFilter( new FetchSourceFilterBuilder().withIncludes("dateStr", "vin", "startTime", "endTime", "rechargePower", "rechargeTimes", "socStartTime", "socEndTime") .build()) .build(); return searchQuery; }
对每一scroll Id对应的分页数据做些处理
combineData2
private List<VehicleRechargeVo> combineData2(List<BusinessVehicleRecharge> content) { List<VehicleRechargeVo> list = new ArrayList<>(); content.stream().forEach(item -> { if (StringUtils.isNotBlank(item.getDateStr())) { VehicleRechargeVo rechargeVo = new VehicleRechargeVo(); rechargeVo.setDateStr(item.getDateStr()); rechargeVo.setVin(item.getVin()); rechargeVo.setStartTime(item.getStartTime()); rechargeVo.setEndTime(item.getEndTime()); rechargeVo.setSocStartTime(item.getSocStartTime()); rechargeVo.setSocEndTime(item.getSocEndTime()); rechargeVo.setRechargePower(item.getRechargePower()); rechargeVo.setRechargeTimes(item.getRechargeTimes()); list.add(rechargeVo); } }); // Collections.sort(list); return list; }
导出实体类
import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; import java.io.Serializable; /** * @Auther: hfl * @Date: 2022/3/15 * @Description: 导出充电明细 */ @Data public class VehicleRechargeVo implements Serializable,Comparable<VehicleRechargeVo> { @ExcelProperty(value = "日期") private String dateStr; @ExcelProperty(value = "VIN") private String vin; /** * 充电开始时间 */ @ExcelProperty(value = "充电开始时间") private String startTime; /** * 充电结束时间 */ @ExcelProperty(value = "充电结束时间") private String endTime; /** * 充电电量 kw.h */ @ExcelProperty(value = "充电电量 kw.h") private float rechargePower; /** * 充电时长 h */ @ExcelProperty(value = "充电时长 h") private float rechargeTimes; /** * SOC开始时间 */ @ExcelProperty(value = "SOC开始时间") private float socStartTime; /** * SOC结束时间 */ @ExcelProperty(value = "SOC结束时间") private float socEndTime; @Override public int compareTo(VehicleRechargeVo o) { return this.dateStr.compareTo(o.dateStr); } }
