Content Search Guide

CHAPTER 9

Implementing SQL-Based Searching

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

The following topics are covered:

 
Top of page

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:

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

  2. Instantiate a document metadata query object, as follows:

      com.sssw.cm.api.EbiDocQuery query = (EbiDocQuery) contentMgr.createQuery(EbiDocQuery.DOC_QUERY);
    
  3. Select the fields to be returned by calling SELECT methods on the EbiDocQuery object.

    EbiDocQuery inherits SELECT methods from com.sssw.cm.api.EbiDocMetaDataQuery.

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

  5. Concatenate the WHERE subclauses by calling methods on EbiQueryExpression to combine terms using logical operators and parentheses.

    For more information    For more details, see Building the search criteria.

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

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

 
Top of page

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.

 
Top of section

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

 
Top of section

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

 
Top of section

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:

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

    For

    Use

    ID

    whereFieldID*() methods

    Name

    whereFieldName*() methods

  2. Construct a second expression that specifies the desired value of the field.

    Use any whereFieldValue*() method.

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

 
Top of page

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:

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:

For more information    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:



Copyright © 2004 Novell, Inc. All rights reserved. Copyright © 1997, 1998, 1999, 2000, 2001, 2002, 2003 SilverStream Software, LLC. All rights reserved.  more ...