1 Introduction

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

2 Pipelets

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

2.2 LoadQuery

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

* One of the two parameter is required.

2.3 UpdateDictionary

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

2.4 ExecuteCountQuery

This pipelet executes count queries.

2.5 ExecuteObjectsQuery

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

2.6 ExecutePageableQuery

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

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

3 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="OracleSQL">
</processor>
<template type="countedobjects" sqlDialect="ANSI">
  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>

3.1 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 by the QueryMgr.

<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"/>
</parameters>

3.2 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. And 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>

3.3 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="OracleSQL">
  <processor-configuration name="readType.StartDate" value="Timestamp.GMT"/>
  <processor-preprocessing output="containsQuery" input="SearchExpression" processing="SearchExpression2Contains"/>
</processor>

3.4 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 future, logical expressions for object paths will be supported; currently only simple evaluations are possible.

  <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

  Since 7.10
  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
  Since 7.10
  If the database-specific part of a statement is short, it can be wrapped into the sql-dialect tag. It 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>

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

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

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

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

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

4.5 QueryParameterHandler

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

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

4.7 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, consitutes a map that holds values for case insensitive names.

4.8 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 three QueryProcessors: the ORMQueryProcessor executes queries via Intershop 7's ORM layer, the JDBCQueryProcessor uses any JDBC connection in a generic way, and the OracleSQLQueryProcessor as a subclass provides particular support for Oracle databases, like custom processing for context index search.

• 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 this 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 exctract 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:

  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]
    Note also that:
  • 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.
• OracleSQLQueryProcessor
  This subclass of JDBCQueryProcessor has the following extensions:
  • handles the Oracle DRG-51030 error that can be thrown on contains queries,
  • handles the GMT time conversion when Date objects are passed as bind parameters,
  • supports the parameter handler LastModifiedDate, which makes an application server date comparable to the database sysdate,
  • 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.

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

5.1 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>

5.2 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>