com.flexive.shared.search.query
Class SqlQueryBuilder

java.lang.Object
  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: 2775 $

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

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

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

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

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