com.hp.util.sql

Class SqlUtils

java.lang.Object

com.hp.util.sql.SqlUtils

public class SqlUtils
extends Object

Utility class for generating SQL.

Author:

Simon Hunt

Method Summary

Methods

Modifier and Type	Method and Description
static Expr	and(Collection<Expr> exprs) This method returns an expression that combines the sub-expressions with logical AND.
static Expr	and(Expr... exprs) This method returns an expression that combines the sub-expressions with logical AND.
static Expr	bool(String columnName, boolean value) This method returns an expression for the specified column name and boolean value.
static Expr[]	exprWrap(Object... objs) This method returns the specified objects wrapped as expressions.
static Expr	exprWrapQuoted(String s) This method returns the specified string wrapped as an expression of that string in single quotes.
static GroupByClause	groupBy(String columnName) Convenience method to create a group-by clause for a single column.
static GroupByClause.Builder	groupByBuilder() Convenience method to create a group-by clause builder.
static Expr	in(String columnName, Collection<String> values) This method returns an IN expression for the specified column name and the given collection of string values, wrapping each value in single quotes.
static Expr	in(String columnName, Expr... exprs) This method returns an IN expression for the specified column name and sub expressions.
static String	join(String... s) This is a convenience method that calls StringUtils.join(), using ", " (comma-space) as the delimiter.
static Expr	leaf(String s) This is a temporary method which may go away in the future.
static Expr	like(String columnName, String string) This method returns a LIKE expression for the specified column name and substring to match.
static LimitClause	limit() Convenience method to return a limit clause.
static Expr	notIn(String columnName, Collection<String> values) This method returns a NOT IN expression for the specified column name and the given collection of string values, wrapping each value in single quotes.
static Expr	notIn(String columnName, Expr... exprs) This method returns a NOT IN expression for the specified column name and sub expressions.
static Expr	or(Collection<Expr> exprs) This method returns an expression that combines the sub-expressions with logical OR.
static Expr	or(Expr... exprs) This method returns an expression that combines the sub-expressions with logical OR.
static OrderByClause	orderByAsc(String columnName) Convenience method to create an order-by clause (ascending) for a single column.
static OrderByClause.Builder	orderByBuilder() Convenience method to create an order-by clause builder.
static OrderByClause	orderByDesc(String columnName) Convenience method to create an order-by clause (descending) for a single column.
static SelectClause	select(Expr e) Convenience method to create a select clause.
static SelectClause	select(Expr e, String tableName) Convenience method to create a select clause.
static SqlStatement	sql(SelectClause sc) Convenience method to create an SQL statement from the given select clause.
static SqlStatement	sql(SelectClause sc, WhereClause wc) Convenience method to create an SQL statement from the given clauses.
static SqlStatement	sql(SelectClause sc, WhereClause wc, GroupByClause gbc, OrderByClause obc) Convenience method to create an SQL statement from the given clauses.
static SqlStatement	sql(SelectClause sc, WhereClause wc, OrderByClause obc, LimitClause l) Convenience method to create an SQL statement from the given clauses.
static SqlStatement.Builder	sqlBuilder() Convenience method to create an SQL statement builder.
static WhereClause	where(Expr e) Convenience method to create a where clause.

Methods inherited from class java.lang.Object

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

Method Detail

leaf

public static Expr leaf(String s)

This is a temporary method which may go away in the future. It creates and returns an "expression" formed of the given string.

Parameters:

s - the string

Returns:

a leaf expression

join

public static String join(String... s)

This is a convenience method that calls StringUtils.join(), using ", " (comma-space) as the delimiter.

Parameters:

s - the strings to be joined

Returns:

the comma delimited list

and

public static Expr and(Expr... exprs)

This method returns an expression that combines the sub-expressions with logical AND.

 (e1 and e2 and e3 and ...)
 

Parameters:

exprs - sub expressions to be ANDed

Returns:

the composite AND expression

and

public static Expr and(Collection<Expr> exprs)

This method returns an expression that combines the sub-expressions with logical AND.

Parameters:

exprs - sub expressions to be ANDed

Returns:

the composite AND expression

or

public static Expr or(Expr... exprs)

This method returns an expression that combines the sub-expressions with logical OR.

 (e1 or e2 or e3 or ...)
 

Parameters:

exprs - sub expressions to be ORed

Returns:

the composite OR expression

or

public static Expr or(Collection<Expr> exprs)

This method returns an expression that combines the sub-expressions with logical OR.

Parameters:

exprs - sub expressions to be ORed

Returns:

the composite OR expression

in

public static Expr in(String columnName,
      Collection<String> values)

This method returns an IN expression for the specified column name and the given collection of string values, wrapping each value in single quotes.

 columnName in ('v1', 'v2', 'v3', ...)
 

Parameters:

columnName - the column name

values - the string values

Returns:

the IN expression

notIn

public static Expr notIn(String columnName,
         Collection<String> values)

This method returns a NOT IN expression for the specified column name and the given collection of string values, wrapping each value in single quotes.

 columnName not in ('v1', 'v2', 'v3', ...)
 

Parameters:

columnName - the column name

values - the string values

Returns:

the NOT IN expression

in

public static Expr in(String columnName,
      Expr... exprs)

This method returns an IN expression for the specified column name and sub expressions.

 columnName in (e1, e2, e3, ...)
 

Parameters:

columnName - the column name

exprs - the sub-expressions to match

Returns:

the IN expression

notIn

public static Expr notIn(String columnName,
         Expr... exprs)

This method returns a NOT IN expression for the specified column name and sub expressions.

 columnName not in (e1, e2, e3, ...)
 

Parameters:

columnName - the column name

exprs - the sub-expressions to match

Returns:

the NOT IN expression

like

public static Expr like(String columnName,
        String string)

This method returns a LIKE expression for the specified column name and substring to match.

 columnName like '%string%'
 

Parameters:

columnName - the column name

string - the string to match

Returns:

the LIKE expression

bool

public static Expr bool(String columnName,
        boolean value)

This method returns an expression for the specified column name and boolean value.

 FALSE: columnName = 0
 TRUE:  columnName = 1
 

Parameters:

columnName - the column name

value - the value to match

Returns:

the expression

exprWrapQuoted

public static Expr exprWrapQuoted(String s)

This method returns the specified string wrapped as an expression of that string in single quotes.

 'stringValue'
 

Parameters:

s - the string

Returns:

the string wrapped as a string expression

exprWrap

public static Expr[] exprWrap(Object... objs)

This method returns the specified objects wrapped as expressions.

Parameters:

objs - the objects to wrap

Returns:

the array of objects wrapped as expressions

select

public static SelectClause select(Expr e)

Convenience method to create a select clause. Note that this form expects "from tableName" to be included at the tail end of the expression. For example, to create a clause of the form "select count(*) from table", one could write:

 import static com.hp.util.sql.SqlUtils.*;
 ...
 SelectClause c = select(leaf("count(*) from table"));
 

Parameters:

e - the expression for the select clause

Returns:

the select clause

See Also:

select(Expr,String)

select

public static SelectClause select(Expr e,
                  String tableName)

Convenience method to create a select clause. This form separates the table name from the expression of what should be selected. For example, to create a clause of the form "select count(*) from table", one could write:

 import static com.hp.util.sql.SqlUtils.*;
 ...
 SelectClause c = select(leaf("count(*)"), "table"));
 

Parameters:

e - the expression of what should be selected

tableName - the table from which the data should be selected

Returns:

the select clause

See Also:

select(Expr)

where

public static WhereClause where(Expr e)

Convenience method to create a where clause. For example, to create a clause of the form "where columnA = 0", one could write:

 import static com.hp.util.sql.SqlUtils.*;
 ...
 WhereClause c = where(leaf("columnA = 0"));
 

or, a slightly more complex example to create "where (columnA = 0 and quantity > 5)"

 Expr a = leaf("columnA = 0");
 Expr b = leaf("quantity > 5");
 WhereClause c = where(and(a, b));
 

Parameters:

e - the expression for the where clause

Returns:

the where clause

groupByBuilder

public static GroupByClause.Builder groupByBuilder()

Convenience method to create a group-by clause builder.

Returns:

a group-by clause builder

groupBy

public static GroupByClause groupBy(String columnName)

Convenience method to create a group-by clause for a single column.

Parameters:

columnName - the column name

Returns:

a group-by clause

orderByBuilder

public static OrderByClause.Builder orderByBuilder()

Convenience method to create an order-by clause builder.

Returns:

an order-by clause builder

orderByAsc

public static OrderByClause orderByAsc(String columnName)

Convenience method to create an order-by clause (ascending) for a single column.

Parameters:

columnName - the column name

Returns:

an (ascending) order-by clause

orderByDesc

public static OrderByClause orderByDesc(String columnName)

Convenience method to create an order-by clause (descending) for a single column.

Parameters:

columnName - the column name

Returns:

a (descending) order-by clause

limit

public static LimitClause limit()

Convenience method to return a limit clause.

Returns:

the limit clause

sqlBuilder

public static SqlStatement.Builder sqlBuilder()

Convenience method to create an SQL statement builder.

Returns:

an SQL statement builder

sql

public static SqlStatement sql(SelectClause sc)

Convenience method to create an SQL statement from the given select clause.

Parameters:

sc - the select clause

Returns:

the SQL statement

sql

public static SqlStatement sql(SelectClause sc,
               WhereClause wc)

Convenience method to create an SQL statement from the given clauses.

Parameters:

sc - the select clause

wc - the where clause

Returns:

the SQL statement

sql

public static SqlStatement sql(SelectClause sc,
               WhereClause wc,
               GroupByClause gbc,
               OrderByClause obc)

Convenience method to create an SQL statement from the given clauses.

Parameters:

sc - the select clause

wc - the where clause

gbc - the group by clause

obc - the order by clause

Returns:

the SQL statement

sql

public static SqlStatement sql(SelectClause sc,
               WhereClause wc,
               OrderByClause obc,
               LimitClause l)

Convenience method to create an SQL statement from the given clauses.

Parameters:

sc - the select clause

wc - the where clause

obc - the order by clause

l - the limit clause

Returns:

the SQL statement