Export Millions of MySQL Records with SpringBoot Without OOM

Dynamic data export is a common feature in projects; the basic logic queries MySQL, loads data into memory, then creates an Excel or CSV file and streams it to the front end.

Reference: https://grokonez.com/spring-framework/spring-boot/excel-file-download-from-springboot-restapi-apache-poi-mysql

When the data volume reaches hundreds of thousands or millions of rows, loading all records into memory inevitably triggers an OutOfMemoryError. To avoid OOM, two main ideas are considered.

Question the necessity of exporting such massive data and the design rationale.

Enforce strict permission control to prevent leakage of sensitive information.

Consider whether the data should be handled by a DBA or delivered via email instead of a real‑time export.

Evaluate the cost of time and traffic for backend‑driven export.

Use pagination (e.g., 20,000 rows per request) if it satisfies business needs.

The core principle is: do not load the entire dataset into memory at once.

MySQL supports stream queries, allowing rows to be fetched one by one and written directly to a file, releasing each row from memory after it is processed. Because CSV handles large row counts better than Excel (Excel 2007 caps at 1,048,576 rows), CSV is recommended for million‑row exports.

JPA implementation for million‑row export

Reference: http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html

Key annotation to add to the repository:

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

Mark the streaming method as read‑only: @Transactional(readOnly = true) Inject EntityManager and detach each processed entity to free memory:

@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(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 for million‑row export

MyBatis requires a custom ResultHandler and the fetchSize="-2147483648" attribute in the mapper XML to enable streaming.

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

Mapper interface:

public interface AuthorsMapper {
    List<Authors> selectByExample(AuthorsExample example);
    List<Authors> streamByExample(AuthorsExample example); // stream fetch
}

Relevant XML snippet (stream version adds fetchSize="-2147483648"):

<select id="streamByExample" fetchSize="-2147483648" parameterType="com.alphathur.mysqlstreamingexport.domain.AuthorsExample" resultMap="BaseResultMap">
    select ... from authors
</select>

Service layer uses the custom handler to stream data:

@Service
public class AuthorsService {
    private final SqlSessionTemplate sqlSessionTemplate;
    private final AuthorsMapper authorsMapper;
    public void streamDownload(HttpServletResponse response) throws IOException {
        AuthorsExample example = new AuthorsExample();
        example.createCriteria();
        Map<String, Object> param = new HashMap<>();
        param.put("oredCriteria", example.getOredCriteria());
        param.put("orderByClause", example.getOrderByClause());
        CustomResultHandler handler = new CustomResultHandler(new DownloadProcessor(response));
        sqlSessionTemplate.select("com.alphathur.mysqlstreamingexport.mapper.AuthorsMapper.streamByExample", param, handler);
        response.getWriter().flush();
        response.getWriter().close();
    }
    public void traditionDownload(HttpServletResponse response) throws IOException {
        AuthorsExample example = new AuthorsExample();
        example.createCriteria();
        List<Authors> authors = authorsMapper.selectByExample(example);
        DownloadProcessor processor = new DownloadProcessor(response);
        authors.forEach(processor::processData);
        response.getWriter().flush();
        response.getWriter().close();
    }
}

Controller exposing the two endpoints:

@RestController
@RequestMapping("download")
public class HelloController {
    private final AuthorsService authorsService;
    public HelloController(AuthorsService authorsService) { this.authorsService = authorsService; }
    @GetMapping("streamDownload")
    public void streamDownload(HttpServletResponse response) throws IOException {
        authorsService.streamDownload(response);
    }
    @GetMapping("traditionDownload")
    public void traditionDownload(HttpServletResponse response) throws IOException {
        authorsService.traditionDownload(response);
    }
}

Table schema for the authors table:

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Performance testing shows the traditional download peaks at ~2.5 GB memory usage, while the streaming approach peaks at ~500 MB, reducing memory consumption by about 80%.