org.datanucleus.store.rdbms.sql
Class SQLStatement

java.lang.Object
  extended by org.datanucleus.store.rdbms.sql.SQLStatement

public class SQLStatement
extends java.lang.Object

Class providing an API for generating SQL statements. Caller builds up the statement using the various methods, and accesses the SQL statement using getStatement(). Generated statement is of the form :-

 SELECT {expr}, {expr}, ...
 FROM {tblExpr} [joinInfo {tblExpr} ON ...] ...
 WHERE {boolExpr} [AND|OR] {boolExpr} ...
 GROUP BY {expr}, {expr}
 HAVING {boolExpr}
 ORDER BY {expr} [ASC|DESC], {expr} [ASC|DESC], ...
 
and also supports UNIONs between SQLStatements, and having sub-queries of other SQLStatements. Alternatively, for an UPDATE,
 UPDATE {tbl}
 SET {expr}={val}, {expr}={val}, ...
 WHERE {boolExpr} [AND|OR] {boolExpr} ...
 

The generated SQL is cached. Any use of a mutating method, changing the composition of the statement will clear the cached SQL, and it will be regenerated when

getStatement
is called next.

Table Groups

When tables are registered in the statement they are split into "table groups". A table group is, in simple terms, an object in the query. If a table has a super-table and a field of the object is selected that is in the super-table then the super-table is added to the table group. If there is a join to a related object then the table of this object will be put in a new table group. So the same datastore table can appear multiple times in the statement, each time for a different object.

Table Aliases

All methods that cause a new SQLTable to be created also allow specification of the table alias in the statement. Where the alias is not provided then we use a table "namer" (definable on the plugin-point "org.datanucleus.store.rdbms.sql_tablenamer"). The table namer can define names simply based on the table number, or based on table group and the number of tables in the group etc etc. To select a particular table "namer", set the extension "datanucleus.sqlTableNamingStrategy" to the key of the namer plugin. The default is "alpha-scheme" which bases table names on the group and number in that group.


Field Summary
protected  boolean aggregated
          whether there is an aggregate expression present in the select
protected  java.lang.String candidateClassName
          Name of class that this statement selects (optional, only typically for unioned statements).
protected  boolean distinct
          Whether the statement is distinct.
protected  java.util.HashMap<java.lang.String,java.lang.Object> extensions
          Map of extensions for use in generating the SQL, keyed by the extension name.
protected  java.util.List<SQLExpression> groupingExpressions
          Expression(s) for the GROUP BY clause.
protected  BooleanExpression having
          Having clause.
protected  java.util.List<SQLJoin> joins
          List of joins for this statement.
protected static org.datanucleus.util.Localiser LOCALISER
          Localisation for messages.
protected  boolean[] orderingDirections
          Directions for any ORDER BY expressions (1 for each orderingExpressions entry).
protected  SQLExpression[] orderingExpressions
          Expressions for any ORDER BY clause.
protected  SQLStatement parent
          Parent statement, if this is a subquery.
protected  SQLTable primaryTable
          Primary table for this statement.
protected  QueryGenerator queryGenerator
          Context of any query generation.
protected  long rangeCount
          The number of records to be retrieved in any range restriction.
protected  long rangeOffset
          The offset for any range restriction.
protected  RDBMSManager rdbmsMgr
          Manager for the RDBMS datastore.
protected  java.util.List<java.lang.String> selects
          List of select objects.
protected  SQLText sql
          Cached SQL statement, generated by getStatement().
protected  java.util.Map<java.lang.String,SQLTableGroup> tableGroups
          Map of table groups keyed by the group name.
protected static java.util.Map<java.lang.String,SQLTableNamer> tableNamerByName
          Map of SQLTable naming instance keyed by the name of the naming scheme.
protected  java.util.Map<java.lang.String,SQLTable> tables
          Map of tables referenced in this statement, keyed by their alias.
protected  java.util.List<SQLStatement> unions
          List of unioned SQLStatements (if any).
protected  SQLExpression[] updates
          Array of update expressions when the statement is an UPDATE.
protected  BooleanExpression where
          Where clause.
 
Constructor Summary
SQLStatement(RDBMSManager rdbmsMgr, org.datanucleus.store.mapped.DatastoreContainerObject table, org.datanucleus.store.mapped.DatastoreIdentifier alias, java.lang.String tableGroupName)
          Constructor for an SQL statement.
SQLStatement(SQLStatement parentStmt, RDBMSManager rdbmsMgr, org.datanucleus.store.mapped.DatastoreContainerObject table, org.datanucleus.store.mapped.DatastoreIdentifier alias, java.lang.String tableGroupName)
          Constructor for an SQL statement that is a subquery of another statement.
 
Method Summary
 void addExtension(java.lang.String key, java.lang.Object value)
          Method to define an extension for this query statement allowing control over its behaviour in generating a query.
 void addGroupingExpression(SQLExpression expr)
          Method to add a grouping expression to the query.
protected  void addOrderingColumnsToSelect()
          Convenience method to add any necessary columns to the SELECT that are needed by the ordering constraint.
 SQLTable crossJoin(org.datanucleus.store.mapped.DatastoreContainerObject target, java.lang.String targetAlias, java.lang.String tableGrpName)
          Method to form a right outer join to the specified table using the provided mappings.
protected  SQLText generateOrderingStatement()
          Convenience method to generate the ordering statement to add to the overall query statement.
protected  java.lang.String generateTableAlias(org.datanucleus.store.mapped.DatastoreContainerObject tbl, java.lang.String groupName)
          Method to generate the alias to be used for a joined table.
 java.lang.String getCandidateClassName()
           
 DatabaseAdapter getDatabaseAdapter()
           
 int getNumberOfSelects()
          Accessor for the number of selected items in the SELECT clause.
 int getNumberOfTables()
          Accessor for the number of tables defined for this statement.
 int getNumberOfUnions()
           
 SQLTable getPrimaryTable()
          Accessor for the primary table of the statement.
 QueryGenerator getQueryGenerator()
           
 RDBMSManager getRDBMSManager()
           
 SQLText getSelectStatement()
          Accessor for the SQL SELECT statement.
 SQLExpressionFactory getSQLExpressionFactory()
           
 SQLTable getTable(org.datanucleus.store.mapped.DatastoreContainerObject table, java.lang.String groupName)
          Accessor for the SQLTable object for the specified table (if defined for this statement) in the specified table group.
 SQLTable getTable(java.lang.String alias)
          Accessor for the SQLTable object with the specified alias (if defined for this statement).
 SQLTableGroup getTableGroup(java.lang.String groupName)
          Accessor for the table group with this name.
 java.util.List<SQLStatement> getUnions()
          Accessor for the unioned statements.
 java.lang.Object getValueForExtension(java.lang.String key)
          Accessor for the value for an extension.
 SQLTable innerJoin(SQLTable sourceTable, org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping, org.datanucleus.store.mapped.DatastoreContainerObject target, java.lang.String targetAlias, org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping, java.lang.Object[] discrimValues, java.lang.String tableGrpName)
          Method to form an inner join to the specified table using the provided mappings.
protected  void invalidateStatement()
          Method to uncache the generated SQL (because some condition has changed).
protected  void join(SQLJoin.JoinType joinType, SQLTable sourceTable, org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping, SQLTable targetTable, org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping, java.lang.Object[] discrimValues)
          Internal method to form a join to the specified table using the provided mappings.
 SQLTable leftOuterJoin(SQLTable sourceTable, org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping, org.datanucleus.store.mapped.DatastoreContainerObject target, java.lang.String targetAlias, org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping, java.lang.Object[] discrimValues, java.lang.String tableGrpName)
          Method to form a left outer join to the specified table using the provided mappings.
 void log(org.datanucleus.util.NucleusLogger logger)
          Method to dump the statement to the supplied log (debug level).
 SQLTable rightOuterJoin(SQLTable sourceTable, org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping, org.datanucleus.store.mapped.DatastoreContainerObject target, java.lang.String targetAlias, org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping, java.lang.Object[] discrimValues, java.lang.String tableGrpName)
          Method to form a right outer join to the specified table using the provided mappings.
 int select(SQLExpression expr, java.lang.String alias)
          Select an expression.
 int select(SQLTable table, org.datanucleus.store.mapped.DatastoreField column, java.lang.String alias)
          Add a select clause for the specified column.
 int[] select(SQLTable table, org.datanucleus.store.mapped.mapping.JavaTypeMapping mapping, java.lang.String alias)
          Add a select clause for the specified field (via its mapping).
protected  int selectSQLExpressionInternal(SQLExpression expr, java.lang.String alias)
          Convenience method for selecting columns when generating the SQL text.
 void setCandidateClassName(java.lang.String name)
           
 void setDistinct(boolean distinct)
          Mutator for whether the query returns distinct results.
 void setHaving(BooleanExpression expr)
          Mutator for the "having" expression.
 void setOrdering(SQLExpression[] exprs, boolean[] descending)
          Mutator for the ordering criteria.
 void setParent(SQLStatement parentStmt)
          Sets the parent SQL statement of this statement.
 void setQueryGenerator(QueryGenerator gen)
           
 void setRange(long offset, long count)
          Method to add a range constraint on any SELECT.
 void setUpdates(SQLExpression[] exprs)
          Method to set the UPDATE clause of the statement.
 void union(SQLStatement stmt)
          Method to union this SQL statement with another SQL statement.
 void whereAnd(BooleanExpression expr, boolean applyToUnions)
          Method to add an AND condition to the WHERE clause.
 void whereOr(BooleanExpression expr, boolean applyToUnions)
          Method to add an OR condition to the WHERE clause.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

LOCALISER

protected static final org.datanucleus.util.Localiser LOCALISER
Localisation for messages.


tableNamerByName

protected static java.util.Map<java.lang.String,SQLTableNamer> tableNamerByName
Map of SQLTable naming instance keyed by the name of the naming scheme.


sql

protected SQLText sql
Cached SQL statement, generated by getStatement().


rdbmsMgr

protected RDBMSManager rdbmsMgr
Manager for the RDBMS datastore.


queryGenerator

protected QueryGenerator queryGenerator
Context of any query generation.


candidateClassName

protected java.lang.String candidateClassName
Name of class that this statement selects (optional, only typically for unioned statements).


distinct

protected boolean distinct
Whether the statement is distinct.


extensions

protected java.util.HashMap<java.lang.String,java.lang.Object> extensions
Map of extensions for use in generating the SQL, keyed by the extension name.


parent

protected SQLStatement parent
Parent statement, if this is a subquery.


unions

protected java.util.List<SQLStatement> unions
List of unioned SQLStatements (if any).


selects

protected java.util.List<java.lang.String> selects
List of select objects.


updates

protected SQLExpression[] updates
Array of update expressions when the statement is an UPDATE.


aggregated

protected boolean aggregated
whether there is an aggregate expression present in the select


primaryTable

protected SQLTable primaryTable
Primary table for this statement.


joins

protected java.util.List<SQLJoin> joins
List of joins for this statement.


tables

protected java.util.Map<java.lang.String,SQLTable> tables
Map of tables referenced in this statement, keyed by their alias.


tableGroups

protected java.util.Map<java.lang.String,SQLTableGroup> tableGroups
Map of table groups keyed by the group name.


where

protected BooleanExpression where
Where clause.


groupingExpressions

protected java.util.List<SQLExpression> groupingExpressions
Expression(s) for the GROUP BY clause.


having

protected BooleanExpression having
Having clause.


orderingExpressions

protected SQLExpression[] orderingExpressions
Expressions for any ORDER BY clause.


orderingDirections

protected boolean[] orderingDirections
Directions for any ORDER BY expressions (1 for each orderingExpressions entry).


rangeOffset

protected long rangeOffset
The offset for any range restriction.


rangeCount

protected long rangeCount
The number of records to be retrieved in any range restriction.

Constructor Detail

SQLStatement

public SQLStatement(RDBMSManager rdbmsMgr,
                    org.datanucleus.store.mapped.DatastoreContainerObject table,
                    org.datanucleus.store.mapped.DatastoreIdentifier alias,
                    java.lang.String tableGroupName)
Constructor for an SQL statement.

Parameters:
rdbmsMgr - The datastore manager
table - The primary table
alias - Alias for this table
tableGroupName - Name of candidate table-group (if any). Uses "Group0" if not provided

SQLStatement

public SQLStatement(SQLStatement parentStmt,
                    RDBMSManager rdbmsMgr,
                    org.datanucleus.store.mapped.DatastoreContainerObject table,
                    org.datanucleus.store.mapped.DatastoreIdentifier alias,
                    java.lang.String tableGroupName)
Constructor for an SQL statement that is a subquery of another statement.

Parameters:
parentStmt - Parent statement
rdbmsMgr - The datastore manager
table - The primary table
alias - Alias for this table
tableGroupName - Name of candidate table-group (if any). Uses "Group0" if not provided
Method Detail

getRDBMSManager

public RDBMSManager getRDBMSManager()

setCandidateClassName

public void setCandidateClassName(java.lang.String name)

getCandidateClassName

public java.lang.String getCandidateClassName()

getQueryGenerator

public QueryGenerator getQueryGenerator()

setQueryGenerator

public void setQueryGenerator(QueryGenerator gen)

getSQLExpressionFactory

public SQLExpressionFactory getSQLExpressionFactory()

getDatabaseAdapter

public DatabaseAdapter getDatabaseAdapter()

addExtension

public void addExtension(java.lang.String key,
                         java.lang.Object value)
Method to define an extension for this query statement allowing control over its behaviour in generating a query.

Parameters:
key - Extension key
value - Value for the key

getValueForExtension

public java.lang.Object getValueForExtension(java.lang.String key)
Accessor for the value for an extension.

Parameters:
key - Key for the extension
Returns:
Value for the extension (if any)

union

public void union(SQLStatement stmt)
Method to union this SQL statement with another SQL statement.

Parameters:
stmt - The other SQL statement to union

getNumberOfUnions

public int getNumberOfUnions()

getUnions

public java.util.List<SQLStatement> getUnions()
Accessor for the unioned statements.

Returns:
The unioned SQLStatements

setParent

public void setParent(SQLStatement parentStmt)
Sets the parent SQL statement of this statement. In SQL it can be exemplified as SELECT 1 FROM PARENT WHERE EXISTS (SELECT 1 FROM THIS) The parent SQLStatement is the outer SELECT, and this SQLStatement is the inner SELECT.

Parameters:
parentStmt - the parent of this statement

setDistinct

public void setDistinct(boolean distinct)
Mutator for whether the query returns distinct results.

Parameters:
distinct - Whether to return distinct

getNumberOfSelects

public int getNumberOfSelects()
Accessor for the number of selected items in the SELECT clause.

Returns:
Number of selected items

select

public int select(SQLExpression expr,
                  java.lang.String alias)
Select an expression. This will be used when adding aggregates to the select clause (e.g "COUNT(*)").

Parameters:
expr - The expression to add to the select statement
alias - Optional alias for this selected expression
Returns:
The index of the expression in the select

select

public int[] select(SQLTable table,
                    org.datanucleus.store.mapped.mapping.JavaTypeMapping mapping,
                    java.lang.String alias)
Add a select clause for the specified field (via its mapping). If an alias is supplied and there are more than 1 column for this mapping then they will have names like "{alias}_n" where n is the column number (starting at 0).

Parameters:
table - The SQLTable to select from (null implies the primary table)
mapping - The mapping for the field
alias - optional alias
Returns:
The column index(es) in the statement for the specified field (1 is first).

select

public int select(SQLTable table,
                  org.datanucleus.store.mapped.DatastoreField column,
                  java.lang.String alias)
Add a select clause for the specified column.

Parameters:
table - The SQLTable to select from (null implies the primary table)
column - The column
alias - Optional alias
Returns:
The column index in the statement for the specified column (1 is first).

setUpdates

public void setUpdates(SQLExpression[] exprs)
Method to set the UPDATE clause of the statement.

Parameters:
exprs - The update clause expression

getPrimaryTable

public SQLTable getPrimaryTable()
Accessor for the primary table of the statement.

Returns:
The primary table

getTable

public SQLTable getTable(java.lang.String alias)
Accessor for the SQLTable object with the specified alias (if defined for this statement).

Parameters:
alias - Alias
Returns:
The SQLTable

getTable

public SQLTable getTable(org.datanucleus.store.mapped.DatastoreContainerObject table,
                         java.lang.String groupName)
Accessor for the SQLTable object for the specified table (if defined for this statement) in the specified table group.

Parameters:
table - The table
groupName - Name of the table group where we should look for this table
Returns:
The SQLTable (if found)

getTableGroup

public SQLTableGroup getTableGroup(java.lang.String groupName)
Accessor for the table group with this name.

Parameters:
groupName - Name of the group
Returns:
The table group

getNumberOfTables

public int getNumberOfTables()
Accessor for the number of tables defined for this statement.

Returns:
Number of tables (in addition to the primary table)

innerJoin

public SQLTable innerJoin(SQLTable sourceTable,
                          org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping,
                          org.datanucleus.store.mapped.DatastoreContainerObject target,
                          java.lang.String targetAlias,
                          org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping,
                          java.lang.Object[] discrimValues,
                          java.lang.String tableGrpName)
Method to form an inner join to the specified table using the provided mappings. Will be applied to all unioned statements.

Parameters:
sourceTable - SQLTable for the source (null implies primaryTable)
sourceMapping - Mapping in this table to join from
target - Table to join to
targetAlias - Alias for the target table (if known)
targetMapping - Mapping in the other table to join to (also defines the table to join to)
discrimValues - Any discriminator values to apply for the joined table (null if not)
tableGrpName - Name of the table group for the target (null implies a new group)
Returns:
SQLTable for the target

leftOuterJoin

public SQLTable leftOuterJoin(SQLTable sourceTable,
                              org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping,
                              org.datanucleus.store.mapped.DatastoreContainerObject target,
                              java.lang.String targetAlias,
                              org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping,
                              java.lang.Object[] discrimValues,
                              java.lang.String tableGrpName)
Method to form a left outer join to the specified table using the provided mappings. Will be applied to all unioned statements.

Parameters:
sourceTable - SQLTable for the source (null implies primaryTable)
sourceMapping - Mapping in this table to join from
target - Table to join to
targetAlias - Alias for the target table (if known)
targetMapping - Mapping in the other table to join to (also defines the table to join to)
discrimValues - Any discriminator values to apply for the joined table (null if not)
tableGrpName - Name of the table group for the target (null implies a new group)
Returns:
SQLTable for the target

rightOuterJoin

public SQLTable rightOuterJoin(SQLTable sourceTable,
                               org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping,
                               org.datanucleus.store.mapped.DatastoreContainerObject target,
                               java.lang.String targetAlias,
                               org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping,
                               java.lang.Object[] discrimValues,
                               java.lang.String tableGrpName)
Method to form a right outer join to the specified table using the provided mappings. Will be applied to all unioned statements.

Parameters:
sourceTable - SQLTable for the source (null implies primaryTable)
sourceMapping - Mapping in this table to join from
target - Table to join to
targetAlias - Alias for the target table (if known)
targetMapping - Mapping in the other table to join to (also defines the table to join to)
discrimValues - Any discriminator values to apply for the joined table (null if not)
tableGrpName - Name of the table group for the target (null implies a new group)
Returns:
SQLTable for the target

crossJoin

public SQLTable crossJoin(org.datanucleus.store.mapped.DatastoreContainerObject target,
                          java.lang.String targetAlias,
                          java.lang.String tableGrpName)
Method to form a right outer join to the specified table using the provided mappings. Will be applied to all unioned statements.

Parameters:
target - Table to join to
targetAlias - Alias for the target table (if known)
tableGrpName - Name of the table group for the target (null implies a new group)
Returns:
SQLTable for the target

join

protected void join(SQLJoin.JoinType joinType,
                    SQLTable sourceTable,
                    org.datanucleus.store.mapped.mapping.JavaTypeMapping sourceMapping,
                    SQLTable targetTable,
                    org.datanucleus.store.mapped.mapping.JavaTypeMapping targetMapping,
                    java.lang.Object[] discrimValues)
Internal method to form a join to the specified table using the provided mappings.

Parameters:
joinType - Type of join (INNER, LEFT OUTER, RIGHT OUTER, CROSS, NON-ANSI)
sourceTable - SQLTable to join from
sourceMapping - Mapping in this table to join from
targetTable - SQLTable to join to
targetMapping - Mapping in the other table to join to (also defines the table to join to)
discrimValues - Any discriminator values to apply for the joined table (null if not)

generateTableAlias

protected java.lang.String generateTableAlias(org.datanucleus.store.mapped.DatastoreContainerObject tbl,
                                              java.lang.String groupName)
Method to generate the alias to be used for a joined table. Names tables according to the extension "datanucleus.sqlTableNamingStrategy".

Parameters:
tbl - Table object
groupName - Name of the table group
Returns:
The alias to use

whereAnd

public void whereAnd(BooleanExpression expr,
                     boolean applyToUnions)
Method to add an AND condition to the WHERE clause.

Parameters:
expr - The condition

whereOr

public void whereOr(BooleanExpression expr,
                    boolean applyToUnions)
Method to add an OR condition to the WHERE clause.

Parameters:
expr - The condition
applyToUnions - Whether to apply to unions

addGroupingExpression

public void addGroupingExpression(SQLExpression expr)
Method to add a grouping expression to the query. Adds the grouping to any unioned queries

Parameters:
expr - The expression

setHaving

public void setHaving(BooleanExpression expr)
Mutator for the "having" expression.

Parameters:
expr - Boolean expression for the having clause

setOrdering

public void setOrdering(SQLExpression[] exprs,
                        boolean[] descending)
Mutator for the ordering criteria.

Parameters:
exprs - The expressions to order by
descending - Whether each expression is ascending/descending

setRange

public void setRange(long offset,
                     long count)
Method to add a range constraint on any SELECT. This typically will use LIMIT/OFFSET where they are supported by the underlying RDBMS.

Parameters:
offset - The offset to start from
count - The number of records to return

getSelectStatement

public SQLText getSelectStatement()
Accessor for the SQL SELECT statement. If any mutator method has been called since this was last called the SQL will be regenerated otherwise the SQL is cached.

Returns:
The SQL statement

generateOrderingStatement

protected SQLText generateOrderingStatement()
Convenience method to generate the ordering statement to add to the overall query statement.

Returns:
The ordering statement

addOrderingColumnsToSelect

protected void addOrderingColumnsToSelect()
Convenience method to add any necessary columns to the SELECT that are needed by the ordering constraint.


selectSQLExpressionInternal

protected int selectSQLExpressionInternal(SQLExpression expr,
                                          java.lang.String alias)
Convenience method for selecting columns when generating the SQL text. Does the same as selectSQLExpression except doesn't invalidate the SQL.

Parameters:
expr - The expression
alias - The alias to use
Returns:
The position of this column

invalidateStatement

protected void invalidateStatement()
Method to uncache the generated SQL (because some condition has changed).


log

public void log(org.datanucleus.util.NucleusLogger logger)
Method to dump the statement to the supplied log (debug level).

Parameters:
logger - The logger


Copyright © 2009. All Rights Reserved.