Develop > Business logic layer > Work with the data service layer

Create a query

Query templates are used to define queries in the Data Service Layer. The template file maps queries on the logical model (XPath queries) to templated SQL statements. These templates are used to generate the actual SQL queries. These are invoked by the Data Service Layer to access the database. Each query is analogous to an EJB finder method.

Procedure

1. Decide how to query the data. For example: find by unique key, find by name. As part of this process, define the XPath query on your logical model.

2. Optional: Create an access profile for an existing XPath expression.

   New queries do not always require a new access profile, as they may want to return the same amount of information as an existing access profile, but find the data by a different key.

3. Generate the XPath key.
   

4. Define a query or multiple queries in the query template file.
   

5. Map the XPath key to the query in the query template file, by defining an XPATH_TO_SQL_STATEMENT block. For example, in the following example, /CatalogEntry[(PartNumber=)] is the XPath key.

   BEGIN_XPATH_TO_SQL_STATEMENT
   
   name=/CatalogEntry[(PartNumber=)]
   base_table=CATENTRY
   sql=
        SELECT
            CATENTRY.$COLS:CATENTRY_ID$
        FROM
            CATENTRY, STORECENT
        WHERE
            CATENTRY.CATENTRY_ID = STORECENT.CATENTRY_ID AND
                STORECENT.STOREENT_ID = $CTX:STORE_ID$ AND
                CATENTRY.PARTNUMBER IN (?partNumber?)
   
   END_XPATH_TO_SQL_STATEMENT
   

   The preceding snippet contains CTX tags that represent business contexts. The data service layer helps to extract context-sensitive information from the database by allowing the developer to specify a special tag, $CTX:KEY$, in the SQL template. This tag is substituted at runtime with the value of the context property, such as language ID or store ID, corresponding to the 'KEY'.

   You can also optionally create workspace-specific queries using CM tags. All workspace-specific tags can only be used in CM queries. See Query template file tags for more information.

• Create an access profile for an existing XPath expression
  A new access profile is required when return different data for a noun than is returned by the default access profiles. If you have extended the WebSphere Commerce schema with custom tables, and need to return data from the custom tables, create a new access profile.

• Generate the XPath key using the wcs_xpathkey utility
  The XPath key generator command-line utility takes an XPath expression as input and generates an XPath key. This key is used to locate the XPATH_TO_SQL_STATEMENT template in the query template file.

Related concepts

Data service layer

Access profiles

Query template file

Related reference

SQL parameters

Query template file tags

Data service layer naming conventions

WebSphere Commerce extended XPath notation

Techniques for improving the performance of SQL queries under workspaces in the Data Service Layer

+

Search Tips   |   Advanced Search