pfx-sql:upsert

Summary: Insert or update data in an external SQL database table based on business keys (conflict resolution).

URI Format

pfx-sql:upsert?table=myTable&businessKeys=sku&dataSource=myDb

Parameters

Parameter	Type	Default	Required	Description
`dataSource`	String	`dataSource`	No	Name of the Spring DataSource bean
`table`	String		Yes	Target database table
`mapper`	String		No	Mapper ID for field transformation
`businessKeys`	String		Yes	Comma-separated business key columns used for conflict resolution (determines whether to insert or update)
`dialect`	SQLDialect	`MYSQL`	No	SQL dialect
`batchSize`	Integer	`5000`	No	Batch size for database writes

How It Works

For each row, the component checks whether a record with the same businessKeys values already exists in the table.
If a match is found, the existing record is updated.
If no match is found, a new record is inserted.

This is equivalent to INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or MERGE (Oracle/MSSQL) or INSERT ... ON CONFLICT DO UPDATE (PostgreSQL).

Examples

Basic Upsert by SKU

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>

Upsert with Composite Business Key

XML

<route id="upsertComposite">
    <from uri="timer://export?repeatCount=1"/>
    <to uri="pfx-api:fetch?objectType=CFLP"/>
    <to uri="pfx-sql:upsert?table=price_list&amp;businessKeys=sku,region&amp;dataSource=myDb&amp;dialect=POSTGRESQL"/>
</route>

Truncate Then Upsert Pattern

XML

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

Common Pitfalls

Problem	Cause	Fix
All rows inserted (no updates)	`businessKeys` don't match existing unique constraint	Ensure `businessKeys` columns match the table's unique index
Missing `businessKeys`	Required parameter not provided	Add `businessKeys=col1,col2` to the URI
Performance issues on large datasets	Too many rows in a single upsert	Adjust `batchSize` or pre-filter the data
Wrong columns updated	Mapper not aligned with table schema	Review and correct the mapper field mappings

See Also

pfx-sql Component — Parent reference
pfx-sql:insert — Simple insert (no conflict resolution)