Deprecated feature

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 we 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'.

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

• Creating an access profile for an existing XPath expression
  A new access profile is required when you need to 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.

• Generating your 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.

• Validating your query template file syntax
  WebSphere Commerce Developer provides a JSP file we use to validate your query template file syntax. Query template file syntax validation can only be performed on local instances of WebSphere Commerce. This JSP file cannot be used for remote access and testing.

• Tracing queries and query parameters
  We can use the WebSphere Application Server administrative console to trace queries serviced by the data service layer. We can also enable tracing for query parameters.

• Overriding generated parametric search SQL
  There may be cases where we want to override the generated SQL for parametric search queries. For example, if a certain parametric search is not performing well, the database administrator may suggest another way to write the SQL. In this case, we can completely override the SQL used for the parametric search query.

• Configure parametric search to search on custom data
  Before new properties can be used as search conditions in a query template, we must configure parametric search to define the object path mapping so that the query can identify the corresponding column in the database table.

• Reusing an access profile under a different alias
  We can reuse an access profile that is registered in the configuration of the data service layer as different access profiles supported by the Get service. We can do this by using access profile aliasing.

• Creating direct SQL statements
  Under certain circumstances, SQL statements may need to be run to select data, update data, delete data, or retrieve data independently of the data model. For example, a business operation may insert or delete records in data tables that are not defined in the logical model.

• Creating a get-data-config.xml file
  We can use getData tags and process-related service requests after creating a get-data-config.xml file. The get-data-config.xml file contains expression builders, each representing a different SQL query defined in the project's query template file. getData tags in the storefront JSP files can retrieve specific data from the database by referencing the appropriate expression builder.

• Creating an expression builder
  An expression builder is used by the wcf:getData tag on a JSP file to construct an XPath expression used by a WebSphere Commerce service to retrieve data.