Creating 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 you want to query your 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: Creating an access profile for an existing XPath expression.
    Note: 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 your XPath key.
  4. Define a query or multiple queries in your 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
    Note: 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.