Introduction

This reference explains the public APIs of the query framework and describes how to use them.

Pipelets

VerifySearchTerm

This pipelet converts a search string entered by a user into an expression that can be used in query templates. This allows for defining queries without regard to the actual search string provided by a user.

LoadQuery

This pipelet loads a query from a file, according to the site context.

* One of the two parameters is required.

UpdateDictionary

This pipelet builds a map with string objects as key. It can be used to build larger sets of parameters for query execution.

ExecuteCountQuery

This pipelet executes count queries.

ExecuteObjectsQuery

This pipelet executes a query for objects. The result is intended to be used in the following pipeline only. In this case, the element count and a sorting are usually not required.

ExecutePageableQuery

This pipelet executes a query for objects. As the result is intended to be displayed to users, it must support sorting and paging.

ExecuteUpdateQuery

This pipelet is intended to execute DML queries. This means, the query executes insert, update and delete statements.

For the pipelets ExecuteCountQuery, ExecuteObjectsQuery, ExecutePageableQuery and ExecuteUpdateQuery, the Properties View allows for easily accessing the query parameters. Developers can directly edit the parameters passed to the query to be invoked or processed by the pipelet.

Query Files

Query files are XML files suffixed with *.query. They are stored in the site and cartridge structure in the subdirectory queries, in parallel to the pipelines and templates directories.
As already outlined, a query file consists of an input parameter declaration, a return mapping, a processor section, and one or more query templates. The following example illustrates the query file contents.

<?xml version="1.0" encoding="UTF-8"?>
<query>
<parameters>
  <parameter name="Domain" type="com.intershop.beehive.core.capi.domain.Domain" optional="false"/>
  <parameter name="SortLocale" type="com.intershop.beehive.core.capi.localization.LocaleInformation" optional="false"/>
</parameters>
<return-mappings>
  <return-mapping name="Rebate" type="orm" class="com.intershop.component.marketing.internal.rebate.PromotionPO">
    <return-attribute name="PromotionUUID"/>
  </return-mapping>
</return-mappings>
<processor name="JDBC">
</processor>
<template type="countedobjects">
  SELECT p.uuid as PromotionUUID, s.stringvalue, COUNT(*) over() AS rowcount
  FROM promotion_av s, promotion p
  WHERE p.DomainID=<template-variable value="Domain:UUID"/>
    AND not exists (select * from abtestgroup ab where ab.promotionuuid=p.uuid)
    AND s.name (+)='displayName'
    AND s.localeid (+)=<template-variable value="SortLocale:LocaleID"/>
    AND p.uuid=s.ownerid (+)
  ORDER BY s.stringvalue asc NULLS LAST
</template>
</query>

Parameter Declaration

The parameter section defines which values the query expects. It consists of a name, the expected type and a flag marking it as optional or required. Only values declared here are accessible in further query processing (parameter pre-processing by the QueryProcessor and template execution). The parameters are checked at runtime. Using the attribute processing enables that the value of a parameter can be computed using object path expressions.

If a default-value is set, this value is used if no other value is set at runtime. If the parameter is not optional, the default is still used and there is no exception since the parameter has a value.
If the parameter-type is a number and the min- and/or max-value is set, it is validated that the number is equal to or greater than the min-value and less than or equal to the max-value.
If a comma-separated list of values is specified, it is checked whether the value that the specified parameter has at runtime is one of the allowed values.

<parameters>
  <parameter name="TypeCode" type="java.lang.Integer" optional="false"/>
  <parameter name="ID" type="java.lang.String" optional="true"/>
  <parameter name="Domains" type="java.util.Iterator" optional="true"/>
  <parameter name="User" type="com.intershop.beehive.core.capi.user.User" optional="false"/>
  <parameter name="SortBy" type="java.lang.String" optional="true"/>
  <parameter name="SortByColumn" type="java.lang.String" optional="true" processing="column(SortBy,names(name,description))"/>
  <parameter name="UnsetParam" type="java.lang.String" optional="false" default="Hello World"/>
  <parameter name="NumVal" type="java.lang.Integer" optional="true" min="1" max="10" />
  <parameter name="AnotherNumVal" type="java.lang.Double" optional="true" min="123.45" />
  <parameter name="sorting" type="java.lang.String" optional="true" default="asc" values="asc, desc"/>
</parameters>

Return Mapping

This section specifies which elements are returned in the resulting iterator of select queries. It not only defines the elements to be returned by the QueryProcessor, but also their transformation into higher level objects using the return mapping performed by the QueryMgr.
It is legal to return multiple values at once. In this case, row objects ( com.intershop.beehive.core.capi.query.Row) are returned that can easily be accessed via the get method or object path expressions. The column names are case-insensitive.

Seven mapping types are implemented:

• rename
  Directly returns the value delivered by the QueryProcessor, optionally using a different name.

  <return-mappings>
  	<return-mapping name="id" type="rename">
    		<return-attribute name="SKU"/>
  	</return-mapping>
  ...
  

• orm
  Builds persistent objects using the getObjectByPrimaryKey method of the according ORM factory (as passed with the class attribute).

  <return-mappings>
  	<return-mapping name="product" type="orm" class="com.intershop.beehive.xcs.internal.product.ProductPO">
  		<return-attribute name="UUID"/>
  	</return-mapping>
  ...
  

• constructor
  Calls the constructor for the given class (as passed with the class attribute) applying the declared arguments.

  <return-mappings>
  	<return-mapping name="price" type="constructor" class="com.intershop.beehive.foundation.quantity.Money">
  		<return-attribute name="currency"/>
  		<return-attribute name="value"/>
  	</return-mapping>
  ...
  

• provider
  Returns the value delivered by the given provider class (as passed with the provider attribute). The provider is determined using NamingMgr.lookupProvider(providerName). At the provider, a create method with the corresponding parameters is called.

  <return-mappings>
  	<return-mapping name="price" type="provider" provider="ProductViewProvider">
  		<return-attribute name="UUID"/>
  		<return-attribute name="DOMAINID"/>
  	</return-mapping>
  ...

• bo-orm
  Extends the orm mapping to convert the retrieved data into a business object based on an additional mapper (as passed with the mapperclass attribute). This mapper must be an instance created via the component framework and must implement the interface com.intershop.beehive.core.capi.util.ObjectMapper.

  <return-mappings>
  	<return-mapping name="OrderBO" type="bo-orm" class="com.intershop.beehive.bts.internal.orderprocess.fulfillment.OrderPO" mapperclass="OrderPOToOrderBOMapper">
  		<return-attribute name="OrderUUID" />
  	</return-mapping>
  ...
  

• bo-constructor
  Extends the constructor mapping to convert the retrieved data into a business object based on an additional mapper (as passed with the mapperclass attribute). This mapper must be an instance created via the component framework and must implement the interface com.intershop.beehive.core.capi.util.ObjectMapper.

• bo-provider
  Extends the provider mapping to convert the retrieved data into a business object based on an additional mapper (as passed with the mapperclass attribute). This mapper must be an instance created via the component framework and must implement the interface com.intershop.beehive.core.capi.util.ObjectMapper.

  <return-mappings>
  	<return-mapping name="Product" type="bo-provider" provider="ProductViewProvider" mapperclass="ProductPOToProductBOMapper">
  		<return-attribute name="UUID"/>
  		<return-attribute name="DOMAINID"/>
  	</return-mapping>
  

The order of the attributes (the input parameters for the mappings) has to be the same as the primary key attributes for the ORM persistent object, the parameters of the constructor or the parameters of the provider. As there is no additional type mapping for these attributes, the QueryProcessor must deliver objects of the correct types.
If there is no return mapping defined, the QueryMgr will perform an identity mapping, which actually returns the original objects retrieved by the QueryProcessor. If only one value per row is returned, this value is automatically unwrapped.
In ISML templates, the returned objects can be accessed as follows:

<isloop iterator="SearchResultIterator" alias="Row">
  <isprint value="Row:ID">
  <isprint value="Row:Product:Name">
  <isprint value="Row:Price" style="MONEY_LONG">
</isloop>

Query Processor Declaration

This mandatory section defines which QueryProcessor performs the query. In addition, it allows for defining a configuration that is passed to the processor as a context. This configuration actually depends on the processor implementation and can be used, for example, for defining return types or the connection to the data source.
Furthermore, the query processor section provides for defining a query input parameter preprocessing. This can be used, for example, for translating a complex search expression into the native language of the underlying search engine. Preprocessing the query input parameters prior to the template processing allows for evaluating the actual result in the template.

Example:

<processor name="JDBC">
  <processor-configuration name="readType.StartDate" value="Timestamp.GMT"/>
  <processor-preprocessing output="containsQuery" input="SearchExpression" processing="SearchExpression2Contains"/>
</processor>

Query Templates

Statements to be executed by the QueryProcessor are expressed with templates. For select queries, there are three types of statements:

• count
  Intended to calculate integers as result.
• objects
  Intended to return objects or manipulate data.
• countedobjects
  Combined queries, intended to calculate a row count and return row(s) as result. The QueryMgr decides which template is executed depending on the expected result and specified types. For update queries, all templates are executed in the order as they are defined in the file. A query file can contain more than one query template. In this case, however, they must refer to the same business task, e.g., searching for a certain object type or performing a specific task in the database. All templates then share the same parameter set, return mapping and processor configuration.
  Templates are usually designed to produce a result that is ready to be used by the underlying search engine. This way, the QueryProcessor just has to pass the QueryStatement to the search engine.
  A template constists of query text, a tag for accessing variables, flow control tags ( if, loop, call) and a comment tag.

• variable Tag
  Passes dynamic values and predefined processing methods ( bind and text) to the QueryProcessor. Custom QueryProcessor-specific methods are possible.

  <template-variable value="text:xyz"/>
  <template-variable value="text:xyz" processing="bind"/>
  <template-variable value="ProductTable" processing="text"/>
  <template-variable value="SearchExpression" processing="contains"/>
  

  The value of the required attribute value is an object path expression that is interpreted similarly as in ISML templates. This means that literals can be used. The attribute processing is optional. The processing method bind, which is the default, means that the QueryProcessor gets tokens of type BindVariableToken. Text tokens are produced for the processing method text. All other processing methods are passed to the QueryProcessor via the CustomVariableToken. This allows for implementing special handlings for values that cannot be handled in a meaningful way in the query template. Custom QueryProcessors should also implement all preprocessing methods as custom processing methods.

• if Tag
  Used for conditional executions. In the Logical expressions for object, paths are supported.

  <template-if condition="isDefined(SortByDisplayName)">
  ...
  <if-elseif condition="isDefined(SortByUserName)"/>
  ...
  <if-else/>
  ...
  </template-if>
  

  The attribute condition is required. It is an object path expression. Branches are introduced using the elseif and else tags, which do not have any members.

• loop Tag
  Used for iterated executions.

  <template-loop iterator="ProductDomains" alias="Domain">
    <loop-statement>...</loop-statement>
    <loop-separator>...</loop-separator>
  </template-loop>
  

  The two attributes iterator and alias are required. The value of iterator is an object path expression that returns an object to be iterated, like arrays, collections, enumerations or iterators. The alias defines the name of the current loop element during the loop execution. A possibly existing value with this name is hidden for the time of the loop execution, but is visible again after the execution.

• call Tag
  Allows for reusing common query parts.

  <template-call name="inc/SearchExpression2Like">
    <call-parameter name="SearchExpression" value="SimpleSearchExpression"/>
    <call-parameter name="ColumnNames" value="'u.lastName u.firstName'"/>
    <call-parameter name="CaseInsensitive" value="true()"/>
    <call-parameter name="ConcatenationOperator" value="' AND '"/>
  </template-call>
  

  The required attribute name contains the name of the query file without the .query postfix.

  Note

  Query files can be grouped in directories. For the query lookup, the same site context as for the current query is used. The sub tags parameter specify the values passed to the sub query for execution. The sub query can access only values defined here. The sub query can contain only one template, or else the interpreter aborts the execution.

  An extension to the call tag allows for passing the entries of a map as the parameter values to the sub query. The parameter map must be of the type java.util.Map and must contain only strings as the key values. The parameter list for the call statement is processed in the order as defined in the query file. That is, later parameter assignments overwrite previous assignments. For example:

  <query>
    <parameters>
      <parameter name="SubqueryParameters" type="java.util.Map" optional="false"/>
    </parameters>
  ...
    <template>
      <template-call name="SubQuery">
        <call-addall map="SubqueryParameters"/>
        <call-parameter name="P2" value="'V2'"/>
      </template-call>
    </template>
  </query>
  

  A corresponding sub query could look like this:

  <query>
    <parameters>
      <parameter name="P1" type="java.lang.Object" optional="false"/>
      <parameter name="P2" type="java.lang.Object" optional="false"/>
      <parameter name="P3" type="java.lang.Object" optional="false"/>
    </parameters>
  ...
  </query>
  

  If the main query is executed with the SubqueryParameters map with the following key/value pairs: ("P1",1), ("P2",2) and ("P3",3), then the sub query is actually called with ("P1",1), ("P2","V2") and ("P3",3).

• comment Tag
  Used to add comments to the query template that are not passed to the search engine.

  <template>
    select uuid from product where domainid = 'xyz'
    <template-comment>and sku like 'abc%'<template-comment>
  </template>
  

  Although SQL statements have their own syntax for comments, the query framework cannot rely on a single syntax since it has to support any query language. Thus, the query framework uses its own comment tags in order to prevent comments from being submitted to the underlying search engine.

• sqlDialect Attribute
  The template-tag has the attribute sqlDialect. The content is the sql-dialect. If no attribute sqlDialect is specified, the default 'ANSI' is used. The used dialect is determined by the first word of the name of the database.

  <template type="countedobjects" sqlDialect="Oracle">
  	-- Oracle specific sql
  </template>
  <template type="countedobjects" sqlDialect="Microsoft">
  	-- MSSQL specific sql
  </template>

• sql-dialect Tag
  If the database-specific part of a statement is short, it can be wrapped into the sql-dialect tag. Its only attribute is 'name'. The name is the used sql-dialect, or the first word of the name of the database.

  <template type="countedobjects">
  	SELECT elephant FROM africa 
  	<sql-dialect name="Oracle">WHERE NVL(a,b)</sql-dialect>
  	<sql-dialect name="Microsoft">WHERE ISNULL(a,b)</sql-dialect>
  </template>

 Query SQL

Order By With NULLS LAST|FIRST

Expressing queries database independently sometimes leads to queries that are hard to read. In particular, NULL LAST|FIRST in order by clauses is not supported by all databases but often used. To ease that problem, object path expression functions are available:

• sortBy({columnDescription}): defines a sort by clause. Use the function identifier() if the column name comes from a parameter to avoid possible SQL injection attacks.
• sortBy(column()+): defines a column in a sort by clause.
• column({columnName}, names(), direction({directionString}, nullsLast(),nullsFirst()) ): defines a column. The {columnName} is required. Valid names can be defined in the names() function.
  
  • names({name}|{columnName:name}|alias({columnName},{name})): defines column names that are valid as column name. The first name or alias is the default one. An alias can be expressed using the alias({columnName,{name}}) function or as a string that separates the columnName and the logical name with a colon ':'

The following example uses a computed parameter 'SortByColumn' for the sort column name. Its value depends on the parameter 'SortBy' which can be 'customerNo' (evaluates to aliased 'c.customerno'), 'disabledflag' (evaluates to aliased 'c.customerno') or 'cp.companyname'. Any other value in 'SortBy', like 'name', 'companyname' evaluates to 'cp.companyname'.

The computed parameter 'SortByColumn' is then used as column name in the SELECT statement as well as in the <template-variable/> which evaluates to an 'ORDER BY' clause. The clause will be 'ASC' (ascending) or 'DESC' (descending), depending on the string given in parameter 'SortDirection' (which can be 'asc', 'Ascending', 'desc', 'Descending', 'A', 'D' and the likes). If sort direction is 'ASC', NULLS LAST is used. If sort direction is 'DESC', NULLS FIRST is used.

...
	<parameter name="SortDirection" type="java.lang.String" optional="true"/>
	<parameter name="SortBy" type="java.lang.String" optional="true"/>
	<parameter name="SortByColumn" type="java.lang.String" optional="true" processing="column(identifier(SortBy),names('cp.companyname','c.customerno:customerNo','cp.disabledflag:disabledflag'))"/> 
...
	SELECT c.uuid, COUNT(c.uuid) OVER() AS "ROWCOUNT", <template-variable value="SortByColumn" processing="text"/>
    FROM customer c left join companyprofile cp on cp.customerid=c.uuid
    <template-variable value="orderBy(SortByColumn, direction(SortDirection),if(isAscending(SortDirection), nullsLast(), nullsFirst()))" processing="text"/>
...

Time Stamp

The object path function timestamp() computes to a database independent function that allows to select a time-stamp.

...
	SELECT uuid, <template-variable value="timestamp()" processing="text"/> ...
...

Public Java Interfaces

The public Java interfaces are located in com.intershop.beehive.core.capi.query. All functionality that is available with the pipelets is accessible at the Java API level.

SearchExpression

As user search expressions are not a core feature of the query framework, they are not located in the core cartridge but in bc_foundation, sub package com.intershop.component.foundation.capi.search. The provided classes are SearchExpressionParser, SearchExpressionFactory and SearchExpressionParsingException.

QueryMgr

The QueryMgr is the central manager of the query framework. It provides the following methods: loadQuery, executeCountQuery, executeObjectsQuery, executePageableQuery, executeUpdateQuery, getQueryExtensions and getQueryProcessor.

• executeUpdateQuery: triggers the executeUpdateQuery of the QueryProcessor for all templates in the given query in the order as they are defined in the query file.
• getQueryExtensions: returns a list that contains the names of all queries registered for the given call extension point and the given query processor.
• getQueryProcessor: returns the QueryProcessor instance registered under the given name. Each query file defines which processor can execute the templates.

QueryStatement

A QueryStatement is the result of the execution of a query template with the given parameter values, which is passed to the QueryProcessor for execution. A QueryStatement contains a list of tokens. Three types of tokens exist:

• TextToken: a plain text fragment of the actual query for the underlying search engine.
• BindVariableToken: a variable value for the actual query to be passed directly to the underlying search engine.
• CustomVariableToken: a variable value with a processing method as specified in the query file to be used for special handling within the QueryProcessor.

QueryContext

The QueryContext contains additional information required by the QueryProcessor for the query execution. It can contain, for example, a JDBC connection and the processor configuration from the query file.

QueryParameterHandler

Before a query template is executed, the parameters can be preprocessed. A class responsible for preprocessing has to implement this interface.

QueryResult

A QueryResult is the result from a select query returned by the QueryProcessor. In addition to the rows, it also contains some meta information, e.g., the columns of the rows or the row count.

Row

A QueryResult has to return Row elements. According to the return declaration in the query file, the QueryMgr maps them to new Rows or to single elements. A Row, basically, constitutes a map that holds values for case insensitive names.

QueryProcessor

The QueryProcessor actually performs the queries. It is triggered by the QueryMgr from the execute methods. Upon customization, this interface can be implemented in order to link other search engines.

Intershop 7 provides the following QueryProcessors: the ORMQueryProcessor executes queries via Intershop 7's ORM layer and the JDBCQueryProcessor uses any JDBC connection in a generic way.

• ORMQueryProcessor
  This query processor executes queries via the ORM layer of Intershop 7. It only supports select queries with the template types objects and countedobjects. The supported parameter handlers include LastModifiedDate and SearchExpression2Contains.

• Queries are executed via the factory method getObjectsBySQLJoin(), which automatically assigns the alias to the selection table that can be used within the WHERE condition. The processor has no configuration, but uses its own query syntax instead to extract the factory name and the parameters required by the getObjectsBySQLJoin() method. The processor returns the retrieved ORM objects encapsulated in rows under the key ORMOBJECT. This allows for defining additional return mappings.
  Template syntax:

  Template Syntax

  SELECT FROM <tablename | ORM factory name> 
  [JOIN <joins>] 
  [WHERE <conditions>]

• JDBCQueryProcessor
  The generic query processor can use any JDBCConnection. It has the following configuration properties:

• • dataSourceName= the name of a datasource
  • readType.<column_name>= [Array | AsciiStream | BigDecimal | BinaryStream | Blob | Boolean | Byte | Bytes | CharacterStream | Clob | Date | Date.GMT | Double | Float | Int | Long | Object | Ref | Short | String | Time | Time.GMT | Timestamp | Timestamp.GMT | URL]
  • ResultSetCaching=[true|false] (valid from 7.10.38.28 or 7.10.40.5 or 11.6.0)
  • RowPrefetchSize= [Integer] (default:1000) (valid from 12.0.0)
  Also note the following:
  • A returned column name ROWCOUNT is interpreted as the count of rows in the result set.
  • If no connection or datasource name is given explicitly, the default data source is used.
  • Upon executing select statements, the shared read connection pool is used if there is no connection given explicitly.
  • The GMT time conversion is handled when Date objects are passed as bind parameters.
  • JDBCQueryProcessor supports the parameter handler LastModifiedDate, which makes an application server date comparable to the database sysdate,
  • JDBCQueryProcessor supports parameter handler SearchExpression2Contains, which builds a contains query from a SearchExpression.

Query processors and parameter handlers are registered in a file named resources/<cartridge_name>/naming/queryprocessors.properties, for example, specifying the following settings:

OracleSQL=com.intershop.beehive.core.capi.query.processor.oracle. \ 
	OracleSQLQueryProcessor
OracleSQL.contains=com.intershop.beehive.core.capi.query.processor.oracle. \ 
	ContainsHandler

The QueryProcessors provide the following methods: executeSelectQuery, executeUpdateQuery and getParameterHandler.

• executeSelectQuery
  Called by the QueryMgr when executing count, objects and countedobjects queries.
  
  • Parameters: QueryStatement (the statement to be executed), QueryContext (context for the processor that holds the data given by the caller of the QueryMgr method and the processor configuration of the query file)
  • Returns: QueryResult if the query was executed sucessfully
  • Exceptions: QueryRejectedException (thrown if the underlying search engine is not able to answer a formally correct query, e.g., the DRG-51030 Oracle error), or else any RuntimeException.
• executeUpdateQuery
  Called by the QueryMgr when executing update queries.
  • Parameters: QueryStatements (lists of statements to be executed), QueryContext (context for the processor that holds the data given by the caller of the QueryMgr method and the processor configuration of the query file)
  • Exceptions: RuntimeException if the query was not executed sucessfully
• getParameterHandler
  Returns an instance of QueryParameterHandler that is responsible for converting a query parameter value into a new value more suitable for the underlying search engine; and used by the QueryMgr to perform the preprocessing actions defined in the query file.
  
  • Parameter: processing (the identifier of a processing method for the handler)

  • Returns: the QueryParameterHandler or null if no processing with the specified name is defined

    Note

    The AbstractQueryProcessor already includes a working implementation of that method. Thus, there is usually no need for a custom implementation.

Query Extensions

Query files support extension points. This allows for defining a query in a base cartridge that contains a placeholder, which can be filled by sub-queries provided by other cartridges. On execution, all query templates registered for an extension point and the according query processor are called with the declared set of parameters.

Query Extension Definition

In the main query file, i.e., that of the base cartridge, the extension is defined in the template section:

<template-call extension="<extension_point_name>">
	<call-parameter name="..." value="..."/> 
	... 
</template-call>

The sub-queries are registered for an extension point via entries in specific queryprocessors.properties files, located in the cartridges' source directories in <cartridge_name>/javasource/resources/<cartridge_name>/naming.
A query registration entry would look like this:

extension.<extension_point_name>.<processor_name>.<instance_name> = <query_name>

Query Extension Example

The following example illustrates the usage of query extension points. The main query may contain, for example:

... 
<template-call extension="PermissionScopeValidatorCondition">
	<call-parameter name="Validator" value="Validator"/> 
	<call-parameter name="TableName" value="TableName"/> 
	<call-parameter name="ObjectAlias" value="ObjectAlias"/> 
</template-call> 
...

A first extension may be defined as follows in a queryprocessors.properties file:
extension.PermissionScopeValidatorCondition.OracleSQL.PCAValidator = permission/validator/PCACondition

The template section in the corresponding query file would look like this:

<template> 
	<template-if condition="Validator:Name EQ 'PCAContextValidator'"> 
	... 
	</template-if> 
</template>

A second extension may be defined as follows:
extension.PermissionScopeValidatorCondition.OracleSQL.OrganizationValidator = permission/validator/OrganizationCondition

The template section in the corresponding query file would, accordingly, look like this:

<template>
	<template-if condition="Validator:Name EQ 'OrganizationContextValidator'"> 
		... 
	</template-if>
</template>