org.skife.sql
Class QueryBuilder

java.lang.Object
  |
  +--org.skife.sql.QueryBuilder

public class QueryBuilder

extends java.lang.Object

QueryBuilder provides a convenient way to build queries programmatically. It supports adding query components in any order and will dynamically construct the resultant SQL query string from all of the components added.

Within a single type of component the ordering is maintained, but it doesn't matter if you specify the where criteria before the tables being queried, etc.

As the ordering can be flexible a mechanism for supporting prepared statements is also included. Tokens of the form {foo} will have ? substituted for them in the generated query string. To then bind the values to the query there are two available techniques: a map with the tokens as keys and get back a List of the objects to bind in order via the getBindList(Map map) method; or the more convenient form, bind(PreparedStatement stmt, Map bindings) method which takes the prepared statement and map of binding values as arguments and binds the values in the correct order for you.

Field Summary

static java.lang.String

TOKEN_PATTERN If in doubt as to whether a token is okay or not, here is the regex used to match tokens: "\\{\\s*?(\\w+?)\\s*?\\}"

Method Summary

org.skife.sql.QueryBuilder	all() Return all columns
java.sql.PreparedStatement	bind(java.sql.PreparedStatement stmt, java.util.Map bindings) Binds the bindings to the stmt assuming the statement was built with this querybuilder
org.skife.sql.QueryBuilder	column(java.lang.String name) Specify a column to include in the result set
org.skife.sql.QueryBuilder	distinct() Specify to return distinct results, ie: "select distinct foo from bar"
org.skife.sql.QueryBuilder	from(java.lang.String table) Specify a table to query against Joins are added to the same component as From tables
org.skife.sql.QueryBuilder	fullOuterJoin(java.lang.String left, java.lang.String right, java.lang.String criteria) Specify a full outer innerJoin.
java.util.ArrayList	getBindList(java.util.Map props) Convenience method used to properly order binding values to the constructed query.
java.lang.String	getQueryString() Obtain the query string built thus far.
java.lang.String	getQueryStringWithTokens() Obtain the query string without token substitution.
java.util.List	getTokens() Obtain a list of all the tokens in the order they appear in the query
org.skife.sql.QueryBuilder	groupBy(java.lang.String clause) Specify an "group by" clause
org.skife.sql.QueryBuilder	in(java.lang.String column, java.util.Collection values) Specify an SQL "IN" clause to include in the where component.
org.skife.sql.QueryBuilder	in(java.lang.String column, java.lang.String[] values) Specify an SQL "IN" clause to include in the where component.
org.skife.sql.QueryBuilder	innerJoin(java.lang.String left, java.lang.String right, java.lang.String criteria) Specify an inner innerJoin.
org.skife.sql.QueryBuilder	leftOuterJoin(java.lang.String left, java.lang.String right, java.lang.String criteria) Specify a left outer innerJoin.
org.skife.sql.QueryBuilder	notIn(java.lang.String column, java.util.Collection values) Specify an SQL "NOT IN" clause to include in the where component.
org.skife.sql.QueryBuilder	notIn(java.lang.String column, java.lang.String[] values) Specify an SQL "NOT IN" clause to include in the where component.
org.skife.sql.QueryBuilder	orderBy(java.lang.String clause) Specify an "order by" clause
org.skife.sql.QueryBuilder	rightOuterJoin(java.lang.String left, java.lang.String right, java.lang.String criteria) Specify a right outer innerJoin.
static org.skife.sql.QueryBuilder	select() Factory method used to generate a "select..." style query
org.skife.sql.QueryBuilder	subSelect(java.lang.String column, org.skife.sql.QueryBuilder query) Specify a sub select for the query of the form "where ([column] in ([query.getQueryString()]))
org.skife.sql.QueryBuilder	subSelect(java.lang.String column, java.lang.String query) Specify a sub select for the query of the form "where ([column] in ([query]))
org.skife.sql.QueryBuilder	subSelectDisjunction(java.lang.String column, org.skife.sql.QueryBuilder query) Specify a sub select disjunction for the query of the form "where ([column] not in ([query]))
org.skife.sql.QueryBuilder	subSelectDisjunction(java.lang.String column, java.lang.String query) Specify a sub select disjunction for the query of the form "where ([column] not in ([query]))
java.lang.String	toString()
org.skife.sql.QueryBuilder	where(java.lang.String clause) Add to the where clause of the query

Methods inherited from class java.lang.Object

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

Field Detail

TOKEN_PATTERN

public static final java.lang.String TOKEN_PATTERN

If in doubt as to whether a token is okay or not, here is the regex used to match tokens: "\\{\\s*?(\\w+?)\\s*?\\}"

See Also:

Constant Field Values

Method Detail

select

public static org.skife.sql.QueryBuilder select()

Factory method used to generate a "select..." style query

Returns:

a new QueryBuilder

distinct

public org.skife.sql.QueryBuilder distinct()

Specify to return distinct results, ie: "select distinct foo from bar"

Returns:

the same QueryBuilder

all

public org.skife.sql.QueryBuilder all()

Return all columns

Returns:

the same QueryBuilder

column

public org.skife.sql.QueryBuilder column(java.lang.String name)

Specify a column to include in the result set

Parameters:

name - The column name ("column"), and optionally an alias ("column alias") to include

Returns:

the same QueryBuilder

from

public org.skife.sql.QueryBuilder from(java.lang.String table)

Specify a table to query against Joins are added to the same component as From tables

Parameters:

table - name of the table to query

Returns:

the same QueryBuilder

where

public org.skife.sql.QueryBuilder where(java.lang.String clause)

Add to the where clause of the query

Parameters:

clause - the criteria to include in the where clause

Returns:

the same QueryBuilder

in

public org.skife.sql.QueryBuilder in(java.lang.String column,
                                     java.lang.String[] values)

Specify an SQL "IN" clause to include in the where component. This form will expand the values of values to the form "where ( column in " (values[0], values[1], ... , values[n-1], values[n]))

Parameters:

column - column for the subject of the in clause

values - values for the object of the in clause

Returns:

the same QueryBuilder

notIn

public org.skife.sql.QueryBuilder notIn(java.lang.String column,
                                        java.lang.String[] values)

Specify an SQL "NOT IN" clause to include in the where component. This form will expand the values of values to the form "where ( column not in " (values[0], values[1], ... , values[n-1], values[n]))

Parameters:

column - column for the subject of the in clause

values - values for the object of the in clause

Returns:

the same QueryBuilder

in

public org.skife.sql.QueryBuilder in(java.lang.String column,
                                     java.util.Collection values)

Specify an SQL "IN" clause to include in the where component. This form will expand the values of values to the form "where ( column in " (values[0], values[1], ... , values[n-1], values[n]))

Parameters:

column - column for the subject of the in clause

values - values for the object of the in clause

Returns:

the same QueryBuilder

notIn

public org.skife.sql.QueryBuilder notIn(java.lang.String column,
                                        java.util.Collection values)

Specify an SQL "NOT IN" clause to include in the where component. This form will expand the values of values to the form "where ( column not in " (values[0], values[1], ... , values[n-1], values[n]))

Parameters:

column - column for the subject of the in clause

values - values for the object of the in clause

Returns:

the same QueryBuilder

subSelect

public org.skife.sql.QueryBuilder subSelect(java.lang.String column,
                                            java.lang.String query)

Specify a sub select for the query of the form "where ([column] in ([query]))

Parameters:

column - subject of the subselect clause

query - object of the subselect clause

Returns:

the same QueryBuilder

subSelect

public org.skife.sql.QueryBuilder subSelect(java.lang.String column,
                                            org.skife.sql.QueryBuilder query)

Specify a sub select for the query of the form "where ([column] in ([query.getQueryString()]))

Parameters:

column - subject of the subselect clause

query - object of the subselect clause

Returns:

the same QueryBuilder

subSelectDisjunction

public org.skife.sql.QueryBuilder subSelectDisjunction(java.lang.String column,
                                                       java.lang.String query)

Specify a sub select disjunction for the query of the form "where ([column] not in ([query]))

Parameters:

column - subject of the subselect clause

query - object of the subselect clause

Returns:

the same QueryBuilder

subSelectDisjunction

public org.skife.sql.QueryBuilder subSelectDisjunction(java.lang.String column,
                                                       org.skife.sql.QueryBuilder query)

Specify a sub select disjunction for the query of the form "where ([column] not in ([query]))

Parameters:

column - subject of the subselect clause

query - object of the subselect clause

Returns:

the same QueryBuilder

leftOuterJoin

public org.skife.sql.QueryBuilder leftOuterJoin(java.lang.String left,
                                                java.lang.String right,
                                                java.lang.String criteria)

Specify a left outer innerJoin.

All joins are specified in the From component

Parameters:

left - column on the left side of the innerJoin

right - column on the right side of the innerJoin

criteria - innerJoin criteria

Returns:

the same QueryBuilder

rightOuterJoin

public org.skife.sql.QueryBuilder rightOuterJoin(java.lang.String left,
                                                 java.lang.String right,
                                                 java.lang.String criteria)

Specify a right outer innerJoin.

All joins are specified in the From component

Parameters:

left - column on the left side of the innerJoin

right - column on the right side of the innerJoin

criteria - innerJoin criteria

Returns:

the same QueryBuilder

fullOuterJoin

public org.skife.sql.QueryBuilder fullOuterJoin(java.lang.String left,
                                                java.lang.String right,
                                                java.lang.String criteria)

Specify a full outer innerJoin.

All joins are specified in the From component

Parameters:

left - column on the left side of the innerJoin

right - column on the right side of the innerJoin

criteria - innerJoin criteria

Returns:

the same QueryBuilder

innerJoin

public org.skife.sql.QueryBuilder innerJoin(java.lang.String left,
                                            java.lang.String right,
                                            java.lang.String criteria)

Specify an inner innerJoin.

All joins are specified in the From component

Parameters:

left - column on the left side of the innerJoin

right - column on the right side of the innerJoin

criteria - innerJoin criteria

Returns:

the same QueryBuilder

orderBy

public org.skife.sql.QueryBuilder orderBy(java.lang.String clause)

Specify an "order by" clause

Returns:

the same QueryBuilder

groupBy

public org.skife.sql.QueryBuilder groupBy(java.lang.String clause)

Specify an "group by" clause

Returns:

the same QueryBuilder

getQueryString

public java.lang.String getQueryString()

Obtain the query string built thus far. This will subsitute "?" for any tokens in the query in order to support prepared statements. Use the bind(...) or getBindList(...) to build the properly ordered prepared statement bind values

getQueryStringWithTokens

public java.lang.String getQueryStringWithTokens()

Obtain the query string without token substitution. Very useful for debugging, and highly suggested for error logging in exceptions or whatnot.

getTokens

public java.util.List getTokens()

Obtain a list of all the tokens in the order they appear in the query

getBindList

public java.util.ArrayList getBindList(java.util.Map props)

Convenience method used to properly order binding values to the constructed query. It requires a map where the key is the token name ("id" not "{id}") and the value is the value to bind for that token.

The binding process can also be handled by this class via the bind(...) method, which is probably more convenient than this form most of the time.

To use with commons-dbutils querying functionality use the toArray() functionality on the returned list.

Parameters:

props - token -> value to bind to that token

Returns:

a List with the values to bind the statement in order of binding

Throws:

java.lang.IllegalArgumentException - if it finds a token in the query string without without a matching binding in the props argument

bind

public java.sql.PreparedStatement bind(java.sql.PreparedStatement stmt,
                                       java.util.Map bindings)
                                throws java.sql.SQLException

Binds the bindings to the stmt assuming the statement was built with this querybuilder

Returns:

the same PreparedStatement with the bindings done (as a convenience)

Throws:

java.lang.IllegalArgumentException - if there is an unmatched token

java.sql.SQLException - if there is a problem setting a binding on the PreparedStatement

toString

public java.lang.String toString()

Overrides:

toString in class java.lang.Object

Returns:

select statement built so far with token subsitution