com.perdues.db
Class Query

java.lang.Object
  |
  +--com.perdues.db.Query

public class Query

extends java.lang.Object

Class that holds some clauses of a SQL select and helps you build select statements. Serves a couple of different purposes.

First, this helps you build the syntax of the statement by letting you add information to any part of the statement at any time, and by inserting punctuation and logical connectives between the parts.

Second, this class builds SQL with properly escaped and quoted literals, so characters such as "'" can appear in your data.

Finally, if you add columns using methods that take ColumnInfo objects, this will automatically add the tables of those columns to the tables list. If you request equijoins for a table, it will use foreign key information in the Schema to generate the conditions that the primary keys of tables refer to match the foreign key into it from the given table.

Field Summary

static boolean

debug

Constructor Summary

Query(Query template)
You can build a select statement from another Query.

Query(Schema schema)

Method Summary

void	addAliasedTable(java.lang.String name, java.lang.String alias) Add a table to the statement, giving an alias.
void	addColumn(ColumnInfo col) Add a column to the list of columns to select, and its table to the set of tables for the query.
void	addColumn(java.lang.String colName) Add a column to the list of columns to select, given its name; and adds the column's table to the set of tables for the query.
void	addColumns(java.lang.String[] colIds) Adds each column ID String in the given array to this Query, as by addColumn.
void	addCondition(java.lang.String expr) Add a condition to the "where" part of the select.
void	addEquiJoins(java.lang.String tableName) Add a table to the join across known tables.
void	addEquiJoins(TableInfo table) Adds equijoin conditions to this Query, requiring that foreign keys of the given TableInfo must be equal to the corresponding primary key of the table referred to.
void	addFieldTest(java.lang.String field, java.lang.String op, java.lang.String value) Adds a condition comparing the value of a field to a specific String value.
void	addHaving(java.lang.String expr) Add a condition to the "having" part of the select.
void	addSearchContainsCondition(java.lang.String field, java.lang.String value) Adds a condition that the value of the named field must contain the given value, or if the field is an empty string or null no condition is added.
void	addSearchEqualCondition(java.lang.String field, java.lang.String value) Adds a condition that the value of the named field must be equal to the given value, or if the field is an empty string or null no condition is added.
void	addTable(java.lang.String tableName) Add a table by name to the list of tables to select from.
void	addTable(TableInfo table) Add a table to the statement, using a TableInfo object.
void	addWhat(java.lang.String what) Add a result column that is not a table column.
static int	getLastInsertID(java.sql.Statement stmt) Returns the last "insert ID" automatically generated by the database for an insert request on the given Statement's connection.
java.lang.String	render() Returns a string containing the complete select statement.
void	setGroupBy(java.lang.String clause) Sets contents for the "group by" clause of the statement.
void	setLimit(java.lang.String clause) Sets contents for the (MySQL-specific) "limit" clause.
void	setOrderBy(java.lang.String clause) Sets contents for the "order by" clause of the statement.
static java.lang.String	sqlEscape(java.lang.String s) Escape the String so it is appropriate as part of a SQL string literal.
static java.lang.String	sqlFormat(java.lang.Object o) Formats an object as a literal for SQL.
java.lang.String	toString() Returns a string containing the complete select statement.

Methods inherited from class java.lang.Object

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

Field Detail

debug

public static boolean debug

Constructor Detail

Query

public Query(Schema schema)

Query

public Query(Query template)

You can build a select statement from another Query.

Method Detail

getLastInsertID

public static int getLastInsertID(java.sql.Statement stmt)
                           throws java.sql.SQLException

Returns the last "insert ID" automatically generated by the database for an insert request on the given Statement's connection. The implementation is specific to the database software.

sqlEscape

public static java.lang.String sqlEscape(java.lang.String s)

Escape the String so it is appropriate as part of a SQL string literal.

sqlFormat

public static java.lang.String sqlFormat(java.lang.Object o)

Formats an object as a literal for SQL. Currently handles Strings, Dates, java.sql.Dates, java.util.Dates, and Numbers. String formatting is specific to mySQL!!!

addWhat

public void addWhat(java.lang.String what)

Add a result column that is not a table column. Simply adds this string to the comma-separated result columns. This may be any SQL expression suitable for the column list part of a Select.

addColumn

public void addColumn(java.lang.String colName)

Add a column to the list of columns to select, given its name; and adds the column's table to the set of tables for the query.

Same as addColumn(ColumnInfo col), but takes a String naming the column. (A string naming the column is a qualified name with the table name or shorthand, and the name of the column, separated by ".".)

addColumn

public void addColumn(ColumnInfo col)

Add a column to the list of columns to select, and its table to the set of tables for the query. Use of the ColumnInfo object reduces potential ambiguity and automatically adds the column's table to the list of tables for the query.

addColumns

public void addColumns(java.lang.String[] colIds)

Adds each column ID String in the given array to this Query, as by addColumn.

addTable

public void addTable(java.lang.String tableName)

Add a table by name to the list of tables to select from. The name may be either a full name or a shorthand.

addTable

public void addTable(TableInfo table)

Add a table to the statement, using a TableInfo object. If the table has a shorthand name, this will establish the shorthand for use in the rest of the query. You may add the same table repeatedly through this method with no harmful effects.

addAliasedTable

public void addAliasedTable(java.lang.String name,
                            java.lang.String alias)

Add a table to the statement, giving an alias. Does not add the table to the list of tables, so it gets no support when building joins. (Any other limitations from this?)

addCondition

public void addCondition(java.lang.String expr)

Add a condition to the "where" part of the select. The conditions are joined by "and".

addHaving

public void addHaving(java.lang.String expr)

Add a condition to the "having" part of the select. The conditions are joined by "and".

addSearchEqualCondition

public void addSearchEqualCondition(java.lang.String field,
                                    java.lang.String value)

Adds a condition that the value of the named field must be equal to the given value, or if the field is an empty string or null no condition is added. This is useful for building search queries from text fields in forms.

addSearchContainsCondition

public void addSearchContainsCondition(java.lang.String field,
                                       java.lang.String value)

Adds a condition that the value of the named field must contain the given value, or if the field is an empty string or null no condition is added. This is useful for building search queries from text fields in forms.

addFieldTest

public void addFieldTest(java.lang.String field,
                         java.lang.String op,
                         java.lang.String value)

Adds a condition comparing the value of a field to a specific String value.

addEquiJoins

public void addEquiJoins(java.lang.String tableName)

Add a table to the join across known tables. Calls addEquiJoins with a TableInfo parameter.

addEquiJoins

public void addEquiJoins(TableInfo table)

Adds equijoin conditions to this Query, requiring that foreign keys of the given TableInfo must be equal to the corresponding primary key of the table referred to.

Better to do this just once per table before rendering the query. Probably buggy if the given table has more than one column that is a foreign key into the the same table.

setGroupBy

public void setGroupBy(java.lang.String clause)

Sets contents for the "group by" clause of the statement.

setOrderBy

public void setOrderBy(java.lang.String clause)

Sets contents for the "order by" clause of the statement.

setLimit

public void setLimit(java.lang.String clause)

Sets contents for the (MySQL-specific) "limit" clause.

toString

public java.lang.String toString()

Returns a string containing the complete select statement.

Overrides:

toString in class java.lang.Object

render

public java.lang.String render()

Returns a string containing the complete select statement.