Upsert Data from CSV to Database Storage

Owned by Ondřej Tesař
Aug 26, 2024
3 min read

This section shows how to upsert data from a simple CSV file to an Oracle database table. Data import can be done in two insertion modes – insert and upsert.

Step by Step

  1. Prepare the database connection as described in Configure Database Connection.

  2. Create a database table that should hold data. Good practice is to save SQL DDL commands to a file which is versioned as well.

  3. Add flatpackDataFormat which describes the CSV file format to the camel-context.xml file.

    <pfx:flatpackDataFormat id="csvFormat" delimiter=","/>

     

  4.  Create loadMapper for transition between CSV columns and database table columns. It is recommended to put it to the mappers-filter.xml file.

    Mapper example

    <loadMapper id="customerToDbMapper"> <body out="CUSTOMER_ID" in="customerId"/> <body out="SALES_ORG" in="salesOrg"/> <body out="DIVISION" in="division"/> </loadMapper>

     

  5. Create a route builder bean which defines transition between the CSV file and database table in camel-context.xml.

    Mapper example

    <bean id="customerBatchImportToDbRoute" class="net.pricefx.integration.route.CsvToDbRoute"> <!-- in case of duplicate keys in source file --> <!-- batchSize must be set to 1 while insert mode is "UPSERT". This is because of --> <!-- deterministic oracle merge statement is used. --> <property name="batchSize" value="1000"/> <property name="completionTimeout" value="500"/> <property name="routeName" value="direct://customerBatchImportToDbRoute"/> <property name="flatpackDataFormat" ref="csvFormat"/> <property name="mapper" ref="customerToDbMapper"/> <property name="statementAndParamsProvider"> <bean class="net.pricefx.integration.db.bean.MapperBasedStatementAndParamsProvider"> <property name="businessKeys" value="CUSTOMER_ID"/> <property name="tableName" value="CUSTOMERS"/> <property name="mapper" ref="customerToDbMapper"/> <property name="dbDialect" value="ORACLE"/> <property name="insertMode" value="UPSERT"/> </bean> </property> <!-- sample: manual definition of insert statement + its params --> <!-- <property name="statementAndParamsProvider"> <bean class="net.pricefx.integration.db.bean.ManuallyDefinedStatementAndParamsProvider"> <property name="insertStatememt"> <value>insert into CUSTOMERS (CUSTOMER_ID,SALES_ORG,DIVISION) values (?,?,?)</value> </property> <property name="params"> <map> <entry key="customerId" value="java.lang.Integer"/> <entry key="salesOrg" value="java.lang.String"/> <entry key="division" value="java.lang.String"/> </map> </property> </bean> </property> --> </bean>

     

  6. Apply the route builder in camelContext in the camel-context.xml file.

    Apply route builder

     

  7. The usage of the route is quite simple. Once you have the CSV file in the route, you can call something like this:



IntegrationManager version 5.8.0