jimm.datavision.source.sql
Class SQLQuery

java.lang.Object
  jimm.datavision.source.Query
      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