Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesUse SQL or JPQL to filter, join, sort, and select data in the database; use Java Streams to process the rows your application needs. With JDBC or JPA, consume the stream while its connection and transaction remain open, then close it promptly. A Stream does not by itself guarantee lazy database fetching, so check the driver or persistence provider’s behavior rather than assuming rows arrive one at a time.
What Java Streams do—and what belongs in the database
A Java Stream is an application-side pipeline over results. It does not replace SQL or JPQL, nor does it automatically turn a query into a database-side operation. Put filtering, joins, ordering, and projection in the query so the database can return only the rows and columns the application needs. Use stream operations for subsequent application-level work.
Streaming can avoid first materializing an entire result as a Java list, but it is not a guarantee of bounded memory or lazy fetching: behavior depends on the JDBC driver or JPA provider, and downstream operations may also retain data. Avoid collecting a large, unbounded result unless that memory use is intentional.
Query rows with JDBC
JDBC exposes query results as a ResultSet cursor. Its first next() call advances to the first row, and the result set is AutoCloseable. The connection, prepared statement, and result set are resources that must remain open while the stream is being consumed.
String sql = "SELECT id, email FROM customer WHERE active = ?";
try (Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setBoolean(1, true);
statement.setFetchSize(200); // Driver hint, not a guaranteed batch size.
try (ResultSet resultSet = statement.executeQuery()) {
Stream<CustomerRow> rows = StreamSupport.stream(
Spliterators.spliteratorUnknownSize(
new Iterator<CustomerRow>() {
private boolean ready;
@Override
public boolean hasNext() {
if (!ready) {
try {
ready = resultSet.next();
} catch (SQLException e) {
throw new UncheckedSQLException(e);
}
}
return ready;
}
@Override
public CustomerRow next() {
if (!hasNext()) throw new NoSuchElementException();
ready = false;
try {
return new CustomerRow(
resultSet.getLong("id"),
resultSet.getString("email")
);
} catch (SQLException e) {
throw new UncheckedSQLException(e);
}
}
}, Spliterator.ORDERED | Spliterator.NONNULL),
false
);
// Consume inside both resource scopes.
rows.forEach(this::process);
}
}This illustrates the lifetime rule, not a universal helper API: production code should define an appropriate exception wrapper (or use an existing utility) and handle SQL failures according to the application’s conventions. Map each current row to an immutable DTO before advancing the cursor; do not expose a row object whose values depend on a cursor that will move or close.
The nested try-with-resources scopes close the result set, statement, and connection when consumption finishes or an exception exits the scope. Do not return a stream from a method after those scopes have closed. If an API deliberately returns a resource-backed stream, its close path must close the underlying JDBC resources, and callers must consume and close it deterministically.
Rank #2
Use JPA or Hibernate query streams carefully
Jakarta Persistence defines Query.getResultStream() as executing a SELECT query and returning an untyped java.util.stream.Stream. But the specification allows the default implementation to delegate to getResultList().stream(); a provider may override it to offer additional capabilities. The method name alone therefore does not establish that rows are fetched lazily from the database. See the Jakarta Persistence specification and API.
For Hibernate, close the stream after processing. Its query API documentation says, “The client should call BaseStream.close() after processing the stream so that resources are freed as soon as possible.” Hibernate’s migration guidance also emphasizes explicit closure to avoid resource leakage. See the Hibernate Query API Javadocs and Hibernate 6 migration guide.
Free tools Windows power users keep installed
One-click scans. No signup required.
try (Stream<Customer> customers = entityManager
.createQuery("select c from Customer c where c.active = true", Customer.class)
.getResultStream()) {
customers.forEach(this::process);
}Keep the transaction and persistence context open for the full consumption period. If processing traverses lazy relationships, do so while the context is still available; accessing them after it closes can fail. Prefer selecting only the columns or entities needed instead of loading a broad object graph for a small downstream task.
JDBC and JPA/Hibernate compared
| Consideration | JDBC | JPA/Hibernate |
|---|---|---|
| Query and cursor control | Direct SQL and explicit access to JDBC statements and result sets. | JPQL or provider query abstractions; provider manages ORM behavior. |
| Mapping and types | Application maps columns to DTOs or other objects; mapping is explicit. | Can map results to entities or typed query results; behavior depends on the query and provider. |
| Resource lifetime | Connection, statement, and result set must stay open during consumption and then close. | Keep transaction and persistence context alive during consumption; close the stream, especially with Hibernate. |
| Streaming guarantee | A result set is a cursor, but fetch behavior depends on the driver and configuration. | getResultStream() may use getResultList().stream() by default; provider-specific behavior may differ. |
| Fetch-size control | JDBC exposes a fetch-size hint on statements and result sets. | Available controls and their effect depend on the provider and underlying driver. |
| Memory behavior | A cursor-based approach can avoid a full application-side list, subject to driver behavior and downstream processing. | A provider may stream or materialize a list; verify implementation and avoid unbounded collection. |
| Parallel processing | Cursor-backed work is generally best consumed sequentially while resources remain open. | Do not assume parallel traversal is safe or useful with a resource-backed query stream; provider, persistence context, and transaction constraints matter. |
Fetch size: tune it as a hint, not a magic number
JDBC defines Statement.setFetchSize(int) as a hint to the driver about how many rows to fetch when more rows are needed. A value of zero leaves the driver free to choose. Oracle’s JDBC documentation describes fetch size as controlling how many rows are retrieved on each database round trip and allows it to be set on a statement or result set. See the Oracle JDBC ResultSet documentation and the JDBC Statement API.
Rank #4
The same configured value can behave differently across drivers and databases. There is no universal optimal fetch size or general speedup figure established by these API documents. Measure with representative row widths, network latency, query plans, transaction duration, driver version, and the actual terminal operation. Larger fetches may reduce round trips but can change memory use; the right balance is workload-specific.
Quick Recap
Best Value
Keep the pipeline safe and useful
- Push predicates, joins, sort order, and narrow projection into SQL or JPQL.
- Consume a resource-backed stream before its connection, statement, transaction, or persistence context closes.
- Close JDBC resources with try-with-resources; close Hibernate query streams explicitly.
- Map JDBC rows to detached immutable values rather than passing a live cursor dependency downstream.
- Do not parallelize cursor-backed processing by default. First establish that the source, provider, transaction, and processing code support it safely.
- Benchmark fetch-size choices and memory behavior with the real driver and workload instead of treating an example value as a universal recommendation.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
