Java JDBC fetch size: a hint, not a memory guarantee

Operational contract

Use a query predicate and explicit order to define the population. Add a separate maximum-row or pagination contract when a result may be large. The method below reads at most 470 ordered receipt IDs into memory and marks that it is only a page, not a complete report. Some drivers buffer more than the hint or require transaction settings for cursor-based fetching. Measure memory and fetch behavior with the deployed driver before claiming streaming.

Failure case

A depot has 47,000 receipts. The reviewer requests a page of 470 IDs, ordered by stable receipt ID, and carries the last ID into a later keyset query. Asking only for fetch size 47 would still permit a 47,000-row result. The code keeps one page in memory; it does not claim a snapshot across later pages without an isolation policy.

Java code

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ReceiptPageReader {
    public static List<String> pageAfter(Connection connection, String lastId) throws SQLException {
        List<String> receiptIds = new ArrayList<>();
        try (PreparedStatement statement = connection.prepareStatement(
                "SELECT receipt_id FROM receipt_intake WHERE receipt_id > ? ORDER BY receipt_id")) {
            statement.setString(1, lastId);
            statement.setFetchSize(47);
            statement.setMaxRows(470);
            try (ResultSet rows = statement.executeQuery()) {
                while (rows.next()) receiptIds.add(rows.getString(1));
            }
        }
        return receiptIds;
    }
}

Performance and ownership cost

Each page retains at most 470 IDs in O(P) application memory for page size P, while the database may scan or index-search according to its plan. Fetch size affects transport hints rather than the logical limit. Repeated pages require stable ordering, an index strategy, and a policy for rows inserted between calls.

Common Mistakes

• Do not call fetch size a row limit.
• Do not promise constant process memory without testing the driver.
• Do not paginate without a stable order and change policy.

Connected lessons

• Java JDBC PreparedStatement: values stay outside SQL grammar
• Java JDBC transactions: atomic updates and rollback
• Java streams: lazy pipelines and bounded results
• Java JDBC batches: inspect update counts and own rollback
• Java ResultSet: cursor position and SQL NULL are separate states
• Java JDBC generated keys: validate one insert and one returned identity
• Java file, JDBC, and subprocess boundaries quiz
• Advanced Java

Continue with: Java JDBC cursor holdability: choose what commit does to a ResultSet.