Import from Database (SQL)

Overview

Import data from an external SQL database into Pricefx using the pfx-sql component. The route queries the database, maps the result set to Pricefx fields, and loads the data. Supports MySQL, PostgreSQL, Oracle, and MSSQL.

Files

routes/import-products-from-db.xml

XML

<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="import-products-from-db">
        <from uri="timer://dbImport?repeatCount=1"/>
        <log message="Starting database import" loggingLevel="INFO"/>

        <to uri="pfx-sql:select?sql={{db.product.query}}&amp;dataSource=externalDb&amp;dialect={{db.dialect}}"/>
        <to uri="pfx-api:loaddata?objectType=P&amp;mapper=import-products-from-db.mapper&amp;businessKeys=sku"/>

        <log message="Database import complete" loggingLevel="INFO"/>
    </route>
</routes>

routes/import-products-from-db-batched.xml (Large Datasets)

For databases with 50k+ rows, use pfx-sql:selectIterator with split to avoid loading all records into memory:

XML

<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="import-products-from-db-batched">
        <from uri="timer://dbImportBatched?repeatCount=1"/>
        <log message="Starting batched database import" loggingLevel="INFO"/>

        <to uri="pfx-sql:selectIterator?sql={{db.product.query}}&amp;dataSource=externalDb&amp;dialect={{db.dialect}}&amp;batchSize=5000"/>
        <split>
            <simple>${body}</simple>
            <to uri="pfx-sql:selectIterator?dataSource=externalDb"/>
            <to uri="pfx-api:loaddata?objectType=P&amp;mapper=import-products-from-db.mapper&amp;businessKeys=sku"/>
        </split>

        <log message="Batched database import complete" loggingLevel="INFO"/>
    </route>
</routes>

mappers/import-products-from-db.mapper.xml

XML

<mappers>
    <loadMapper id="import-products-from-db.mapper">
        <body in="PART_NUMBER" out="sku"/>
        <body in="DESCRIPTION" out="label"/>
        <body in="UNIT_OF_MEASURE" out="unitOfMeasure"/>
        <body in="CURRENCY_CODE" out="currency"/>
        <body in="PRODUCT_GROUP" out="attribute1"/>
        <body in="CATEGORY" out="attribute2"/>
        <body in="LIST_PRICE" out="attribute3" converterExpression="stringToDecimal"/>
        <body in="STATUS" out="attribute4"/>
    </loadMapper>
</mappers>

config/application.properties (additions)

# Database connection (Spring DataSource bean named "externalDb")
integration.connections.externalDb.type=jdbc
integration.connections.externalDb.url=jdbc:{{db.type}}://{{db.host}}:{{db.port}}/{{db.name}}
integration.connections.externalDb.username={{db.username}}
integration.connections.externalDb.password={{db.password}}

# SQL dialect: MYSQL, POSTGRESQL, ORACLE, MSSQL
db.dialect=POSTGRESQL
db.host=db.example.com
db.port=5432
db.name=erp_system
db.type=postgresql
db.username=${DB_USERNAME}
db.password=${DB_PASSWORD}

# SQL query
db.product.query=SELECT PART_NUMBER, DESCRIPTION, UNIT_OF_MEASURE, CURRENCY_CODE, PRODUCT_GROUP, CATEGORY, LIST_PRICE, STATUS FROM products WHERE active = true

How It Works

Timer Trigger: timer://dbImport?repeatCount=1 fires once on startup. For scheduled runs, use scheduler://dbImport?delay=3600000 (hourly) or quartz://db/import?cron=0+0+6+*+*+? (daily at 6 AM).
SQL Select: pfx-sql:select executes the configured SQL query against the externalDb data source. The dialect parameter ensures correct SQL generation for your database.
Result Mapping: The query result (a List<Map>) is passed to pfx-api:loaddata. The mapper maps database column names (uppercase by convention) to Pricefx field names.
Upsert: businessKeys=sku ensures existing products are updated rather than duplicated.

Supported Databases

Dialect	JDBC URL Pattern
`MYSQL`	`jdbc:mysql://host:3306/dbname`
`POSTGRESQL`	`jdbc:postgresql://host:5432/dbname`
`ORACLE`	`jdbc:oracle:thin:@host:1521:sid`
`MSSQL`	`jdbc:sqlserver://host:1433;databaseName=dbname`

Common Pitfalls

Column name case: Database columns are typically uppercase (PART_NUMBER), while Pricefx fields are camelCase (sku). The mapper in attribute must match the exact column name returned by the query.
DataSource bean name: The dataSource=externalDb parameter must match the connection name configured in integration.connections.externalDb.* properties.
Large result sets: Without batching, pfx-sql:select loads all rows into memory. Use pfx-sql:selectIterator with split for 50k+ rows.
Connection pooling: IM manages connection pooling internally. Do not configure external connection pool settings.
Credentials: Never hardcode database credentials. Use ${ENV_VAR} for environment variables or {{property}} for IM property placeholders.
SQL injection: The sql parameter is a static query string -- it does not support parameter binding. For dynamic queries, use a Groovy processor to build the query safely.