pfx-sql Component | Pricefx Knowledge Base

Summary: Reference for the pfx-sql Camel component — database operations (select, insert, upsert, delete, truncate) for external SQL databases.

URI Format

pfx-sql:method?param=value

Producer only (no consumer). Used to read from or write to external SQL databases.

Available Methods

Method	Description
`select`	Execute SQL query and return results
`selectIterator`	Execute SQL query with batched iteration (for large datasets)
`insert`	Insert data into a table
`upsert`	Insert or update data based on business keys
`delete`	Delete records matching a filter
`truncate`	Remove all data from a table

URI Parameters

Parameter	Type	Default	Description
`dataSource`	String	`dataSource`	Name of the Spring DataSource bean
`table`	String		Target database table
`mapper`	String		Mapper ID for field transformation
`batchSize`	Integer	`5000`	Batch size for database fetch
`batchMode`	Boolean	`false`	Enable batch mode for select
`dialect`	SQLDialect	`MYSQL`	SQL dialect (MYSQL, ORACLE, DERBY, POSTGRESQL, SNOWFLAKE)
`sql`	String		SQL query for select operations
`businessKeys`	String		Comma-separated business key columns for upsert
`outputTarget`	String		Where to store results: `body`, `header`, or `property`
`outputTargetName`	String		Name of the header/property for output
`filter`	String		Filter reference for delete operations

Common Patterns

Select Data from Database

XML

<route id="selectFromDb">
    <from uri="timer://dbFetch?repeatCount=1"/>
    <to uri="pfx-sql:select?sql=SELECT * FROM products WHERE active = 1&amp;dataSource=myDb&amp;dialect=MYSQL"/>
    <to uri="pfx-api:loaddata?objectType=P&amp;mapper=productMapper"/>
</route>

Batched Select (Large Datasets)

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>

Insert/Upsert to Database

XML

<route id="exportToDb">
    <from uri="timer://export?repeatCount=1"/>
    <to uri="pfx-api:fetch?objectType=P&amp;filter=allProducts"/>
    <to uri="pfx-sql:upsert?table=products&amp;mapper=dbMapper&amp;businessKeys=sku&amp;dataSource=myDb"/>
</route>

Truncate Table

XML

<to uri="pfx-sql:truncate?table=staging_products&amp;dataSource=myDb"/>

Delete with Filter

XML

<to uri="pfx-sql:delete?table=products&amp;filter=inactiveFilter&amp;dataSource=myDb"/>

DataSource Configuration

Define the DataSource bean in your Spring context or application.properties:

# Example: MySQL DataSource
spring.datasource.url=jdbc:mysql://{{db.host}}:3306/{{db.name}}
spring.datasource.username={{db.username}}
spring.datasource.password={{db.password}}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

For multiple databases, define named DataSource beans and reference them with the dataSource parameter.

SQL Dialects

Dialect	Database
`MYSQL`	MySQL (default)
`ORACLE`	Oracle
`DERBY`	Apache Derby
`POSTGRESQL`	PostgreSQL
`SNOWFLAKE`	Snowflake