Implementing SQL-Based Searching

This chapter describes how to implement SQL-based searching in exteNd Director applications.

The following topics are covered:

Logic flow for implementing SQL-based search

You can write search components to implement SQL-based searching in exteNd Director applications. Use the following logic flow in the getComponentData() method of the exteNd Director component:

Instantiate a content manager delegate, as follows:
```
  com.sssw.cm.api.EbiContentMgmtDelegate contentMgr = com.sssw.cm.client.EboFactory.getDefaultContentMgmtDelegate();
```
NOTE: A delegate is a wrapper that hides the location of a service. The delegate model follows the J2EE Business Delegate pattern. For more information about delegates, see the chapter on coding Java for exteNd Director applications in Developing exteNd Director Applications.

Instantiate a document metadata query object, as follows:

  com.sssw.cm.api.EbiDocQuery query = (EbiDocQuery) contentMgr.createQuery(EbiDocQuery.DOC_QUERY);

Select the fields to be returned by calling SELECT methods on the EbiDocQuery object.

EbiDocQuery inherits SELECT methods from com.sssw.cm.api.EbiDocMetaDataQuery.
Specify the search criteria by calling WHERE methods on the EbiDocQuery object.

Each method returns a com.sssw.fw.api.EbiQueryExpression object, which represents a SQL WHERE subclause.
Concatenate the WHERE subclauses by calling methods on EbiQueryExpression to combine terms using logical operators and parentheses.

For more details, see Building the search criteria.
Specify the order in which to return results in ORDER BY clause(s) by calling ORDERBY methods on the com.sssw.cm.api.EbiDocMetaDataQuery object.
Execute the search by calling findElements() on the EbiContentMgmtDelegate object.

The findElements() method executes the query and returns a collection of EbiDocument objects. The collection contains the IDs and data for only those fields you designated using SELECT methods in Step 3. As such, the objects in the collection are not complete representations of the documents in the CM repository.

Building the search criteria

You specify search criteria for metadata by calling WHERE methods on the document metadata query object com.sssw.cm.api.EbiDocQuery. WHERE methods match values against data and include a NOT parameter that can negate the clause.

For standard metadata, use WHERE methods that correspond to the property of interest. For example, if you want to find documents whose author is Smith, use the whereAuthor() method.
For custom (extension) metadata, use the whereField*() methods, as described in Defining criteria for searching custom metadata.

Using operators to match values against data

WHERE methods can match values against data by using SQL, relational, or string operators defined in com.sssw.fw.api.EbiMetaDataQuery, a generic interface for defining SQL queries over metadata.

Here is a summary of available operators:

Constant	Definition
SQL operators
OP_BETWEEN	SQL BETWEEN operator
OP_IN	SQL IN operator
OP_IS_NULL	SQL IS NULL operator (the value argument in the WHERE method is not ignored)
Relational operators
ROP_EQUAL	Is equal to (=)
ROP_GEQ	Is greater than or equal to (>=)
ROP_GREATER	Is greater than (>)
ROP_LEQ	Is less than or equal to (<=)
ROP_LESS	Is less than (<)
String operators
SOP_ENDS_WITH	Whether the target string ends with the specified character sequence
SOP_EQUALS_IGNORE_CASE	Whether two strings are equivalent without considering case
SOP_LIKE	SQL LIKE operator
SOP_LIKE_IGNORE_CASE	SQL LIKE operator, where the case of the operands is ignored
SOP_STARTS_WITH	Whether the target string begins with the specified character sequence

Concatenating WHERE expressions

When you need to connect WHERE expressions for several metadata fields, you can concatenate them using com.sssw.fw.api.EbiQueryExpression methods. Here are some examples illustrating patterns that assign the result to expression1:

Example	Pattern
`expression1.andExpression(expression2);`	Join two expressions using AND
`expression1.parenthesize();`	Enclose expression in parentheses before joining it to another expression
`expression1.orExpression(expression3);`	Join two expressions using OR

Defining criteria for searching custom metadata

To define criteria for searching custom (extension) metadata fields, use whereField*() methods defined on com.sssw.cm.api.EbiDocQuery.

Here are the steps to follow:

Construct an expression that identifies the field of interest, by either ID or name:

For

Use

ID

whereFieldID*() methods

Name

whereFieldName*() methods
Construct a second expression that specifies the desired value of the field.

Use any whereFieldValue*() method.
Concatenate the WHERE expressions you just created using the andExpression() method and the parenthesize() method.

The resulting expression restricts the search to the field identified in Step 1 and the values specified in Step 2.

TIP: To search another field, set up another pair of field identifier/value expressions in the same way and concatenate the result with the rest of the WHERE clause.

For	Use
ID	whereFieldID*() methods
Name	whereFieldName*() methods

Example: searching standard document metadata

The following example illustrates how to perform a SQL-based search on standard document metadata. In this example, a method called executeDocMetaSearch() finds documents of type Movie Review that meet the following search criteria:

Movie was released between 1990 and 2000

Author is JSmith or title starts with A

  public void executeDocMetaSearch(EbiContentMgmtDelegate cmgr, EbiContext context, String mrDocTypeID, String yorFieldID)
          throws EboUnrecoverableSystemException, EboSecurityException, EboItemExistenceException
      {
          // Search for all the Movie Review documents where
          // (Author is 'JSmith' or Title starts with 'A')
          // AND
          // where YearOfRelease is between the year 1990 and the year 2000.
  
          EbiDocQuery docQuery = (EbiDocQuery)cmgr.createQuery(EbiDocQuery.DOC_QUERY);
          docQuery.selectAll();
  
          EbiQueryExpression expr = docQuery.whereDocTypeID(mrDocTypeID, EbiDocQuery.ROP_EQUAL, false);
          EbiQueryExpression expr2 = docQuery.whereAuthor("JSmith", EbiDocQuery.ROP_EQUAL, false);
          EbiQueryExpression expr3 = docQuery.whereTitle("A", EbiDocQuery.SOP_STARTS_WITH, false);
  
  	 	 EbiQueryExpression expr4 = docQuery.whereFieldValueBetween(yorFieldID, EboMisc.getInteger(1990), EboMisc.getInteger(2000), false);
  
          // (Author is 'JSmith' or Title starts with 'A)
          expr2.orExpression(expr3);
          expr2.parenthesize();
  
          // YearOfRelease was between the year 1990 and the year 2000
          expr4.andExpression(expr5);
  
          // (Author is 'JSmith' or whose Title starts with 'A')
          // AND
          // YearOfRelease was between the year 1990 and the year 2000
          expr.andExpression(expr2);
          expr.andExpression(expr4);
  
          docQuery.setWhere(expr);
  
          // Sort results by creation date/time, in ascending order
          docQuery.orderByCreateDate(true);
  
          // Execute the search and filter the results based
          // on security constraints
          Collection results = cmgr.findElementsFiltered(context, docQuery);
      }

In this example, the com.sssw.cm.api.EbiDocQuery.whereFieldValueBetween() method allows you to specify your custom metadata query in a single line of code. Other similar methods include:

com.sssw.cm.api.EbiDocQuery.whereFieldValue()
com.sssw.cm.api.EbiDocQuery.whereFieldValue_ByName()
com.sssw.cm.api.EbiDocQuery.whereFieldValueBetween_ByName()
com.sssw.cm.api.EbiDocQuery.whereFieldValueIn()
com.sssw.cm.api.EbiDocQuery.whereFieldValueIn_ByName()

For more information on these methods, see the javadoc for EbiDocQuery.

The EbiDocument objects in the returned collection contain all the properties that have been defined for the movie reviews in ascending order by creation date.

The executeDocMetaSearch() method accesses the following objects as input arguments:

Content manager—EbiContentMgmtDelegate
Context object—EbiContext
Document type
Field of interest