Extract Transform Load (ETL) Example

The ETL (Extract, Transform, Load) example shows how to load data into a database using Camel. In this example we will poll for files, transform them and then store them in the database via the JPA component.


This example lives in the examples/camel-example-etl directory and will poll for XML files in the child src/data directory. When the files are detected, they are converted, using the fallback JAXB Type Converter to a PersonDocument class. This POJO is then transformed using a custom Type Converter into a CustomerEntity bean using the CustomerTransformer class. Then this bean is stored in the database via the JPA component.

The code for this example is as follows

Java Class Purpose
CustomerEntity The JPA entity bean (i.e. a POJO with @Entity)
PersonDocument The JAXB2 POJO used to parse the XML
CustomerTransformer The custom Type Converter used to convert a PersonDocument into a CustomerEntity
EtlRoutes The Camel routing DSL to define the flow from the files to the converter to the JPA endpoint

The there is the spring configuration file in src/resources/META-INF/services/camel-context.xml which defines the JPA template and tells Camel to look in the org.apache.camel.example.etl package to find its routes.

Code walkthrough

So lets start with the route definition in EtlRoutes

public class EtlRoutes extends SpringRouteBuilder {
    public void configure() throws Exception {


        // the following will dump the database to files
            .setHeader(Exchange.FILE_NAME, el("${in.body.userName}.xml"))

The above sets up a route from the src/data directory. Notice we're using the noop mode of the File component so that the files are not moved or deleted when they are processed (so when the tool is restarted they will be processed again).

We're converting the body of the message to a PersonDocument which since this POJO as an @XmlRootElement annotation from JAXB will kick in the Type Converter to use JAXB to unmarshall the object.

Then we send the message with a PersonDocument body to the JPA endpoint. Notice how this endpoint specifies the expected type. So the Type Converter is gonna try convert the PersonDocument to a CustomerEntity. Here Camel will find the CustomerTransformer class which has an @Converter method

public class CustomerTransformer {

    private static final transient Logger LOG = LoggerFactory.getLogger(CustomerTransformer.class);

     * A transformation method to convert a person document into a customer
     * entity
     * @throws Exception 
    public CustomerEntity toCustomer(PersonDocument doc, Exchange exchange) throws Exception {
        JpaTemplate template = exchange.getIn().getHeader("CamelJpaTemplate", JpaTemplate.class);

        String user = doc.getUser();
        CustomerEntity customer = findCustomerByName(template, user);

        // let's convert information from the document into the entity bean

        LOG.debug("Created object customer: " + customer);
        return customer;

     * Finds a customer for the given username
    protected CustomerEntity findCustomerByName(JpaTemplate template, String user) throws Exception {
        List<CustomerEntity> list = CastUtils.cast(template.find("select x from customer"
                                                                 + " x where x.userName = ?1", user));
        if (list.isEmpty()) {
            CustomerEntity answer = new CustomerEntity();
            return answer;
        } else {
            return list.get(0);


which performs the necessary conversion to an entity bean which is then stored in the database

Running the example

To run the example we use the Camel Maven Plugin. For example from the source or binary distribution the following should work

cd examples/camel-example-etl
mvn camel:run

If you prefer you can just run the Main directly using

mvn compile exec:java

Failing that you can run the Main from inside your IDE if you prefer. Follow the Building instructions to create an Eclipse/IDEA project to import

© 2004-2011 The Apache Software Foundation.
Apache Camel, Camel, Apache, the Apache feather logo, and the Apache Camel project logo are trademarks of The Apache Software Foundation. All other marks mentioned may be trademarks or registered trademarks of their respective owners.
Graphic Design By Hiram