com.flexive.shared.search.query
Class SqlQueryBuilder

java.lang.Object
  extended by com.flexive.shared.search.query.SqlQueryBuilder
All Implemented Interfaces:
java.io.Serializable

public class SqlQueryBuilder
extends java.lang.Object
implements java.io.Serializable

Query Builder for flexive SQL queries. Supports incremental adding of conditions, filter and order by columns.

Version:
$Rev: 2906 $
Author:
Daniel Lichtenberger (daniel.lichtenberger@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
See Also:
Serialized Form

Field Summary
static int COL_USERPROPS
          Start column for user defined properties
(package private) static long serialVersionUID
           
 
Constructor Summary
SqlQueryBuilder()
          Instantiates an empty query builder.
SqlQueryBuilder(ResultLocation location, ResultViewType viewType)
          Instantiates an empty query builder for the specified result location and view type.
SqlQueryBuilder(SqlQueryBuilder other)
          Copy constructor.
 
Method Summary
 SqlQueryBuilder addFilterBriefcase(long briefcaseId)
          Return only objects of the given briefcase, add this to an existing briefcase filter if one exists.
protected  void addSelectColumns(java.lang.String... columns)
           
 SqlQueryBuilder allRows()
          Remove row limitations on the search result and fetch all rows.
 SqlQueryBuilder andSub()
          Shorthand for #enterSub(Operator) with QueryOperatorNode.Operator.AND as the operator.
 void clearOrderBy()
          Clears the current order by settings.
 SqlQueryBuilder closeSub()
          Close a scope.
 SqlQueryBuilder condition(FxAssignment assignment, PropertyValueComparator comparator, FxValue<?,?> value)
          Render a condition for a common FxAssignment query.
 SqlQueryBuilder condition(FxProperty property, PropertyValueComparator comparator, FxValue<?,?> value)
          Render a condition for a common FxProperty query.
 SqlQueryBuilder condition(java.lang.String propertyName, PropertyValueComparator comparator, FxValue<?,?> value)
          Render a condition for a FxProperty query.
 SqlQueryBuilder condition(java.lang.String propertyName, PropertyValueComparator comparator, java.lang.Object value)
          Render a condition for a FxProperty query.
 SqlQueryBuilder copy()
          Returns a new instance of this query builder.
 SqlQueryBuilder enterSub(QueryOperatorNode.Operator operator)
          Create a new subquery scope with the given operator.
 SqlQueryBuilder fetchRows(int fetchRows)
          Set the maximum number of rows to be returned to the caller when using getResult().
 SqlQueryBuilder filterBriefcase(long briefcaseId)
          Return only objects of the given briefcase.
 SqlQueryBuilder filterType(long typeId)
          Uses a content type filter for the given content type.
 SqlQueryBuilder filterType(java.lang.String name)
          Uses a content type filter for the given content type name.
 SqlQueryBuilder filterVersion(VersionFilter filter)
          Filter the result set using the given version filter mode (live, max, auto or all).
 SqlQueryBuilder fulltext(java.lang.String value)
          Performs a fulltext query over all indexed properties against the given text.
 java.util.List<java.lang.String> getColumnNames()
          Return the selected column names.
 java.lang.String getConditions()
          Return the conditional ("WHERE") statement(s) contained in this query.
 int getFetchRows()
           
 java.lang.String getFilters()
          Return the filters defined for this query (if any).
 ResultLocation getLocation()
          Return the result location used in this query builder.
 int getMaxRows()
           
 java.lang.String getOrderBy()
          Return the order by columns defined for this query.
 FxSQLSearchParams getParams()
           
 java.lang.String getQuery()
          Returns the complete query generated by the builder.
 FxResultSet getResult()
          Convenience method for executing a search query using this query builder.
 int getStartRow()
           
 ResultViewType getViewType()
          Return the result view type used in this query builder.
 SqlQueryBuilder isChild(long nodeId)
          Limits the (sub-)query to children of the given node.
 SqlQueryBuilder isChild(java.lang.String path)
          Limits the (sub-)query to children of the given node.
 SqlQueryBuilder isDirectChild(long nodeId)
          Limits the (sub-)query to the direct children of the given node.
 SqlQueryBuilder isDirectChild(java.lang.String path)
          Limits the (sub-)query to the direct children of the given node.
 boolean isFrozen()
          Return true if this query builder is frozen and cannot be further modified.
 boolean isIncludeBasicSelects()
          Returns true if the predefined columns are selected by the condition.
 SqlQueryBuilder maxRows(int maxRows)
          Set the maximum number of rows that the query should return.
 SqlQueryBuilder noResultInfo()
          Don't fetch result set metadata such as the total number of rows, found content types, or XPaths of the selected values.
 SqlQueryBuilder orderBy(int columnIndex, SortDirection direction)
          Order the results by the given column (1-based).
 SqlQueryBuilder orderBy(java.lang.String column, SortDirection direction)
          Order the results by the given column.
 SqlQueryBuilder orderByColumns(java.util.List<Pair<java.lang.String,SortDirection>> columns)
          Order the results by the given column names.
 SqlQueryBuilder orderByColumns(Pair<java.lang.String,SortDirection>... columns)
          Order the results by the given column names.
 SqlQueryBuilder orderByIndices(java.util.List<Pair<java.lang.Integer,SortDirection>> columns)
          Order the results by the given column indices (1-based).
 SqlQueryBuilder orderByIndices(Pair<java.lang.Integer,SortDirection>... columns)
          Order the results by the given column indices (1-based).
 SqlQueryBuilder orSub()
          Shorthand for #enterSub(Operator) with QueryOperatorNode.Operator.OR as the operator.
 SqlQueryBuilder saveInBriefcase(java.lang.String name)
          Saves the result of this query in a briefcase (if the result is obtained via #getResult()).
 SqlQueryBuilder saveInBriefcase(java.lang.String name, java.lang.String description, long aclId)
          Saves the result of this query in a briefcase (if the result is obtained via #getResult()).
 SqlQueryBuilder searchLanguages(java.util.Collection<FxLanguage> languages)
          Limit the language(s) in which queries are performed (applied to multilingual properties only).
 SqlQueryBuilder select(java.util.Collection<java.lang.String> columns)
          Select one or more columns.
 SqlQueryBuilder select(java.lang.String... columns)
          Select one or more columns.
 SqlQueryBuilder setIncludeBasicSelects(boolean includeBasicSelects)
          Enables or disables the default select columns.
 SqlQueryBuilder startRow(int startRow)
           
 SqlQueryBuilder timeout(int seconds)
          Sets the query timeout in seconds.
 SqlQueryBuilder type(long typeId)
          Adds a condition that selects only objects of the given type.
 SqlQueryBuilder type(long typeId, boolean includeSubTypes)
          Adds a condition that selects only objects of the given type.
 SqlQueryBuilder type(java.lang.String typeName)
          Adds a condition that selects only objects of the given type.
 SqlQueryBuilder type(java.lang.String typeName, boolean includeSubTypes)
          Adds a condition that selects only objects of the given type.
 SqlQueryBuilder viewType(ResultViewType viewType)
           
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

serialVersionUID

static final long serialVersionUID
See Also:
Constant Field Values

COL_USERPROPS

public static final int COL_USERPROPS
Start column for user defined properties

Constructor Detail

SqlQueryBuilder

public SqlQueryBuilder()
Instantiates an empty query builder.


SqlQueryBuilder

public SqlQueryBuilder(ResultLocation location,
                       ResultViewType viewType)
Instantiates an empty query builder for the specified result location and view type.

Parameters:
location - the result location
viewType - the view type

SqlQueryBuilder

public SqlQueryBuilder(SqlQueryBuilder other)
Copy constructor. Creates an independent query builder based on the query builder other

Parameters:
other - the query builder to be copied
Method Detail

copy

public SqlQueryBuilder copy()
Returns a new instance of this query builder.

Returns:
a new instance of this query builder.
Since:
3.1

getQuery

public java.lang.String getQuery()
Returns the complete query generated by the builder.

Returns:
the SQL search query

getFilters

public java.lang.String getFilters()
Return the filters defined for this query (if any).

Returns:
the filters defined for this query

getOrderBy

public java.lang.String getOrderBy()
Return the order by columns defined for this query.

Returns:
the order by columns defined for this query.

condition

public SqlQueryBuilder condition(FxAssignment assignment,
                                 PropertyValueComparator comparator,
                                 FxValue<?,?> value)
Render a condition for a common FxAssignment query.

Parameters:
assignment - the assignment
comparator - the comparator to be used
value - the value to be compared against
Returns:
this

condition

public SqlQueryBuilder condition(FxProperty property,
                                 PropertyValueComparator comparator,
                                 FxValue<?,?> value)
Render a condition for a common FxProperty query.

Parameters:
property - the property
comparator - the comparator to be used
value - the value to be compared against
Returns:
this

condition

public SqlQueryBuilder condition(java.lang.String propertyName,
                                 PropertyValueComparator comparator,
                                 FxValue<?,?> value)
Render a condition for a FxProperty query.

Parameters:
propertyName - the property name
comparator - the comparator to be used
value - the value to be compared against
Returns:
this

condition

public SqlQueryBuilder condition(java.lang.String propertyName,
                                 PropertyValueComparator comparator,
                                 java.lang.Object value)
Render a condition for a FxProperty query.

Parameters:
propertyName - the property name
comparator - the comparator to be used
value - the value to be compared against
Returns:
this

fulltext

public SqlQueryBuilder fulltext(java.lang.String value)
Performs a fulltext query over all indexed properties against the given text.

Parameters:
value - the fulltext query
Returns:
this

type

public SqlQueryBuilder type(java.lang.String typeName)
Adds a condition that selects only objects of the given type. Note that this is different to filterType(String), since filtering is applied on the result that also may contain other types, but this is a standard search condition that restrains the search result.

Parameters:
typeName - the type name to be selected
Returns:
this

type

public SqlQueryBuilder type(java.lang.String typeName,
                            boolean includeSubTypes)
Adds a condition that selects only objects of the given type. Note that this is different to filterType(String), since filtering is applied on the result that also may contain other types, but this is a standard search condition that restrains the search result.

Parameters:
typeName - the type name to be selected
includeSubTypes - if subtypes should be included
Returns:
this
Since:
3.1

type

public SqlQueryBuilder type(long typeId)
Adds a condition that selects only objects of the given type. Note that this is different to filterType(long), since filtering is applied on the result that also may contain other types, but this is a standard search condition that restrains the search result.

Parameters:
typeId - the type id to be selected
Returns:
this

type

public SqlQueryBuilder type(long typeId,
                            boolean includeSubTypes)
Adds a condition that selects only objects of the given type. Note that this is different to filterType(long), since filtering is applied on the result that also may contain other types, but this is a standard search condition that restrains the search result.

Parameters:
typeId - the type id to be selected
includeSubTypes - if subtypes should be included
Returns:
this
Since:
3.1

isChild

public SqlQueryBuilder isChild(long nodeId)
Limits the (sub-)query to children of the given node.

Parameters:
nodeId - the root node for the (sub-)query
Returns:
this

isChild

public SqlQueryBuilder isChild(java.lang.String path)
Limits the (sub-)query to children of the given node.

Parameters:
path - the tree path of the node
Returns:
this

isDirectChild

public SqlQueryBuilder isDirectChild(long nodeId)
Limits the (sub-)query to the direct children of the given node.

Parameters:
nodeId - the root node for the (sub-)query
Returns:
this

isDirectChild

public SqlQueryBuilder isDirectChild(java.lang.String path)
Limits the (sub-)query to the direct children of the given node.

Parameters:
path - the tree path of the node
Returns:
this

getConditions

public java.lang.String getConditions()
Return the conditional ("WHERE") statement(s) contained in this query. If the initial subquery scope is still open, it will be closed.

Returns:
the conditional statement(s)

getColumnNames

public java.util.List<java.lang.String> getColumnNames()
Return the selected column names.

Returns:
the selected column names.

select

public SqlQueryBuilder select(java.lang.String... columns)
Select one or more columns.

Parameters:
columns - columns to be selected
Returns:
this

addSelectColumns

protected void addSelectColumns(java.lang.String... columns)

select

public SqlQueryBuilder select(java.util.Collection<java.lang.String> columns)
Select one or more columns.

Parameters:
columns - columns to be selected
Returns:
this
Since:
3.1

filterBriefcase

public SqlQueryBuilder filterBriefcase(long briefcaseId)
Return only objects of the given briefcase.

Parameters:
briefcaseId - the briefcase ID
Returns:
this

addFilterBriefcase

public SqlQueryBuilder addFilterBriefcase(long briefcaseId)
Return only objects of the given briefcase, add this to an existing briefcase filter if one exists.

Parameters:
briefcaseId - the briefcase ID
Returns:
this
Since:
3.2.0

filterType

public SqlQueryBuilder filterType(java.lang.String name)
Uses a content type filter for the given content type name.

Parameters:
name - the content type name
Returns:
this

filterType

public SqlQueryBuilder filterType(long typeId)
Uses a content type filter for the given content type.

Parameters:
typeId - the content type ID, or -1 to disable the content type filter
Returns:
this

filterVersion

public SqlQueryBuilder filterVersion(VersionFilter filter)
Filter the result set using the given version filter mode (live, max, auto or all).

Parameters:
filter - the version filter to be applied
Returns:
this

searchLanguages

public SqlQueryBuilder searchLanguages(java.util.Collection<FxLanguage> languages)
Limit the language(s) in which queries are performed (applied to multilingual properties only).

Parameters:
languages - the language(s)
Returns:
this
Since:
3.1.6

orderBy

public SqlQueryBuilder orderBy(java.lang.String column,
                               SortDirection direction)
Order the results by the given column. Any previously set order by columns are removed.

Parameters:
column - the column to be sorted
direction - the sort direction
Returns:
this

orderBy

public SqlQueryBuilder orderBy(int columnIndex,
                               SortDirection direction)
Order the results by the given column (1-based). Any previously set order by columns are removed.

Parameters:
columnIndex - the 1-based index of the column to be sorted
direction - the sort direction
Returns:
this

orderByIndices

public SqlQueryBuilder orderByIndices(Pair<java.lang.Integer,SortDirection>... columns)
Order the results by the given column indices (1-based). Any previously set order by columns are removed.

Parameters:
columns - the order by columns
Returns:
this
Since:
3.1

orderByIndices

public SqlQueryBuilder orderByIndices(java.util.List<Pair<java.lang.Integer,SortDirection>> columns)
Order the results by the given column indices (1-based). Any previously set order by columns are removed.

Parameters:
columns - the order by columns
Returns:
this
Since:
3.1.5

orderByColumns

public SqlQueryBuilder orderByColumns(Pair<java.lang.String,SortDirection>... columns)
Order the results by the given column names. Any previously set order by columns are removed.

Parameters:
columns - the order by columns
Returns:
this
Since:
3.1

orderByColumns

public SqlQueryBuilder orderByColumns(java.util.List<Pair<java.lang.String,SortDirection>> columns)
Order the results by the given column names. Any previously set order by columns are removed.

Parameters:
columns - the order by columns
Returns:
this
Since:
3.1.5

clearOrderBy

public void clearOrderBy()
Clears the current order by settings.


isIncludeBasicSelects

public boolean isIncludeBasicSelects()
Returns true if the predefined columns are selected by the condition.

Returns:
true if the predefined columns are selected by the condition.

setIncludeBasicSelects

public SqlQueryBuilder setIncludeBasicSelects(boolean includeBasicSelects)
Enables or disables the default select columns. By default they are included only if no select clause was specified by select(String...).

Parameters:
includeBasicSelects - true if the pre-defined selects should be included
Returns:
this

enterSub

public SqlQueryBuilder enterSub(QueryOperatorNode.Operator operator)
Create a new subquery scope with the given operator. Subsequent conditions will be added to that scope. Not that all subscopes (except the first one) must be closed before the query can be generated.

Parameters:
operator - the operator to be used inside the new scope
Returns:
this

andSub

public SqlQueryBuilder andSub()
Shorthand for #enterSub(Operator) with QueryOperatorNode.Operator.AND as the operator.

Returns:
this

orSub

public SqlQueryBuilder orSub()
Shorthand for #enterSub(Operator) with QueryOperatorNode.Operator.OR as the operator.

Returns:
this

closeSub

public SqlQueryBuilder closeSub()
Close a scope.

Returns:
this

isFrozen

public boolean isFrozen()
Return true if this query builder is frozen and cannot be further modified. A query builder freezes when the query or the query conditions are queried with the getQuery() and getConditions() methods. A frozen query builder does not guarantee, however, that the query itself is valid.

Returns:
true if this query builder is frozen and cannot be further modified.

getLocation

public ResultLocation getLocation()
Return the result location used in this query builder.

Returns:
the result location used in this query builder.

getViewType

public ResultViewType getViewType()
Return the result view type used in this query builder.

Returns:
the result view type used in this query builder.

viewType

public SqlQueryBuilder viewType(ResultViewType viewType)

getStartRow

public int getStartRow()

startRow

public SqlQueryBuilder startRow(int startRow)

getMaxRows

public int getMaxRows()

maxRows

public SqlQueryBuilder maxRows(int maxRows)
Set the maximum number of rows that the query should return. This determines the size of the entire result set. You can then specify a window of rows using startRow(int) and fetchRows(int) to return a subset of the search result.

This value defaults to SearchEngine.DEFAULT_MAX_ROWS.

Note: Be aware that restricting the result size with this parameter can lead to random results, especially in combination with ORDER BY. This is because first the result set is built using at most maxRows rows, which are then sorted and returned to the client. In general you might want to increase this value to guarantee precise results for large datasets and unspecific queries.

Parameters:
maxRows - the maximum number of rows in the query result
Returns:
this

getFetchRows

public int getFetchRows()

fetchRows

public SqlQueryBuilder fetchRows(int fetchRows)
Set the maximum number of rows to be returned to the caller when using getResult(). Unless specified, all rows will be returned.

Parameters:
fetchRows - the number of rows to be returned
Returns:
this
Since:
3.1

allRows

public SqlQueryBuilder allRows()
Remove row limitations on the search result and fetch all rows.

Returns:
this
Since:
3.2.0

noResultInfo

public SqlQueryBuilder noResultInfo()
Don't fetch result set metadata such as the total number of rows, found content types, or XPaths of the selected values. This speeds up queries considerably in cases where these informations are not required (usually for internal lookup queries).

Returns:
this
Since:
3.2.0

saveInBriefcase

public SqlQueryBuilder saveInBriefcase(java.lang.String name,
                                       java.lang.String description,
                                       long aclId)
Saves the result of this query in a briefcase (if the result is obtained via #getResult()).

Parameters:
name - the briefcase name (required)
description - the briefcase description (optional)
aclId - the briefcase ACL (if -1, the briefcase will be private for the calling user)
Returns:
this

timeout

public SqlQueryBuilder timeout(int seconds)
Sets the query timeout in seconds. Will be applied only when the result is fetched with getResult().

Parameters:
seconds - the query timeout in seconds (default: 120 seconds)
Returns:
this
Since:
3.0.2

saveInBriefcase

public SqlQueryBuilder saveInBriefcase(java.lang.String name)
Saves the result of this query in a briefcase (if the result is obtained via #getResult()).

Parameters:
name - the briefcase name (required)
Returns:
this

getResult

public FxResultSet getResult()
                      throws FxApplicationException
Convenience method for executing a search query using this query builder.

Returns:
the search result for the current query
Throws:
FxApplicationException - if the search failed

getParams

public FxSQLSearchParams getParams()
Returns:
the optional search parameters for submitting the query.
Since:
3.1.6