Adding 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 lesson, you add query template files that help retrieve the custom warranty information. Query template files store the SQL and access profile definitions for a service module, which isolate the module from the business logic layer completely.

About this task

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 can reuse existing XPath keys but must always define a new access profile because a different view of the data is returned. For more information about query template files, see Query template file.

The query template file that you are adding consists of the following components:

A symbol definition section that defines the tables that the query template uses (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 data for the CatalogEntry noun and CatalogEntryDescription noun part must 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:

workspace_dir\wc\xml\config\com.ibm.commerce.catalog\wc-query-CatalogEntry-update.tpl

The default SELECT query that is used to update the CatalogEntry noun is identified by the IBM_CatalogEntryUpdate access profile. 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:

In Enterprise Explorer, expand WC > xml > config. Right-click the com.ibm.commerce.catalog-ext folder.

Note: If the com.ibm.commerce.catalog-ext folder is not visible, select config and select File > Refresh.
Select New > File.
Name the file: wc-query-MyCompanyCatalogEntry-get.tpl
The name is important – it must begin with wc-query- and end with the .tpl file extension.
Click Finish.

Copy and paste the following query template into the file.

BEGIN_SYMBOL_DEFINITIONS
<!-- WebSphere Commerce tables -->
COLS:CATENTRY=CATENTRY:*
COLS:CATENTDESC=CATENTDESC:*

<!-- MyCompany extension tables -->
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

This code includes two subsections of the example query template:

SYMBOL_DEFINITIONS: In this subsection, you define symbols for the columns that are used in your SQL. If your physical schema changes, you can adjust the symbols without rewriting all of your SQL.
XPATH_TO_SQL_STATEMENT: Maps an XPath expression directly to an SQL statement. In the example above, an XPath statement was defined, named, and mapped to a base table and an SQL statement.

Save the file.

Create a custom update query file:

Right-click the com.ibm.commerce.catalog-ext folder.
Select 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
<!-- WebSphere Commerce tables -->
COLS:CATENTRY=CATENTRY:*
COLS:CATENTDESC=CATENTDESC:*

<!-- MyCompany extension tables -->
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 file to instruct the catalog service module to use the newly defined access profiles. This profile causes the catalog service module to use the new queries, which include the custom tables, instead of the default queries that are provided by WebSphere Commerce.

Expand WC > xml > config > com.ibm.commerce.catalog-ext.
Open the wc-business-object-mediator.xml file for editing.

Select the source view and 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 found 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.