Data Synchronization with BlazeDS: Handling AUTOINCREMENT

Upcoming release of the ClearDataBuilder supports AUTOINCREMENTed property in the DataCollection items populated from databases based on autogenerated values like MSSqlServer/ Sybase identity, or Oracle’s sequence, and so forth. All you need to do is add identity parameter to the CDB annotation
[quickcode:noclick] /**
* @daoflex:sql
* sql=select * from employee
* transferType=EmployeeDTO[]
* keyColumns=id
* identity=id
* updateTable=employee
*/

public abstract List getEmployees();[/quickcode]
and allow server changes to reflect back on the DataCollection:
[quickcode:noclick]dataCollection.roundTripSync = true; [/quickcode]


Sample Use Case

Imagine a MSSqlServer table “employee”, with IDENTITY column named “id”. Here is the abstract Java class you would need to write:

package com.farata.test;
import java.util.List;
/**
 * @daoflex:webservice
 *   pool=jdbc/test
 */
public abstract class Employee
{
    /**
    * @daoflex:sql
    *  sql=select * from employee
    *  transferType=EmployeeDTO[]
    *  keyColumns=id
    *  identity=id
    *  updateTable=employee
    */

    public abstract List getEmployees();
}

A Clean CDB build, as usual, will generate concrete implementation of the Assembler, already registered for Flex remoting, ActionScript DTO classes matching the Java counterparties and so forth.

Importantly, the specifics of the identity annotation will force CDB to enquire the value of the autogenerated id column along with the execution of the executeUpdate() and send the modified record back to the Flex client.


Two-Way Syncronization: DataCollection – BlazeDS Assembler

You have to allow the echo of the server-side changes back to the client. You have two options.

  • dataCollection.roundTripSync=true; – to allow Flex process the immediate result of “your own” dataCollection.sync() call
  • dataCollection.autoSyncEnabled=true; – to allow Flex consume server-born asyncronous messages with changes inflicted by other clients. This option requires design-time autoSyncEnabled=true CDB annotation as well.

    Here is the explanation. During the sync() operation changes originated in the DataCollection are remoted to method(s) of a server Assembler as Array of com.farata.remoting.ChangeObject elements. All of these methods are also returning the Array of changes, including, if any, changes originated by the server. The value of the roundTripSync determines whether these changes effect DataCollection when the synchronization is complete. Default value is false


    Pluggable Identity Factory

    No two databases are born equal. The default setting of the CDB is to handle identity compliant with MSSqlServer, i.e. to use SELECT SCOPE_IDENTITY() as the cleanest possible computation of the identity. If you are running Sybase, you would have to modify the default daoflex.identity.factory in the daoflex-build/daoflex-build-custom.properties:

    daoflex.identity.factory=com.farata.daoflex.SybaseIdentityFactory
    

    It gets worse if you are an Oracle sequence guy. Plugging a foo.bar.OracleIdentityFactory class that has to implement com.farata.daoflex.IIdentityFactory interface is not any different from the Sybase, but CDB does not know upfront which sequence to use. So roll up your sleeves and create something like following:
    package foo.bar;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.Map;
    
    import com.farata.daoflex.DAOException;
    import com.farata.daoflex.IIdentityFactory;
    
    public class OracleIdentityFactory implements IIdentityFactory {
      public long getIdentity(Map<String, Object> properties) {
        long identity = 0;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
        
          Connection conn = (Connection) properties.get(CONNECTION);
          String table = (String) properties.get(TABLE);
          String sequenceName="UNKNOWN_SEQUENCE";  
          // Your custom logic to determine the sequence give the table goes here    
          if (table==table) 
               sequenceName = "MY_SEQUENCE";      
          stmt = conn.prepareStatement("SELECT " + sequenceName + ".NEXTVAL AS identity FROM DUAL");
          rs = stmt.executeQuery();
          while( rs.next() ) {
            identity = rs.getLong("identity");
          }
          return identity;
        } catch (Throwable te) {
          te.printStackTrace();
          throw new DAOException("Failed creating identity", te);
        }
        finally {
          try {rs.close(); rs = null;} catch (Exception e){}
          try {stmt.close(); stmt = null;} catch (Exception e){}
        }
      }
    }
    

    Victor Rasputnis

  • Deep Data Synchronization with BlazeDS

    Upcoming release of the ClearDataBuilder supports deep synchronization of hierarchical DataCollections with the Server, so that if an item of the collection contains child collections and so on, the entire tree of changes, if any, gets synchronized with the Java back end in one transaction:
    [quickcode:noclick]orders.sync(true); // Deep sync of the DataCollection and all nested children
    [/quickcode]


    Sample Use Case

    Listing 9 presents two SQL tables Orders and OrderItems. Sample application enables end user to navigate from order to order, editing the master information (order) as well as details (order items). All interactive changes are accumulated in Flex – until button “Commit” is clicked. That’s exactly when deep sync happens – in one transaction, i.e. all or nothing, “commit” of all changes or complete “rollback”:

    Snapshot of the Orders-OrderItems application
    Figure 1. Order-OrderItems application snapshot


    Client Data Persistance

    Two DataCollections – OrderCollection, Listing 2 and OrderItemCollection, Listing 3 – assist in persisting the data on the client; they remote to the methods of the Java class OrderAssembler generated by the ClearDataBuilder given SQL-annotated abstract class Order.java, Listing 8. Each item of the OrderCollection carries orderItems referring to the child collection of line items of this order. At the application level, Listing 1 below, we expose only the master collection orders, which holds the entire hierarchy:

    <?xml version="1.0" encoding="UTF-8"?>
    <!--OrderEntryDemo.mxml -->
    <mx:Application  
      xmlns:mx="http://www.adobe.com/2006/mxml" 
      xmlns="*" xmlns:collections="collections.*">
      <collections:OrderCollection id="orders"/>
      <mx:ControlBar>
        <mx:Button label="Fill"  click="selectedOrder=null;orders.fill()"  />
        <mx:Button label="Commit"  click="orders.sync(true)" 
          enabled="{orders.commitRequired}" />    
      </mx:ControlBar>
      <mx:VDividedBox  >
        <OrdersPanel id="master" orders="{orders}" 
          orderSelectionChange="selectedOrder = event.order"
        />
        <OrderItemsPanel id="detail" width="100%"  
          selectedOrder="{selectedOrder}"
        />
      </mx:VDividedBox>
      <mx:Script>
        <![CDATA[
          import com.farata.test.dto.OrderDTO;
          [Bindable] private var selectedOrder:OrderDTO;
        ]]>
      </mx:Script>
    </mx:Application>
    



    Hierarchical DTO Items

    Not that you would not have to write any code. For one, you would have to make sure that your DTOs implements com.farata.collections.dto.IHierarchicalDTO interface, i.e. “get” accessor of childCollections. One way to do it is via helper class com.farata.collections.dto.HierarchicalDTOAdapter. Please note OrderDTO in the snippet below extends the similarly named class prefixed with the “_” symbol. This is how, by default, CDB names a pair of generated ActionScript DTOs: the class with the “_” gets regenerated by CDB on every “Clean” of the project, while the extending class is preserving manual edits. Also, do notice how child collection orderItems – for each item – gets filled asynchronously, on order_id setter:

    package com.farata.test.dto{
    import collections.OrderItemCollection;
    import com.farata.collections.dto.HierarchicalDTOAdapter;
    import com.farata.collections.dto.IHierarchicalDTO;
    
    [RemoteClass(alias="com.farata.test.dto.OrderDTO")]
    public class OrderDTO extends _OrderDTO implements IHierarchicalDTO{
    [Transient] [Bindable] public var orderItems:OrderItemCollection;
    [Transient] public var adapter:HierarchicalDTOAdapter;
    
    public function OrderDTO() {
        super();
        adapter = new HierarchicalDTOAdapter(this);
        orderItems = new OrderItemCollection();
        adapter.addCollection(orderItems);
    }
    
    public function get childCollections():Array {
        return adapter.childCollections;
    }
    
    public override function set order_id(orderId:String):void {
        if (orderId !== super.order_id) {
             super.order_id = orderId;      
             orderItems.fill(order_id);  
        }      
    } 
    }//OrderDTO
    }
    

    Browse Listings 1-8: except “view”-Panel components there is no other code you would need to write. As long a you start with the SQL-annotated abstract Java source everything else gets code generated by the CDB.


    What if you use CDB with the existing DAO, i.e. IJavaDAO?

    You are not confined to SQL-annotations to use the fill() and sync(), of course. CDB allows your DataCollections to remote to any Java class implementing com.farata.daoflex.IJavaDAO interface (see CDB documentation for more details):

    package com.farata.daoflex;
    import java.util.List;
    import flex.data.ChangeObject;
    
    public interface IJavaDAO {
        List fill();
        List<ChangeObject> sync(List<ChangeObject> items);
    }
    

    In addition to IJavaDAO, participation in the deep sync requires your Java class to implement IBatchTransactionServiceSupport. Methods of this interface allow to synchronize changes accumulated by the DataCollection in three independent steps: all deletes, all updates and all inserts:
    package com.farata.daoflex;
    import java.util.List;
    import flex.data.ChangeObject;
    
    public interface IBatchServiceTransactionSupport  {
        List<ChangeObject> deleteItems(List<ChangeObject> items);
        List<ChangeObject> updateItems(List<ChangeObject> items);
        List<ChangeObject> insertItems(List<ChangeObject> items);
    }
    



    BatchService

    Splitting of monolythic sync() into three steps is required to support the referential integrity of data: children have to be deleted prior to parents and parents need to be inserted prior to children. And yet we can not addord three remoting calls instead of one (sync()), six calls altogether in our Orders-OrderItems scenario, because server-side transaction can not be spawned across several remoting calls.

    That’s where BatchService class from clear.swc comes in play. It refers to sequence of several remote method calls-to-do as a “batch”, or, simply array of BatchMember elements: destination name, method name, array of arguments. Instead of multiple remote calls it sends the “batch” as argument of one remote call – to Java class com.farata.remoting.BatchGateway (daoflex-runtime.jar). In turn, BatchGateway’s method execute(List<BatchMember>) invokes the required remote calls sequentially, wrapping the entire sequence in the JTA begin/commit/rollback.

    Snapshot of the Orders-OrderItems application

    Here is a snippet illustrating the work of the BatchService:

      var bs: com.farata.remoting.BatchService;
      .  .  .
      bs = new BatchService();
      bs.addEventListener(FaultEvent.FAULT, onFault);
      bs.registerCollection(orders, 0); //0 - default (top) priority, parent
      bs.registerCollection(orderItems,1); //1 - priority, child of "0"
      .  .  .
      var batch:Array = bs.batchRegisteredCollections();
      bs.send(batch);
    

    As you can see, BatchService batch is not confined to DataCollections: you can batch for synchronical server-side execution any sequence of remote calls. While BatchService has been around since 2006. the dataCollection.sync(true) further reduces the amount of code required to transactionally update associated collections. Users of SQL-based branch of the CDB benefit from automatic generation of the required Java functions. Otherwise, your Java DAO has to implement IBatchTransactionServiceSupport.

    package collections {
    import com.farata.collections.DataCollection;
    
    public class OrderCollection extends DataCollection {
        public function OrderCollection(source:Array=null) {
            super(source);
            destination="com.farata.test.Order";
            method="getOrders";
        }
    }
    }
    


    package collections {
    import com.farata.collections.DataCollection;
    
    public class OrderItemCollection extends DataCollection {
    
       public function OrderItemCollection(source:Array=null) {
            super(source);
            destination="com.farata.test.Order";
            method="getOrderItems";
       }
    }
    }
    


    <?xml version="1.0" encoding="UTF-8"?>
    <!-- OrdersPanel.mxml -->
    <mx:Panel title="Orders" 
      xmlns:mx="http://www.adobe.com/2006/mxml" >
    
        <mx:Metadata> 
            [Event(name="orderSelectionChange", type="events.OrderEvent")] 
        </mx:Metadata> 
      
      <mx:DataGrid id="dg" dataProvider="{orders}" editable="true"  change="onChange()" height="100%" >
        <mx:columns>
          <mx:DataGridColumn dataField="order_id" headerText="Order Id" />
          <mx:DataGridColumn dataField="customer_first_name" headerText="First Name" />
          <mx:DataGridColumn dataField="customer_last_name" headerText="Last Name" />
          <mx:DataGridColumn dataField="order_date" headerText="Order Date"  itemEditor="mx.controls.DateField" editorDataField="selectedDate"/>
        </mx:columns>
      </mx:DataGrid>
      <mx:ControlBar width="100%">
        <mx:Button label="Remove" click="onRemove(dg.selectedIndex)" enabled="{dg.selectedIndex != -1}"/>
        <mx:Button label="Add" click="onAdd(Math.max(0,dg.selectedIndex+1)); "/>
        <mx:Label text="Deleted: {orders.deletedCount}"/>
        <mx:Label text="Modified: {orders.modifiedCount}"/>
      </mx:ControlBar>
      <mx:Script>
      <![CDATA[
      import events.OrderEvent;
      import com.farata.test.dto.OrderDTO;
      import com.farata.collections.DataCollection;
    
      [Bindable]   public var orders:DataCollection;
      
      private function onAdd(position:int):void  {
        var item:OrderDTO = new OrderDTO();
        item.order_id = "###" + String(orders.length + 1);  
        item.order_date = new Date();
                
        orders.addItemAt(item, position);
        dg.selectedIndex = position;
        onChange();
      }  
      private function onRemove(position:int):void {
        orders.removeItemAt(position);
      }
      
      private function onChange():void {
        var event:OrderEvent = new OrderEvent(OrderEvent.ORDER_SELECTION_CHANGE);
        event.order = dg.selectedItem as OrderDTO;
        dispatchEvent(event);
      }  
    
      ]]>
      </mx:Script>
    </mx:Panel>
    

    <?xml version="1.0" encoding="utf-8"?>
    <!-- OrderItemsPanel.mxml -->
    <mx:Panel title="OrderItems" xmlns:mx="http://www.adobe.com/2006/mxml" >
      <mx:DataGrid id="dg" dataProvider="{orderItems}" editable="true" height="100%">
        <mx:columns>
          <mx:DataGridColumn dataField="order_id" headerText="Order Id" />
          <mx:DataGridColumn dataField="item_id" headerText="Item Id" />
          <mx:DataGridColumn dataField="product_name" headerText="Product" />
          <mx:DataGridColumn dataField="quantity" headerText="Quantity" />
        </mx:columns>
      </mx:DataGrid>
      <mx:ControlBar width="100%">
        <mx:Button label="Remove" click="onRemove(dg.selectedIndex);" enabled="{dg.selectedIndex != -1}"/>
        <mx:Button label="Add" click="onAdd(Math.max(0,dg.selectedIndex + 1)); " enabled="{selectedOrder!=null}"/>
        <mx:Label text="Deleted: {orderItems.deletedCount}"/>
        <mx:Label text="Modified: {orderItems.modifiedCount}"/>
      </mx:ControlBar>
      <mx:Script>
        <![CDATA[
        import com.farata.test.dto.OrderItemDTO;
        import com.farata.test.dto.OrderDTO;
        import com.farata.collections.DataCollection;
            
          [Bindable]private var orderItems:DataCollection ;
        
        private var _selectedOrder:OrderDTO;
        [Bindable]
        public function set selectedOrder(order:OrderDTO):void {
          _selectedOrder = order;
          if (order != null) {
            orderItems = order.orderItems;
          } else 
             orderItems = null; 
        }
        public function get selectedOrder():OrderDTO {
          return _selectedOrder;
        }
    
        private function onAdd(position:int):void  {
          var item:OrderItemDTO = new OrderItemDTO();
          item.order_id = selectedOrder.order_id;      
          if (item.quantity ==0) item.quantity = 1;
    
          orderItems.addItemAt(item, position);
          dg.selectedIndex = position;
        }
        private function onRemove(position:int):void {
          orderItems.removeItemAt(position);
        }
      ]]>
      </mx:Script>  
    </mx:Panel>
    


    package events{
    import com.farata.test.dto.OrderDTO;
    import flash.events.Event;
    public class OrderEvent extends Event {
        public static const ORDER_SELECTION_CHANGE:String ="orderSelectionChange";
    
        public var order:OrderDTO;
        public function OrderEvent(type:String, bubbles:Boolean=false, cancelable:Boolean=false){
            super(type, bubbles, cancelable);
        }
    }
    }
    


    /* Generated only when absent by ClearDataBuilder according to UserActionScriptDTO.xsl */
    package com.farata.test.dto{
    import collections.OrderItemCollection;
    import com.farata.collections.dto.HierarchicalDTOAdapter;
    import com.farata.collections.dto.IHierarchicalDTO;
    
    [RemoteClass(alias="com.farata.test.dto.OrderDTO")]
    public class OrderDTO extends _OrderDTO implements IHierarchicalDTO{
    //--------------------------------------------//
    // Your custom code goes here                       //
    //--------------------------------------------//
    
    [Transient] [Bindable] public var orderItems:OrderItemCollection;
    [Transient] public var adapter:HierarchicalDTOAdapter;
    
    public function OrderDTO() {
        super();
        adapter = new HierarchicalDTOAdapter(this);
        orderItems = new OrderItemCollection();
        adapter.addCollection(orderItems);
    }
    
    public function get childCollections():Array {
        return adapter.childCollections;
    }
    
    public override function set order_id(orderId:String):void {
        if (orderId !== super.order_id) {
            super.order_id = orderId;
            orderItems.fill(order_id);
        }
    }
    }//OrderDTO
    }
    


    package com.farata.test;
    import java.util.List;
    /**
    * @daoflex:webservice
    * pool=jdbc/test
    */
    public abstract class Order
    {
    /**
    * @daoflex:sql
    * sql=:: select order_id, customer_first_name,
    * customer_last_name, order_date from simple_order
    * ::
    * transferType=OrderDTO[]
    * keyColumns=order_id
    * updateTable=simple_order
    * autoSyncEnabled=true
    */
    public abstract List getOrders();
    
    /**
    * @daoflex:sql
    * sql=select * from simple_order_item WHERE ORDER_ID=:orderId
    * transferType=OrderItemDTO[]
    * updateTable=simple_order_item
    * keyColumns=order_id,item_id,product_name
    * autoSyncEnabled=true
    */
    public abstract List getOrderItems(String orderId);
    }
    


    CREATE TABLE `simple_order` (
    `order_id` char(32) NOT NULL default '',
    `customer_first_name` varchar(32) NOT NULL default '',
    `customer_last_name` varchar(32) NOT NULL default '',
    `order_date` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
    
    INSERT INTO `simple_order` (`order_id`,`customer_first_name`,`customer_last_name`,`order_date`) VALUES
    ('###1','Victor','Rasputnis','2006-02-04 00:00:00'),
    ('###2','Yakov','Fain','2006-09-25 00:00:00'),
    ('###3','Anatole','Tartakovsky','2006-07-01 00:00:00')$$
    
    CREATE TABLE `simple_order_item` (
    `order_id` char(32) NOT NULL default '',
    `item_id` char(32) NOT NULL default '',
    `product_name` varchar(32) NOT NULL default '',
    `quantity` int(11) NOT NULL default '1',
    PRIMARY KEY (`order_id`,`item_id`,`product_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
    
    INSERT INTO `simple_order_item` (`order_id`,`item_id`,`product_name`,`quantity`) VALUES
    ('###1','1','Laptop',1),
    ('###1','2','Battery',1),
    ('###1','3','Laptop Backpack',1),
    ('###3','1','Apple',1),
    ('###3','2','Orange',1)
    

    Victor Rasputnis