skip to content

When reading from a database in Spring Batch, when would you choose a cursor-based reader (JdbcCursorItemReader) over a paging reader (JpaPagingItemReader), and what are the trade-offs?

level: seniorimportance: should knowfreq 65%

answer

  1. cursor = 1 query, 1 open ResultSet, streamed
  2. paging = N queries, LIMIT/OFFSET, connection per page
  3. paging needs deterministic ORDER BY
  4. paging is thread/partition safe; cursor is not
  5. JpaPagingItemReader clears persistence context per page

basics

~20 s

Cursor readers stream one big result set over a single open connection/ResultSet — low memory, one query, but the connection stays open and it is not restartable across threads. Paging readers run repeated LIMIT/OFFSET queries per page — connection released between pages, restartable and partition-friendly, but more queries and needs a stable sort.

solid answer

~40 s

JdbcCursorItemReader executes one SQL query and streams rows via a single JDBC ResultSet, moving the cursor forward on each read(). It is memory-efficient (rows aren't all loaded) and issues one query, but it holds one database connection/cursor open for the whole step and isn't safe for multi-threaded or partitioned steps. JpaPagingItemReader (and JdbcPagingItemReader) instead fetches a fixed-size page at a time with separate queries (LIMIT/OFFSET or keyset), releasing the connection between pages. Paging tolerates long-running jobs, works across threads/partitions, and clears the JPA persistence context per page, but issues many queries and requires a deterministic ORDER BY or rows can be skipped/duplicated as data shifts. For huge single-threaded exports, cursor; for concurrent/partitioned or long jobs, paging.

code

java · 23 lines
java
// Cursor: one query, single ResultSet, single-threaded
@Bean
public JdbcCursorItemReader<Order> cursorReader(DataSource ds) {
    return new JdbcCursorItemReaderBuilder<Order>()
            .name("orderCursorReader")
            .dataSource(ds)
            .sql("SELECT id, amount FROM orders WHERE status = 'NEW'")
            .fetchSize(1000)                 // driver buffering hint
            .rowMapper((rs, i) -> new Order(rs.getLong("id"), rs.getBigDecimal("amount")))
            .build();
}

// Paging (JPA): page at a time, restartable, partition-friendly
@Bean
public JpaPagingItemReader<Order> pagingReader(EntityManagerFactory emf) {
    return new JpaPagingItemReaderBuilder<Order>()
            .name("orderPagingReader")
            .entityManagerFactory(emf)
            .pageSize(500)
            // deterministic ORDER BY is essential to avoid skips/dupes across pages
            .queryString("SELECT o FROM Order o WHERE o.status = 'NEW' ORDER BY o.id")
            .build();
}

go deeper

for a junior

Knows both read from a DB; fuzzy on trade-offs.

for a middle

Can name the readers and that paging uses pages.

for a senior

Articulates connection lifetime, query count, ordering requirement, thread-safety, JPA context clearing.

for a principal

Ties choice to partitioning strategy, connection-pool pressure, keyset vs OFFSET, and driver fetchSize behavior at scale.

### The two strategies Spring Batch offers two families of relational readers: **Cursor-based** — `JdbcCursorItemReader`, `HibernateCursorItemReader`, `JpaCursorItemReader`, `StoredProcedureItemReader`. **Paging-based** — `JdbcPagingItemReader`, `JpaPagingItemReader`, `HibernatePagingItemReader`, `MongoPagingItemReader`. ### Cursor readers: one query, streamed A cursor reader runs the SQL **once** and keeps a live JDBC `ResultSet` open. Each `read()` calls `ResultSet.next()` and maps the current row (via a `RowMapper` for JDBC). Characteristics: - **Memory:** low — the driver streams rows (subject to the JDBC `fetchSize`, which hints how many rows the driver buffers per round-trip). - **Queries:** exactly one. - **Connection:** a single connection + cursor is held open for the **entire step duration**. Long steps risk connection timeouts and hold DB resources. - **Concurrency:** a `ResultSet` is inherently stateful and **not thread-safe** — cursor readers cannot be safely used in multi-threaded steps or as the reader in local partitioning without extra care. - **Restart:** `JdbcCursorItemReader` tracks the current row count in the `ExecutionContext`; on restart it re-runs the query and skips forward to that row (`setDriverSupportsAbsolute`/`setUseSharedExtendedConnection` affect how efficiently). ### Paging readers: many queries, page at a time A paging reader fetches a **fixed page** (`pageSize`) at a time using a bounded query (classically `ORDER BY ... LIMIT ... OFFSET ...`, or keyset/seek). After a page is consumed, it issues the **next** query. Characteristics: - **Memory:** low and bounded by `pageSize`. - **Queries:** many — one per page (N/pageSize queries). - **Connection:** obtained per page and **released between pages**, so no long-held connection — friendlier to connection pools and long jobs. - **Concurrency:** each page is a fresh, self-contained query, so paging readers work well in **multi-threaded** and **partitioned** steps. - **Restart:** the page number / read count is saved in the `ExecutionContext`; a restart resumes at the right page. - **JPA specifics:** `JpaPagingItemReader` **clears the persistence context** after each page, avoiding the memory bloat and stale-entity issues of a growing first-level cache. ### The ordering gotcha (paging) Because paging re-queries with `OFFSET`, results **must have a stable, deterministic `ORDER BY`** on a unique/immutable column. Without it, rows can be **skipped or duplicated** across pages if the underlying data changes (or even due to nondeterministic ordering). Large `OFFSET` values also degrade performance on big tables — **keyset pagination** (WHERE id > lastId) avoids the OFFSET cost; `JdbcPagingItemReader` uses a `PagingQueryProvider` that can express this. ### Choosing | Concern | Cursor | Paging | |---|---|---| | Memory | Low | Low (bounded by page) | | Number of queries | One | Many | | Connection held open | Whole step | Per page | | Multi-threaded / partitioned | No (unsafe) | Yes | | Long-running jobs | Risky (timeouts) | Safe | | Requires deterministic ORDER BY | No | Yes | | JPA context growth | n/a | Cleared per page | **Rule of thumb:** single-threaded large export where you control the connection → cursor. Concurrent/partitioned processing, long jobs, or connection-pool pressure → paging. ### Other gotchas - Set the JDBC `fetchSize` on cursor readers or drivers (notably MySQL) may buffer the **entire** result set in memory, defeating the point. - `JpaCursorItemReader` (added in Batch 4.3) gives a JPA cursor option but still holds the `EntityManager`/connection. - Cursor readers can suffer if the transaction commits mid-step in some setups; `setUseSharedExtendedConnection(true)` keeps the cursor on a connection separate from the chunk transaction.

  • Why must a paging reader use a deterministic ORDER BY?
    Each page is a separate query with an OFFSET. Without a stable, unique ordering, rows can shift between pages, causing some to be read twice and others skipped. A unique immutable column (e.g. primary key) in ORDER BY guarantees consistent paging.
  • What does JpaPagingItemReader do to the persistence context between pages, and why does it matter?
    It clears the EntityManager after each page. Otherwise the first-level cache would accumulate every entity read, causing memory growth and holding stale references — clearing keeps memory bounded and avoids stale-entity surprises.
  • Why can't you safely use JdbcCursorItemReader in a multi-threaded step?
    It wraps a single stateful ResultSet/cursor. Concurrent read() calls would corrupt the cursor position. Paging readers, issuing independent per-page queries, are the safe choice — or wrap the reader in SynchronizedItemStreamReader (which serializes access, losing concurrency benefit).

saying these in an interview costs you the question

  • Claiming cursor readers load the whole result set into memory (they stream, subject to fetchSize)
  • Using a paging reader without a deterministic ORDER BY
  • Assuming any DB reader is thread-safe
  • Thinking paging issues a single query

context

resolving links…