Add query templates to include the new information

Tutorials > Management Center > Add new properties to a WebSphere Commerce service using the data service layer
< Previous | Next >

Add query templates to include the new information

The Catalog Service Module needs to be configured to update warranty and care instruction user data. In this step we will add query template files that will help retrieve our custom warranty information. Query template files store the SQL and access profile definitions for a service module, isolating it from the business logic layer completely.
A query template relates an XPath key and an access profile of a logical object to a template SQL query to select the data. Custom query templates may reuse existing XPath keys but must always define a new access profile because a different view of the data is returned. You can read more about query template files and how they are used in the Query template file topic.
Our query template file will consist of the following:

A symbol definition section that defines the tables our query template will use (CATENTRY, CATENTDESC, XWARRANTY, XCAREINSTRUCTION.

An XPath to SQL statement that maps the XPath key and access profile to a specific template SQL query.

A new access profile, MyCompany_All that is used along with the XPath key to identify the SQL template query.

The default queries to fetch the data before updating the CatalogEntry noun, and CatalogEntryDescription noun part need to be changed to include the XWARRANTY and XCAREINSTRUCTION tables.

The default SELECT queries for updating the CatalogEntry nouns and its parts is located inside the following file, WC_EAR\xml\config\com.ibm.commerce.catalog\wc-query-CatalogEntry-update.tpl.

The default SELECT query used to update the CatalogEntry noun is identified by the IBM_CatalogEntryUpdate access profile and the default SELECT query to update the CatalogEntryDescription noun part is identified by the IBM_CatalogEntryDescription_Update access profile.

To configure the Catalog service module to update the new user data, these two queries are copied and pasted into extension update query template files. The queries are modified to also select the new tables, and a new access profile to uniquely identify each query is added.

Procedure
Create a custom Get query template file:
Right-click the WC\xml\config\com.ibm.commerce.catalog-ext folder. (If the com.ibm.commerce.catalog-ext folder is not visible, select the WC\config folder and select File > Refresh.)

Click New > File.

Name the file: wc-query-MyCompanyCatalogEntry-get.tpl. The name is important – it must begin with wc-query- and end with the suffix .tpl.

Click Finish.
Copy and paste the following query template into the file.
BEGIN_SYMBOL_DEFINITIONS

COLS:CATENTRY=CATENTRY:*
COLS:CATENTDESC=CATENTDESC:*


COLS:XWARRANTY=XWARRANTY:*
COLS:XCAREINSTRUCTION=XCAREINSTRUCTION:*

END_SYMBOL_DEFINITIONS

BEGIN_XPATH_TO_SQL_STATEMENT
  name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]+MyCompany_All
  base_table=CATENTRY
    sql=
      SELECT
        CATENTRY.$COLS:CATENTRY$,         CATENTDESC.$COLS:CATENTDESC$,         XWARRANTY.$COLS:XWARRANTY$,         XCAREINSTRUCTION.$COLS:XCAREINSTRUCTION$
      FROM
        CATENTRY 
           LEFT OUTER JOIN XWARRANTY ON (CATENTRY.CATENTRY_ID = XWARRANTY.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 = CATENTDESC.LANGUAGE_ID)
      WHERE
        CATENTRY.CATENTRY_ID IN (?UniqueID?) AND
        CATENTRY.MARKFORDELETE = 0
END_XPATH_TO_SQL_STATEMENT
The two subsections of this example query template are:

SYMBOL_DEFINITIONS

Allows you to define symbols for the columns used in the SQL. If the physical schema changes, you can adjust the symbols without rewriting all of the SQL.

XPATH_TO_SQL_STATEMENT

Maps an XPath expression directly to an SQL statement. In the example above, an XPath statement has been defined, named, and mapped to a base table and an SQL statement.
Save the file.
Create a custom update query file:
Right-click the WC\xml\config\com.ibm.commerce.catalog-ext folder.

Click New > File.

Name the file: wc-query-MyCompanyCatalogEntry-update.tpl.

Click Finish.
Copy and paste the following query template into the file.
BEGIN_SYMBOL_DEFINITIONS

COLS:CATENTRY=CATENTRY:*
COLS:CATENTDESC=CATENTDESC:*


COLS:XWARRANTY=XWARRANTY:*
COLS:XCAREINSTRUCTION=XCAREINSTRUCTION:*

END_SYMBOL_DEFINITIONS
BEGIN_ASSOCIATION_SQL_STATEMENT
    name=MyCompanyWarrantyCatalogEntry
    base_table=CATENTRY
    additional_entity_objects=true
    sql=
        SELECT 
            CATENTRY.$COLS:CATENTRY$,             XWARRANTY.$COLS:XWARRANTY$
        FROM
            CATENTRY
              JOIN STORECENT ON STORECENT.CATENTRY_ID=CATENTRY.CATENTRY_ID 
              LEFT OUTER JOIN XWARRANTY ON (CATENTRY.CATENTRY_ID = XWARRANTY.CATENTRY_ID) 
        WHERE
            CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$)
            AND STORECENT.STOREENT_ID IN ($STOREPATH:catalog$) 
            AND CATENTRY.MARKFORDELETE=0
END_ASSOCIATION_SQL_STATEMENT

BEGIN_XPATH_TO_SQL_STATEMENT
name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]/Description+MyCompany_CatalogEntryDescription_Update
base_table=CATENTDESC 
sql=
SELECT 
    CATENTDESC.$COLS:CATENTDESC$,     XCAREINSTRUCTION.$COLS:XCAREINSTRUCTION$
FROM
    CATENTDESC
      LEFT OUTER JOIN XCAREINSTRUCTION ON (CATENTDESC.CATENTRY_ID = XCAREINSTRUCTION.CATENTRY_ID AND XCAREINSTRUCTION.LANGUAGE_ID = CATENTDESC.LANGUAGE_ID)

WHERE
    CATENTDESC.CATENTRY_ID IN (?UniqueID?) 

END_XPATH_TO_SQL_STATEMENT 

BEGIN_PROFILE
    name=MyCompany_CatalogEntry_Update
    extends = IBM_Admin_CatalogEntryUpdate
    BEGIN_ENTITY
        associated_sql_statement=MyCompanyWarrantyCatalogEntry
    END_ENTITY
END_PROFILE
Save the file.
Update the wc-business-object-mediator.xml to instruct the Catalog service module to use the newly defined access profiles. This will cause the Catalog service module to use the new queries, which include the custom tables, instead of the default queries provided by WebSphere Commerce.
Open WC\xml\config\com.ibm.commerce.catalog-ext\wc-business-object-mediator.xml.
Find the following element:
<_config:object logicalType="com.ibm.commerce.catalog.facade.datatypes.CatalogEntryType" 
physicalType="com.mycompany.commerce.catalog.facade.server.entity.datatypes.MyCompanyCatalogEntry">
Copy and paste the following mediation configuration after the line you located in step 3b:
<_config:mediator interfaceName="com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.ChangeBusinessObjectMediator" 
className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.ChangeCatalogEntryMediator" 
updateAccessProfile="MyCompany_CatalogEntry_Update">
   
<_config:part-mediator interfaceName="com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.ChangeBusinessObjectPartMediator">
     
<_config:part-mediator-implementation className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.ChangeCatalogEntryBasePartMediator" updateAccessProfile="MyCompany_CatalogEntry_Update"/>
     
<_config:part-mediator-implementation className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.ChangeCatalogEntryDescriptionMediator" updateAccessProfile="MyCompany_CatalogEntryDescription_Update"/>
   
</_config:part-mediator> 
</_config:mediator> 
Save the file.
< Previous | Next >

+
Search Tips | Advanced Search