Spring Boot exporting huge database to csv via REST endpoint

Question

I need to build a spring boot application which exposes a REST endpoint to export a huge database table as CSV file with different filter parameters. I am trying to find an efficient solution to this problem.

Currently, I am using spring-data-jpa to query the database table, which returns a list of POJOs. Then write this list to HttpServletResponse as CSV file using Apache Commons CSV. There are couple of issues with this approach. First, it loads all the data into memory. And secondly, it is slow.

I am not doing any business logic with the data, is it necessary to use jpa and entity(POJO) in this case. I feel this is the area where causing the problem.

Responsion

If you can't afford to load all of the data into memory, then you'll need to stream the response yourself. Spring does support OutputStream/Writer as a controller return value. I believe the Servlet 3 support adds more options, but I'm not particularly familiar with them. – Cyclothymia

StreamingResponseBody might be what you're looking for. – Cyclothymia

Put pagination on your endpoint baeldung.com/spring-data-jpa-pagination-sorting . – Hypophosphite

Instead of hitting the HTTP request multiple times by passing page number (as mentioned by @PeterMmm), you can pass the number of items per page to be retrieved from the DB per trip, and write them into your CSV file. This will let you hit one HTTP request, and on your backend code, you will go over of the data that you have per page, and check if you have more pages left in the DB. – Higgs

Answer 1

You can try the new SpringWebflux introduced with Spring 5: https://www.baeldung.com/spring-webflux

Caddie

Answer 2

First create the controller a Flux from DataBuffer:

@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);
}

Now the DataBuffer must be created in my case create it using opencsv with a StringBuffer

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;
    }

Waldgrave