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 . 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 .
-
Select .
-
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 .
-
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 .
-
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.