com.sssw.fw.util
Class EboSqlHelper

java.lang.Object
 |
 +--com.sssw.fw.util.EboSqlHelper

public class EboSqlHelper

extends Object

This class contains SQL-related utility methods that are used by objects generating and executing queries. See EbiQuery and its subinterfaces.

Field Summary

static int	JOIN_FULL SQL JOIN type value for FULL JOIN
static int	JOIN_INNER SQL JOIN type value for INNER JOIN
static int	JOIN_LEFT_OUTER SQL JOIN type value for LEFT OUTER JOIN
static int	JOIN_RIGHT_OUTER SQL JOIN type value for RIGHT OUTER JOIN
static int	OP_BETWEEN This operator corresponds to the SQL 'BETWEEN' operator, e.g., Customer.ID BETWEEN (1, 4)
static int	OP_IN This operator corresponds to the SQL 'IN' operator, e.g., Customer.ID IN (1, 2, 3, 4)
static int	OP_IS_NULL This operator corresponds to the SQL 'IS NULL' condition, e.g., Customer.FIELD1 IS NULL
static int	ROP_EQUAL Relational operator that corresponds to the '=' comparison condition in SQL.
static int	ROP_GEQ Relational operator that corresponds to the '>=' comparison condition in SQL.
static int	ROP_GREATER Relational operator that corresponds to the '>' comparison condition in SQL.
static int	ROP_LEQ Relational operator that corresponds to the '<=' comparison condition in SQL.
static int	ROP_LESS Relational operator that corresponds to the '<' comparison condition in SQL.
static int	SOP_ENDS_WITH String operator that corresponds to the SQL 'LIKE' operator where the pattern specifies items that end with a certain sequence of characters.
static int	SOP_EQUALS_IGNORE_CASE String operator that corresponds to the SQL '=' comparison condition executed on two string operands, where the case of the operands is ignored (e.g.
static int	SOP_LIKE String operator that corresponds to the SQL 'LIKE' operator (e.g.
static int	SOP_LIKE_IGNORE_CASE String operator that corresponds to the SQL 'LIKE' operator executed on two string operands, where the case of the operands is ignored (e.g.
static int	SOP_STARTS_WITH String operator that corresponds to the SQL 'LIKE' operator where the pattern specifies items that start with a certain sequence of characters.

Constructor Summary

EboSqlHelper()

Method Summary

static String	genBetween(String tableName, String colName, Object first, Object last, boolean not) Generate a SQL query expression using the SQL BETWEEN operator
static String	genExpression(String tableName, String colName, Object value, boolean rightAsIs, int op, boolean not) Generate a SQL query expression
static String	genIn(String tableName, String colName, Object[] ins, boolean not) Generate a SQL query expression using the SQL IN operator
static String	genOrderBy(String tableName, String colName, boolean asc) Return the ORDER BY entry for a SQL data element
static String	getAnsiJoinAsString(int joinType) Return the string value of an ANSI join type
static String	getDbmsName() Identify the DBMS for the default data source
static String	getDbmsName(String dataSource) Identify the DBMS for a specified data source
static int	getDriverMajorVersion() Return the major version number of the driver for the default data source
static int	getDriverMajorVersion(String dataSource) Return the major version number of the driver for a specified data source
static int	getDriverMinorVersion() Return the minor version number of the driver for the default data source
static int	getDriverMinorVersion(String dataSource) Return the minor version number of the driver for a specified data source
static String	getDriverName() Identify the jdbc driver for the default data source
static String	getDriverName(String dataSource) Identify the JDBC driver for a specified data source
static String	getOpAsString(int op, boolean not) Return the string value of a SQL query operator
static String	getOrderByClause(Collection orderBys) Return the ORDER BY clause for a SQL query
static boolean	isUnaryOp(int op) Indicate whether the specified operator is unary i.e., operator that works on one operand such as IS NULL
static boolean	isValidJoinType(int type) Indicate whether the specified join type is valid for use in SQL query methods
static boolean	isValidOp(int op) Indicate whether the specified operator is valid for use in SQL query methods
static boolean	isValidQueryExprObj(Object o) Indicate whether the specified object is valid for SQL query expressions
static String	parenthesize(String str) Return a parenthesized string
static String	parseDbmsName(String dbmsName) Parse the JDBC database product name and return a standard name
static String	parseDriverName(String driverName) Parse the JDBC driver name and return a standard name
static String	qualifyColName(String tableName, String colName) Return the fully qualified name of a SQL data element
static String	sqlEscape(String value, boolean javaEscape) Escape illegal characters in a literal so that it can be used in SQL statements.
static String	wrapString(String wrappee, String wrapper) Return a wrapped string

Methods inherited from class java.lang.Object

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

Field Detail

ROP_EQUAL

public static final int ROP_EQUAL

Relational operator that corresponds to the '=' comparison condition in SQL.

ROP_LESS

public static final int ROP_LESS

Relational operator that corresponds to the '<' comparison condition in SQL.

ROP_LEQ

public static final int ROP_LEQ

Relational operator that corresponds to the '<=' comparison condition in SQL.

ROP_GREATER

public static final int ROP_GREATER

Relational operator that corresponds to the '>' comparison condition in SQL.

ROP_GEQ

public static final int ROP_GEQ

Relational operator that corresponds to the '>=' comparison condition in SQL.

SOP_LIKE

public static final int SOP_LIKE

String operator that corresponds to the SQL 'LIKE' operator (e.g. name LIKE 'a%b_'). The right hand side operand (value) may use the SQL wildcard characters such as '%' and '_'.

SOP_LIKE_IGNORE_CASE

public static final int SOP_LIKE_IGNORE_CASE

String operator that corresponds to the SQL 'LIKE' operator executed on two string operands, where the case of the operands is ignored (e.g. (LCASE("Customer"."FirstName") LIKE LCASE('Susan')) ). The right hand side operand (value) may use the SQL wildcard characters such as '%' and '_'.

SOP_STARTS_WITH

public static final int SOP_STARTS_WITH

String operator that corresponds to the SQL 'LIKE' operator where the pattern specifies items that start with a certain sequence of characters. (e.g. name LIKE 'foo%'). The right hand side operand (value) may use the SQL wildcard characters such as '%' and '_'.

SOP_ENDS_WITH

public static final int SOP_ENDS_WITH

String operator that corresponds to the SQL 'LIKE' operator where the pattern specifies items that end with a certain sequence of characters. (e.g. name LIKE '%bar'). The right hand side operand (value) may use the SQL wildcard characters such as '%' and '_'.

SOP_EQUALS_IGNORE_CASE

public static final int SOP_EQUALS_IGNORE_CASE

String operator that corresponds to the SQL '=' comparison condition executed on two string operands, where the case of the operands is ignored (e.g. (LCASE("Customer"."FirstName") = LCASE('Susan')) )

OP_IN

public static final int OP_IN

This operator corresponds to the SQL 'IN' operator, e.g., Customer.ID IN (1, 2, 3, 4)

OP_BETWEEN

public static final int OP_BETWEEN

This operator corresponds to the SQL 'BETWEEN' operator, e.g., Customer.ID BETWEEN (1, 4)

OP_IS_NULL

public static final int OP_IS_NULL

This operator corresponds to the SQL 'IS NULL' condition, e.g., Customer.FIELD1 IS NULL

JOIN_INNER

public static final int JOIN_INNER

SQL JOIN type value for INNER JOIN

JOIN_LEFT_OUTER

public static final int JOIN_LEFT_OUTER

SQL JOIN type value for LEFT OUTER JOIN

JOIN_RIGHT_OUTER

public static final int JOIN_RIGHT_OUTER

SQL JOIN type value for RIGHT OUTER JOIN

JOIN_FULL

public static final int JOIN_FULL

SQL JOIN type value for FULL JOIN

Constructor Detail

EboSqlHelper

public EboSqlHelper()

Method Detail

isValidOp

public static boolean isValidOp(int op)

Indicate whether the specified operator is valid for use in SQL query methods

Parameters:

op - the operator identifier

Returns:

true if valid, false otherwise

isValidJoinType

public static boolean isValidJoinType(int type)

Indicate whether the specified join type is valid for use in SQL query methods

Parameters:

type - the join type identifier

Returns:

true if valid, false otherwise

isValidQueryExprObj

public static boolean isValidQueryExprObj(Object o)

Indicate whether the specified object is valid for SQL query expressions

Parameters:

obj - the expression value

Returns:

true if valid, false otherwise

wrapString

public static String wrapString(String wrappee,
                                String wrapper)

Return a wrapped string

Parameters:

wrappee - a string to be wrapped (e.g., "value")

wrapper - the wrapping value (e.g., "'");

Returns:

the wrapped value (e.g., "'value'")

parenthesize

public static String parenthesize(String str)

Return a parenthesized string

Parameters:

str - a string to be parenthesized (e.g., "col = value")

Returns:

the parenthesized value (e.g., "(col = value)")

getOpAsString

public static String getOpAsString(int op,
                                   boolean not)

Return the string value of a SQL query operator

Parameters:

int - an operator value (e.g., EboSqlHelper.ROP_EQUAL)

not - specifies whether the value should be negated

Returns:

the operator as a string (e.g., "=" or "<>")

getAnsiJoinAsString

public static String getAnsiJoinAsString(int joinType)

Return the string value of an ANSI join type

Parameters:

int - a join type (e.g., EboSqlHelper.JOIN_INNER)

Returns:

the type as a string (e.g., "INNER JOIN")

qualifyColName

public static String qualifyColName(String tableName,
                                    String colName)

Return the fully qualified name of a SQL data element

Parameters:

tableName - a table name (e.g., CMDOCUMENTS)

colName - a column name (e.g., DOCNAME)

Returns:

the qualified name (e.g., "CMDOCUMENTS.DOCNAME")

genExpression

public static String genExpression(String tableName,
                                   String colName,
                                   Object value,
                                   boolean rightAsIs,
                                   int op,
                                   boolean not)

Generate a SQL query expression

Parameters:

tableName - the table name

colName - the column name

value - the value to be incorporated in the expression

op - the query expression operator (must satisfy isValidOp(op))

rightAsIs - specifies whether the value should be adjusted in any way (e.g., Strings are wrapped in single quotes). This should normally be set to false.

not - specifies whether the entire expression should be negated

Returns:

the expression

genIn

public static String genIn(String tableName,
                           String colName,
                           Object[] ins,
                           boolean not)

Generate a SQL query expression using the SQL IN operator

Parameters:

tableName - the table name

colName - the column name

ins - the set of values to be included

not - specifies whether the entire expression should be negated

Returns:

the expression

genBetween

public static String genBetween(String tableName,
                                String colName,
                                Object first,
                                Object last,
                                boolean not)

Generate a SQL query expression using the SQL BETWEEN operator

Parameters:

tableName - the table name

colName - the column name

first - the lowest value to be included

last - the highest value to be included

not - specifies whether the entire expression should be negated

Returns:

the expression

genOrderBy

public static String genOrderBy(String tableName,
                                String colName,
                                boolean asc)

Return the ORDER BY entry for a SQL data element

Parameters:

tableName - a table name (e.g., CMDOCUMENTS)

colName - a column name (e.g., DOCNAME)

asc - specifies whether the sequence is ascending

Returns:

the ORDER BY entry (e.g., "CMDOCUMENTS.DOCNAME ASC")

getOrderByClause

public static String getOrderByClause(Collection orderBys)

Return the ORDER BY clause for a SQL query

Parameters:

orderbys - a collection of ORDER BY entries (e.g., "CMDOCUMENTS.DOCNAME ASC")

Returns:

the ORDER BY clause (e.g., "ORDER BY CMDOCUMENTS.DOCNAME ASC, CMDOCUMENTS.AUTHOR ASC")

sqlEscape

public static String sqlEscape(String value,
                               boolean javaEscape)

Escape illegal characters in a literal so that it can be used in SQL statements.

Parameters:

value - the literal

javaEscape - if true, escape by the rules applicable for Java parsers, otherwise by the rules applicable for SQL parsers

Returns:

escaped string value of the literal

isUnaryOp

public static boolean isUnaryOp(int op)

Indicate whether the specified operator is unary i.e., operator that works on one operand such as IS NULL

Parameters:

op - the operator identifier

Returns:

true if unary operator, false otherwise

getDbmsName

public static String getDbmsName()

Identify the DBMS for the default data source

Returns:

the dbms name; null if it can't be resolved

getDbmsName

public static String getDbmsName(String dataSource)

Identify the DBMS for a specified data source

Parameters:

dataSource - the data source name

Returns:

the dbms name; null if it can't be resolved

parseDbmsName

public static String parseDbmsName(String dbmsName)

Parse the JDBC database product name and return a standard name

Parameters:

dbmsName - the database product name reported by JDBC DatabaseMetaData

Returns:

a parsed name matching a EboSqlHelper database name constant

getDriverName

public static String getDriverName()

Identify the jdbc driver for the default data source

Returns:

the driver name; null if it can't be resolved

getDriverName

public static String getDriverName(String dataSource)

Identify the JDBC driver for a specified data source

Parameters:

dataSource - the data source name

Returns:

the driver name; null if it can't be resolved

parseDriverName

public static String parseDriverName(String driverName)

Parse the JDBC driver name and return a standard name

Parameters:

driverName - the driver name reported by JDBC DatabaseMetaData

Returns:

a parsed name matching a EboSqlHelper driver name constant

getDriverMajorVersion

public static int getDriverMajorVersion()

Return the major version number of the driver for the default data source

Returns:

the major version number of the driver; -1 if it can't be resolved

getDriverMajorVersion

public static int getDriverMajorVersion(String dataSource)

Return the major version number of the driver for a specified data source

Returns:

the major version number of the driver; -1 if it can't be resolved

getDriverMinorVersion

public static int getDriverMinorVersion()

Return the minor version number of the driver for the default data source

Returns:

the minor version number of the driver; -1 if it can't be resolved

getDriverMinorVersion

public static int getDriverMinorVersion(String dataSource)

Return the minor version number of the driver for a specified data source

Returns:

the minor version number of the driver; -1 if it can't be resolved