Configure the table object builder

Use the TableObjectBuilder to build a table object for directly loading data for a business object into one or more database tables. We can configure this builder by defining sub elements and attribute values in the business object configuration file. By using the table object builder and mediator, we can load data from a single input file into multiple database tables.

For general information about the table object builder and table object mediator, see Data Load utility table-based mediator and builder.

Procedure

1. Open your business object configuration for editing.

2. Find the <_config:BusinessObjectBuilder> element. If we are creating a business object configuration instead of editing an existing file, create this <_config:BusinessObjectBuilder> element within the <_config:DataLoader> element in your business object configuration file. Add your <_config:BusinessObjectBuilder> element after the <_config:DataReader> element.

3. Set the className attribute value to be the TableObjectBuilder object builder class For example, the following snippet specifies the table object builder class

   <_config:BusinessObjectBuilder 
     className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
   ...
   </_config:BusinessObjectBuilder>

4. Configure the tables that the data is to be loaded into. Specify each of the tables that the Data Load utility is to load data into within a separate <_config:Table> element. Specify the attributes for this <_config:Table> configuration property. The available attributes are:

   name

   The table name in the database.

   deleteKey

   It points to a column name in the CSV file. See the description of deleteValue attribute on how to use this attribute.

   deleteValue

   It is a delete flag. If the data in the column name specified by the deleteKey matches the deleteValue, the row is deleted from the database.

   excludeUnListedColumns

   Indicates whether to exclude any unlisted columns from loading. The values for this element can be true or false. Default is false and indicates that all columns are to be overwritten.

   For example, the following snippet declares the <_config:Table> element for loading attachment data into WebSphere Commerce:

   <_config:BusinessObjectBuilder 
     className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
     <_config:Table name="ROLE">
     ...
     </_config:Table>
     ...
   </_config:BusinessObjectBuilder>

5. Configure the columns for each of the tables that the utility is loading data into. Specify the configuration for each column separately within the appropriate <_config:Table>. Use the <_config:Column> element to configure a column. Specify the attributes for each column configuration. The available attributes we use to configure a column are:

   name

   The column name in the database.

   value

   The column value. Typically, it is a key and the real value depends on the valueFrom attribute to indicate how the value is resolved.

   valueFrom

   It indicates how to resolve the value. The following are the supported valueFrom:

   InputData

   It indicates that the value is from InputData.

   Fixed

   The value is a fixed value specified directly within the value attribute in this business object configuration file.

   IDResolve

   The value is from the ID resolver. In this case, the <_config:Column> element might contain an optional <_config:IDResolve> element to indicate how to resolve the ID.

   BusinessContext

   The value is from the business context section in the data load environment configuration file.

   CurrentTimestamp

   The value is the current time stamp.

   ColumnHandler

   The value is from a column handler. In this case, the <_config:ColumnHandler> element specifies the column handler class to compute the column value.

   defaultValue

   The default column value. This column is used when the valueFrom is InputData but the value is omitted in the input data.

   defaultValueFrom

   Indicates how to resolve the defaultValue. The following are the supported defaultValueFrom values:

   • InputData: Indicates that the defaultValue is from InputData.

   • Fixed: Indicates that the defaultValue is a fixed value.

   • BusinessContext: The defaultValue is from the business context.

   • CurrentTimestamp: The defaultValue is the current time stamp.

   • ColumnHandler: The defaultValue is from a column handler. In this case, the <_config:ColumnHandler> element specifies the column handler class to compute the column value.

   The following snippet specifies the column configuration for loading data into the MBRROLE database table:

   <_config:BusinessObjectBuilder 
     className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
     ...
      <_config:Table name="MBRROLE">
       <_config:Column name="MEMBER_ID" value="-2001" valueFrom="Fixed" />
       <_config:Column name="ROLE_ID" value="ROLE_ID" valueFrom="IDResolve" />
       <_config:Column name="ORGENTITY_ID" value="-2001" valueFrom="Fixed" />
     </_config:Table>
     ...
   </_config:BusinessObjectBuilder>

6. Optional: Configure the ID resolve for any columns that require this property. Specify this property with the <_config:IDResolve> element. The attribute definitions for this property are specified as follows:

   tableName

   The name of the table to be used for the ID resolver.

   generateNewKey

   true

   If the ID cannot be resolved from the database, a new key is generated.

   false

   If the ID cannot be resolved from the database, an exception is thrown, instead of generating a new key.

   primaryKeyColumnName

   The name of the primary key column to be resolved. This parameter is optional if the primary key for this table has only one column. Otherwise, it is mandatory.

   The following snippet specifies the ID resolve property for the ROLE table.

   <_config:Column name="ROLE_ID" value="ROLE_ID" valueFrom="IDResolve">
     <_config:IDResolve tableName="ROLE" generateNewKey="true" >
       ...
     </_config:IDResolve>
   </_config:Column>

   Note: If the ID for a column is declared in the attributes for the column property, or is declared in a different preceding element in the business object configuration file, the ID resolve property is not required.

7. Optional: Configure any unique index column sub elements for the ID resolve element. use the <_config:UniqueIndexColumn> element to specify these properties. A <_config:IDResolve> property can contain one or more <_config:UniqueIndexColumn> sub elements to help to resolve the ID. The attribute definitions are specified as follows:

   name

   The unique index column name.

   value

   The unique index column value. Typically, it is a key and the real value depends on the valueFrom attribute to indicate how the value is resolved.

   valueFrom

   It indicates how to resolve the value. The following are supported valueFrom:

   InputData

   It indicates that the value is from InputData.

   Fixed

   The value is a fixed value.

   BusinessContext

   The value is from the business context.

   CurrentTimestamp

   The value is the current time stamp.

   ColumnHandler

   The value is from a column handler. In this case, the <_config:ColumnHandler> element specifies the column handler class to compute the column value.

   skipIDResolveIfNull

   Indicates whether to skip the ID resolve when the column value is null. The value for this column can be true or false. Default is false.

   The following snippet specifies the unique index column index for the ID resolve property for the ROLE table.

   <_config:Column name="ROLE_ID" value="ROLE_ID" valueFrom="IDResolve">
     <_config:IDResolve tableName="ROLE" generateNewKey="true" >
       <_config:UniqueIndexColumn name="NAME" value="roleName" />
     </_config:IDResolve>
   </_config:Column>

8. Specify the business object mediator to be the TableObjectMediator class. If we are using the TableObjectBuilder class as the builder, always use this table object mediator as the business object mediator. This class is the mediator that the Data Load utility uses for all table-based data load. The following snippet specifies the table object mediator:

   <_config:BusinessObjectBuilder 
     className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
     ...
     <_config:BusinessObjectMediator 
       className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" >
     </_config:BusinessObjectMediator>
   </_config:BusinessObjectBuilder>

9. Optional: To include the objects that we are loading within the WebSphere Commerce search index, specify the search index mediator for the business object that we are loading. We must also specify the data mapping for the object that is to be included within the search index.

   To specify the search index mediator and data mapping, create a second business object builder configuration for indexing the business object. Add the second business object builder configuration after the </_config:BusinessObjectBuilder> element that closes the initial business object builder configuration. For a list of the available search index mediators, see Data Load utility business object mediators. For example, the following snippet specifies the search index mediator for loading categories and the data that is to be included in the search index.

   <_config:BusinessObjectBuilder 
     className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
     ...
     <_config:BusinessObjectMediator 
       className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" >
     </_config:BusinessObjectMediator>
   </_config:BusinessObjectBuilder>
   
   <!--  Catalog Group Search Index -->
   <_config:BusinessObjectBuilder 
     className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.BaseBusinessObjectBuilder"  
     packageName="com.ibm.commerce.catalog.facade.datatypes.CatalogPackage" 
     dataObjectType="CatalogGroupType" >
     <_config:DataMapping>
       <!-- The unique reference number of the category -->
       <_config:mapping xpath="CatalogGroupIdentifier/UniqueID" value="GroupUniqueId" />
       <!-- The identifier of the category -->
       <_config:mapping xpath="CatalogGroupIdentifier/ExternalIdentifier/GroupIdentifier" 
         value="GroupIdentifier" />
       <!-- The unique reference number of the store that owns the category -->
       <_config:mapping 
         xpath="CatalogGroupIdentifier/ExternalIdentifier/StoreIdentifier/ExternalIdentifier/NameIdentifier" 
         value="StoreIdentifier" />
       <!-- The unique reference number of the parent category -->
       <_config:mapping xpath="ParentCatalogGroupIdentifier/UniqueID" value="ParentGroupUniqueId" />
       <!-- The identifier of the parent category -->
       <_config:mapping xpath="ParentCatalogGroupIdentifier/ExternalIdentifier/GroupIdentifier" 
         value="ParentGroupIdentifier" />
       <!-- The unique reference number of the store that owns the parent category -->
       <_config:mapping xpath="ParentCatalogGroupIdentifier/ExternalIdentifier/StoreIdentifier/UniqueID" 
         value="ParentStoreUniqueId" />
       <!-- The identifier of the store that owns the parent category -->
       <_config:mapping 
         xpath="ParentCatalogGroupIdentifier/ExternalIdentifier/StoreIdentifier/ExternalIdentifier/NameIdentifier" 
         value="ParentStoreIdentifier" />
       <!-- The identifier of the catalog -->
       <_config:mapping xpath="Attributes/CatalogIdentifier" 
         value="CatalogIdentifier" />
       </_config:DataMapping>
     <_config:BusinessObjectMediator 
       className="com.ibm.commerce.catalog.dataload.mediator.CatalogGroupRelationshipSearchIndexMediator" 
       componentId="com.ibm.commerce.catalog" >
     </_config:BusinessObjectMediator>
   </_config:BusinessObjectBuilder>

   As a reference to help you configure the data mapping for an object, review the business object configuration files provided by default for use with Catalog Upload. To review these files, go to the following directory:

   • (Linux) utilities_root/ts.ear/xml/config/com.ibm.commerce.catalog/dataload

   • (Developer) workspace_dir\WC\xml\config\com.ibm.commerce.catalog\dataload

   The following files include the configuration to run search indexing:

   • wc-loader-AD-attribute-allowed-values.xml

   • wc-loader-catalog-entry.xml

   • wc-loader-catalog-group.xml

   • wc-loader-catalog-group-description.xml

Results

The table object builder configuration is complete. For example, the following code snippet specifies the configuration for loading member roles:

<_config:BusinessObjectBuilder 
  className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder" >
  
  <_config:Table name="ROLE">
    <_config:Column name="ROLE_ID" value="roleID" valueFrom="IDResolve">
      <_config:IDResolve tableName="ROLE" generateNewKey="true" >
        <_config:UniqueIndexColumn name="NAME" value="roleName" />
      </_config:IDResolve>
    </_config:Column>
    <_config:Column name="NAME" value="roleName" />
    <_config:Column name="DESCRIPTION" value="description" />
  </_config:Table>     
  
  <_config:Table name="MBRROLE">
    <_config:Column name="MEMBER_ID" value="-2001" valueFrom="Fixed" />
    <_config:Column name="ROLE_ID" value="roleID" valueFrom="IDResolve" />
    <_config:Column name="ORGENTITY_ID" value="-2001" valueFrom="Fixed" />
  </_config:Table>
  
  <_config:BusinessObjectMediator 
    className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" >
  </_config:BusinessObjectMediator>
  
</_config:BusinessObjectBuilder>

In this code snippet, the unique index column for name to set the value as roleName is included. This property is optional in this case. The value for the column NAME is defined previously in the column configuration property for the Name column