分批导出大文件：JPA Slice 使用解决page查询做导出时无效count问题

1.要重写底层readPage方法，删除掉count的部分

package com.konka.api.bms.repository; import com.konka.api.voucher.domain.SnView; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageImpl; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Slice; import org.springframework.data.jpa.domain.Specification; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; import org.springframework.data.jpa.repository.support.JpaEntityInformation; import org.springframework.data.jpa.repository.support.SimpleJpaRepository; import org.springframework.stereotype.Repository; import javax.persistence.EntityManager; import javax.persistence.TypedQuery; import java.io.Serializable; import java.util.List; public class CustomNotCountRepository<T,ID extends Serializable> extends SimpleJpaRepository<T,ID> { public CustomNotCountRepository(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) { super(entityInformation, entityManager); } public CustomNotCountRepository(Class<T> domainClass, EntityManager em) { super(domainClass, em); } @Override protected <S extends T> Page<S> readPage(TypedQuery<S> query, Class<S> domainClass, Pageable pageable, Specification<S> spec) { query.setFirstResult((int)pageable.getOffset()); query.setMaxResults(pageable.getPageSize()); List<S> content = query.getResultList(); return new PageImpl<>(content, pageable, content.size()); } }

2.封装slice查询方法

package com.konka.api.bms.repository; import com.konka.api.voucher.domain.SnView; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Slice; import org.springframework.data.jpa.domain.Specification; import org.springframework.stereotype.Repository; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; @Repository public class HelperRepository<T>{ @PersistenceContext private EntityManager em; public <T> Slice<T> findAll(Specification<T> specification, Pageable pageable, Class<T> domainClass){ CustomNotCountRepository customNotCountRepository = new CustomNotCountRepository<>(domainClass, em); return customNotCountRepository.findAll(specification, pageable); } }

3.调用查询

spec = getSpec(searchData); pageable = Pageutil.getPageRequest(searchData); all = this.helperRepository.findAll(spec, pageable, SnView.class);

4.分批导出

@Async @Transactional(readOnly = true) public void pageForExportV2(PageParam<SnViewDTO> searchData, Authentication currentAuth) { SecurityUtils.buildAuthentication(currentAuth); Specification<SnView> spec; String entityName = "条码库存"; DownloadTaskCenterDTO taskCenterDTO = commonFeignService.saveDownloadTaskSimple("条码库存", entityName, entityName); Class<SnViewDTO> clazz = SnViewDTO.class; String fileName = entityName + "-" + DateUtil.umsNow() + ExcelTypeEnum.XLSX.getValue(); Path f = Paths.get(FileUtils.getCachePath(), fileName); ExcelWriter excelWriter = EasyExcel.write(f.toString(), clazz) .registerConverter(new LocalDateConverter()) .registerConverter(new LocalDateTimeConverter()) .registerConverter(new BooleanConverter()) // 注意：移除入参中的excelTemps，避免全量加载 .build(); WriteSheet sheet1 = EasyExcel.writerSheet("sheet1").build(); // 一次 EXPORT_BATCH_SIZE 条 searchData.setPageSize(EXPORT_BATCH_SIZE); int page = 1; Pageable pageable; Slice<SnView> all; try{ while (true) { spec = getSpec(searchData); pageable = Pageutil.getPageRequest(searchData); all = this.helperRepository.findAll(spec, pageable, SnView.class); if (!CollectionUtils.isEmpty(all.getContent())){ excelWriter.write(all.getContent(), sheet1); }else { break; } // 终止循环条件：当前批次无数据（说明已查询完所有数据） if (all.getContent().size() < EXPORT_BATCH_SIZE) { break; } searchData.setPageIndex(++page); } if (excelWriter != null) { excelWriter.finish(); log.info("Excel文件写入完成，已生成完整文件"); } FileInfoDTO fileInfo = commonFeignService.uploadFile(entityName, f); commonFeignService.updateDownloadTaskCenter(fileInfo.getId(), taskCenterDTO.getId(), null); }catch (Exception e){ // 全局异常捕获：所有步骤的异常均在此处理，更新任务为失败 log.error("条码库存导出失败，任务ID：{}", e.getMessage()); if (Objects.nonNull(taskCenterDTO)) { taskCenterDTO.setTaskArgs(e.getMessage()); commonFeignService.updateDownloadMsg(taskCenterDTO); } }finally { // ########################################################### // 关键修复2：finally块做**双重兜底**+资源释放，确保无遗漏 // ########################################################### try { // 兜底：若未主动执行finish，在此执行（防止异常导致主动finish未执行） if (excelWriter != null) { excelWriter.finish(); } } catch (Exception e) { log.error("ExcelWriter关闭失败", e); } try { // 删除临时Excel文件：无论成功/失败，都删除，避免临时文件堆积 if (f != null && Files.exists(f)) { Files.deleteIfExists(f); log.info("临时Excel文件已删除：{}", f); } } catch (IOException e) { log.error("删除临时Excel文件失败，文件路径：{}", f, e); } } }