OpenCsv's CsvWriter Out of memory in Java heap when exporting large csv file from database query, is there something wrong with my implementation?

Question

I want to export a large amount of records from a csv file. Previously I have implemented using OpenCsv's CsvWriter to load one line into memory each time I write into csv. It did alleviate the problem abit but with even larger amount of records I am still having issues with out of memory.

For now I implemented pagination of database records as a workaround but the out of memory error is in the CsvWriter object.

Here is my implementation (ignore my horrible code):

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void getExtremelyLargeLogFile(HttpServletResponse response) throws ParseException, SQLException {
        SqlSession session = sqlSessionFactory.openSession();
        session.getConnection().setAutoCommit(false);
        
        int batch = 0; 
        
        DateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = "log_" + sdf.format(new Date());
        List<Log> list = // some query like 'select * from table limit 10000 offset 10000*batch;
        String[] titleName = new String[] { "No.", "Modules", "System activities", "Event status",

        buildCsvLineList(response, list, titleName, fileName, "dd-MM-yyyy HH:mm:ss", batch);
        while (!list.isEmpty()) {
            batch++; 
            list = // some query like 'select * from table limit 10000 offset 10000*batch;
            buildCsvLineList(response, list, titleName, fileName, "dd-MM-yyyy HH:mm:ss", batch);
        }

        session.commit();
        session.clearCache();   
        session.close();
    }

    private void buildCsvLineList(HttpServletResponse response, List<Logs> logs, String[] titleName,
            String fileName, String pattern, int batch) {
        OutputStream out = null;
        CSVWriter csvWriter = null;

        try {

            out = response.getOutputStream();
            csvWriter = new CSVWriter(new OutputStreamWriter(out),
                    ',',
                    CSVWriter.NO_QUOTE_CHARACTER,
                    '\\',
                    System.lineSeparator());

            String lastFileName = fileName + ".csv";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
            if (batch == 0) {
            csvWriter.writeNext(titleName);
            }

            for (Logs log : logs) {
                 // some implementation 
                csvWriter.writeNext(line.toArray(new String[0]));
                csvWriter.flush();

            }
        } catch (Exception e) {
            log.error("Export csv exception, errorMessage:{}", e.getMessage(), e);
        } finally {

            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.error("Export Csv exception! errorMessage:{}", e.getMessage());
                }

            }

            if (csvWriter != null) {
                try {
                    csvWriter.flush();
                    csvWriter.close();
                } catch (IOException e) {
                    log.error("Export Csv exception! errorMessage:{}", e.getMessage());
                }

            }
        }
    }

Here is stack trace:

Caused by: java.lang.OutOfMemoryError: Java heap space
        at org.springframework.util.FastByteArrayOutputStream.addBuffer(FastByteArrayOutputStream.java:304)
        at org.springframework.util.FastByteArrayOutputStream.write(FastByteArrayOutputStream.java:125)
        at org.springframework.web.util.ContentCachingResponseWrapper$ResponseServletOutputStream.write(ContentCachingResponseWrapper.java:230)
        at java.base/sun.nio.cs.StreamEncoder.writeBytes(StreamEncoder.java:234)
        at java.base/sun.nio.cs.StreamEncoder.implWrite(StreamEncoder.java:304)
        at java.base/sun.nio.cs.StreamEncoder.implWrite(StreamEncoder.java:282)
        at java.base/sun.nio.cs.StreamEncoder.write(StreamEncoder.java:132)
        at java.base/sun.nio.cs.StreamEncoder.write(StreamEncoder.java:142)
        at java.base/java.io.OutputStreamWriter.write(OutputStreamWriter.java:223)
        at java.base/java.io.Writer.write(Writer.java:249)
        at com.opencsv.CSVWriter.writeNext(CSVWriter.java:91)
        at com.opencsv.AbstractCSVWriter.writeNext(AbstractCSVWriter.java:76)
        at com.opencsv.ICSVWriter.writeNext(ICSVWriter.java:162)

Hence I would like to ask what is wrong with my implementation, if it seems that the entire result set is still being written into csvWriter object? In the long run we would like to implement streaming.

Edit: The filter code. This is a custom filter that will get the request and response body for logging purposes. After this ApplicationFilterChain will do further processing

import jakarta.servlet.FilterChain; 
import jakarta.servlet.ServletException; 
import jakarta.servlet.http.HttpServletRequest; 
import jakarta.servlet.http.HttpServletResponse; 
import lombok.extern.slf4j.Slf4j; 
import org.jetbrains.annotations.NotNull; 
import org.springframework.stereotype.Component; 
import org.springframework.web.filter.OncePerRequestFilter; 
import org.springframework.web.util.ContentCachingRequestWrapper; import org.springframework.web.util.ContentCachingResponseWrapper;

import java.io.IOException; 
import java.io.UnsupportedEncodingException;

@Slf4j 
@Component 

public class LogFilter extends OncePerRequestFilter {

private static final int MAX_PAYLOAD_LENGTH = 2000;

@Override
protected void doFilterInternal(@NotNull HttpServletRequest request, @NotNull HttpServletResponse response, FilterChain filterChain)
        throws ServletException, IOException {

    long startTime = System.currentTimeMillis();
    if (request.getRequestURI().matches("(?i).*csv.*") ) {
        filterChain.doFilter(request, response);
        return;
    }

    ContentCachingRequestWrapper requestWrapper = new ContentCachingRequestWrapper(request);
    ContentCachingResponseWrapper responseWrapper = new ContentCachingResponseWrapper(response);
    filterChain.doFilter(requestWrapper, responseWrapper);
    long timeTaken = System.currentTimeMillis() - startTime;

    String requestBody = getStringValue(requestWrapper.getContentAsByteArray(),
            request.getCharacterEncoding());
    String responseBody = null;

    if (!request.getRequestURI().matches("(?i).*csv.*") || response.getStatus() / 100 != 2) {
        responseBody = getStringValue(responseWrapper.getContentAsByteArray(),
                response.getCharacterEncoding());
    }

        log.info("Client IP: " + clientIp);
        log.info("Finished processing request: {}  {}", request.getMethod(), request.getRequestURI());
        log.info("Query String: " + request.getQueryString());

        // log headers
        log.info("Headers: ");
        request.getHeaderNames().asIterator()
                .forEachRemaining(headerName -> log.info("    {}: {}", headerName, request.getHeader(headerName)));

        log.info("Response Code: " + response.getStatus());


        // if requestBody is > 1000 characters, only print the first 1000 characters and append a "..."
        if (requestBody.length() > MAX_PAYLOAD_LENGTH) {
            requestBody = requestBody.substring(0, MAX_PAYLOAD_LENGTH) + "...[omitted " + (requestBody.length() - MAX_PAYLOAD_LENGTH) + " characters]";
        }
        log.info("Request: " + requestBody);


        // process response body logging
        log.info("Time Taken: {}ms", timeTaken);
        log.info("***************************************************");
    }
    responseWrapper.copyBodyToResponse();
}

@Override
protected boolean shouldNotFilter(HttpServletRequest request) throws ServletException {         
    return request.getRequestURI().matches("csv");      }

}