WebSphere Commerce 7 | Custom Dataload | Catalog Entry Dataload | BOD Objects to Set in Custom Data Reader

If you are writing custom datareader for Catalog Entry Dataload, here is the sample snippet for different BOD Objects to set,

// Create object for partnumber
PartNumberIdentifierType partNumberIdentifierType = CommerceFoundationFactory.eINSTANCE.createPartNumberIdentifierType();
partNumberIdentifierType.setPartNumber("Reebok_765687");

CatalogEntryIdentifierType identifier = CommerceFoundationFactory.eINSTANCE.createCatalogEntryIdentifierType();
identifier.setExternalIdentifier(partNumberIdentifierType);
       
// Create object for parent catgroup
CatalogGroupExternalIdentifierType catGroupExternalIdentifierType = CommerceFoundationFactory.eINSTANCE.createCatalogGroupExternalIdentifierType();
catGroupExternalIdentifierType.setGroupIdentifier("reebokShoes");

CatalogGroupIdentifierType catGroupIdentifierType = CommerceFoundationFactory.eINSTANCE.createCatalogGroupIdentifierType();
catGroupIdentifierType.setExternalIdentifier(catGroupExternalIdentifierType);

// Create object for catalog description
CatalogDescriptionType catalogDescType = CatalogFactory.eINSTANCE.createCatalogDescriptionType();
catalogDescType.setLanguage("-1");
catalogDescType.setName("Reebok Express Fancy Shoes");
catalogDescType.setShortDescription("Reebok Express Fancy Shoes sold in India");
catalogDescType.setLongDescription("Reebok Express Fancy Shoes - For comfort and styling");
catalogDescType.getAttributes().put(AVAILABLE, "1");
catalogDescType.getAttributes().put(PUBLISHED, "1");       

// Create Catentry Attributes       
CatalogEntryAttributesType catalogEntryAttributesType = CatalogFactory.eINSTANCE.createCatalogEntryAttributesType();

// Manufacturer Partnumber
StringValueType stringMFPartNumberType = CatalogFactory.eINSTANCE.createStringValueType();
stringMFPartNumberType.setValue("reebokManufacturesIndia");
CatalogAttributeType catalogMFPartNumberAttrType = CatalogFactory.eINSTANCE.createCatalogAttributeType();
catalogMFPartNumberAttrType.setName("ManufacturerPartNumber");
catalogMFPartNumberAttrType.setStringValue(stringMFPartNumberType);
catalogEntryAttributesType.getAttributes().add(catalogMFPartNumberAttrType);
       
// Manufacturer Name
StringValueType stringMFNameType = CatalogFactory.eINSTANCE.createStringValueType();
stringMFNameType.setValue("Reebok Manufacturers India");
CatalogAttributeType catalogMFNameAttrType = CatalogFactory.eINSTANCE.createCatalogAttributeType();
catalogMFNameAttrType.setName("Manufacturer");
catalogMFNameAttrType.setStringValue(stringMFNameType);       
catalogEntryAttributesType.getAttributes().add(catalogMFNameAttrType);
       
// Buyable Flag
StringValueType stringBuyableValType = CatalogFactory.eINSTANCE.createStringValueType();
stringBuyableValType.setValue("1");
CatalogAttributeType catalogBuyableAttrType = CatalogFactory.eINSTANCE.createCatalogAttributeType();
catalogBuyableAttrType.setName("buyable");
catalogBuyableAttrType.setStringValue(stringBuyableValType);
catalogEntryAttributesType.getAttributes().add(catalogBuyableAttrType);       
// Create Basic Details of Catentry Object
CatalogEntryType catentry = CatalogFactory.eINSTANCE.createCatalogEntryType();

// Setting all the constructed objects to Catentry Object
catentry.setCatalogEntryIdentifier(catEntIdType);           
catentry.setParentCatalogGroupIdentifier(catalogGroupIdentifierType);
catentry.getDescription().add(catalogDescType);
catentry.setCatalogEntryAttributes(catalogAttributesType);
catentry.setCatalogEntryTypeCode("ProductBean");   

Set this prepared CatalogEntryType to DataLoadBusinessObject and return the same in next() method for the Custom Data Reader.

Refer WCS | Dataload Utility | Writing Custom Dataload link to know more details of custom data readers

WCS | Custom Dataload | Attribute Dictionary Dataload | BOD Objects to Set in Custom Data Reader

If you are writing custom datareader for Attribute Dictionary Dataload, here is the sample snippet for different BOD Objects to set,

// AttributeDictionary External Identifier Type
AttributeExternalIdentifierType attributeExternalIdentifierType = CommerceFoundationFactory.eINSTANCE.createAttributeExternalIdentifierType();
attributeExternalIdentifierType.setIdentifier("OSInstalled");

// Attribute Dictionary Identifier Type
AttributeIdentifierType attributeIdentifierType = CommerceFoundationFactory.eINSTANCE.createAttributeIdentifierType();
attributeIdentifierType.setExternalIdentifier(attributeExternalIdentifierType);

// Attribute Dictionary Attribute Type
AttributeDictionaryAttributeType attrDictAttrType = CatalogFactory.eINSTANCE.createAttributeDictionaryAttributeType();

// Basic Details of the Attribute Dictionary Attribute
attrDictAttrType.setAttributeIdentifier(attributeIdentifierType);
attrDictAttrType.setName("Operating System Installed");
attrDictAttrType.setAttributeType("AssignedValues");
attrDictAttrType.setAttributeDataType("STRING");
attrDictAttrType.setLanguage("-1");
attrDictAttrType.setDescription("Default Operating System Installed in the Laptop");

// Flags for Attribute Dictionary Attribute
attrDictAttrType.setDisplayable(true);
attrDictAttrType.setSearchable(true);
attrDictAttrType.setComparable(true);
attrDictAttrType.setFacetable(true);
attrDictAttrType.setMerchandisable(true);
attrDictAttrType.setDisplaySequence(Double.valueOf("1"));

Set this prepared AttributeDictionaryAttributeType to DataLoadBusinessObject and return the same in next() method for the Custom Data Reader.

Refer WCS | Dataload Utility | Writing Custom Dataload link to know more details of custom data readers

WCS | Custom Dataload | Catalog Group Dataload | BOD Objects to Set in Custom Data Reader

If you are writing custom datareader for Catgroup Dataload, here is the sample snippet for different BOD Objects to set,

// Catalog Group Details
CatalogGroupExternalIdentifierType catGroupExternalIdentifierType = CommerceFoundationFactory.eINSTANCE.createCatalogGroupExternalIdentifierType();
catGroupExternalIdentifierType.setGroupIdentifier("shoes")

CatalogGroupIdentifierType catGroupIdentifierType = CommerceFoundationFactory.eINSTANCE.createCatalogGroupIdentifierType();
catGroupIdentifierType.setExternalIdentifier(catGroupExternalIdentifierType);
       
// Catalog Group Parent Details
CatalogGroupExternalIdentifierType catGroupParentExternalIdentifierType = CommerceFoundationFactory.eINSTANCE.createCatalogGroupExternalIdentifierType();
catGroupParentExternalIdentifierType.setGroupIdentifier("footwear")

CatalogGroupIdentifierType catGroupParentIdentifierType = CommerceFoundationFactory.eINSTANCE.createCatalogGroupIdentifierType();
catGroupParentIdentifierType.setExternalIdentifier(catGroupParentExternalIdentifierType);
       
// Catalog Group Description Details       
CatalogDescriptionType catalogDescType = CatalogFactory.eINSTANCE.createCatalogDescriptionType();
catalogDescType.setLanguage("-1");
catalogDescType.setName("Shoes");       

// Setting Details to CatalogGroupType
CatalogGroupType catGroupType = CatalogFactory.eINSTANCE.createCatalogGroupType();
catGroupType.setCatalogGroupIdentifier(catalogGroupIdentifierType);
catGroupType.setParentCatalogGroupIdentifier(catGroupParentIdentifierType);               
catGroupType.getDescription().add(catalogDescriptionType);
catGroupType.setTopCatalogGroup(false);

Set this prepared CatalogGroupType to  DataLoadBusinessObject and return the same in next() method for the Custom Data Reader.

Refer http://yashodtechtalk.blogspot.in/2013/11/wcs-dataload-utility-writing-custom.html link to know more details of custom data readers

WCS | Dataload Utility | Custom Dataload on Custom Tables

In the Dataload, if your datasource is not CSV or XML file and it might be some third party Datasource, property file, SAP or some other table in WCS DB then you can go with Custom Dataloads.

Refer my previous posts on how to create a custom Data Readers & Mediators.

If your dataload is intended for standard catalog load then we have fair enough of BOD Objects to prepare and set them to DataLoadBusinessObject, which will be used to push into Catalog related tables. But, if you wanted to go with custom tables then you can use TableObjectMediator.

Here is the small POC for the same,

Step 1:

Created source table and inserted the data,

CREATE TABLE XSourceTable (
empid number NOT NULL,
firstName VARCHAR(254),
lastName VARCHAR(254),
salary number
);

insert into XSourceTable(empid, firstname, lastname, salary) values (1000, 'Yashod', 'Kumar', 5000);
insert into XSourceTable(empid, firstname, lastname, salary) values (1001, 'Praneeth', 'N', 6000);
insert into XSourceTable(empid, firstname, lastname, salary) values (1002, 'Naveen', 'Kumar', 7000);    

Step 2:

Created two target tables,

 a. XTargetTable - Insert empid, firstname, lastname from XSourceTable and custId will be autogenerated key

b. XTagetSalTable - Insert salary from XSourceTable, fetch the custid from XTargetTable and salId is autogenerated key

CREATE TABLE XTargetTable (
custId number NOT NULL,
empId number NOT NULL,
firstName VARCHAR(254),
lastName VARCHAR(254)
);

ALTER TABLE XTargetTable ADD PRIMARY KEY (empId);
CREATE UNIQUE INDEX I0000_XTargetTable ON XTargetTable(custId ASC);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
    VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'xtargettable', 'custid', 0000, 1, 1000, 999999, 1);

CREATE TABLE XTargetSalTable (
salId number not null,
custId number NOT NULL,
salary number
);

ALTER TABLE XTargetSalTable ADD PRIMARY KEY (custId);

CREATE UNIQUE INDEX I0000_XTargetSalTable ON XTargetSalTable(salId ASC);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
    VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'xtargetsaltable', 'salid', 0000, 1, 1000, 999999, 1);

Step 3:   Writing custom Data Reader to read the source tables

CustomTableDataReader, which reads the data from source table and inserts into two target tables using TableObjectMediator

public class CustomTableDataReader extends AbstractDataReader {
    /*
     * Class name used for trace and logging.
     */
    private static final String CLASSNAME = CustomTableDataReader.class.getName();
  
    /*
     * logger used for logging.
     */
    private static Logger logger = Logger.getLogger(CLASSNAME);

     /*
     * BlockingQueue<Map> blockingBODQueue
     */
    private BlockingQueue<Map> blockingBODQueue = null;
   
    /**
     * This method is used by the OOB implementation to do pre-operations prior to Custom data reading
     * <ul>
     * <li>It make connection to the source DB</li>
     * <li>Call fetchSourceDataInput() to fetch the data from source tables</li>
     * <li>Prepare Map object out of the source data fetched</li>
     * </ul>
     * <p>
     * </p>
     * @throws DataLoadException
     * @see com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
     * @since 1.0
     */
    @Override
    public void init() throws DataLoadException {
        final String METHODNAME = "init";

        if (logger.isLoggable(Level.FINER)) {
            logger.entering(CLASSNAME, METHODNAME);
        }
        super.init();
           
        try {
            blockingBODQueue = new LinkedBlockingQueue<Map>(500);
            fetchSourceDataInput();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DataLoadException(e.getMessage());
        }
           
        if (logger.isLoggable(Level.FINER)) {
            logger.exiting(CLASSNAME, METHODNAME);
        }
    }
   
    /**
     * This method will be called at every data chunk by the OOB command for pushing the data to WCS Database
     * <ul>
     * <li>Loop through the BlockingQueue</li>
     * <li>Fetch the Map object and return the same</li>
     * </ul>
     * <p>
     * </p>
     * @return DataLoadBusinessObject
     * @throws DataLoadException
     * @see com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
     * @since 1.0
     */
    @Override
    public Object next() throws DataLoadException {
        synchronized(this) {   
            if (blockingBODQueue.isEmpty()) {
                return null;
            } else {
                Map customMap = null;
                try {
                    customMap = blockingBODQueue.take();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                    throw new DataLoadException(e.getMessage());
                }
                return customMap;
            }
        }
    }
   
    /**
     * Close the opened resources
     * <ul>
     * <li></li>
     * </ul>
     * <p>
     * </p>
     * @throws DataLoadException
     * @see com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
     * @since 1.0
     */
    @Override
    public void close() throws DataLoadException {
        final String METHODNAME = "close";
        if (logger.isLoggable(Level.FINER)) {
            logger.entering(CLASSNAME, METHODNAME);
        }
        super.close();
        logger.info("End of Dataload to custom tables at: " + new Date());
       
        if (logger.isLoggable(Level.FINER)) {
            logger.exiting(CLASSNAME, METHODNAME);
        }
    }
   
    /**
     * This method is used to prepare data as Map Object out of source tables
     * <ul>
     * <li>Fetch the required data from source tables</li>
     * <li>Loop through the ResultSet object</li>
     * <li>Prepare Map objects out of it</li>
     * <li>Set it back to BlockingQueue object</li>
     * </ul>
     * <p>
     * </p>
     * @throws DataLoadException
     * @see java.util.concurrent.BlockingQueue
     * @since 1.0
     */
    private void fetchSourceDataInput() throws DataLoadException, InterruptedException {
        final String METHODNAME = "fetchSourceDataInput";
        if (logger.isLoggable(Level.FINER)) {
            logger.entering(CLASSNAME, METHODNAME);
        }
       
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet customResultSet = null;
        try {
            String actualQuery = "select empid, firstname, lastname, salary from XSourceTable";
           
            // Get Connection of your DB
            conn = getConnection();
           
            // Get PreparedStatement Object
            pstmt = getPreparedStatement(conn, actualQuery);
           
            // Execute the Query with your JDBC
            customResultSet = executeQuery(pstmt);

            while(customResultSet.next()){
                Map customMap = new HashMap();
                customMap.put("empid", customResultSet.getString("empid"));
                customMap.put("firstname", customResultSet.getString("firstname"));
                customMap.put("lastname", customResultSet.getString("lastname"));
                customMap.put("salary", customResultSet.getString("salary"));
                blockingBODQueue.put(customMap);
            }
            logger.info("Fetch records count: " + blockingBODQueue.size());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataLoadException(e.getMessage());
        } catch (PDHException e) {
            e.printStackTrace();
            throw new DataLoadException(e.getMessage());       
        } finally {
            // Close all the resources
        }
        if (logger.isLoggable(Level.FINER)) {
            logger.exiting(CLASSNAME, METHODNAME);
        }
    }
}

Step 4: Updated the following configuration files,

a.       wc-dataload-env.xml : Update with your environment specific configurations

b.      wc-dataload.xml: Call wc-loader-custom-table.xml as businessObjectConfigFile,

<?xml version="1.0" encoding="UTF-8"?>
<_config:DataLoadConfiguration
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config xsd/wc-dataload.xsd"
    xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config">
    <_config:DataLoadEnvironment configFile="wc-dataload-env.xml" />
    <_config:LoadOrder >       
        <_config:LoadItem commitCount="20" batchSize="20" dataLoadMode="Replace" maxError="25" name="CustomTableDataLoad" businessObjectConfigFile="wc-loader-custom-table.xml">
               <_config:DataSourceLocation location=""/>
        </_config:LoadItem>
    </_config:LoadOrder>
</_config:DataLoadConfiguration>

c.       wc-loader-custom-table.xml: Mapping of Columns in the target tables

<?xml version="1.0" encoding="UTF-8"?>
<_config:DataloadBusinessObjectConfiguration
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config xsd/wc-dataload-businessobject.xsd"
    xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config">

     <_config:DataLoader className="com.ibm.commerce.foundation.dataload.BusinessObjectLoader" >
        <_config:DataReader className="com.hp.ecom.common.pdh.dataload.reader.Custom.CustomTableDataReader" />   
        <_config:BusinessObjectBuilder className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder">
            <!-- XTargetTable -->
            <_config:Table name="XTargetTable">
                <!-- System generated primary key-->
                <_config:Column name="CUSTID" value="custid" valueFrom="IDResolve">
                    <_config:IDResolve tableName="XTargetTable" generateNewKey="true">
                        <_config:UniqueIndexColumn name="EMPID" value="empid" />
                        <_config:UniqueIndexColumn name="FIRSTNAME" value="firstname" />
                    </_config:IDResolve>
                </_config:Column>
                <_config:Column name="EMPID" value="empid">
                </_config:Column>
                <_config:Column name="FIRSTNAME" value="firstname">
                </_config:Column>
                <_config:Column name="LASTNAME" value="lastname">
                </_config:Column>
            </_config:Table>
           
            <!-- XTargetSalTable -->
            <_config:Table name="XTargetSalTable">
                <_config:Column name="SALID" value="salid" valueFrom="IDResolve">
                    <_config:IDResolve tableName="XTargetTable" generateNewKey="true">
                        <_config:UniqueIndexColumn name="CUSTID" value="custid" valueFrom="IDResolve"/>
                    </_config:IDResolve>
                </_config:Column>
                <_config:Column name="CUSTID" value="custid" valueFrom="IDResolve">
                </_config:Column>
                <_config:Column name="SALARY" value="salary">
                </_config:Column>
            </_config:Table>

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