Java实现mysql的分页及流式查询导出

一、前言
在实际应用中，当我们需要导出大数据量数据时，可能会存在一些问题，因为当我们一下子将数据全部加载出来到内存中，很可能会发生OOM(内存溢出)，而且查询会很慢，因为框架耗费大量的时间和内存去把数据库查询的结果封装成我们想要的对象（实体类）。

那么如何实现大数据量导出呢？下面会具体说明。

二、如何是实现？
1.常规查询：一次性读取大量数据或者分页读取。
分页查询实现大数据量导出。
（1）首先引入easyexcel依赖。

 <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.1.0</version>
 </dependency>

（2）定义工具类实现分页导出。

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.function.UnaryOperator;

@Slf4j
public class PageExcelUtils {
    public static final Integer EXCEL_SHEET_ROW_MAX_SIZE = 1000001; // excel sheet最大行数(算标题)
    private static final long DEF_PAGE_SIZE = 1000; // 默认页大小
    private HttpServletResponse httpServletResponse;
    private long pageSize = DEF_PAGE_SIZE;
    private String fileName;

    private PageExcelUtils() {
    }

    public static PageExcelUtils export(HttpServletResponse response, String fileNamePrefix) throws UnsupportedEncodingException {
        PageExcelUtils excelUtil = new PageExcelUtils();
        excelUtil.httpServletResponse = response;
        excelUtil.fileName = fileNamePrefix + "_" + DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN) + ".xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String downloadFileName = URLEncoder.encode(excelUtil.fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + downloadFileName);
        return excelUtil;
    }

    public PageExcelUtils pageSize(int pageSize) {
        this.pageSize = pageSize;
        return this;
    }

    private static <T> void pageExcelWriter(OutputStream outputStream, String fileName, Class<T> head, long pageSize, UnaryOperator<IPage<T>> pageFunction) {
        long start = System.currentTimeMillis();
        log.debug("fileName:{}, excel writer start", fileName);
        try (ExcelWriter excelWriter = EasyExcel.write(outputStream, head).build()) {
            IPage<T> page = null;
            WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
            do {
                long pageSearchStartTime = System.currentTimeMillis();
                page = pageFunction.apply(page == null ? new Page<>(1, pageSize) : new Page<>(page.getCurrent() + 1, page.getSize(), page.getTotal(), false)); // 分页查询
                long pageExcelWriteStartTime = System.currentTimeMillis();
                writeSheet.setSheetNo((int) (page.getCurrent() * page.getSize() / EXCEL_SHEET_ROW_MAX_SIZE));
                excelWriter.write(page.getRecords(), writeSheet); // excel写入数据
                log.debug("fileName:{}, total:{}, pageSize:{}, totalPage:{}, pageNo:{}, sheetNo:{}, pageSearchTime:{}ms, pageExcelWriterTime:{}ms", fileName, page.getTotal(), page.getSize(), page.getPages(), page.getCurrent(), writeSheet.getSheetNo(), pageExcelWriteStartTime - pageSearchStartTime, System.currentTimeMillis() - pageExcelWriteStartTime);
            } while (page.getCurrent() < page.getPages()); // 是否还有下一页
        }
        log.debug("fileName:{}, excel writer done, totalTime:{}ms", fileName, System.currentTimeMillis() - start);
    }
}

（3）新建controller实现分页导出接口。

@RestController
    public class DemoController {
        @Resource
        private DemoService demoService;

        /**
         * 分页导出Excel(先查后写)
         * @param response 响应
         * @throws IOException
         */
        @GetMapping("/exportExcel")
        public void exportExcel(HttpServletResponse response) throws IOException {
            PageExcelUtils.export(response, "测试").pageSize(5000).pageExcelWriter(Demo.class, page -> demoService.lambdaQuery().page(page));
        }
    }

其中demoService如下：

/**
 * 数据访问层接口实现类
 */
@Service
public class DemoServiceImpl extends BaseServiceImpl<DemoMapper, Demo> implements DemoService {
}

Demo类示例：

@TableName
@Data
public class Demo {
    @ExcelProperty("编号")
    @TableId(type = IdType.AUTO)
    private Integer id;
    @ExcelProperty("标题")
    private String title;
    @ExcelProperty("内容")
    private String content;
}

这样就基本实现了数据分页导出了。

但是需要注意的是：在使用分页查询需要注意深分页问题，否则会导致导出效率非常低。而分页查询效率取决于表设计，如果设计的不好，就无法执行高效的分页查询。因此流式查询是一个数据库访问框架必须具备的功能。

2.流式查询实现大数据量导出。

（1）定义一个mapper

 @Select("select * from data t ${ew.customSqlSegment}")
 @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = -2147483648)
 @ResultType(User.class)
 void exportData(@Param(Constants.WRAPPER) QueryWrapper<Demo> wrapper, ResultHandler<Demo> handler);

（2）通过service来实现调用逻辑，示例如下：

QueryWrapper<Demo> wrapper = new QueryWrapper<Demo>();

    demoMapper.exportData(wrapper,resultContext -> {

        Demo data = resultContext.getResultObject();
        //这边循环调用就可以实现导出相关业务了
        System.out.println(data);
    });

这种方式就大致可以实现流式导出。

因此，使用流式查询导出可以有效避免大数据量导出时的OOM问题，但是流式查询需要注意长时间占用数据库连接的问题。