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.


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