Vaadin中分批读取MySQL海量数据导出PDF的方法

问题背景 #

MySQL中存储了20亿条Position实体记录，通过Vaadin框架的懒加载+筛选功能在Grid中展示，核心代码如下：

private void showData(Filter filter) {
    positionGrid.setItems(query -> {
        var vaadinSortOrders = query.getSortOrders();
        var springSortOrders = new ArrayList<Sort.Order>();
        for (QuerySortOrder so : vaadinSortOrders) {
            String colKey = so.getSorted();
            if (so.getDirection() == SortDirection.ASCENDING) {
                springSortOrders.add(Sort.Order.asc(colKey));
            } else if (so.getDirection() == SortDirection.DESCENDING) {
                springSortOrders.add(Sort.Order.desc(colKey));
            }
        }
        return positionService.getAll(
                filter,
                PageRequest.of(query.getPage(),
                        query.getPageSize(),
                        Sort.by(springSortOrders))
        ).stream();
    });
}

尝试用itextpdf导出PDF时，调用positionGrid.getLazyDataView().getItems().forEach()出现java.lang.OutOfMemoryError: Java heap space，导出核心代码：

positionGrid.getLazyDataView().getItems().forEach(elem -> {
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(String.valueOf(num.incrementAndGet())).setTextAlignment(TextAlignment.CENTER)));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getSsi().toString())).setTextAlignment(TextAlignment.CENTER));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(PositionGrid.formatter.format(elem.getDatetime())).setTextAlignment(TextAlignment.CENTER)));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getPosx().toString())).setTextAlignment(TextAlignment.CENTER));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getPosy().toString())).setTextAlignment(TextAlignment.CENTER));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph((elem.getVelocity() != null) ? elem.getVelocity().toString() : "")).setTextAlignment(TextAlignment.CENTER));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph((elem.getRssi() != null) ? elem.getRssi().toString() : "")).setTextAlignment(TextAlignment.CENTER));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph((elem.getPathDelay() != null) ? elem.getPathDelay().toString() : "")).setTextAlignment(TextAlignment.CENTER));
    table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph((elem.getBsId() != null) ? elem.getBsId().toString() : "")).setTextAlignment(TextAlignment.CENTER));
});

Position实体类代码：

@Entity
@Table(name = "Positions")
@Getter
@Setter
public class Position {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "datetime", nullable = false)
    private Instant datetime;

    @Column(name = "posx", nullable = false)
    private Double posx;

    @Column(name = "posy", nullable = false)
    private Double posy;

    @Column(name = "velocity")
    private Double velocity;

    @Column(name = "rssi")
    private Short rssi;

    @Column(name = "pathDelay")
    private Short pathDelay;

    @Column(name = "bs_id")
    private Integer bsId;

    @Column(name = "ssi", nullable = false)
    private Long ssi;
}

解决思路 #

核心是放弃使用Vaadin LazyDataView的全量数据拉取，直接复用服务层的分页查询逻辑，分批读取数据并逐批写入PDF，避免一次性加载所有数据到内存。

具体实现步骤 #

1. 复用筛选与排序条件 #

从Vaadin Grid的LazyDataView中获取当前的筛选器和排序规则，转换成Spring Data的Pageable参数，和Grid展示的逻辑保持一致。

2. 分批查询+逐批写入PDF #

设置合理的分页大小（比如1000条/批，可根据内存情况调整），循环查询每一页数据，处理完后立即写入PDF，然后释放当前批次的内存。

3. 优化PDF写入逻辑 #

确保itextpdf的Document和PdfWriter始终打开，不要每批重建；每处理完一批数据后，可手动触发Minor GC（可选），减少内存占用。

示例代码 #

private void exportToPdf(Filter filter, List<QuerySortOrder> vaadinSortOrders, OutputStream outputStream) throws DocumentException {
    // 转换排序规则为Spring Data Sort
    List<Sort.Order> springSortOrders = new ArrayList<>();
    for (QuerySortOrder so : vaadinSortOrders) {
        String colKey = so.getSorted();
        if (so.getDirection() == SortDirection.ASCENDING) {
            springSortOrders.add(Sort.Order.asc(colKey));
        } else {
            springSortOrders.add(Sort.Order.desc(colKey));
        }
    }
    Sort sort = Sort.by(springSortOrders);

    // 初始化PDF文档
    Document document = new Document(PageSize.A4.rotate());
    PdfWriter.getInstance(document, outputStream);
    document.open();

    // 创建PDF表格（和Grid列对应）
    Table table = new Table(9);
    table.setWidth(100);
    // 添加表头
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("序号")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("SSI")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("时间")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("POS X")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("POS Y")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("速度")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("RSSI")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("路径延迟")));
    table.addHeaderCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph("基站ID")));
    document.add(table);

    AtomicInteger num = new AtomicInteger(0);
    int pageSize = 1000; // 每批处理1000条，可根据内存调整
    int page = 0;
    Page<Position> positionPage;

    // 循环分批查询并写入PDF
    do {
        PageRequest pageRequest = PageRequest.of(page, pageSize, sort);
        positionPage = positionService.getAll(filter, pageRequest);

        // 处理当前批次数据
        positionPage.getContent().forEach(elem -> {
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(String.valueOf(num.incrementAndGet()))));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getSsi().toString())));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(PositionGrid.formatter.format(elem.getDatetime()))));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getPosx().toString())));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getPosy().toString())));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getVelocity() != null ? elem.getVelocity().toString() : "")));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getRssi() != null ? elem.getRssi().toString() : "")));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getPathDelay() != null ? elem.getPathDelay().toString() : "")));
            table.addCell(new Cell().setTextAlignment(TextAlignment.CENTER).add(new Paragraph(elem.getBsId() != null ? elem.getBsId().toString() : "")));
        });

        // 强制写入当前批次内容到输出流，释放内存
        document.flush();
        // 清空当前批次的列表，帮助GC回收
        positionPage.getContent().clear();
        page++;

        // 可选：手动触发Minor GC，减少内存占用
        System.gc();
    } while (!positionPage.isLast());

    // 关闭PDF文档
    document.close();
}

额外优化建议 #

• 数据库索引优化：确保Position表的筛选、排序字段（如datetime、ssi、bs_id等）建立合适的索引，避免全表扫描，加快分页查询速度。
• 调整分页大小：根据服务器内存情况调整pageSize，内存充足可适当增大（如5000），内存紧张则减小（如500）。
• 使用流式查询：如果Spring Data JPA支持，可使用Stream<Position>代替Page<Position>，进一步减少内存占用（注意关闭Stream）。
• 异步导出：20亿条数据导出耗时较长，建议将导出逻辑放到异步任务中，避免阻塞Vaadin UI线程。

内容的提问来源于stack exchange，提问作者Алексей Жуков