This example illustrates how Smooks can be used to extract data from an EDI message and load this data into a database, all without writing a single line of code (except for the "Main" class that executes Smooks).

SVN - Download

To Build: "mvn clean install"
To Run: "mvn exec:java"

Overview

This example works by filtering the sample EDI message defined in input-message.edi using the EdiSax parser configuration defined in edi-to-sax-order-mapping.xml. As it filters the message, Smooks extracts and binds components from the message into a Virtual Data Model (defined in smooks-configs/bindings.xml). This binding data is then used in a number of SQLExecutor resources which are triggered to execute (perform inserts on the database) on passing each order and order item in the message.

An important feature of this example is how it manages its memory footprint. It processes the message using the SAX filter (therefore no in memory DOM), persisting the order and order items without ever holding more than the current order's header info, plus the current order-item being processed i.e. it never holds a full order in memory. This means that Smooks can process huge messages (GBs) using this processing model. The same principals can easily applied to splitting, transforming and routing of huge messages.

The Input Message

The sample input EDI message is as follows:

MLS*Wed Nov 15 13:45:28 EST 2006
HDR*1*0*59.97*64.92*4.95
CUS*user1*Harry^Fletcher*SD
ORD*1*1*364*The 40-Year-Old Virgin*28.98
ORD*2*1*299*Pulp Fiction*30.99
HDR*2*0*81.30*91.06*9.76
CUS*user2*George^Hook*SD
ORD*3*2*983*Gone with The Wind*25.80
ORD*4*3*299*Lethal Weapon 2*55.50

To convert this EDI message stream format into a stream of SAX events that can be processed by Smooks, we use the EdiSax parser with the following mapping configuration (defined in edi-to-sax-order-mapping.xml).

<?xml version="1.0" encoding="UTF-8"?>
<medi:edimap xmlns:medi="http://www.milyn.org/schema/edi-message-mapping-1.1.xsd">

    <medi:description name="DVD Order" version="1.0"/>

    <medi:delimiters segment="&#10;" field="*" component="^" sub-component="~"/>

    <medi:segments xmltag="orders">

        <medi:segment segcode="MLS" xmltag="message-header">
            <medi:field xmltag="date"/>
        </medi:segment>

        <medi:segment segcode="HDR" xmltag="order" minOccurs="1" maxOccurs="-1">
            <medi:field xmltag="order-id"/>
            <medi:field xmltag="status-code"/>
            <medi:field xmltag="net-amount"/>
            <medi:field xmltag="total-amount"/>
            <medi:field xmltag="tax"/>

            <medi:segment segcode="CUS" xmltag="customer-details" minOccurs="1" maxOccurs="1">
                <medi:field xmltag="username"/>
                <medi:field xmltag="name">
                    <medi:component xmltag="firstname"/>
                    <medi:component xmltag="lastname"/>
                </medi:field>
                <medi:field xmltag="state"/>
            </medi:segment>

            <medi:segment segcode="ORD" xmltag="order-item" minOccurs="1" maxOccurs="-1">
                <medi:field xmltag="position"/>
                <medi:field xmltag="quantity"/>
                <medi:field xmltag="productId"/>
                <medi:field xmltag="title"/>
                <medi:field xmltag="price"/>
            </medi:segment>

        </medi:segment>

    </medi:segments>

</medi:edimap>

We configure the EDI parser as follows (defined in smooks-configs/edi-orders-parser.xml):

<?xml version="1.0"?>
<smooks-resource-list
    xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
    xmlns:edi="http://www.milyn.org/xsd/smooks/edi-1.1.xsd">

    <!--
    Configure the EDI Reader to parse the message stream into a stream of SAX events.
    -->
    <edi:reader mappingModel="/edi-to-sax-order-mapping.xml" />

</smooks-resource-list> 

Binding the Order Data into the Virtual Data Model

In order to process the order data contained in the EDI message, we extract items from the message stream as it is being filtered and bind them into a Virtual Data Model (Vs defining a physical Java Data Model). The process of doing this is the same as for any of the other samples that use the Javabean Cartridge (see *-to-java tutorials).

The binding configuration is as follows (defined in smooks-configs/bindings.xml):

<?xml version="1.0"?>
<smooks-resource-list xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd" 
                      xmlns:jb="http://www.milyn.org/xsd/smooks/javabean-1.1.xsd">

    <!--
        Virtual Model Binding Configurations for the order message elements...

        Just capturing the order and order-item element details into 2
        Maps, overwriting each as we iterate through the message i.e. not
        accumulating them in memory => low memory footprint because we only
        have details of the current order + current order-item in memory
        at any given time (i.e. we never even have a full order in memory)...

        The Database Schema (need to capture enough from the message to populate this):
        ORDERS (ORDERNUMBER INTEGER, USERNAME VARCHAR(50), STATUS INTEGER, NET DOUBLE, TOTAL DOUBLE, ORDDATE DATE)
        ORDERITEMS (ORDERNUMBER INTEGER, QUANTITY INTEGER, PRODUCT INTEGER, TITLE VARCHAR(50), PRICE DOUBLE)
    -->

    <jb:bindings beanId="message" class="java.util.HashMap" createOnElement="message-header">
        <jb:value property="date" data="message-header/date" decoder="Date">
            <jb:decodeParam name="format">EEE MMM dd HH:mm:ss z yyyy</jb:decodeParam>
        </jb:value>
    </jb:bindings>

    <jb:bindings beanId="order" class="java.util.HashMap" createOnElement="order">
        <jb:value property="orderNum"      data="order/order-id"     decoder="Integer" />
        <jb:value property="customerUname" data="order/customer-details/username" />
        <jb:value property="status"        data="order/status-code"  decoder="Integer" />
        <jb:value property="net"           data="order/net-amount"   decoder="BigDecimal" />
        <jb:value property="total"         data="order/total-amount" decoder="BigDecimal" />
    </jb:bindings>

    <jb:bindings beanId="orderItem" class="java.util.HashMap" createOnElement="order-item">
        <!-- Just bind in all elements of the orderItem into the orderItem map.  Property name is
             taken from the element name... -->
        <jb:value data="order-item/*" />
    </jb:bindings>

</smooks-resource-list> 

Configuring The Database Datasource

The inserts are performed on the database via the SQLExecutor element visitor. This resource requires a database datasource resource to be configured in Smooks. Smooks supports 2 types of datasource resources:

1. DirectDataSource: A direct connection datasource, where the driver etc is explicitly defined in the resource configuration.
2. JndiDataSource: A javax.sql.DataSource resource accessed via the local JNDI tree.

In this example, we use a Hypersonic database and for it we configure a DirectDataSource as follows (definde in smooks-configs/datasources.xml):

<?xml version="1.0"?>
<smooks-resource-list
    xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
    xmlns:ds="http://www.milyn.org/xsd/smooks/datasource-1.1.xsd">

    <ds:direct
        bindOnElement="$document"
        datasource="DBExtractTransformLoadDS"
        driver="org.hsqldb.jdbcDriver"
        url="jdbc:hsqldb:hsql://localhost:9201/milyn-hsql-9201"
        username="sa"
        password=""
        autoCommit="false" />

</smooks-resource-list>

See the database creation script in db-create.script  in the example source.

Configuring The SQLExecutor Resources

The top level configuration in this sample pulls together the resource configs for the EDI parser, Data Bindings and Database Datasource. It then adds the SQLExecutor configurations that use the data bindings via the DBExtractTransformLoadDS Datasource.

The configuration is as follows (smooks-configs/smooks-config.xml):

<?xml version="1.0"?>
<smooks-resource-list
    xmlns="http://www.milyn.org/xsd/smooks-1.1.xsd"
    xmlns:db="http://www.milyn.org/xsd/smooks/db-routing-1.1.xsd">

    <!--
    Filter the message using the SAX Filter (i.e. not DOM, so no
    intermediate DOM, so we can process huge messages...
    -->
    <params>
        <param name="stream.filter.type">SAX</param>
    </params>

    <!--
    Define the EDI stream parser for the orders message...
    -->
    <import file="edi-orders-parser.xml" />

    <!--
    Define the Database Datasource(s)...
    -->
    <import file="datasources.xml"/>

    <!--
    Define the Data Bindings.  This is to bind the order and orderItem data into the ExecutionContext so it
    can be used by the SQLExecutor for performing the inserts...
    -->
    <import file="bindings.xml"/>

    <!-- ==================================================================================
    Now define the DB Executor resource that will use the data bound from the EDI message
    into the virtual data model defined in bindings.xml...
    =================================================================================== -->

    <!-- Assert whether it's an insert or update.  Need to do this just before we do the
         insert/update, which is triggered to happen just after the customer-details are processed... -->
    <db:executor executeOnElement="customer-details" datasource="DBExtractTransformLoadDS" executeBefore="true">
        <db:statement>select ORDERNUMBER from ORDERS where ORDERNUMBER = ${order.orderNum}</db:statement>
        <db:resultSet name="orderExistsRS"/>
    </db:executor>

    <!-- If it's an insert (orderExistsRS.isEmpty()), insert the order at the end of the customer-details i.e. just before we process the order items... -->
    <db:executor executeOnElement="customer-details" datasource="DBExtractTransformLoadDS" executeBefore="false">
        <condition>orderExistsRS.isEmpty()</condition>
        <db:statement>INSERT INTO ORDERS VALUES(${order.orderNum}, ${order.customerUname}, ${order.status}, ${order.net}, ${order.total}, ${message.date})</db:statement>
    </db:executor>

    <!-- And insert each orderItem... -->
    <db:executor executeOnElement="order-item" datasource="DBExtractTransformLoadDS" executeBefore="false">
        <condition>orderExistsRS.isEmpty()</condition>
        <db:statement>INSERT INTO ORDERITEMS VALUES (${order.orderNum}, ${orderItem.quantity}, ${orderItem.productId}, ${orderItem.title}, ${orderItem.price})</db:statement>
    </db:executor>

    <!-- Ignoring updates for now!! -->

</smooks-resource-list>

Executing Smooks

See the example/Main.java  in the example source.