Customizing the WebSphere Commerce service
In this section, you will add new tables in the WebSphere Commerce schema to store the additional product information. Figure 1 shows the changes in the schema.
Figure 1. Schema extensions
The schema shows the new tables and their relationship with the existing tables. The new tables are XPRODUCT, XPRODUCT_KIOSK, and XCAREINSTRUCTION:
- XPRODUCT table: This table has a
foreign key to the CATENTRY table. For demonstration purposes,
this table holds the data about import information for a given
catalog entry. This allows the Data Service Layer (DSL) to
populate data from the XPRODUCT table in the USERDATA element of
the CatalogEntry noun.
Note: USERDATA is an out-of-box extension point that allows customers to add simple name value pairs to the noun or service response. This data comes from custom tables that have a foreign key to the base table of the noun. This relationship must be one-to-one. One-to-many relationships are not supported for user data.
- XPRODUCT_KIOSK table: This table also has a foreign key to the CATENTRY table. For demonstration purposes, this table holds the data specific to Kiosk channel. This allows the Data Service Layer (DSL) to populate data from the XPRODUCT_KIOSK table in the USERDATA element of the CatalogEntry noun.
- XCAREINSTRUCTION table: This table has a foreign key to the CATENTDESC table to allow DSL to populate data in the ATTRIBUTE element of the CatalogEntryDescription noun part.
The XCAREINSTRUCTION table has a foreign key to the CATENTRY table to provide support to the DSL search function. This table holds information about care instructions, for example, "Fragile, handle with care" for a given catalog entry.
To add the new tables:
- Connect to the development database and execute the following
- Click on Start > Run
- Change the directory to
- Connect to the database using
db2 connect to <DB_Name>.
- Run the SQL to create the tables as shown below:
db2 -tvf CustomTablesDefinitions.sql CREATE TABLE XPRODUCT ( CATENTRY_ID BIGINT NOT NULL, IMPORTED VARCHAR(1), IMPORTED_COUNTRY VARCHAR(30), OPTCOUNTER SMALLINT, CONSTRAINT XPRODUCT_PK PRIMARY KEY (CATENTRY_ID), CONSTRAINT XPRODUCT_FK FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY (CATENTRY_ID) on delete cascade ); CREATE TABLE XPRODUCT_KIOSK ( CATENTRY_ID BIGINT NOT NULL, AISLE_NUMBER VARCHAR(5), AISLE_Information VARCHAR(100), OPTCOUNTER SMALLINT, CONSTRAINT XPRODUCT_KIOSK_PK PRIMARY KEY (CATENTRY_ID), CONSTRAINT XPRODUCT_KIOSK_FK FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY (CATENTRY_ID) on delete cascade ); CREATE TABLE XCAREINSTRUCTION ( CATENTRY_ID BIGINT NOT NULL, LANGUAGE_ID INTEGER NOT NULL, CAREINSTRUCTION VARCHAR(254), OPTCOUNTER SMALLINT, CONSTRAINT XCAREINST_PK PRIMARY KEY (CATENTRY_ID, LANGUAGE_ID), CONSTRAINT XCAREINST_FK1 FOREIGN KEY (CATENTRY_ID,LANGUAGE_ID) REFERENCES CATENTDESC(CATENTRY_ID,LANGUAGE_ID) on delete cascade, CONSTRAINT XCAREINST_FK2 FOREIGN KEY (CATENTRY_ID)REFERENCES CATENTRY (CATENTRY_ID) on delete cascade );
- Click on Start > Run and type
- Populate the custom tables with some sample data by running the
following SQL statements. Make sure you replace the right
CatalogEntry_idin the SQL shown below. In the DB2® command prompt opened above in Step 1a, issue the following command to insert the sample data into the custom tables:
db2 -tvf SampleData.sql INSERT INTO XPRODUCT (CATENTRY_ID, IMPORTED, IMPORTED_COUNTRY) VALUES (10001,'Y', 'Japan, The Land of Rising Sun'); INSERT INTO XPRODUCT (CATENTRY_ID, IMPORTED, IMPORTED_COUNTRY) VALUES (10002,'Y', 'India, The subcontinent'); INSERT INTO XCAREINSTRUCTION (CATENTRY_ID, LANGUAGE_ID, CAREINSTRUCTION) VALUES (10001, -1, 'Never use an abrasive cleaner or material on any finished product'); INSERT INTO XCAREINSTRUCTION (CATENTRY_ID, LANGUAGE_ID, CAREINSTRUCTION) VALUES (10002, -1, 'Never use an abrasive cleaner or material on any finished product'); INSERT INTO XPRODUCT_KIOSK (CATENTRY_ID, AISLE_NUMBER, AISLE_Information) VALUES (10001, '10', 'Available in aisle'); INSERT INTO XPRODUCT_KIOSK (CATENTRY_ID, AISLE_NUMBER, AISLE_Information) VALUES (10002, '12', 'Available near checkout counter');
In this section, you will customize the physical layer by generating object-relational metadata, and by generating the physical service data objects (SDOs). To do so, you will use a tool called the "Data Service Layer" wizard.
Physical SDOs are service data objects that represent tables in the WebSphere Commerce schema. Each data object type corresponds to a table definition in the schema, and each data object property corresponds to a table column or a reference to another data object type. These references correspond to the foreign key relationships between the database tables. The Data Service Layer wizard is used to generate object-relational metadata and physical data objects for your schema customization.
For each service module, there is object-relational metadata that
contains the information to relate the physical data object to a
database table. The custom object-relational metadata is stored in the
component configuration extension directories and the custom physical
SDOs are stored inside the
This wizard performs the following functions:
- Creates an extension configuration folder for the Catalog service
module if one does not already exist. The directory path is
- Generates a custom object-relational metadata file. This file is
- Generates SDO Java classes and places it in the
Note: The IBM®-supplied CatalogEntry objects are extended to include the new getters.
- Creates a utility Java™ class to return the physical SDO
root class (and its package) for the service module. This root
class ensures that all WebSphere Commerce physical SDOs for the
service module, and any additional physical SDOs for the
customization, are available at runtime.
Note: This configuration tells the system to instantiate the new CatalogEntry SDO instead of the IBM-supplied CatalogEntry SDO so that it now has access to the new data.
- Creates an extension service module configuration file that
instructs WebSphere Commerce to use the newly created catalog
physical SDO class in
- Creates an extension business object mediator configuration file
that configures the business object mediator to include data from
the XPRODUCT, XPRODUCT_KIOSK, and XCAREINSTRUCTION tables in the
user data of a CatalogEntry noun. This file is located at
To run the Data Service Layer wizard:
- Select File > New > Other > WebSphere Commerce > Data Service Layer.
- Click Next.
- Select Extend a default WebSphere Commerce service
Note: In this tutorial, the catalog service module will be extended. Therefore, this option has to be selected. If you are creating a new service, then you must select the other option, "Work with a custom service module".
- Click Next.
- Enter the following information:
- Service module: Select com.ibm.commerce.catalog
- Extension class prefix:
- Extension Java package name:
- Click Next.
- Select the XPRODUCT, XPRODUCT_KIOSK, and XCAREINSTRUCTION tables.
- Expand the XPRODUCT, XPRODUCT_KIOSK, and XCAREINSTRUCTION tables and make sure all columns are selected.
- Click Next.
- Under UserData, ensure that the IMPORTED,
IMPORTED_COUNTRY, AISLE_NUMBER, AISLE_INFORMATION, and
CAREINSTRUCTION database columns are set to "True", as shown in
Figure 2. Set the other columns to "False" if they are set as
"True" by default.
Note: This step ensures that the columns you want are included in the UserData area of the logical schema.
Figure 2. Extension table using Metadata Editing in the DSL wizard
- Click Finish.
- Verify that the following files are created in the
WebSphereCommerceServerExtensionsLogicproject, as shown in Figure 3.
Figure 3. Files generated from the DSL wizard
Note: The physical SDOs are generated in the
com.mycompany.commerce.catalog.facade.server.entity.datatypes. These SDOs provide a Java view of the database for the custom catalog service.
In this section, you will add query template files that retrieve your additional product information. Query template files store the SQL and access profile definitions for a service module, isolating it from the business logic layer completely.
The query template file consists of the following:
- A symbol definition section that defines the tables your query template will use: CATENTRY, CATENTDESC, XPRODUCT, XPRODUCT_KIOSK, and XCAREINSTRUCTION.
- Associated SQL statements to define SQL queries. You can reuse
these queries to build different access profiles defined in the
Note: Access profiles determine how much information is returned. For example, a "Summary" may return only a product description, but a "Details" profile might return description, price, attributes, and more. Since you are adding more data, you must create a new access profile.
- A new access profile,
MyCompany_All, is used along with the XPath key to identify the SQL template query.
- Many other access profiles are defined in the query template, which will be used in different channels.
To create a customer Get query template file:
- Right-click the
folder. (If the
com.ibm.commerce.catalog-extfolder is not visible, select the WC\config folder and select File > Refresh.)
Note: The custom TPL files must be created under the
- Click New > File.
- Name the file as
Note: The name must begin with
wc-query-and end with the suffix
- Click Finish.
- Copy and paste the following query template into the
BEGIN_SYMBOL_DEFINITIONS <!-- WebSphere Commerce tables --> COLS:CATENTRY=CATENTRY:* COLS:CATENTDESC=CATENTDESC:* <!-- MyCompany extension tables --> COLS:XPRODUCT =XPRODUCT:* COLS:XCAREINSTRUCTION=XCAREINSTRUCTION:* COLS:XPRODUCT_KIOSK=XPRODUCT_KIOSK:* END_SYMBOL_DEFINITIONS BEGIN_ASSOCIATION_SQL_STATEMENT name=MyCompanyImportgetCatalogEntryDetails base_table=CATENTRY additional_entity_objects=true sql= SELECT CATENTRY.$COLS:CATENTRY$, CATENTDESC.$COLS:CATENTDESC$, XPRODUCT.$COLS:XPRODUCT$, XCAREINSTRUCTION.$COLS:XCAREINSTRUCTION$ FROM CATENTRY LEFT OUTER JOIN XPRODUCT ON (CATENTRY.CATENTRY_ID = XPRODUCT.CATENTRY_ID) LEFT OUTER JOIN CATENTDESC ON (CATENTDESC.CATENTRY_ID = CATENTRY.CATENTRY_ID AND CATENTDESC.LANGUAGE_ID in ($CONTROL:LANGUAGES$)) LEFT OUTER JOIN XCAREINSTRUCTION ON (CATENTRY.CATENTRY_ID = XCAREINSTRUCTION.CATENTRY_ID AND XCAREINSTRUCTION.LANGUAGE_ID in ($CONTROL:LANGUAGES$)) WHERE CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$) AND CATENTRY.MARKFORDELETE = 0 END_ASSOCIATION_SQL_STATEMENT BEGIN_ASSOCIATION_SQL_STATEMENT name=MyCompanyImportgetCatalogEntryWithKioskDetails base_table=CATENTRY additional_entity_objects=true sql= SELECT CATENTRY.$COLS:CATENTRY$, XPRODUCT_KIOSK.$COLS:XPRODUCT_KIOSK$ FROM CATENTRY LEFT OUTER JOIN XPRODUCT_KIOSK ON (CATENTRY.CATENTRY_ID = XPRODUCT_KIOSK.CATENTRY_ID) WHERE CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$) AND CATENTRY.MARKFORDELETE = 0 END_ASSOCIATION_SQL_STATEMENT BEGIN_PROFILE name=MyCompany_All extends=IBM_Admin_All BEGIN_ENTITY associated_sql_statement=MyCompanyImportgetCatalogEntryDetails END_ENTITY END_PROFILE BEGIN_PROFILE name=MyCompany_Admin_All extends=IBM_Admin_All BEGIN_ENTITY associated_sql_statement=MyCompanyImportgetCatalogEntryDetails END_ENTITY END_PROFILE BEGIN_PROFILE name=MyCompany_Mobile_Description extends=IBM_Admin_CatalogEntryDescription BEGIN_ENTITY associated_sql_statement=MyCompanyImportgetCatalogEntryDetails END_ENTITY END_PROFILE BEGIN_PROFILE name=MyCompany_Store_CatalogEntryAllDescriptions extends=IBM_Admin_CatalogEntryAllDescriptions BEGIN_ENTITY associated_sql_statement=MyCompanyImportgetCatalogEntryDetails END_ENTITY END_PROFILE BEGIN_PROFILE name=MyCompany_Store_CatalogEntryDetailsWithKioskInfo extends=IBM_Admin_Details BEGIN_ENTITY associated_sql_statement=MyCompanyImportgetCatalogEntryDetails associated_sql_statement=MyCompanyImportgetCatalogEntryWithKioskDetails END_ENTITY END_PROFILE
- Save the file.
The previous section created a new access profile,
MyCompany_All. By default, only the users
with a site administrator role have access to this new data. In this
section, you will update the Catalog service access control policy to
state that all users have access to view this data.
The new policy defines a new action for the
MyCompany_All access profile and adds the
new action to the CatalogEntry All users group.
- Create the following file:
- Copy and paste the following access control policy XML into this
<?xml version="1.0" encoding="ISO-8859-1" standalone="no" ?> <!DOCTYPE Policies SYSTEM "../dtd/accesscontrolpolicies.dtd"> <Policies> <Action Name="GetCatalogEntry.MyCompany_All" CommandName= "GetCatalogEntry.MyCompany_All"/> <ActionGroup Name="Catalog-CatalogEntry-AllUsers- AccessProfileActionGroup" OwnerID="RootOrganization"> <ActionGroupAction Name="GetCatalogEntry.MyCompany_All"/> </ActionGroup> <!-- === Following entries are for mobile access profile which will be used in Section 5 of the lab === --> <Action Name="GetCatalogEntry.MyCompany_Mobile_Description" CommandName="GetCatalogEntry.MyCompany_Mobile_Description"/> <ActionGroup Name="Catalog-CatalogEntry-AllUsers- AccessProfileActionGroup" OwnerID="RootOrganization"> <ActionGroupAction Name="GetCatalogEntry. MyCompany_Mobile_Description"/> </ActionGroup> </Policies>
- Run the
acploadcommand to load the access control policy:
- Open a command prompt and navigate to
- Run the following command:
acpload inst1db db2admin passw0rd MyCompanyCatalogAccessControlPolicies.xml db2admin
- Navigate to the
- Open a command prompt and navigate to
messages.txt files to ensure that the
access control policy loaded successfully. There may not be a
messages.txt file present if everything
This section shows how to test your customization using JUnit. JUnit is a standalone Java program that will use the IBM Client library (a set of Java APIs) to access the WebSphere Commerce service. It does this by sending HTTP requests to the service. This unit test assumes a starter store with a store ID of 10152 (MadisonsESite) and a catalog ID of 10001 (Extended Sites Catalog Asset Store) have been published to your WebSphere Commerce development environment.
For demonstration purposes, this is considered as the
first channel to
invoke the services.
- Import the JUnit Test project for the Catalog service to your
workspace. The project interchange zip
CatalogExtensions-UnitTest.zip, is provided in the
- In the Enterprise Explorer view, open:
Note: Take a moment to understand how this JUnit test case is built.
There are two important methods:
- setUp(): This initializes the businesscontext and callbackhandler objects. These objects are used to create the CatalogEntry client.
- testCatalogEntryCustomization(): In this method, the actual call to the service happens. Form the Get expression by using the SelectionCriteriaHelper class and create the Get verb using the createGetVerb method of the catalog entry facade client. Perform the get request by using the CatalogEntry client created earlier in the setUp method. Navigate and print (or assert) the UserData element in the response object (in this case, it is of type ShowCatalogEntryDataAreaType).
- Notice the following constants with the machine specific values
that are already configured:
- USER_ID = wcsadmin
- PASSWORD = passw0rd
- STORE_ID = 10152 (Since we are using an eSite for this tutorial, we have set the storeID of the MadisonESite store.)
- CATALOG_ID = 10001 (Since the eSite that is set up for this tutorial uses catalog asset store, we have set the catalogID of catalog asset store.)
- catEntryId = 10002 (This is the catentryId against in which the sample data got populated.)
- On the Server page, right-click the WebSphere Commerce Test Server and select Start.
- If the WebSphere Commerce (WC) project is not already published to
the WebSphere Commerce Test Server, publish the WC
- On the Server page, right-click the WebSphere Commerce Test Server and select Add and Remove Projects.
- Select the WC project.
- Click Add.
- Click Finish.
If the WC project is already published, right-click the WebSphere Commerce Test Server and select Publish.
- Set up a TCP/IP monitor in WebSphere Commerce Developer. You will
use this TCP/IP monitor to observe the request and response
documents going to and from the WebSphere Commerce service you
created. To create a TCP/IP Monitor that will forward requests to
- Select Window > Preferences.
- From the Preferences page, click Run/Debug > TCP/IP Monitor.
- Click Add.
- Type the following information:
- Local monitoring port:
localhost- the hostname of the WebSphere Commerce Server where the catalog service is running.
80for WebSphere Commerce Developer
- Check the check box Start monitor automatically.
- Local monitoring port:
- Click OK.
- Select the created TCP/IP monitor.
- Click Start. (This should have been started already, in which case skip the next step.)
- Click OK.
- Right-click the
and select Run As > JUnit Test. You see that
the request and response XML documents are transmitted between the
client and server on the TCP/IP monitor.
Verify the results:
- This test performs a Get request to retrieve a CatalogEntry noun, including the Product extension and care instruction data.
- The Get request uses the XPath key of
/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]], and the
MyCompany_Allaccess profile defined in Step 3 above.
- The data service layer uses the XPath key and access profile to identify the SQL query template to run against the database and populate physical data objects with the results.
- The business object mediator transforms the physical data objects into logical nouns. This process populates additional product information into the CatalogEntry noun's UserData element and populates care instructions into the Catalog description noun part's attributes element.