基于Spring Data JPA流式导出百万级PostgreSQL数据至Excel

问题根源分析 #

1. 重复写入工作簿：在forEach循环里每次调用workbook.write(outputStream)会重复输出整个工作簿，破坏流结构导致后续写入失败。
2. JPA Stream未正确管理：JPA返回的Stream绑定数据库连接，必须确保关闭否则会造成连接泄漏，且事务边界需覆盖Stream生命周期。
3. 常规POI不适合大数据量：HSSF/XSSFWorkbook会把所有数据加载到内存，百万级数据会触发OOM，必须使用SXSSFWorkbook（POI的流式实现）。

具体修复方案 #

1. 仓库层：保持JPA Stream配置 #

现有BookRepository配置无需改动，注意HINT_FETCH_SIZE值与PostgreSQL游标配置匹配即可：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import org.springframework.data.repository.CrudRepository;
import javax.persistence.QueryHint;
import static org.hibernate.jpa.QueryHints.HINT_FETCH_SIZE;

public interface BookRepository extends CrudRepository<Book, Long> {
    @QueryHints(value = {
        @QueryHint(name = HINT_FETCH_SIZE, value = "10000")
    })
    @Query("select b from Book")
    Stream<Book> getAll();
}

2. 控制器层：正确管理流与Excel写入 #

使用try-with-resources自动关闭JPA Stream和SXSSF资源，仅在所有数据写入完成后一次性写出工作簿：

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.stream.Stream;

@Transactional(readOnly = true)
public void exportBooksToExcel(HttpServletResponse response) throws IOException {
    // 设置响应头，指定输出为Excel文件
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=\"books.xlsx\"");

    // SXSSFWorkbook设置内存保留行数，超出部分写入临时文件避免OOM
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
         Stream<Book> bookStream = bookRepository.getAll()) { // 自动关闭JPA Stream

        Sheet sheet = workbook.createSheet("Books");
        // 写入表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("书名");
        headerRow.createCell(2).setCellValue("作者");

        // 流式遍历数据，逐行写入Excel
        int rowNum = 1;
        bookStream.forEach(book -> {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(book.getId());
            row.createCell(1).setCellValue(book.getTitle());
            row.createCell(2).setCellValue(book.getAuthor());
        });

        // 所有数据写入完成后，一次性输出到响应流
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
    }
}

3. 关键注意事项 #

• 事务边界：@Transactional(readOnly = true)必须覆盖整个Stream的处理过程，否则JPA会提前释放数据库连接导致Stream关闭。
• SXSSF临时文件：SXSSF默认将超出内存的行写入系统临时目录，可通过SXSSFWorkbook.setTempFileCreationStrategy自定义路径，避免磁盘空间不足。
• PostgreSQL游标配置：Spring Boot项目需在application.properties中补充配置，确保fetchSize生效：

  spring.datasource.hikari.data-source-properties.fetchSize=10000
  

内容的提问来源于stack exchange，提问作者Veerendra