pfx-sql:selectIterator

Summary: Execute a SQL query against an external database with batched iteration, suitable for large datasets that cannot fit in memory at once.

URI Format

pfx-sql:selectIterator?sql=SELECT * FROM myTable&dataSource=myDb&batchSize=5000

Parameters

Parameter	Type	Default	Required	Description
`dataSource`	String	`dataSource`	No	Name of the Spring DataSource bean
`sql`	String		Yes	SQL query to execute
`dialect`	SQLDialect	`MYSQL`	No	SQL dialect
`batchSize`	Integer	`5000`	No	Number of rows to fetch per batch
`batchMode`	Boolean	`false`	No	Enable batch mode for processing

How It Works

The query is executed against the database.
Instead of loading all rows into memory, results are fetched in batches of batchSize rows.
The body is set to an iterator that yields one batch at a time.
Use a Camel <split> to process each batch individually.

This approach keeps memory usage constant regardless of total result set size.

Examples

Batched Select with Split

XML

<route id="batchedSelectFromDb">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:selectIterator?sql=SELECT * FROM products&amp;dataSource=myDb&amp;batchSize=5000"/>
    <split>
        <simple>${body}</simple>
        <to uri="pfx-api:loaddata?objectType=P&amp;mapper=productMapper"/>
    </split>
</route>

Large Dataset with Custom Batch Size

XML

<route id="largeDataset">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:selectIterator?sql=SELECT * FROM transactions&amp;dataSource=myDb&amp;batchSize=10000&amp;dialect=ORACLE"/>
    <split>
        <simple>${body}</simple>
        <to uri="pfx-api:loaddata?objectType=T&amp;mapper=txMapper"/>
    </split>
</route>

Batched Select from PostgreSQL

XML

<route id="pgBatched">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:selectIterator?sql=SELECT * FROM large_table&amp;dataSource=pgDb&amp;batchSize=2000&amp;dialect=POSTGRESQL"/>
    <split>
        <simple>${body}</simple>
        <to uri="pfx-api:loaddata?objectType=P&amp;mapper=pgMapper"/>
    </split>
</route>

Common Pitfalls

Problem	Cause	Fix
Only one batch processed	Missing `<split>` after `selectIterator`	Always wrap downstream processing in a `<split>` block
Slow performance	`batchSize` too small for the dataset	Increase `batchSize` (e.g., 10000 or 20000)
Memory issues despite iterator	`batchSize` too large	Reduce `batchSize` to a value that fits comfortably in memory
Results not split correctly	Wrong split expression	Use `<simple>${body}</simple>` as the split expression

See Also

pfx-sql Component — Parent reference
pfx-sql:select — Simple select (loads all rows at once)