Spring Boot通过RESTendpoint将大型数据库导出到csv

Question

我需要构建一个Spring Boot应用程序，它公开一个RESTendpoint，以将一个巨大的数据库表导出为具有不同过滤器参数的CSV文件。我正试图找到一个有效的解决这个问题的办法。

目前，我使用spring数据jpa查询数据库表，该表返回POJO列表。然后使用Apache Commons CSV将此列表作为CSV文件写入HttpServletResponse。这种方法有几个问题。首先，它将所有数据加载到内存中。其次，速度很慢。

我没有对数据进行任何业务逻辑，在这种情况下是否需要使用jpa和实体（POJO）。我觉得这就是造成问题的地方。

Answer 1

首先从DataBuffer创建控制器Flux：

@GetMapping(path = "/report/detailReportFile/{uid}" ,  produces = "text/csv")

public Mono<Void> getWorkDoneReportDetailSofkianoFile (@PathVariable(name = "uid") String uid,
                                                       @RequestParam(name = "startDate", required = false, defaultValue = "0") long start,
                                                       @RequestParam(name = "endDate" , required = false, defaultValue = "0") long end,
                                                       ServerHttpResponse response) {

    var startDate = start == 0 ? GenericData.GENERIC_DATE : new Date(start);

    var endDate = end == 0 ? new Date() : new Date(end);

    response.getHeaders().set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="+uid+".csv");
    response.getHeaders().add("Accept-Ranges", "bytes");

    Flux<DataBuffer> df = queryWorkDoneUseCase.findWorkDoneByIdSofkianoAndDateBetween(uid, startDate, endDate).collectList()
            .flatMapMany(workDoneList -> WriteCsvToResponse.writeWorkDone(workDoneList));

    return response.writeWith(df);
}

现在必须创建DataBuffer，在我的示例中，使用带有StringBuffer的opencsv创建它

public static Flux<DataBuffer> writeWorkDone(List<WorkDone> workDoneList) {

        try {
            StringWriter writer = new StringWriter();

            ColumnPositionMappingStrategy<WorkDone> mapStrategy = new ColumnPositionMappingStrategy<>();

            mapStrategy.setType(WorkDone.class);

            String[] columns = new String[]{"idSofkiano", "nameSofkiano","idProject", "nameProject", "description", "hours", "minutes", "type"};
            mapStrategy.setColumnMapping(columns);

            StatefulBeanToCsv<WorkDone> btcsv = new StatefulBeanToCsvBuilder<WorkDone>(writer)
                    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
                    .withMappingStrategy(mapStrategy)
                    .withSeparator(',')
                    .build();

            btcsv.write(workDoneList);

            return Flux.just(stringBuffer(writer.getBuffer().toString()));

        } catch (CsvException ex) {

            return Flux.error(ex.getCause());
        }
    }


    private static DataBuffer stringBuffer(String value) {
        byte[] bytes = value.getBytes(StandardCharsets.UTF_8);

        NettyDataBufferFactory nettyDataBufferFactory = new NettyDataBufferFactory(ByteBufAllocator.DEFAULT);
        DataBuffer buffer = nettyDataBufferFactory.allocateBuffer(bytes.length);
        buffer.write(bytes);
        return buffer;
    }

Answer 2

您可以尝试Spring 5：https://www.baeldung.com/spring-webflux中引入的新SpringWebFlow