org.apache.oozie.util.db

Class SqlStatement

java.lang.Object

org.apache.oozie.util.db.SqlStatement

Direct Known Subclasses:

SqlStatement.Delete, SqlStatement.Insert, SqlStatement.Select, SqlStatement.Update

public abstract class SqlStatement
extends Object

The SqlStatement is used to generate SQL Statements. Prepare the generated Statements and also to parse the resultSets

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	SqlStatement.Condition Construct the condition statement that will be used in the where clause
static class	SqlStatement.Delete SQL Statement generator for DELETE Statements
static class	SqlStatement.Insert INSERT Statement generator
static class	SqlStatement.ResultSetReader ResultSetReader is used to parse the result set and gives methods for getting appropriate type of data given the column name
static class	SqlStatement.Select Select Statement generator.
static class	SqlStatement.Update UPDATE SQL Statement generator

Field Summary

Fields

Modifier and Type	Field and Description
protected boolean	forUpdate
protected StringBuilder	sb
protected List<Object>	values

Constructor Summary

Constructors

Constructor and Description
SqlStatement()

Method Summary

Modifier and Type	Method and Description
static SqlStatement.Condition	and(SqlStatement.Condition... conds) Logical AND condition Generator
static SqlStatement.Condition	between(Schema.Column column, Object lVal, Object rVal) BETWEEN range checking statement
static SqlStatement.Delete	deleteFrom(Schema.Table table) Return a new Delete Statement
static SqlStatement.Select	getCount(Schema.Table... tables) Select count(*) Statement generator.
SqlStatement	getNewStatementWithValues(Map<Object,Object> newValues) Replace the keys(newValues) which are place holders in the sql statements with the corresponding new values.
static SqlStatement.Condition	greaterThan(Schema.Column column, Object value) Generate Condition statement for GREATER THAN condition checking
static SqlStatement.Condition	greaterThanOrEqual(Schema.Column column, Object value) Generate Condition statement for GREATER THAN OR EQUAL condition checking
static SqlStatement.Condition	in(Schema.Column column, Object... values) IN Condition for checking multiple values
static SqlStatement.Condition	in(Schema.Column column, SqlStatement.Select select) Sub query with IN condition
static SqlStatement.Insert	insertInto(Schema.Table table) Return a new Insert Statement
static SqlStatement.Condition	isEqual(Schema.Column column, Object value) Generate Condition statement for equality check
static SqlStatement.Condition	isLike(Schema.Column column, String value) Generate condition statement for IS LIKE
static SqlStatement.Condition	isNotEqual(Schema.Column column, Object value) Generate InEquality Condition statement
static SqlStatement.Condition	isNotLike(Schema.Column column, String value) Generates condition statement for IS NOT LIKE
static SqlStatement.Condition	isNotNull(Schema.Column column) Generate condition statement for IS NOT NULL
static SqlStatement.Condition	isNull(Schema.Column column) Generate condition statement for IS NULL
static SqlStatement.Condition	lessThan(Schema.Column column, Object value) Generate Condition statement for LESS THAN condition checking
static SqlStatement.Condition	lessThanOrEqual(Schema.Column column, Object value) Generate Condition statement for LESS THAN OR EQUAL condition checking
static SqlStatement.Condition	notBetween(Schema.Column column, Object lVal, Object rVal) NOT BETWEEN range checking statement
static SqlStatement.Condition	notIn(Schema.Column column, Object... values) NOT IN Condition for checking multiple values
static SqlStatement.Condition	notIn(Schema.Column column, SqlStatement.Select select) Sub query with NOT IN condition
static SqlStatement.Condition	or(SqlStatement.Condition... conds) Logical OR condition generator
static SqlStatement.ResultSetReader	parse(ResultSet rSet) Create the ResultSetReader object that has the methods to access the data from the result set
PreparedStatement	prepare(Connection conn) Prepare the SQL Statement.
PreparedStatement	prepare(PreparedStatement pStmt) Assign the values to Prepared Statement.
PreparedStatement	prepareAndSetValues(Connection conn) Prepare the SQL Statement that is generated and assign the values to prepared statement.
PreparedStatement	prepareForBatch(Connection conn, List<? extends Map<Object,Object>> values, PreparedStatement pStmt) Preparing Multiple statements for batch execution.
static SqlStatement.Select	selectAllFrom(Schema.Table... tables) Return a Select All Statement
static SqlStatement.Select	selectColumns(Schema.Column... columns) Return a Select Statement
SqlStatement	setValue(Object oldVal, Object newVal) Replace the place holders with actual values in the sql statement
static SqlStatement.Update	update(Schema.Table table) Return a new Update Statement

Methods inherited from class java.lang.Object

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

Field Detail

forUpdate

protected boolean forUpdate

sb

protected StringBuilder sb

values

protected List<Object> values

Constructor Detail

SqlStatement

public SqlStatement()

Method Detail

isNull

public static SqlStatement.Condition isNull(Schema.Column column)

Generate condition statement for IS NULL

Parameters:

column - column name

Returns:

IS NULL condition statement

isNotNull

public static SqlStatement.Condition isNotNull(Schema.Column column)

Generate condition statement for IS NOT NULL

Parameters:

column - column name

Returns:

IS NOT NULL condition statement

isLike

public static SqlStatement.Condition isLike(Schema.Column column,
                                            String value)

Generate condition statement for IS LIKE

Parameters:

column - column name

value - value to be checked

Returns:

IS LIKE condition statement

isNotLike

public static SqlStatement.Condition isNotLike(Schema.Column column,
                                               String value)

Generates condition statement for IS NOT LIKE

Parameters:

column - column name

value - value to be checked

Returns:

IS NOT LIKE condition statement

isEqual

public static SqlStatement.Condition isEqual(Schema.Column column,
                                             Object value)

Generate Condition statement for equality check

Parameters:

column - the column

value - the value

Returns:

Equality Condition statement

isNotEqual

public static SqlStatement.Condition isNotEqual(Schema.Column column,
                                                Object value)

Generate InEquality Condition statement

Parameters:

column - the column

value - the value

Returns:

Inequality Condition statement

lessThan

public static SqlStatement.Condition lessThan(Schema.Column column,
                                              Object value)

Generate Condition statement for LESS THAN condition checking

Parameters:

column - the column

value - the value

Returns:

less than condition statement

greaterThan

public static SqlStatement.Condition greaterThan(Schema.Column column,
                                                 Object value)

Generate Condition statement for GREATER THAN condition checking

Parameters:

column - the column

value - the value

Returns:

greater than condition statement

lessThanOrEqual

public static SqlStatement.Condition lessThanOrEqual(Schema.Column column,
                                                     Object value)

Generate Condition statement for LESS THAN OR EQUAL condition checking

Parameters:

column - the column

value - the value

Returns:

less than or equal condition statement

greaterThanOrEqual

public static SqlStatement.Condition greaterThanOrEqual(Schema.Column column,
                                                        Object value)

Generate Condition statement for GREATER THAN OR EQUAL condition checking

Parameters:

column - the column

value - the value

Returns:

greater than or equal condition statement

in

public static SqlStatement.Condition in(Schema.Column column,
                                        Object... values)

IN Condition for checking multiple values

Parameters:

column - the column

values - the values

Returns:

In condition statement

notIn

public static SqlStatement.Condition notIn(Schema.Column column,
                                           Object... values)

NOT IN Condition for checking multiple values

Parameters:

column - the column

values - the values

Returns:

not in condition statement

in

public static SqlStatement.Condition in(Schema.Column column,
                                        SqlStatement.Select select)

Sub query with IN condition

Parameters:

column - the column

select - the sub query

Returns:

Sub query using in

notIn

public static SqlStatement.Condition notIn(Schema.Column column,
                                           SqlStatement.Select select)

Sub query with NOT IN condition

Parameters:

column - the column

select - the sub query

Returns:

sub query using not in

between

public static SqlStatement.Condition between(Schema.Column column,
                                             Object lVal,
                                             Object rVal)

BETWEEN range checking statement

Parameters:

column - the column

lVal - min value for range checking

rVal - max value for range checking

Returns:

between condition statement

notBetween

public static SqlStatement.Condition notBetween(Schema.Column column,
                                                Object lVal,
                                                Object rVal)

NOT BETWEEN range checking statement

Parameters:

column - the column

lVal - min value for range checking

rVal - max value for range checking

Returns:

not between condition statement

and

public static SqlStatement.Condition and(SqlStatement.Condition... conds)

Logical AND condition Generator

Parameters:

conds - list of conditions for AND

Returns:

AND statement

or

public static SqlStatement.Condition or(SqlStatement.Condition... conds)

Logical OR condition generator

Parameters:

conds - list of conditions for OR

Returns:

OR statement

prepareAndSetValues

public PreparedStatement prepareAndSetValues(Connection conn)
                                      throws SQLException

Prepare the SQL Statement that is generated and assign the values to prepared statement. setValues should be called to set the Real Values for place holders

Parameters:

conn - Connection

Returns:

Prepared SQL Statement

Throws:

SQLException - in case of error

prepare

public PreparedStatement prepare(PreparedStatement pStmt)
                          throws SQLException

Assign the values to Prepared Statement. setValues should be called to set the Real Values for place holders

Parameters:

pStmt - Prepared Statement

Returns:

PreparedStatement with values set

Throws:

SQLException - in case of error

prepare

public PreparedStatement prepare(Connection conn)
                          throws SQLException

Prepare the SQL Statement. Doesn't set the values.

Parameters:

conn - Connection

Returns:

PreparedStatement

Throws:

SQLException - in case of error

prepareForBatch

public PreparedStatement prepareForBatch(Connection conn,
                                         List<? extends Map<Object,Object>> values,
                                         PreparedStatement pStmt)
                                  throws SQLException

Preparing Multiple statements for batch execution.

Parameters:

conn - Connection

values - A list of maps that contains the actual values

Returns:

Prepared Statement

Throws:

SQLException - in case of error

setValue

public SqlStatement setValue(Object oldVal,
                             Object newVal)

Replace the place holders with actual values in the sql statement

Parameters:

oldVal - Place holder

newVal - Actual Value

Returns:

SQL Statement with oldVal(place holder) replaced with newVal(actual value)

getNewStatementWithValues

public SqlStatement getNewStatementWithValues(Map<Object,Object> newValues)

Replace the keys(newValues) which are place holders in the sql statements with the corresponding new values. And Gives back a new SQL Statement so that the actual statement can be re-used

Parameters:

newValues - the new values

Returns:

A New SQL Statement object with actual values set in its member

parse

public static SqlStatement.ResultSetReader parse(ResultSet rSet)

Create the ResultSetReader object that has the methods to access the data from the result set

Parameters:

rSet - Result Set

Returns:

ResultSet Reader

insertInto

public static SqlStatement.Insert insertInto(Schema.Table table)

Return a new Insert Statement

Parameters:

table - the target table

Returns:

Insert statement

update

public static SqlStatement.Update update(Schema.Table table)

Return a new Update Statement

Parameters:

table - the target table

Returns:

Update statement

deleteFrom

public static SqlStatement.Delete deleteFrom(Schema.Table table)

Return a new Delete Statement

Parameters:

table - the target table

Returns:

Delete Statement

selectAllFrom

public static SqlStatement.Select selectAllFrom(Schema.Table... tables)

Return a Select All Statement

Parameters:

tables - the target tables

Returns:

Select * statement

selectColumns

public static SqlStatement.Select selectColumns(Schema.Column... columns)

Return a Select Statement

Parameters:

columns - columns to select

Returns:

select statement

getCount

public static SqlStatement.Select getCount(Schema.Table... tables)

Select count(*) Statement generator.

Parameters:

tables - the target table

Returns:

"select count(*) from tables" statement