Streaming Large-Scale Data Export in SpringBoot Using JPA and MyBatis to Avoid OOM

Dynamic data export is a common requirement; loading large MySQL result sets into memory for Excel/CSV generation can cause OutOfMemoryError.

To avoid OOM, the article proposes streaming data from MySQL using JPA or MyBatis, writing each record directly to a CSV file and detaching entities after use.

JPA implementation uses a repository method returning Stream<Todo> with @QueryHints(fetchSize = Integer.MIN_VALUE) and @Transactional(readOnly = true); the controller writes each line to the HTTP response and detaches the entity to free memory.

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "" + Integer.MIN_VALUE))
@Query("select t from Todo t")
Stream<Todo> streamAll();

@RequestMapping(value = "/todos.csv", method = RequestMethod.GET)
@Transactional(readOnly = true)
public void exportTodosCSV(HttpServletResponse response) {
    response.addHeader("Content-Type", "application/csv");
    response.addHeader("Content-Disposition", "attachment; filename=todos.csv");
    response.setCharacterEncoding("UTF-8");
    try (Stream<Todo> todoStream = todoRepository.streamAll()) {
        PrintWriter out = response.getWriter();
        todoStream.forEach(rethrowConsumer(todo -> {
            String line = todoToCSV(todo);
            out.write(line);
            out.write("
");
            entityManager.detach(todo);
        }));
        out.flush();
    } catch (IOException e) {
        log.info("Exception occurred " + e.getMessage(), e);
        throw new RuntimeException("Exception occurred while exporting results", e);
    }
}

MyBatis implementation defines a custom ResultHandler and sets fetchSize="-2147483648" in the mapper XML to stream rows without loading them all into memory.

public class DownloadProcessor {
    private final HttpServletResponse response;
    public DownloadProcessor(HttpServletResponse response) {
        this.response = response;
        String fileName = System.currentTimeMillis() + ".csv";
        this.response.addHeader("Content-Type", "application/csv");
        this.response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
        this.response.setCharacterEncoding("UTF-8");
    }
    public <E> void processData(E record) {
        try {
            response.getWriter().write(record.toString());
            response.getWriter().write("
");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

public class CustomResultHandler implements ResultHandler {
    private final DownloadProcessor downloadProcessor;
    public CustomResultHandler(DownloadProcessor downloadProcessor) {
        this.downloadProcessor = downloadProcessor;
    }
    @Override
    public void handleResult(ResultContext resultContext) {
        Authors authors = (Authors) resultContext.getResultObject();
        downloadProcessor.processData(authors);
    }
}

public interface AuthorsMapper {
    List<Authors> selectByExample(AuthorsExample example);
    List<Authors> streamByExample(AuthorsExample example); // fetchSize="-2147483648" in XML
}

The service provides streamDownload (streaming) and traditionDownload (batch) methods; memory‑usage tests show streaming reduces peak memory from about 2.5 GB to roughly 500 MB while producing identical CSV files.

Test data can be generated via stored procedures or downloaded from a provided link, and the article also shows how to monitor memory usage with jconsole.exe.