Configuring 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. You 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, you 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 you 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 com.ibm.commerce.foundation.dataload.businessobjectbuilder.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 that is 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. The default value 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 that you can 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 that is 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. The default value 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 com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator class. If you are using the com.ibm.commerce.foundation.dataload.businessobjectbuilder.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: If you want to include the objects that you are loading within the WebSphere Commerce search index, specify the search index mediator for the business object that you are loading. You 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 that are provided by default for use with Catalog Upload. To review these files, go to the following directory:
    • For IBM i OS operating systemSolarisLinuxAIXWC_eardir/xml/config/com.ibm.commerce.catalog/dataload
    • WindowsWC_eardir\xml\config\com.ibm.commerce.catalog\dataload
    • WebSphere Commerce Developerworkspace_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

For more information about using the table object builder and table object mediator, see Tutorial: Loading data into custom tables with the Data Load utility.