Red Hat Training

A Red Hat training course is available for Red Hat Fuse

11.14. Routing to a Database

  1. To route an order and order item data to a database, you should define a set of Java bindings that extract the order and order-item data from the data stream: 
    <?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.4.xsd">
 
    <!-- Extract the order data... -->
    <jb:bean beanId="order" class="java.util.Hashtable" createOnElement="order">
        <jb:value property="orderId" decoder="Integer" data="order/@id"/>
        <jb:value property="customerNumber" decoder="Long" data="header/customer/@number"/>
        <jb:value property="customerName" data="header/customer"/>
    </jb:bean>
 
    <!-- Extract the order-item data... -->
    <jb:bean beanId="orderItem" class="java.util.Hashtable" createOnElement="order-item">
        <jb:value property="itemId" decoder="Integer" data="order-item/@id"/>
        <jb:value property="productId" decoder="Long" data="order-item/product"/>
        <jb:value property="quantity" decoder="Integer" data="order-item/quantity"/>
        <jb:value property="price" decoder="Double" data="order-item/price"/>
    </jb:bean>
  2. Next you need to define datasource configuration and a number of db:executor configurations that will use that datasource to insert the data that was bound into the Java Object model into the database. This is the datasource configuration (namespace http://www.milyn.org/xsd/smooks/datasource-1.3.xsd) for retrieving a direct database connection: 
    <?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.3.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>
  3. It is possible to use a JNDI datasource for retrieving a database connection: 
    <?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.3.xsd">
 
    <!-- This JNDI datasource can handle JDBC and JTA transactions or 
           it can leave the transaction managment to an other external component.
           An external component could be an other Smooks visitor, the EJB transaction manager
           or you can do it your self. -->
    <ds:JNDI
        bindOnElement="#document"
        datasource="DBExtractTransformLoadDS"
        datasourceJndi="java:/someDS"
        transactionManager="JTA"
        transactionJndi="java:/mockTransaction"
        targetProfile="jta"/>
 
</smooks-resource-list>
  4. The datasource schema describes and documents how you can configure the datasource. This is the db:executor configuration (namespace http://www.milyn.org/xsd/smooks/db-routing-1.1.xsd): 
    <?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">
 
    <!-- Assert whether it's an insert or update. Need to do this just before we do the insert/update... -->
    <db:executor executeOnElement="order-items" datasource="DBExtractTransformLoadDS" executeBefore="true">
        <db:statement>select OrderId from ORDERS where OrderId = ${order.orderId}</db:statement>
        <db:resultSet name="orderExistsRS"/>
    </db:executor>
 
    <!-- If it's an insert (orderExistsRS.isEmpty()), insert the order before we process the order items... -->
    <db:executor executeOnElement="order-items" datasource="DBExtractTransformLoadDS" executeBefore="true">
        <condition>orderExistsRS.isEmpty()</condition>
        <db:statement>INSERT INTO ORDERS VALUES(${order.orderId}, ${order.customerNumber}, ${order.customerName})</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 (${orderItem.itemId}, ${order.orderId}, ${orderItem.productId}, ${orderItem.quantity}, ${orderItem.price})</db:statement>
    </db:executor>
 
    <!-- Ignoring updates for now!! -->
 
</smooks-resource-list>