如何分块读取Oracle大BLOB避免Java堆内存溢出？

一、OOM异常的核心原因 #

• Oracle JDBC驱动预读取：默认配置下，Oracle JDBC驱动会将整个BLOB预读取到内存，哪怕调用了getBinaryStream()。这是因为defaultLobPrefetchSize默认值不为-1，触发了全量加载逻辑。
• 响应输出流缓冲：Tomcat等Servlet容器的响应输出流默认会缓冲全部响应数据，直到处理完成才发送给客户端。大BLOB会填满缓冲区，占用大量堆内存。
• Blob资源未及时回收：即使使用了try-with-resources，部分场景下驱动的Blob对象内存可能未即时释放，后续执行deleteRecord时，之前的内存堆积触发OOM。

二、正确的分块读取实现方案 #

1. 禁用Oracle JDBC的BLOB预读取 #

在数据源配置中添加参数，强制驱动流式读取BLOB：

# application.properties 示例
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultLobPrefetchSize=-1

或者在代码中给PreparedStatement设置流式获取属性：

preparedStatement.setFetchSize(Integer.MIN_VALUE);

2. 优化流处理逻辑，避免内存堆积 #

调整代码，确保数据边读边发，强制刷新输出流，同时显式释放Blob资源：

public void datasetDownload(String id, HttpServletResponse response) throws SQLException, IOException {
    try (Connection connection = dataSource.getConnection()) {
        String sql = "SELECT CONTENT FROM MY_TABLE_NAME WHERE ID = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setFetchSize(Integer.MIN_VALUE);
            preparedStatement.setString(1, id);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    // 直接获取二进制流，跳过Blob对象的中间层
                    try (InputStream inputStream = resultSet.getBinaryStream("CONTENT");
                         OutputStream outputStream = response.getOutputStream()) {
                        // 设置响应头，启用分块传输，禁用容器缓冲
                        response.setContentType("application/zip");
                        response.setHeader("Content-Disposition", "attachment; filename=\"dataset.zip\"");
                        response.setHeader("Transfer-Encoding", "chunked");
                        response.setBufferSize(1024 * 1024);

                        byte[] buffer = new byte[1024 * 1024];
                        int bytesRead;
                        while ((bytesRead = inputStream.read(buffer)) != -1) {
                            outputStream.write(buffer, 0, bytesRead);
                            // 强制刷新，避免数据在内存中堆积
                            outputStream.flush();
                        }
                    } finally {
                        // 显式释放Blob资源
                        Blob blob = resultSet.getBlob("CONTENT");
                        if (blob != null) {
                            blob.free();
                        }
                    }
                    deleteRecord(id, connection);
                    log.info("BLOB download completed");
                }
            }
        }
    }
}

3. 推荐使用Spring StreamingResponseBody #

Spring提供的StreamingResponseBody可以更优雅地处理流式响应，自动适配容器的分块传输：

@GetMapping("/download/{id}")
public ResponseEntity<StreamingResponseBody> datasetDownload(@PathVariable String id) {
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("attachment", "dataset.zip");
    headers.setTransferEncodingChunked(true);

    StreamingResponseBody body = outputStream -> {
        try (Connection connection = dataSource.getConnection()) {
            String sql = "SELECT CONTENT FROM MY_TABLE_NAME WHERE ID = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setFetchSize(Integer.MIN_VALUE);
                preparedStatement.setString(1, id);
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        try (InputStream inputStream = resultSet.getBinaryStream("CONTENT")) {
                            byte[] buffer = new byte[1024 * 1024];
                            int bytesRead;
                            while ((bytesRead = inputStream.read(buffer)) != -1) {
                                outputStream.write(buffer, 0, bytesRead);
                                outputStream.flush();
                            }
                        } finally {
                            Blob blob = resultSet.getBlob("CONTENT");
                            if (blob != null) {
                                blob.free();
                            }
                        }
                        deleteRecord(id, connection);
                        log.info("BLOB download completed");
                    }
                }
            }
        } catch (SQLException | IOException e) {
            log.error("Download failed", e);
            throw new RuntimeException(e);
        }
    };

    return new ResponseEntity<>(body, headers, HttpStatus.OK);
}

三、临时解决方法的原理 #

你用ZipOutputStream规避了OOM，本质是因为压缩过程中数据被即时输出，减少了内存中待发送的原始数据量，但这只是临时方案，没有解决BLOB全量加载的核心问题。

内容的提问来源于stack exchange，提问作者Calu River