com.filenet.api.query

Class SearchSQL

java.lang.Object

com.filenet.api.query.SearchSQL

public class SearchSQL
extends java.lang.Object

A helper class used to assist in building valid SQL statements. An instance of this class is passed in to the SearchScope.fetchObjects and SearchScope.fetchRows methods to perform a query.

The SearchSQL class can be used in either of the following ways:

• Use the helper methods to specify the parts of the SQL statement. For example, use setFromClauseInitialValue to set the name of the class being searched, setSelectList to set the names of the properties to include, and setWhereClause to specify what to search for. When the SearchSQL instance is passed in as an argument to SearchScope.fetchRows, the complete SQL statement string is constructed out of the parts supplied by the calls to the SearchSQL helper methods. You also can get the complete SQL statement string constructed by calling the toString method.
• Pass in a complete SQL statement. If you have the complete SQL statement that needs to be executed, pass the SQL statement in to either the setQueryString method or the SearchSQL(String) constructor. The SearchSQL instance is then complete, and no other SearchSQL method can be called on this instance.

The settings for the following properties can directly affect the search behavior:

• ObjectStore.DefaultQueryTimeLimit
• ObjectStore.MaxQueryTimeLimit
• ObjectStore.QueryDatabaseTimeout
• ServerCacheConfiguration.QueryPageMaxSize
• ServerCacheConfiguration.QueryPageDefaultSize
• ServerCacheConfiguration.NonPagedQueryMaxSize
• ServerCacheConfiguration.QueryCountDefaultSize
• ServerCacheConfiguration.QueryCountMaxSize

Note: Date/Time values in queries are evaluated with respect to the Content Engine server clock. If the client and server clocks are not in sync, be careful when translating relative time concepts into absolute times. For example, specifying a value that means "within the last 5 minutes" on the client will translate to a comparison to an absolute time in the SQL query, which may not mean "within the last 5 minutes" relative to the server clock.

See Also:

SQL Syntax Reference

Constructor Summary

Constructors

Constructor and Description
SearchSQL() Use this when you are going to build the SQL statement using the SearchSQL helper methods, rather than passing in the complete SQL statement.
SearchSQL(java.lang.String queryString) Specify the complete SQL statement as a string.

Method Summary

Modifier and Type	Method and Description
void	setAll() Specifies that duplicate rows can be returned in the result set.
void	setContainsRestriction(java.lang.String symbolicClassName, java.lang.String searchExpression) Restricts the query to return only items where the text in the content element or elements matches the specified string.
void	setContainsRestriction(java.lang.String symbolicClassName, java.lang.String searchExpression, java.lang.String searchDialect) Restricts the query to return only items where the text in the content element or elements matches the specified string.
void	setDistinct() Restricts the query to return only distinct rows.
void	setFolderRestriction(java.lang.String symbolicClassName, java.lang.String folderName) Restricts the query to return only items from the specified class within the specified folder.
void	setFreetextRestriction(java.lang.String symbolicClassName, java.lang.String searchExpression) Deprecated. As of release 5.2.0
void	setFromClauseAdditionalJoin(JoinOperator joinOperator, java.lang.String symbolicClassName, java.lang.String aliasName, java.lang.String joinVar1, JoinComparison joinComparison, java.lang.String joinVar2, boolean includeSubclasses) Adds another class to the FROM clause used in the SQL statement.
void	setFromClauseInitialValue(java.lang.String symbolicClassName, java.lang.String aliasName, boolean includeSubclasses) Sets the first class to be used in the FROM clause for the statement.
void	setMaxRecords(int maxRecords) Sets the maximum number of rows that can be returned in the result set.
void	setOrderByClause(java.lang.String orderByClause) Sets the ORDER BY clause to be used for the SQL statement to the specified string.
void	setQueryString(java.lang.String queryString) Specifies the complete SQL statement.
void	setSelectList(java.lang.String selectList) Sets the SELECT list for SQL statement to the string specified.
void	setTimeLimit(int timeLimit) Sets the maximum duration the query runs on the server, in seconds.
void	setWhereClause(java.lang.String whereClause) Sets the WHERE clause to be used for the SQL statement to the specified string.
java.lang.String	toString() Returns either the SQL statement constructed using the helper methods (in this case, setFromClauseInitialValue must have been called prior to this method), or the SQL statement specified in its entirety using either setQueryString or the SearchSQL(String) constructor.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

SearchSQL

public SearchSQL()

Use this when you are going to build the SQL statement using the SearchSQL helper methods, rather than passing in the complete SQL statement.

SearchSQL

public SearchSQL(java.lang.String queryString)

Specify the complete SQL statement as a string. Do not use this in conjunction with any SearchSQL methods. Calling any of methods on this instance will either overwrite or nullify the SQL statement specified here.

No SQL validation is performed on the specified string.

Parameters:

queryString - A String containing he complete SQL statement to use.

See Also:

setQueryString(String), SQL Syntax Reference

Method Detail

setSelectList

public void setSelectList(java.lang.String selectList)

Sets the SELECT list for SQL statement to the string specified. This must be called prior to calling setFromClauseAdditionalJoin.

Parameters:

selectList - A String containing the query SELECT list.

Throws:

EngineRuntimeException - Thrown when the parameter is null or invalid.

setFromClauseInitialValue

public void setFromClauseInitialValue(java.lang.String symbolicClassName,
                                      java.lang.String aliasName,
                                      boolean includeSubclasses)

Sets the first class to be used in the FROM clause for the statement.

Parameters:

symbolicClassName - A String containing the symbolic name of the class.

aliasName - A String containing the alias name of the class. This can be null.

Note: Alias names cannot be used when EngineObject objects are to be returned by the query operation.

includeSubclasses - A boolean value of true if the FROM clause should include any subclasses of the class specified in symbolicClassName; false otherwise.

Throws:

EngineRuntimeException - Thrown when a required parameter is null or invalid.

setFromClauseAdditionalJoin

public void setFromClauseAdditionalJoin(JoinOperator joinOperator,
                                        java.lang.String symbolicClassName,
                                        java.lang.String aliasName,
                                        java.lang.String joinVar1,
                                        JoinComparison joinComparison,
                                        java.lang.String joinVar2,
                                        boolean includeSubclasses)

Adds another class to the FROM clause used in the SQL statement.

Parameters:

joinOperator - A JoinOperator instance specifying the type of join to use for the additional class.

symbolicClassName - A String containing the symbolic name of the class.

aliasName - A String containing the alias name of the class. This can be null. Note: Alias names cannot be used when EngineObject objects are to be returned by the query operation.

joinVar1 - A String containing the name of a property on the initial class specified in setFromClauseInitialValue. In tandem with the property specified in joinVar2, this property specifies the ON clause constituents of the join.

joinComparison - A JoinComparison instance specifying the comparison to use for the constituents of the ON clause (joinVar1 and joinVar2).

joinVar2 - A String containing the name of a property on the class specified in symbolicClassName. In tandem with the property specified in joinVar1, this property specifies the ON clause constituents of the join.

includeSubclasses - A boolean value of true if the FROM clause should include any subclasses of the class specified in symbolicClassName; false otherwise.

Throws:

EngineRuntimeException - Thrown when a required parameter is null or invalid.

setWhereClause

public void setWhereClause(java.lang.String whereClause)

Sets the WHERE clause to be used for the SQL statement to the specified string.

Parameters:

whereClause - A String containing the WHERE clause to use.

Throws:

EngineRuntimeException - Thrown when the parameter is null or invalid.

setOrderByClause

public void setOrderByClause(java.lang.String orderByClause)

Sets the ORDER BY clause to be used for the SQL statement to the specified string.

Parameters:

orderByClause - A String containing the property or properties to use for the ORDER BY clause. You can specify multiple property names separated by commas.

Throws:

EngineRuntimeException - Thrown when the parameter is null or invalid.

setFolderRestriction

public void setFolderRestriction(java.lang.String symbolicClassName,
                                 java.lang.String folderName)

Restricts the query to return only items from the specified class within the specified folder.

Parameters:

symbolicClassName - A String containing the symbolic name of the class.

folderName - A String containing the name of the folder storing the class.

Throws:

EngineRuntimeException - Thrown when the parameters are null or invalid.

setFreetextRestriction

public void setFreetextRestriction(java.lang.String symbolicClassName,
                                   java.lang.String searchExpression)

Deprecated. As of release 5.2.0

Restricts the query to return only items where the text in the content elements matches the specified string. This method uses the FREETEXT function for CBR (Content-Based Retrieval) queries.

The FREETEXT function searches all content on all CBR-enabled properties on the supported content elements. To search content on a single CBR-enabled property, use setContainsRestriction.

Parameters:

symbolicClassName - A String containing the symbolic name of the class.

searchExpression - A String containing the search text to use for the FREETEXT function.

Throws:

EngineRuntimeException - Thrown when the parameters are null or invalid.

See Also:

setContainsRestriction(java.lang.String, java.lang.String), The FREETEXT Function in the SQL Syntax Reference

setContainsRestriction

public void setContainsRestriction(java.lang.String symbolicClassName,
                                   java.lang.String searchExpression)

Restricts the query to return only items where the text in the content element or elements matches the specified string. This method uses the CONTAINS function for CBR (Content-Based Retrieval) queries.

The CONTAINS function can perform the search on CBR-enabled properties for the supported content elements in any of the following ways:

• Search a single property
• Search all properties within a specified zone
• Search all properties

Parameters:

symbolicClassName - A String containing the symbolic name of the class.

searchExpression - A String containing the search text to use for the CONTAINS function.

Throws:

EngineRuntimeException - Thrown when the parameters are null or invalid.

See Also:

setFreetextRestriction(java.lang.String, java.lang.String), The CONTAINS Function in the SQL Syntax Reference

setContainsRestriction

public void setContainsRestriction(java.lang.String symbolicClassName,
                                   java.lang.String searchExpression,
                                   java.lang.String searchDialect)

Restricts the query to return only items where the text in the content element or elements matches the specified string. This method uses the CONTAINS function for CBR (Content-Based Retrieval) queries.

The CONTAINS function can perform the search on CBR-enabled properties for the supported content elements in any of the following ways:

• Search a single property
• Search all properties within a specified zone
• Search all properties

Parameters:

symbolicClassName - A String containing the symbolic name of the class.

searchExpression - A String containing the search text to use for the CONTAINS function.

searchDialect - A String containing the search dialect to use which can be Lucene, K2VQL or null.

Throws:

E_NULL_OR_INVALID_PARAM_VALUE - If symbolicClassName or searchExpression is null or has a length of zero.

CBR_INVALID_SEARCH_DIALECT - If searchDialect is not null and neither Lucene nor K2VQL.

See Also:

setFreetextRestriction(java.lang.String, java.lang.String), The CONTAINS Function in the SQL Syntax Reference

setDistinct

public void setDistinct()

Restricts the query to return only distinct rows. This setting is valid only when all properties in the select list are orderable (as specified in the ORDER BY clause).

See Also:

setOrderByClause(java.lang.String)

setAll

public void setAll()

Specifies that duplicate rows can be returned in the result set.

setMaxRecords

public void setMaxRecords(int maxRecords)

Sets the maximum number of rows that can be returned in the result set. When unspecified, all records that satisfy the query will be returned, subject to the limit of ServerCacheConfiguration property NonPagedQueryMaxSize.

Parameters:

maxRecords - An integer specifying the maximum number of rows to be returned. If unspecified, the limit is determined by ServerCacheConfiguration.NonPagedQueryMaxSize.

Throws:

EngineRuntimeException - Thrown when the parameter is invalid.

See Also:

ServerCacheConfiguration.get_QueryPageDefaultSize, ServerCacheConfiguration.get_QueryPageMaxSize, ServerCacheConfiguration.get_NonPagedQueryMaxSize

setTimeLimit

public void setTimeLimit(int timeLimit)

Sets the maximum duration the query runs on the server, in seconds. When unspecified, the value of the ObjectStore property DefaultQueryTimeLimit is used.

Warning: Date/Time values in queries are evaluated with respect to the Content Engine server clock. If the client and server clocks are not in sync, be careful when translating relative time concepts into absolute times. For example, specifying a value that means "within the last 5 minutes" on the client will translate to a comparison to an absolute time in the SQL query, which may not mean "within the last 5 minutes" relative to the server clock.

Parameters:

timeLimit - An integer specifying the maximum duration of the query. If unspecified, ObjectStore.DefaultQueryTimeLimit is used.

Throws:

EngineRuntimeException - Thrown when the parameter is invalid.

See Also:

ObjectStore.get_DefaultQueryTimeLimit

setQueryString

public void setQueryString(java.lang.String queryString)

Specifies the complete SQL statement. This method cannot be used in conjunction with any other set method on this class. Doing so will have the following effect:

• Any values set by calling another SearchSQL set method (such as, setMaxRecords), then calling this method will overwrite or nullify the value initially set.
• Calling this method, then calling any other SearchSQL set method will overwrite or nullify the SQL statement specified here.

No SQL validation is performed on the specified string.

Parameters:

queryString - A String containing the SQL statement to use.

See Also:

SQL Syntax Reference

toString

public java.lang.String toString()

Returns either the SQL statement constructed using the helper methods (in this case, setFromClauseInitialValue must have been called prior to this method), or the SQL statement specified in its entirety using either setQueryString or the SearchSQL(String) constructor.

Overrides:

toString in class java.lang.Object

Returns:

A String containing the SQL statement used for this instance.

Throws:

EngineRuntimeException - Thrown when the SQL statement is null. Either the SQL statement was not set (constructed or specified in its entirety), or was nullified by calling one of the SearchSQL methods subsequent to using either setQueryString or the SearchSQL(String) constructor.

See Also:

setFromClauseInitialValue(java.lang.String, java.lang.String, boolean), setQueryString(java.lang.String)