jimm.datavision.source.sql
Class SQLQuery

java.lang.Object
  extended by jimm.datavision.source.Query
      extended by jimm.datavision.source.sql.SQLQuery
All Implemented Interfaces:
Writeable
Direct Known Subclasses:
NCQuery, SubreportQuery

public class SQLQuery
extends Query

Queries build SQL query strings. They contain tables, joins, and where clauses.

Author:
Jim Menard, jimm@io.com
See Also:
ParserHelper

Field Summary
protected  java.util.ArrayList preparedStmtValues
           
protected  java.util.Set tables
           
 
Fields inherited from class jimm.datavision.source.Query
joins, report, selectables, SORT_ASCENDING, SORT_DESCENDING, SORT_UNDEFINED, sortOrders, sortSelectables, whereClause
 
Constructor Summary
SQLQuery(Report report)
          Constructor.
 
Method Summary
protected  void addParameter(java.lang.StringBuffer buf, java.lang.String prevWord, java.lang.String idAsString)
          Given a parameter id string, add its value(s) to the parameter list and add prepared statement placeholders in the query buffer.
protected  void addParameterForDisplay(java.lang.StringBuffer buf, java.lang.String prevWord, java.lang.String idAsString)
          Given a parameter id string, add it and a possible modified previous word to buf.
protected  void addTable(Table t)
          Adds the table t to tables, but only if t is not null and is not already in tables.
protected  void buildFrom(java.lang.StringBuffer str)
           
protected  void buildJoins(java.lang.StringBuffer str)
           
protected  void buildOrderBy(java.lang.StringBuffer str)
           
protected  void buildSelect(java.lang.StringBuffer str)
           
protected  void buildUserWhereClause(java.lang.StringBuffer str, boolean forDisplay)
           
protected  void buildWhereClause(java.lang.StringBuffer str, boolean forDisplay)
           
 void findSelectablesUsed()
          Builds collections of the report tables and selectable fields actually used in the report.
 int getNumTables()
          Returns the number of tables in the query.
 java.util.Collection getTablesUsed()
          Returns a collection containing the tables used by this query.
protected  java.lang.String getWhereClauseForDisplay()
          Returns the where clause string; may be null.
protected  java.lang.String getWhereClauseForPreparedStatement()
          Returns the where clause string; may be null.
 java.lang.String prepare(java.lang.String clause)
          Given a clause (really any string), replace all formulas and parameters with their values.
protected  java.lang.String queryAsString(boolean forDisplay)
          Returns the query as either a human-readable SQL statement or a SQL string suitable for building a prepared statement.
 java.lang.String quoted(java.lang.String name)
          Quotes those parts of a table or column name that need to be quoted.
 void setParameters(java.sql.PreparedStatement stmt)
          Given a prepared statement created with the text returned by toPreparedStatementString, plug in all the parameter and formula values.
 java.lang.String toPreparedStatementString()
          Returns the query as a SQL string suitable for building a prepared statement.
 java.lang.String toString()
          Returns the query as a human-readable SQL statement, including parameter, formula, and user column display strings.
 
Methods inherited from class jimm.datavision.source.Query
addAllJoins, addJoin, addSort, clearJoins, clearSorts, containsReferenceTo, getEditableWhereClause, getNumSelectables, getWhereClause, indexOfSelectable, joins, reloadColumns, removeJoin, removeSort, selectables, setEditableWhereClause, setWhereClause, sortedSelectables, sortOrderOf, writeExtras, writeXML
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 

Field Detail

tables

protected java.util.Set tables

preparedStmtValues

protected java.util.ArrayList preparedStmtValues
Constructor Detail

SQLQuery

public SQLQuery(Report report)
Constructor.

Parameters:
report - the report for which this query will generate SQL
Method Detail

getWhereClauseForPreparedStatement

protected java.lang.String getWhereClauseForPreparedStatement()
Returns the where clause string; may be null. If there are any parameter values, we return '?' in their place and save the values for later use.

This code may also modify the clause. For example, a parameter can change the previous comparison operator ("=", "is") based on its arity.

Returns:
the where clause string; may be null
See Also:
Query.getWhereClause()

prepare

public java.lang.String prepare(java.lang.String clause)
Given a clause (really any string), replace all formulas and parameters with their values. Anything else in curly braces must be a column; we remove the curly braces and quote the name.

Implementation note: we can't use StringUtils.replaceDelimited because we modify the symbol that appears before some of the delimited items.


addParameter

protected void addParameter(java.lang.StringBuffer buf,
                            java.lang.String prevWord,
                            java.lang.String idAsString)
Given a parameter id string, add its value(s) to the parameter list and add prepared statement placeholders in the query buffer. Appends the previous word to the buffer. The previous word may be modified if the circumstances call for it. For example, we want to turn "foo in {?Range Parameter}" into "foo between ? and ?". The value of prevWord here would be "in". We would append "between" to the buffer and return the "word" "? and ".

Parameters:
buf - a string buffer containing the SQL query so far
prevWord - the previous word
idAsString - the parameter id

addParameterForDisplay

protected void addParameterForDisplay(java.lang.StringBuffer buf,
                                      java.lang.String prevWord,
                                      java.lang.String idAsString)
Given a parameter id string, add it and a possible modified previous word to buf. Does not modify preparedStmtValues list.

Parameters:
buf - a string buffer containing the SQL query so far
prevWord - the previous word
idAsString - the parameter id
See Also:
addParameter(java.lang.StringBuffer, java.lang.String, java.lang.String)

findSelectablesUsed

public void findSelectablesUsed()
Builds collections of the report tables and selectable fields actually used in the report.

Overrides:
findSelectablesUsed in class Query

addTable

protected void addTable(Table t)
Adds the table t to tables, but only if t is not null and is not already in tables. We compare tables by name instead of value (pointer) because different table object may refer to the same table, for example if one is from the report and the other is from a subreport.

Parameters:
t - a Table

getNumTables

public int getNumTables()
Returns the number of tables in the query. Does not recalculate the columns or tables used; we assume this is being called after the query has been run, or at least after findSelectablesUsed has been called.

This method is only used for testing, so far.


getTablesUsed

public java.util.Collection getTablesUsed()
Returns a collection containing the tables used by this query.

Returns:
the collection of tables used by this query

getWhereClauseForDisplay

protected java.lang.String getWhereClauseForDisplay()
Returns the where clause string; may be null. If there are any column names contained in curly braces, we remove the curly braces. Formulas, parameters, and user colums remain as-is.

Implementation note: we can't use StringUtils.replaceDelimited because we modify the symbol that appears before some of the delimited items.

Returns:
the where clause string; may be null
See Also:
Query.getWhereClause()

toString

public java.lang.String toString()
Returns the query as a human-readable SQL statement, including parameter, formula, and user column display strings.

Overrides:
toString in class java.lang.Object
Returns:
a SQL query string

toPreparedStatementString

public java.lang.String toPreparedStatementString()
Returns the query as a SQL string suitable for building a prepared statement.

Returns:
a SQL query string

queryAsString

protected java.lang.String queryAsString(boolean forDisplay)
Returns the query as either a human-readable SQL statement or a SQL string suitable for building a prepared statement.

Parameters:
forDisplay - if true return a human-readable string, else return a SQL string suitable for building a prepared statement
Returns:
a SQL string

buildSelect

protected void buildSelect(java.lang.StringBuffer str)

buildFrom

protected void buildFrom(java.lang.StringBuffer str)

buildWhereClause

protected void buildWhereClause(java.lang.StringBuffer str,
                                boolean forDisplay)

buildJoins

protected void buildJoins(java.lang.StringBuffer str)

buildUserWhereClause

protected void buildUserWhereClause(java.lang.StringBuffer str,
                                    boolean forDisplay)

buildOrderBy

protected void buildOrderBy(java.lang.StringBuffer str)

setParameters

public void setParameters(java.sql.PreparedStatement stmt)
                   throws java.sql.SQLException
Given a prepared statement created with the text returned by toPreparedStatementString, plug in all the parameter and formula values.

Throws:
java.sql.SQLException
See Also:
toPreparedStatementString()

quoted

public java.lang.String quoted(java.lang.String name)
Quotes those parts of a table or column name that need to be quoted.

Different databases and JDBC drivers treat case sensitively differently. We use the database metadata case sensitivity values to determine which parts of the name need to be quoted.

Parameters:
name - a table or column name
Returns:
a quoted version of the name