com.objectwave.persist.sqlConstruction
Class SQLSelect

java.lang.Object
  |
  +--com.objectwave.persist.sqlConstruction.SQLObject
        |
        +--com.objectwave.persist.sqlConstruction.SQLSelect
All Implemented Interfaces:
SQLAssembler

public class SQLSelect
extends SQLObject
implements SQLAssembler

A class that handles the creation of SQL Select statements. For example:


  SQLSelect sql = new SQLSelect("TABLENAME");

Version:
$Id: SQLSelect.java,v 2.5 2002/03/09 17:13:47 dave_hoag Exp $
Author:
Dave Hoag
See Also:
com.objectwave.persist.SQLAssembler, com.objectwave.persist.RDBBroker

Nested Class Summary
static class SQLSelect.Test
           
 
Field Summary
 java.util.ArrayList columnList
           
 java.lang.StringBuffer columnListBuffer
           
static char nextAlias
           
 
Fields inherited from class com.objectwave.persist.sqlConstruction.SQLObject
availableForPool, table
 
Constructor Summary
SQLSelect()
           
SQLSelect(java.lang.String tableName)
          Create a SQLSelect to find information from the specified table.
SQLSelect(java.lang.String tableName, boolean exclusiveReadLock)
           
 
Method Summary
 void addColumnList(java.lang.String aVal)
          Used for adding one element May update the instance variable 'first' to indicate that at least one column has been added to the column list buffer.
protected  void addColumnList(java.lang.String[] columns)
          Add a list of columns to our list of columns.
 void addOrderBy(java.util.Vector paths, Persistence source)
          This can not be called until the SQL is completely built...other than the Order by clause.
protected  void addOrderByImpl(java.lang.String orderByColumn, boolean descend)
          Assemble a string buffer that will represent the order by part of a Sql statement.
 void addWhereClause(java.lang.String clause)
          Add the clause to our list of where clauses.
 void clean()
          When returning the object to the ObjectPool, the object must be 'clean'.
 void clearColumnList()
          Empty the list of columns.
 java.lang.StringBuffer createWhereClause(java.lang.String compareStatment, java.lang.String columnName)
          Add the alias to the column name & append the compare statement.
 java.lang.String findColumnName(Persistence p, java.lang.String path)
          Find the aliased column name that matchs the path.
 java.lang.String findColumnName(Persistence p, java.lang.String path, java.lang.String alias)
          Find the aliased column name that matchs the path.
protected  void formatForUpdateClause(java.lang.StringBuffer buf)
           
protected  void formatOrderBy(java.lang.StringBuffer buf)
          The point at which the order by portion of the Sql statement is added to our statement.
protected  java.lang.StringBuffer formatSelectList(java.lang.StringBuffer buf)
          Format the beginning of the select statement.
protected  void formatTableList(java.lang.StringBuffer buf)
          Format the list of tables that will be participating in this query.
protected  void formatWhereClause(java.lang.StringBuffer buf)
          Constrain the result set with the where clause elements.
protected static char generateNextAlias()
          Create new alias and return it.
protected  char genNextAlias()
          Create new alias, update this object to know of this new alias, and return the char alias value.
 char getAlias()
          Alias are used to eliminate problems when the same database column name is found on two tables in a single select statement.
protected  java.lang.String getColumnName(java.lang.String tableName, java.lang.String colName)
          Find out what the alias is for the provided table and append it to the 'colName'.
protected static char getNextAlias()
          Use the static member 'nextAlias' to keep track of the next available alias.
protected  int getRecordSize()
          The number of columns that are found by this select statement.
protected  int getRowLimit()
          The result set is limited to the result of this method.
 java.lang.StringBuffer getSqlStatement()
           
 java.util.Vector getWhereClause()
          Get the elements that will make up the where clause.
protected  void initializeAttributes(java.lang.String tableName, boolean exclusiveReadLock)
          Initialize the instance variables to their default values.
 void insertAnyOfClause(java.lang.String compareWith, java.lang.String column, java.util.Vector values)
          Special support for dealing with 'any of (values,...)' clauses.
 void insertConstraintWhereClause(java.lang.String compareStatement, java.lang.String columnName)
          Very similar to insertWhereClause except the value has already been formatted.
protected  void insertTable(java.lang.String tableName, boolean exclusiveReadLock)
          Add a table to our list of tables.
 void insertWhereClause(java.lang.String column, java.lang.Object value)
          The default where clause will do an '=' comparison.
 void insertWhereClause(java.lang.String compareWith, java.lang.String column)
           
 void insertWhereClause(java.lang.String compareWith, java.lang.String column, java.lang.Object value)
          Add a where clause to the current select statement.
 void joinWith(SQLSelect sqlObj, java.lang.String sourceCol, java.lang.String withCol, java.lang.String compareOperator)
          Method used to add additional join columns for multiple primary key support.
 void joinWith(java.lang.String attribLink, SQLSelect sqlObj, java.lang.String sourceCol, java.lang.String withCol)
          Join with another formatted sql statement.
 void joinWith(java.lang.String attribLink, SQLSelect sqlObj, java.lang.String sourceCol, java.lang.String withCol, java.lang.String compareOperator)
          Joins the receiver with sqlObj.
protected static void refreshNextAlias()
          Update the static member nextAlias to contain the next available table alias.
 void setAlias(char c)
          Alias' are used to prevent name collisions with columns from multiple tables.
 void setAsDelete(boolean value)
          Change this query to a delete statement.
 void setCount(boolean value)
          A state variable that indicates that this select is merely doing a count of matching elements.
 void setDistinct(boolean value)
          Sets the Distinct attribute of the SQLSelect object
static void setNextAlias(char aValue)
          Table aliasing allows for the joining of tables with out worry of column name overlap.
 void setOrderBy(boolean value)
          A state variable that indicates if there are order by constraints on this select.
 void setRowLimit(int limit)
          Limit the result set the provided number of rows.
 void setTableName(java.lang.String tableName)
          A sql select could span multiple tables.
 
Methods inherited from class com.objectwave.persist.sqlConstruction.SQLObject
formatValue, getDefaultFormatter, getObjectFormatter, isAvailableForPool, setAvailableForPool, setObjectFormatter
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

nextAlias

public static char nextAlias

columnList

public java.util.ArrayList columnList

columnListBuffer

public java.lang.StringBuffer columnListBuffer
Constructor Detail

SQLSelect

public SQLSelect()

SQLSelect

public SQLSelect(java.lang.String tableName)
Create a SQLSelect to find information from the specified table.

Parameters:
tableName - The name of the table from which this statement is selecting data.

SQLSelect

public SQLSelect(java.lang.String tableName,
                 boolean exclusiveReadLock)
Parameters:
tableName - The name of the table from which this statement is selecting data.
exclusiveReadLock - Lock the table using database level locking.
Method Detail

setNextAlias

public static void setNextAlias(char aValue)
Table aliasing allows for the joining of tables with out worry of column name overlap.

Parameters:
aValue - The new NextAlias value

getNextAlias

protected static char getNextAlias()
Use the static member 'nextAlias' to keep track of the next available alias.

Returns:
A char of the available alias. (A --> Z).

generateNextAlias

protected static char generateNextAlias()
Create new alias and return it. Get the next alias from the static data member, update the next alias value, and return the available alias.

Returns:
char That is to represent a table alias.

refreshNextAlias

protected static void refreshNextAlias()
Update the static member nextAlias to contain the next available table alias. We start with A and when we get to Z we just start over again. This obviously limits the number of tables in one select statement to 26.


setTableName

public void setTableName(java.lang.String tableName)
A sql select could span multiple tables.

Overrides:
setTableName in class SQLObject
Parameters:
tableName - The new TableName value

setAsDelete

public void setAsDelete(boolean value)
Change this query to a delete statement.

Parameters:
value - true would have this statement start with 'delete' instead of 'select'.

setRowLimit

public void setRowLimit(int limit)
Limit the result set the provided number of rows.

Parameters:
limit - The number of rows to limit in the result set.

setAlias

public void setAlias(char c)
Alias' are used to prevent name collisions with columns from multiple tables.

Parameters:
c - The new Alias value
See Also:
getAlias()

setCount

public void setCount(boolean value)
A state variable that indicates that this select is merely doing a count of matching elements.

Parameters:
value - boolean true if we are to find only a count of elements that would match our constraints.

setDistinct

public void setDistinct(boolean value)
Sets the Distinct attribute of the SQLSelect object

Parameters:
value - The new Distinct value

setOrderBy

public void setOrderBy(boolean value)
A state variable that indicates if there are order by constraints on this select.

Parameters:
value - true if this select statement is to include order by constraints.

getAlias

public char getAlias()
Alias are used to eliminate problems when the same database column name is found on two tables in a single select statement. The table is given an alias and that alias is used when providing the column names.

Returns:
The Alias value

getSqlStatement

public java.lang.StringBuffer getSqlStatement()
Specified by:
getSqlStatement in interface SQLAssembler
Returns:
java.lang.StringBuffer containing legal sql code.

getWhereClause

public java.util.Vector getWhereClause()
Get the elements that will make up the where clause.

Returns:
The WhereClause value

getRowLimit

protected int getRowLimit()
The result set is limited to the result of this method.

Returns:
int The row limit for this select.

getRecordSize

protected int getRecordSize()
The number of columns that are found by this select statement.

Returns:
The RecordSize value

getColumnName

protected java.lang.String getColumnName(java.lang.String tableName,
                                         java.lang.String colName)
Find out what the alias is for the provided table and append it to the 'colName'.

Parameters:
tableName - Hopefully the name of one of the tables in the sqlObj.
colName - The column name without any alias.
Returns:
The ColumnName value
See Also:
#findAttributes

clean

public void clean()
When returning the object to the ObjectPool, the object must be 'clean'.

Overrides:
clean in class SQLObject

addColumnList

public void addColumnList(java.lang.String aVal)
Used for adding one element May update the instance variable 'first' to indicate that at least one column has been added to the column list buffer.

Parameters:
aVal - The feature to be added to the ColumnList attribute

addWhereClause

public void addWhereClause(java.lang.String clause)
Add the clause to our list of where clauses. The clause is expected to be complete, meaning the column name contains the appropriate alias and both sides of the comparison operator are provided.

Parameters:
clause - String of the clause. Ex. "a.tableField = 'aValue'"

clearColumnList

public void clearColumnList()
Empty the list of columns.


addOrderBy

public void addOrderBy(java.util.Vector paths,
                       Persistence source)
This can not be called until the SQL is completely built...other than the Order by clause. Since this is not done until the end, concerns about joins and the such are not an issue.

Parameters:
source - Persistence This is usually the persistent object for which we are querying
paths - The feature to be added to the OrderBy attribute

insertAnyOfClause

public void insertAnyOfClause(java.lang.String compareWith,
                              java.lang.String column,
                              java.util.Vector values)
Special support for dealing with 'any of (values,...)' clauses.

Parameters:
column - The column name. The left hand side of the comparison.
values - java.util.Vector The objects to place in the any of clause.
compareWith -

insertWhereClause

public void insertWhereClause(java.lang.String column,
                              java.lang.Object value)
The default where clause will do an '=' comparison.

Overrides:
insertWhereClause in class SQLObject
Parameters:
column - The column name. The left hand side of the comparison.
value - The right hand side of the comparison.

insertWhereClause

public void insertWhereClause(java.lang.String compareWith,
                              java.lang.String column,
                              java.lang.Object value)
Add a where clause to the current select statement. The clause will add the table alias, the parameter column, the comparsion operator and finally a formatted version of the value. Ex. insertWhereClause("=", "aColumn", "aValue") would add the following where clause where a. is the table alias. a.aColumn = 'aValue'

Parameters:
compareWith - The comparison operator. Ex. '=', ' <=', etc...
column - The column name. The left hand side of the comparison.
value - The right hand side of the comparison.

insertWhereClause

public void insertWhereClause(java.lang.String compareWith,
                              java.lang.String column)
Parameters:
compareWith -
column -

createWhereClause

public final java.lang.StringBuffer createWhereClause(java.lang.String compareStatment,
                                                      java.lang.String columnName)
Add the alias to the column name & append the compare statement. ex. "A.columnName != 9"
The value to be compared is a part of the compareStatement parameter.

Parameters:
compareStatment - String A string like "!= 9".
columnName - The column name. The left hand side of the comparison.
Returns:
A string buffer containing the new elements.

insertConstraintWhereClause

public void insertConstraintWhereClause(java.lang.String compareStatement,
                                        java.lang.String columnName)
Very similar to insertWhereClause except the value has already been formatted. The value is part of the compareStatement parameter.

Parameters:
columnName - The column name. The left hand side of the comparison.
compareStatement -

joinWith

public void joinWith(java.lang.String attribLink,
                     SQLSelect sqlObj,
                     java.lang.String sourceCol,
                     java.lang.String withCol)
Join with another formatted sql statement.

Parameters:
sqlObj - Another sql select representing the table to which this is joining.
sourceCol - A column on this SQLSelect that will join with a column on the parameter SQLSelect.
withCol - A column on the parameter SQLSelect that will join with a column on this SQLSelect.
attribLink -

joinWith

public void joinWith(java.lang.String attribLink,
                     SQLSelect sqlObj,
                     java.lang.String sourceCol,
                     java.lang.String withCol,
                     java.lang.String compareOperator)
Joins the receiver with sqlObj. A join condition is added to the whereClause based on the column maps if provided. Adds all of the columns in the parameter SQLSelect to this SQLSelect. Adds all of the tables in the parameter SQLSelect to this SQLSelect.

Parameters:
sqlObj - Another sql select representing the table to which this is joining.
sourceCol - A column on this SQLSelect that will join with a column on the parameter SQLSelect.
withCol - A column on the parameter SQLSelect that will join with a column on this SQLSelect.
attribLink -
compareOperator -

joinWith

public void joinWith(SQLSelect sqlObj,
                     java.lang.String sourceCol,
                     java.lang.String withCol,
                     java.lang.String compareOperator)
Method used to add additional join columns for multiple primary key support. Joins the receiver with sqlObj. A join condition is added to the whereClause based on the column maps if provided. Adds all of the columns in the parameter SQLSelect to this SQLSelect. Adds all of the tables in the parameter SQLSelect to this SQLSelect.

Parameters:
sourceCol - A column on this SQLSelect that will join with a column on the parameter SQLSelect.
withCol - A column on the parameter SQLSelect that will join with a column on this SQLSelect.
compareOperator -
sqlObj -

findColumnName

public java.lang.String findColumnName(Persistence p,
                                       java.lang.String path)
Find the aliased column name that matchs the path.

Parameters:
p - The source object from which the path statement will begin it's search.
path -
Returns:
The columnName to use in this query that will correspond to the 'path'.
See Also:
#findAttributes

findColumnName

public java.lang.String findColumnName(Persistence p,
                                       java.lang.String path,
                                       java.lang.String alias)
Find the aliased column name that matchs the path.

Parameters:
p - The source object from which the path statement will begin it's search.
path -
alias -
Returns:
The columnName to use in this query that will correspond to the 'path'.
See Also:
#findAttributes

initializeAttributes

protected void initializeAttributes(java.lang.String tableName,
                                    boolean exclusiveReadLock)
Initialize the instance variables to their default values.

Parameters:
tableName - The name of the table on which this select will be centered.
exclusiveReadLock -

addColumnList

protected void addColumnList(java.lang.String[] columns)
Add a list of columns to our list of columns.

Parameters:
columns - The column names include in the select statement.

formatForUpdateClause

protected void formatForUpdateClause(java.lang.StringBuffer buf)
Parameters:
buf - The buffer containing the statment being built.

addOrderByImpl

protected void addOrderByImpl(java.lang.String orderByColumn,
                              boolean descend)
Assemble a string buffer that will represent the order by part of a Sql statement. The parameter value must have the alias set before calling this method.

Parameters:
orderByColumn - A database column that is part of the query.
descend - The feature to be added to the OrderByImpl attribute

formatOrderBy

protected void formatOrderBy(java.lang.StringBuffer buf)
The point at which the order by portion of the Sql statement is added to our statement.

Parameters:
buf - The buffer containing the statment being built.

formatSelectList

protected java.lang.StringBuffer formatSelectList(java.lang.StringBuffer buf)
Format the beginning of the select statement.

Parameters:
buf - The buffer containing the statment being built.
Returns:

formatTableList

protected void formatTableList(java.lang.StringBuffer buf)
Format the list of tables that will be participating in this query.

Parameters:
buf - The buffer containing the statment being built.

formatWhereClause

protected void formatWhereClause(java.lang.StringBuffer buf)
Constrain the result set with the where clause elements.

Parameters:
buf - The buffer containing the statment being built.

genNextAlias

protected char genNextAlias()
Create new alias, update this object to know of this new alias, and return the char alias value.

Returns:
char That is to represent a table alias for this instance of SQLSelect.

insertTable

protected void insertTable(java.lang.String tableName,
                           boolean exclusiveReadLock)
Add a table to our list of tables. An alias is assigned to each table.

Parameters:
exclusiveReadLock - boolean Indicating if the table should be locked.
tableName - String Name of the table.