What are the requirements and pitfalls of returning Stream<T> from a Spring Data JPA repository method?
answer
- Stream<T> = lazy cursor, not a List
- must run inside open @Transactional
- try-with-resources / stream.close()
- detach/clear or readOnly to bound heap
- MySQL fetch size Integer.MIN_VALUE
basics
~20 sA repository method can return Stream<T> to read rows lazily one at a time instead of loading them all. You must run it inside an open transaction and close the stream (try-with-resources), because it keeps a database cursor open.
solid answer
~40 sSpring Data JPA lets a query method return `Stream<T>`, backed by a scrollable JDBC `ResultSet`/cursor so rows are fetched lazily rather than materialized into a `List`. Two hard requirements: (1) it must execute within an **open transaction** — the underlying connection/cursor has to stay open while you consume the stream, so you need `@Transactional` (often `readOnly = true`) around the consuming code; and (2) you **must close** the stream to release the cursor and connection — use try-with-resources or an explicit `stream.close()`, ideally in a `finally`. Because the persistence context accumulates every entity you touch, you should periodically `entityManager.detach(...)`/`clear()` (or load read-only) to avoid a growing heap and dirty-checking cost. In practice you pair streaming with `@QueryHints` fetch size and read-only. Streaming shines for large exports/batch processing where a full `List` would exhaust memory.
code
java · 26 linespublic interface OrderRepository extends JpaRepository<Order, Long> {
@QueryHints({
@QueryHint(name = "org.hibernate.fetchSize", value = "1000"),
@QueryHint(name = "org.hibernate.readOnly", value = "true")
})
@Query("select o from Order o where o.status = :status")
Stream<Order> streamByStatus(@Param("status") OrderStatus status);
}
@Service
class ExportService {
private final OrderRepository repo;
private final EntityManager em;
ExportService(OrderRepository repo, EntityManager em) { this.repo = repo; this.em = em; }
@Transactional(readOnly = true) // tx must span consumption
void export(OrderStatus status, Writer out) {
try (Stream<Order> orders = repo.streamByStatus(status)) { // must close
orders.forEach(o -> {
writeRow(out, o);
em.detach(o); // keep persistence context small
});
}
}
}go deeper
Know Stream<T> reads rows lazily instead of loading a full List.
State the two rules: open transaction for consumption and close the stream via try-with-resources.
Also manage persistence-context growth (detach/clear, readOnly) and pair with fetch-size hints.
Own the batch/export strategy: transaction scoping, connection-pool safety, MySQL fetch-size quirk, and when streaming beats paging/keyset.
## Why streaming exists A normal query method returns a `List<T>` — Spring/Hibernate loads **all** rows into memory before you touch any. For a huge result set (exports, batch jobs) that can exhaust the heap. Returning **`java.util.stream.Stream<T>`** instead lets you process rows **lazily**: Hibernate scrolls a JDBC cursor and produces entities one (or a fetch-batch) at a time. ## How Spring Data implements it The method signature simply declares `Stream<T>`: ```java @Query("select o from Order o") Stream<Order> streamAll(); ``` Behind it, Hibernate uses `ScrollableResults` / a forward-only cursor. Nothing is materialized up front. ## Requirement 1 — an open transaction The stream holds an **open database cursor and connection** for its whole lifetime. That connection must remain open while you iterate, which means the consuming code must run inside a **transaction**. If you call a streaming method with no active transaction (or the tx ends before you finish consuming), the connection may be returned to the pool / closed and you get errors or a closed-stream. So wrap consumption in `@Transactional` — typically `@Transactional(readOnly = true)` for exports. Crucially, the transaction must span the *consumption*, not just the repository call; consuming a stream returned from a method whose tx already committed is a classic bug. ## Requirement 2 — close the stream Because the cursor/connection is held open, you **must close the Stream** to free those resources. `Stream` is `AutoCloseable`, so use **try-with-resources**: ```java try (Stream<Order> orders = repo.streamAll()) { orders.forEach(this::process); } ``` Forgetting to close leaks the cursor and connection — over time this exhausts the connection pool. `stream.forEach` alone does NOT close it. ## Requirement 3 — manage the persistence context Every managed entity the stream produces is added to the `EntityManager`'s persistence context (first-level cache) and, unless read-only, gets a dirty-check snapshot. Over millions of rows this grows the heap and slows flush. Mitigate by: - loading **read-only** (`org.hibernate.readOnly` hint / `@Transactional(readOnly=true)`), and/or - periodically calling `entityManager.detach(entity)` or `entityManager.clear()`. ## Pairing with query hints Streaming is almost always combined with **`@QueryHints`**: - `org.hibernate.fetchSize` — controls how many rows the JDBC driver pulls per round trip while scrolling. On **MySQL**, row-by-row streaming specifically needs fetch size `Integer.MIN_VALUE` (a driver quirk); a positive value there still buffers the whole result. - `org.hibernate.readOnly` — avoids snapshots for the streamed entities. ## Gotchas summary - No transaction around consumption → connection closed / errors. - Not closing the stream → leaked cursor + connection, pool exhaustion. - Growing persistence context → OOM; detach/clear or go read-only. - MySQL needs `Integer.MIN_VALUE` fetch size to truly stream. - Don't return the stream up the call stack past the transaction boundary; consume it inside the transactional method. ## When to use Use `Stream<T>` for large, sequential read/export/batch workloads where holding the whole result in memory is unacceptable. For ordinary page-sized reads, a `List` or `Page` is simpler and safer.
- What happens if you consume a Stream<T> outside any transaction?The underlying connection/cursor isn't guaranteed to stay open — you typically get an exception (closed connection/stream) or the connection is returned to the pool mid-read. Consumption must occur inside an open transaction, usually @Transactional(readOnly=true).
- Why must you close the stream, and does forEach do it for you?The stream holds an open JDBC cursor and DB connection; closing releases them. forEach does NOT close the stream — you need try-with-resources or an explicit close(), or you leak connections and exhaust the pool.
- How do you stop the persistence context from growing while streaming millions of rows?Load read-only (readOnly hint / readOnly tx) so no dirty-check snapshots are kept, and periodically call entityManager.detach(entity) or clear() to evict processed entities.
saying these in an interview costs you the question
- Returning Stream<T> and consuming it after the transactional method returns
- Relying on forEach to close the stream (it doesn't)
- No @Transactional around stream consumption
- Ignoring persistence-context growth, causing OutOfMemoryError
- Assuming a positive fetchSize streams row-by-row on MySQL (needs Integer.MIN_VALUE)