com.healthmarketscience.sqlbuilder

Class SelectQuery

java.lang.Object

com.healthmarketscience.common.util.AppendeeObject

com.healthmarketscience.sqlbuilder.SqlObject

com.healthmarketscience.sqlbuilder.CustomizableSqlObject

com.healthmarketscience.sqlbuilder.Query<ThisType>

com.healthmarketscience.sqlbuilder.BaseCTEQuery<SelectQuery>

com.healthmarketscience.sqlbuilder.SelectQuery

All Implemented Interfaces:

Appendee, Verifiable<SelectQuery>

public class SelectQuery
extends BaseCTEQuery<SelectQuery>

Query which generates a SELECT statement. Supports arbitrary columns (including "DISTINCT" modifier), "FOR UPDATE" clause, all join types, "WHERE" clause, "GROUP BY" clause, "ORDER BY" clause, and "HAVING" clause.

Note that the "OFFSET" and "FETCH NEXT" clauses are supported from "SQL 2008".

If Columns are used for any referenced columns, and no complicated joins are required, the table list may be left empty and it will be auto-generated in the append call. Note, that this is not the most efficient method (as this list will not be cached for the future due to mutability constraints on appendTo).

Note that this query supports custom SQL syntax, see SelectQuery.Hook for more details.

Author:

James Ahlborn

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	SelectQuery.Hook The HookAnchors supported for SELECT queries.
static class	SelectQuery.JoinType Enum which defines the join types supported in a FROM clause.

Field Summary

Fields inherited from class com.healthmarketscience.sqlbuilder.SqlObject

ALL_SYMBOL, NULL_VALUE, QUESTION_MARK

Constructor Summary

Constructors

Constructor and Description
SelectQuery()
SelectQuery(boolean isDistinct)

Method Summary

Modifier and Type	Method and Description
SelectQuery	addAliasedColumn(Object column, String alias) Adds the given column with the given alias to the SELECT column list.
SelectQuery	addAllColumns() Adds the ALL_SYMBOL to the select column list.
SelectQuery	addAllTableColumns(Table table) Adds a "<alias>.*" column to the select column list.
SelectQuery	addColumns(Column... columns) Adds the given columns to the SELECT column list.
SelectQuery	addCondition(Condition newCondition) Adds a condition to the WHERE clause for the select query (AND'd with any other WHERE conditions).
SelectQuery	addCustomColumns(Object... columnStrs) Adds the given columns to the SELECT column list.
SelectQuery	addCustomFromTable(Object tableStr) Adds a table to the FROM clause, should not be used with any add*Join methods
SelectQuery	addCustomGroupings(Object... columnStrs) Adds the given columns to the "GROUP BY" clause
SelectQuery	addCustomization(CustomSyntax obj) Adds custom SQL to this query.
SelectQuery	addCustomization(SelectQuery.Hook hook, HookType type, Object obj) Adds custom SQL to this query.
SelectQuery	addCustomJoin(Object joinStr) Adds a custom join string.
SelectQuery	addCustomJoin(SelectQuery.JoinType joinType, Object fromTableStr, Object toTableStr, Condition joinCond) Adds a join of the given type from fromTableStr to toTableStr on joinCond of the given join type.
SelectQuery	addCustomOrdering(Object columnStr, OrderObject.Dir dir) Adds the given column with the given direction to the "ORDER BY" clause
SelectQuery	addCustomOrderings(Object... columnStrs) Adds the given columns to the "ORDER BY" clause
SelectQuery	addFromTable(Table table) Adds a table to the FROM clause, should not be used with any add*Join methods
SelectQuery	addGroupings(Column... columns) Adds the given columns to the "GROUP BY" clause
SelectQuery	addHaving(Condition newCondition) Adds a condition to the HAVING clause for the select query (AND'd with any other HAVING conditions).
SelectQuery	addIndexedOrdering(Integer columnIdx, OrderObject.Dir dir) Adds the given column index with the given direction to the "ORDER BY" clause
SelectQuery	addIndexedOrderings(Integer... columnIdxs) Adds the given column index to the "ORDER BY" clause
SelectQuery	addJoin(SelectQuery.JoinType joinType, Table fromTable, Table toTable, Column fromColumn, Column toColumn) Adds a join of the given type from fromTable to toTable with a join condition requiring fromColumn to equal toColumn.
SelectQuery	addJoin(SelectQuery.JoinType joinType, Table fromTable, Table toTable, Condition joinCond) Adds a join of the given type from fromTable to toTable on joinCond of the given join type.
SelectQuery	addJoin(SelectQuery.JoinType joinType, Table fromTable, Table toTable, List<? extends Column> fromColumns, List<? extends Column> toColumns) Adds a join of the given type from fromTable to toTable with a join condition requiring each column in fromColumns to equal the corresponding column in toColumns.
SelectQuery	addJoins(SelectQuery.JoinType joinType, Join... joins) Adds all of the joins of the given join type where each join is from join.getFromTable() to join.getToTable() with a join condition requiring each column in join.getFromColumns() to equal the corresponding column in join.getToColumns().
SelectQuery	addOrdering(Column column, OrderObject.Dir dir) Adds the given column with the given direction to the "ORDER BY" clause
SelectQuery	addOrderings(Column... columns) Adds the given columns to the "ORDER BY" clause
SelectQuery	addWindowDefinition(String name, Object window) Adds a named window definition to the select query's WINDOW definitions
protected void	appendTo(AppendableExt app, SqlContext newContext) Appends the sql query to the given AppendableExt within the given, modifiable SqlContext.
protected void	collectSchemaObjects(ValidationContext vContext) Used during Query.validate() calls to collect the dbschema objects referenced in a query.
ComboCondition	getHavingClause() Allows access to the AND ComboCondition of the having clause to facilitate common condition building code.
ComboCondition	getWhereClause() Allows access to the AND ComboCondition of the where clause to facilitate common condition building code.
SelectQuery	setFetchNext(Object fetchCount) Sets the value for the "FETCH NEXT" clause.
SelectQuery	setForUpdate(boolean forUpdate) Iff forUpdate is true, adds the FOR UPDATE clause to the end of the SELECT clause
SelectQuery	setIsDistinct(boolean isDistinct) Iff isDistinct is true, adds the DISTINCT keyword to the SELECT clause so that only unique rows are returned
SelectQuery	setOffset(Object offset) Sets the value for the "OFFSET" clause.
void	validate(ValidationContext vContext) Runs validation on this verifiable object using a previously collected ValidationContext.
protected static void	validateOrdering(int numColumns, SqlObjectList<SqlObject> ordering, boolean ignoreColumnCount) Checks any indexed ordering values for validity using a variety of criteria.

Methods inherited from class com.healthmarketscience.sqlbuilder.BaseCTEQuery

addCommonTableExpression, prependTo, setRecursive

Methods inherited from class com.healthmarketscience.sqlbuilder.Query

appendTo, getThisType, validate, validateTables

Methods inherited from class com.healthmarketscience.sqlbuilder.CustomizableSqlObject

addCustomization, customAppendTo, customAppendTo, maybeAppendTo, maybeAppendTo

Methods inherited from class com.healthmarketscience.sqlbuilder.SqlObject

collectSchemaObjects, doValidate, toString

Methods inherited from class com.healthmarketscience.common.util.AppendeeObject

toString, toString

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

SelectQuery

public SelectQuery()

SelectQuery

public SelectQuery(boolean isDistinct)

Method Detail

setIsDistinct

public SelectQuery setIsDistinct(boolean isDistinct)

Iff isDistinct is true, adds the DISTINCT keyword to the SELECT clause so that only unique rows are returned

setForUpdate

public SelectQuery setForUpdate(boolean forUpdate)

Iff forUpdate is true, adds the FOR UPDATE clause to the end of the SELECT clause

addCustomColumns

public SelectQuery addCustomColumns(Object... columnStrs)

Adds the given columns to the SELECT column list.

Object -> SqlObject conversions handled by Converter.COLUMN_VALUE_TO_OBJ.

addAllColumns

public SelectQuery addAllColumns()

Adds the ALL_SYMBOL to the select column list.

addAllTableColumns

public SelectQuery addAllTableColumns(Table table)

Adds a "<alias>.*" column to the select column list.

addColumns

public SelectQuery addColumns(Column... columns)

Adds the given columns to the SELECT column list.

addAliasedColumn

public SelectQuery addAliasedColumn(Object column,
                                    String alias)

Adds the given column with the given alias to the SELECT column list. This is equivalent to addCustomColumns(Converter.toColumnSqlObject(column, alias)).

addCustomFromTable

public SelectQuery addCustomFromTable(Object tableStr)

Adds a table to the FROM clause, should not be used with any add*Join methods

Object -> SqlObject conversions handled by Converter.toCustomTableDefSqlObject(Object).

addFromTable

public SelectQuery addFromTable(Table table)

Adds a table to the FROM clause, should not be used with any add*Join methods

addCustomJoin

public SelectQuery addCustomJoin(Object joinStr)

Adds a custom join string.

Object -> SqlObject conversions handled by Converter.toCustomTableDefSqlObject(Object).

addCustomJoin

public SelectQuery addCustomJoin(SelectQuery.JoinType joinType,
                                 Object fromTableStr,
                                 Object toTableStr,
                                 Condition joinCond)

Adds a join of the given type from fromTableStr to toTableStr on joinCond of the given join type.

Object -> SqlObject conversions handled by Converter.toCustomTableDefSqlObject(Object).

addJoin

public SelectQuery addJoin(SelectQuery.JoinType joinType,
                           Table fromTable,
                           Table toTable,
                           Condition joinCond)

Adds a join of the given type from fromTable to toTable on joinCond of the given join type.

addJoin

public SelectQuery addJoin(SelectQuery.JoinType joinType,
                           Table fromTable,
                           Table toTable,
                           List<? extends Column> fromColumns,
                           List<? extends Column> toColumns)

Adds a join of the given type from fromTable to toTable with a join condition requiring each column in fromColumns to equal the corresponding column in toColumns.

addJoin

public SelectQuery addJoin(SelectQuery.JoinType joinType,
                           Table fromTable,
                           Table toTable,
                           Column fromColumn,
                           Column toColumn)

Adds a join of the given type from fromTable to toTable with a join condition requiring fromColumn to equal toColumn.

addJoins

public SelectQuery addJoins(SelectQuery.JoinType joinType,
                            Join... joins)

Adds all of the joins of the given join type where each join is from join.getFromTable() to join.getToTable() with a join condition requiring each column in join.getFromColumns() to equal the corresponding column in join.getToColumns().

addCustomOrdering

public SelectQuery addCustomOrdering(Object columnStr,
                                     OrderObject.Dir dir)

Adds the given column with the given direction to the "ORDER BY" clause

Object -> SqlObject conversions handled by Converter.toCustomColumnSqlObject(Object).

addCustomOrderings

public SelectQuery addCustomOrderings(Object... columnStrs)

Adds the given columns to the "ORDER BY" clause

Note, NULL ordering can be specified by passing an appropriately configured instance of OrderObject to this method.

Object -> SqlObject conversions handled by Converter.CUSTOM_COLUMN_TO_OBJ.

addOrdering

public SelectQuery addOrdering(Column column,
                               OrderObject.Dir dir)

Adds the given column with the given direction to the "ORDER BY" clause

addOrderings

public SelectQuery addOrderings(Column... columns)

Adds the given columns to the "ORDER BY" clause

addIndexedOrdering

public SelectQuery addIndexedOrdering(Integer columnIdx,
                                      OrderObject.Dir dir)

Adds the given column index with the given direction to the "ORDER BY" clause

addIndexedOrderings

public SelectQuery addIndexedOrderings(Integer... columnIdxs)

Adds the given column index to the "ORDER BY" clause

addCustomGroupings

public SelectQuery addCustomGroupings(Object... columnStrs)

Adds the given columns to the "GROUP BY" clause

Object -> SqlObject conversions handled by Converter.CUSTOM_COLUMN_TO_OBJ.

addGroupings

public SelectQuery addGroupings(Column... columns)

Adds the given columns to the "GROUP BY" clause

getWhereClause

public ComboCondition getWhereClause()

Allows access to the AND ComboCondition of the where clause to facilitate common condition building code.

Returns:

the AND ComboCondition of the WHERE clause for the select query.

addCondition

public SelectQuery addCondition(Condition newCondition)

Adds a condition to the WHERE clause for the select query (AND'd with any other WHERE conditions). Note that the WHERE clause will only be generated if some conditions have been added.

For convenience purposes, the SelectQuery generates it's own ComboCondition allowing multiple conditions to be AND'd together. To OR conditions or perform other logic, the ComboCondition must be built and added to the SelectQuery.

getHavingClause

public ComboCondition getHavingClause()

Allows access to the AND ComboCondition of the having clause to facilitate common condition building code.

Returns:

the AND ComboCondition of the HAVING clause for the select query.

addHaving

public SelectQuery addHaving(Condition newCondition)

Adds a condition to the HAVING clause for the select query (AND'd with any other HAVING conditions). Note that the HAVING clause will only be generated if some conditions have been added.

For convenience purposes, the SelectQuery generates it's own ComboCondition allowing multiple HAVING conditions to be AND'd together. To OR conditions or perform other logic, the ComboCondition must be built and added to the SelectQuery.

addWindowDefinition

public SelectQuery addWindowDefinition(String name,
                                       Object window)

Adds a named window definition to the select query's WINDOW definitions

Object -> SqlObject conversions handled by Converter.toCustomSqlObject(java.lang.Object).

See Also:

"SQL 2003"

setOffset

public SelectQuery setOffset(Object offset)

Sets the value for the "OFFSET" clause. Note that this clause is defined in "SQL 2008".

Object -> SqlObject conversions handled by Converter.toValueSqlObject(java.lang.Object).

See Also:

"SQL 2008"

setFetchNext

public SelectQuery setFetchNext(Object fetchCount)

Sets the value for the "FETCH NEXT" clause. Note that this clause is defined in "SQL 2008".

Object -> SqlObject conversions handled by Converter.toValueSqlObject(java.lang.Object).

See Also:

"SQL 2008"

addCustomization

public SelectQuery addCustomization(SelectQuery.Hook hook,
                                    HookType type,
                                    Object obj)

Adds custom SQL to this query. See com.healthmarketscience.sqlbuilder.custom for more details on custom SQL syntax.

Parameters:

hook - the part of the query being customized

type - the type of customization

obj - the custom sql. The Object -> SqlObject conversions handled by Converter.toCustomSqlObject(java.lang.Object).

addCustomization

public SelectQuery addCustomization(CustomSyntax obj)

Adds custom SQL to this query. See com.healthmarketscience.sqlbuilder.custom for more details on custom SQL syntax.

Parameters:

obj - the custom sql syntax on which the CustomSyntax.apply(SelectQuery) method will be invoked (may be null).

collectSchemaObjects

protected void collectSchemaObjects(ValidationContext vContext)

Description copied from class: SqlObject

Used during Query.validate() calls to collect the dbschema objects referenced in a query. Any subclass of this class should add all referenced tables and columns to the appropriate collections.

Overrides:

collectSchemaObjects in class BaseCTEQuery<SelectQuery>

Parameters:

vContext - handle to the current validation context

validate

public void validate(ValidationContext vContext)
              throws ValidationException

Description copied from interface: Verifiable

Runs validation on this verifiable object using a previously collected ValidationContext.

In general, this method will only be called internally, not by users.

Specified by:

validate in interface Verifiable<SelectQuery>

Overrides:

validate in class Query<SelectQuery>

Parameters:

vContext - handle to the current, filled-in validation context

Throws:

ValidationException

validateOrdering

protected static void validateOrdering(int numColumns,
                                       SqlObjectList<SqlObject> ordering,
                                       boolean ignoreColumnCount)
                                throws ValidationException

Checks any indexed ordering values for validity using a variety of criteria.

Parameters:

numColumns - number of column objects in the query

ordering - the ordering objects for the query

ignoreColumnCount - whether the given numColumns is meaningful (i.e. does not include a '*' character)

Throws:

ValidationException

appendTo

protected void appendTo(AppendableExt app,
                        SqlContext newContext)
                 throws IOException

Description copied from class: Query

Appends the sql query to the given AppendableExt within the given, modifiable SqlContext. This method is invoked by the Query.appendTo(AppendableExt) method within the context of calls to SqlContext.pushContext(com.healthmarketscience.common.util.AppendableExt) and SqlContext.popContext(com.healthmarketscience.common.util.AppendableExt, com.healthmarketscience.sqlbuilder.SqlContext), so the implementation is free to modify the given SqlContext.

Specified by:

appendTo in class Query<SelectQuery>

Parameters:

app - the target for the sql query generation

newContext - modifiable SqlContext for nested Appendees

Throws:

IOException