public final class SqlUtils
extends Object
Modifier and Type | Field and Description |
---|---|
static String |
CHAR_SEPARATOR_PROP
The CHAR SEPARATOR property.
|
static String |
DATE_FORMAT_PROP
The DATE_FORMAT property.
|
static String |
DATE_TIME_FORMAT_PROP
The DATE_TIME_FORMAT property.
|
static String |
DECIMAL_AS_INT_PROP
The DECIMAL_AS_INT property.
|
static String |
DEFAULT_DELIMITER
The DEFAULT_DELIMITER.
|
static String |
ENCODED_FIELD_NAME_PREFIX
The Constant ENCODED_FIELD_NAME_PREFIX.
|
static simplenlg.lexicon.Lexicon |
LEXICON |
static int |
LONGNVARCHAR
The LONGNVARCHAR type.
|
static int |
NCHAR
The NCHAR type.
|
static int |
NCLOB
The NCLOB type.
|
static simplenlg.framework.NLGFactory |
NLG_FACTORY |
static int |
NVARCHAR
The NVARCHAR type.
|
static String |
PARSE_DATES_PROP
PARSE DATES property.
|
static simplenlg.realiser.english.Realiser |
REALIZER |
static int |
ROWID
The ROWID type.
|
static int |
SQLXML
The SQLXML type.
|
static String |
TIME_FORMAT_PROP
The TIME_FORMAT property.
|
static String |
TRIM_PROP
TRIM VALUES property.
|
static Map<Integer,String> |
TYPES
The TYPES.
|
static int |
UNDEFINED
The Constant UNDEFINED.
|
Constructor and Description |
---|
SqlUtils() |
Modifier and Type | Method and Description |
---|---|
static boolean |
addBindVar(String[] usingVars,
String name,
Object value,
Map<String,Object> bindVars)
Adds the bind variable to the
bindVars using given name,
value and array of names usingVars . |
static boolean |
alreadyFormatedType(String typeName)
Checks if typeName is already formatted type.
|
static Object |
array2Object(Array value)
Convert java.sql.Array to the object which is recognizable by framework,
for example ResultSet.
|
static String |
baseName2Name(String baseName)
Converts owner.name to name.
|
static void |
cleanUpSQLData(Object caller,
Statement... statements)
Close allocated resources for the passed SQL Statement and ResultSet
objects.
|
static void |
cleanUpSQLData(Statement statement,
ResultSet rs,
Object caller)
Close allocated resources for the passed SQL Statement and ResultSet
objects.
|
static String |
convertDataType(FieldDef source,
FieldDef dest)
Converts source data type into destination data type using given source
and dest field definitions.
|
static boolean |
dataTypeHasSize(int fType)
Checks if data type has size.
|
static String |
date2Str(Object fieldValue,
Map<String,String> params)
Converts date to string using either default DATE FORMAT or format
defined in the
params . |
static String |
dateTime2Str(Object fieldValue,
Map<String,String> params)
Converts date+time to string using either default DATE_TIME FORMAT or
format defined in the
params . |
static String |
decimal2Str(Object value,
Map<String,String> params)
Converts decimal to string.
|
static String |
decodeFieldNameWithBase64(String encoded)
Decode field name with base 64.
|
static String |
encodeFieldNameWithBase64(String name)
Encode field name with base64.
|
static void |
executeScript(Connection con,
String script,
boolean commit,
boolean isCallable,
Object... bindVariables)
Execute SQL script.
|
static void |
executeScript(Connection con,
String script,
boolean commit,
Object... bindVariables)
Execute SQL script.
|
static void |
executeScript(Connection con,
String script,
Driver driver,
DataSet dataSet,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static void |
executeScript(Connection con,
String script,
Driver driver,
DataSet dataSet,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit,
boolean isCallable)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static void |
executeScript(Connection con,
String script,
Driver driver,
DataSet dataSet,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit,
boolean isCallable,
boolean supportsPrepared)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static void |
executeScript(Connection con,
String script,
Object... bindVariables)
Execute SQL script.
|
static TypedKeyValue<ResultSet,Statement> |
executeScriptAndReturnResultSet(Connection con,
String script,
Driver driver,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static TypedKeyValue<ResultSet,Statement> |
executeScriptAndReturnResultSetAndSaveException(Connection con,
String script,
Driver driver,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit,
InfoLogger infoLogger)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static void |
executeScriptAndSaveException(Connection con,
String script,
Driver driver,
DataSet dataSet,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit,
boolean isCallable,
boolean supportsPrepared,
InfoLogger infoLogger)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static void |
executeScriptAndSaveException(Connection con,
String script,
Driver driver,
DataSet dataSet,
OnException onException,
Map<String,Object> bindVariables,
String using,
boolean commit,
boolean isCallable,
InfoLogger infoLogger)
Executes SQL script for all rows of the data set if its not empty or just
once if it is.
|
static int |
executeSql(Connection con,
String sql,
boolean isCallable,
Object... bindVars)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static int |
executeSql(Connection con,
String sql,
int action,
String exception,
Driver driver,
boolean isCallable,
Object... bindVars)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static int |
executeSql(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static int |
executeSql(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver,
boolean isCallable)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static int |
executeSql(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver,
boolean isCallable,
boolean supportsPrepared)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static void |
executeSql(Connection con,
String sql,
LinkedHashMap<String,Object> params,
Driver driver)
Prepares sql, set bind variables using given
params and
finally executes sql. |
static int |
executeSql(Connection con,
String sql,
Object... bindVars)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static TypedKeyValue<Integer,TypedKeyValue<ResultSet,Statement>> |
executeSqlAndReturnResultSet(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static TypedKeyValue<Integer,TypedKeyValue<ResultSet,Statement>> |
executeSqlAndReturnResultSet(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver,
boolean supportsPrepared)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static TypedKeyValue<Integer,TypedKeyValue<ResultSet,Statement>> |
executeSqlAndReturnResultSetAndSaveException(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver,
boolean supportsPrepared,
InfoLogger infoLogger)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static int |
executeSqlAndSaveException(Connection con,
String sql,
int action,
String exception,
Map<String,Object> bindVars,
Driver driver,
boolean isCallable,
boolean supportsPrepared,
InfoLogger infoLogger)
Prepares sql, set bind variables using given
bindVars and
finally executes sql. |
static boolean |
exists(Connection con,
String table,
Driver driver,
List<TypedKeyValue<String,Object>> fieldAndValues,
boolean useBindVariables)
Checks if record exists.
|
static boolean |
exists(Connection con,
String table,
Driver driver,
TypedKeyValue<List<String>,List<Object>> fieldsAndValues,
String fields,
boolean useBindVariables)
Checks if record exists for the given dataset and list of fields.
|
static boolean |
fieldHasParams(int fieldType)
Checks if field must have parameters.
|
static boolean |
fieldNameNeedQuotes(String fieldName,
char quote,
Driver driver)
Checks if field name need quotes.
|
static String |
filter(Driver driver,
String value,
int fieldType)
Executes
Driver.filter(String) if field is a flavor of char or
clob. |
static String |
fullName2BaseName(String fullName)
Converts full name to base name.
|
static String |
fullName2Name(String fullName)
Converts full name to name.
|
static String |
fullName2NodeName(String fullName)
Converts full name to node name.
|
static String |
fullName2OwnerName(String fullName)
Converts full name to owner name.
|
static String |
getAfterFromFromSelect(String sql)
Gets part of the sql statement after "from" clause.
|
static String |
getAfterWhereFromSelect(String sql)
Gets part of the sql statement after "where" clause.
|
static String |
getAlterTableAddColumns(String tableName,
DataSet dataSet,
List<String> missingColumns,
Driver driver)
Gets the SQL for "alter table add column".
|
static String |
getAlterTableAddColumns(String tableName,
DataSet dataSet,
List<String> missingColumns,
Driver driver,
boolean withNotNull)
Gets the SQL for "alter table add column".
|
static String |
getBindParameterName(String fieldName,
Driver driver)
Gets the bind parameter name from field name.
|
static Object |
getBlob(ResultSet rs,
int pos)
Reads the blob field from the result set.
|
static InputStream |
getBlobInputStreamFromResultSet(ResultSet rs,
int columnNum)
Gets the blob as an input stream from the result set.
|
static OutputStream |
getBlobOutputStream(Blob blob)
Getter an output stream to write data into the passed Blob.
|
static OutputStream |
getBlobOutputStreamFromResultSet(ResultSet rs,
int columnNum)
Gets the blob output stream from the result set.
|
static Object |
getClob(ResultSet rs,
int pos)
Reads the clob from the result set.
|
static String |
getColName(Map<String,Integer> cols,
String colName)
Calculates column name using given name.
|
static String |
getConditions(List<TypedKeyValue<String,Object>> fieldAndValues,
Driver driver,
boolean useBindVariables)
Gets the where clause conditions for the list of fields.
|
static String |
getFieldAsText(Driver driver,
String name,
String fType,
Object size,
Object decimal,
Object nullable,
int javaType)
Gets the field definition.
|
static String[] |
getFieldNamesWithSpaces(String[] fields,
char quote,
Driver driver)
Gets the field names with spaces.
|
static String |
getFieldNameWithoutQuotes(String fieldName,
char quote)
Gets the field name without quotes.
|
static String |
getFieldNameWithSpaces(String fieldName,
char quote,
Driver driver)
Gets the field name with spaces.
|
static String |
getFieldNameWithSpacesForced(String fieldName,
char quote,
Driver driver)
Gets the field name with spaces forced.
|
static String |
getFieldsFromSelect(String sql)
Gets the fields from "select" sql statement.
|
static String |
getFieldsSql(DataSet dataSet,
Driver driver,
String key,
FieldsRepository fieldsRepository)
Gets the fields definitions for all fields in the data set.
|
static String |
getFieldsSql(DataSet dataSet,
Driver driver,
String key,
FieldsRepository fieldsRepository,
boolean withNotNull)
Gets the fields definitions for all fields in the data set.
|
static int |
getFieldType(int newType,
int existingType,
boolean hasNotNullValue)
Gets the field type.
|
static String |
getFullNativeType(FieldDef fieldDef)
Gets the native data type.
|
static String |
getIdFromPattern(String value,
String delim,
String idFieldName)
Gets the id from pattern.
|
static String |
getInsertSelectSql(DataSet dataSet,
Driver driver,
String from,
String to,
boolean withTruncate) |
static String |
getJustTypeName(String typeName)
Gets the type name without scale and precision.
|
static String |
getNameForWildcard(String wildcard,
String name,
Driver fromDriver,
Driver toDriver)
Gets the table name for wildcard.
|
static String |
getNameWithoutOwnersAndWithOptionalQuotes(String name,
Driver fromDriver,
Driver toDriver)
Gets the name without quotes and owners.
|
static String |
getNativeTypeByJdbcType(int type)
Gets the native type by jdbc type.
|
static TypeAndValue |
getNumberTypeAndValue(String text)
Gets the type (key) and value (value) from the given text.
|
static Object |
getObject(ResultSet rs,
int index,
FieldDef fieldDef,
Driver driver)
Gets the object from result set.
|
static PreparedStatement |
getPreparedStatementAndSetBindVars(Connection con,
String sql,
List<TypedKeyValue<String,Object>> fieldAndValues,
Driver driver,
boolean useBindVariables)
Gets the prepared statement and set bind variables.
|
static int[] |
getReplaceableTypes(int type)
Gets the array of types which can replace given type.
|
static Savepoint |
getSavepoint(Connection con)
Sets the savepoint.
|
static TypedKeyValue<Integer,Integer> |
getScaleAndPrecision(String nativeType)
Gets the scale (value) and precision (key).
|
static String |
getSelectClause(Driver driver)
Gets the select clause for metadata extraction.
|
static String |
getSelectSql(String sql)
Gets the select sql.
|
static String |
getSqlForExplainPlan(String sql,
Driver driver,
SqlParser sqlParser)
Gets the sql for explain plan.
|
static String |
getSqlForMetadataExtraction(String objectName,
Driver driver)
Gets the sql for metadata extraction.
|
static String |
getSqlWithWhereClause(String sql,
String condition)
Gets the sql with where clause.
|
static String |
getTableNameWithSpaces(String tableName,
char quote,
Driver driver)
Gets the table name with spaces.
|
static String |
getTableNameWithSuffix(String tableName,
String suffix,
Driver driver)
Gets the table name with suffix.
|
static TypeAndValue |
getTypeAndValue(String text,
Map<String,String> params,
boolean allStrings)
Gets the type (key) and value (value.
|
static Integer |
getTypeByName(String name)
Gets the sql type
Types by type name. |
static String |
getTypeName(Object fieldType)
Gets the type name using given
Types . |
static String |
getTypeRange(String typeName,
int maxPrec,
int maxScale)
Gets the type range.
|
static String |
getTypeRange(String typeName,
int maxPrec,
int maxScale,
boolean scaleLessOrEqualPrec)
Gets the type range.
|
static String |
getTypeRange(String typeName,
int maxPrec,
int maxScale,
boolean scaleLessOrEqualPrec,
boolean forcePrecAndScale)
Gets the type range.
|
static String |
getTypeRange(String typeName,
int maxPrec,
int maxScale,
boolean scaleLessOrEqualPrec,
boolean forcePrecAndScale,
int precMultiplier)
Gets the type range.
|
static String |
getUnquotedText(Map<String,String> params,
String text)
Gets the unquoted text.
|
static String |
getUrlFromWildCard(String url,
String name)
Gets the url from the wild card.
|
static String |
getValidSqlName(String value,
String replacement,
boolean withSpaces,
String... additionalChars)
Gets the valid sql column or table name.
|
static String |
getValidSqlName(String value,
String replacement,
String... additionalChars)
Gets the valid sql column or table name.
|
static String |
getValue(String value,
int index,
String delim)
Gets the value.
|
static int |
getVarType(String bindVar)
Gets the bind variable type.
|
static int |
getVarTypeByObjType(Object bindVar)
Gets the variable type by object type.
|
static String |
getWhereClause(Driver driver)
Gets the where clause for metadata extraction.
|
static boolean |
isArray(int fType)
Checks if fType is aeeay.
|
static boolean |
isBinaryTimestamp(Object fieldValue,
FieldDef fieldDef)
Checks if field is binary timestamp.
|
static boolean |
isBit(Object value)
Checks if is bit.
|
static boolean |
isBlob(int fType)
Checks if fType is blob.
|
static boolean |
isBoolean(int fType)
Checks if fType is boolean.
|
static boolean |
isCallStatement(String sql)
Checks if sql statement is in fact "select" statement.
|
static boolean |
isChar(int fType)
Checks if fType is char or varchar.
|
static boolean |
isCharActual(int fType)
Checks if fType is char.
|
static boolean |
isClob(int fType)
Checks if fType is clob.
|
static boolean |
isCompatible(int original,
int compareTo)
Checks if types are compatible.
|
static boolean |
isDate(int fType)
Checks if fType is date.
|
static boolean |
isDateOnly(int fType)
Checks if fType is date only.
|
static boolean |
isDateTime(int fType)
Checks if fType is date+time.
|
static boolean |
isDecimal(int fType)
Checks if fType is decimal.
|
static boolean |
isLargeObject(int fType)
Checks if fType is large object.
|
static boolean |
isNumber(int fType)
Checks if fType is number.
|
static boolean |
isOther(int fType)
Checks if fType is other.
|
static boolean |
isSelectStatement(String sql)
Checks if sql statement is in fact "select" statement.
|
static boolean |
isStruct(int fType)
Checks if fType is struct.
|
static boolean |
isTableName(String sql)
Checks if given sql is a table name.
|
static boolean |
isTime(int fType)
Checks if fType is time.
|
static boolean |
isTimestamp(int fType)
Checks if fType is timestamp.
|
static boolean |
isValidPartOfSqlName(String name) |
static boolean |
isVarChar(int fType)
Checks if type is varchar.
|
static boolean |
isXml(int fType)
Checks if data type is xml.
|
static String |
name2RightCase(Driver driver,
String name)
Converts name to the right case.
|
static String |
nameToSqlName(String name)
Name to sql name.
|
static String[] |
parseSql(String sql)
Splits given sql on multiple sql statements using ";" as a separator.
|
static void |
populateDataSet(DataSet dataSet,
Driver driver,
ResultSet rs,
ListHashMap<String,String> fieldsMapping,
boolean unique,
boolean checkKeyField)
Populates data set from the result set.
|
static void |
populateDataSet(DataSet dataSet,
Driver driver,
ResultSet rs,
ListHashMap<String,String> fieldsMapping,
boolean unique,
boolean checkKeyField,
String filterByField,
String filterByFieldValue)
Populates data set from the result set.
|
static void |
populateDataSet(DataSet dataSet,
Driver driver,
ResultSet rs,
ListHashMap<String,String> fieldsMapping,
boolean unique,
boolean checkKeyField,
String filterByField,
String filterByFieldValue,
int maxRows,
boolean ignoreGetObjectErrors)
Populates data set from the result set.
|
static String |
prepareParams(ResultSet rs,
String params,
String field)
Reads value for the
field from the result set
rs and adds it to the params . |
static String |
prepareParams(String params,
Object param)
Prepare parameters.
|
static String |
readStringFromReader(Reader r)
Creates string from reader.
|
static void |
registerOutputParams(CallableStatement cst,
String output,
Driver driver,
int start)
Register output params.
|
static String |
removeQuotes(String name)
Removes the quotes.
|
static String |
removeQuotes(String name,
Driver driver)
Removes the quotes.
|
static boolean |
requiresTranslation(int fType)
Checks if value of the field of the type
fType requires
translation to display. |
static void |
setBindVar(PreparedStatement statement,
Object bindVar,
int index)
Sets the bind variable.
|
static void |
setBindVar(PreparedStatement statement,
Object bindVar,
int type,
int index)
Sets the bind variable.
|
static void |
setBindVar(PreparedStatement statement,
String bindVar,
int index)
Sets the bind variable.
|
static void |
setBindVar(PreparedStatement statement,
String bindVar,
int type,
int index)
Sets the bind variable.
|
static void |
setBindVarAndRegisterOutputParams(PreparedStatement statement,
Object bindVar,
int index,
Driver driver)
Sets the bind var and register output parameters.
|
static void |
setBindVars(PreparedStatement statement,
List<TypedKeyValue<String,Object>> fieldAndValues,
Driver driver)
Sets the bind variables.
|
static void |
setBlob(PreparedStatement pstmt,
Object value,
int pos)
Sets the blob.
|
static void |
setClob(PreparedStatement pstmt,
Object value,
int pos)
Sets the clob.
|
static void |
setNull(PreparedStatement statement,
int type,
int index)
Sets the null.
|
static Object |
storageValue2Value(int fieldType,
Object value,
Map<String,String> params)
Converts field value.
|
static Date |
str2Date(String text,
Map<String,String> params)
Converts text to date using value for the DATE_FORMAT_PROP property as a
format string.
|
static Date |
str2DateTime(String text,
Map<String,String> params)
Converts text to date+time using value for the DATE_TIME_FORMAT_PROP
property as a format string.
|
static Date |
str2Time(String text,
Map<String,String> params)
Converts text to time using value for the TIME_FORMAT_PROP property as a
format string.
|
static Object |
struct2Object(Struct value)
Convert java.sql.Struct to the object which is recognizable by framework,
for example DataSet.
|
static String |
suggestBestFieldForTheKey(List<String> fields,
String tableName,
Driver driver,
boolean includingFirst)
Suggest best field for the key.
|
static String |
time2Str(Object fieldValue,
Map<String,String> params)
Converts time to the string using value for the TIME_FORMAT_PROP property
as a format string.
|
static Object |
value2DisplayValue(int fieldType,
Object value,
Map<String,String> params)
Converts field value to the display value.
|
static Object |
value2StorageValue(int fieldType,
Object value,
Map<String,String> params,
boolean booleanToNumber)
Converts value to the storage value.
|
public static simplenlg.lexicon.Lexicon LEXICON
public static simplenlg.framework.NLGFactory NLG_FACTORY
public static simplenlg.realiser.english.Realiser REALIZER
public static final int UNDEFINED
public static final int ROWID
public static final int NCHAR
public static final int NVARCHAR
public static final int LONGNVARCHAR
public static final int NCLOB
public static final int SQLXML
public static final String DEFAULT_DELIMITER
public static final String DATE_FORMAT_PROP
public static final String TIME_FORMAT_PROP
public static final String DATE_TIME_FORMAT_PROP
public static final String CHAR_SEPARATOR_PROP
public static final String PARSE_DATES_PROP
public static final String TRIM_PROP
public static final String DECIMAL_AS_INT_PROP
public static Map<Integer,String> TYPES
public static final String ENCODED_FIELD_NAME_PREFIX
public static boolean addBindVar(String[] usingVars, String name, Object value, Map<String,Object> bindVars)
bindVars
using given name,
value and array of names usingVars
.usingVars
- the array of variable names. Similar to sql using clause.name
- the name of the variablevalue
- the value of the variablebindVars
- the bind variablespublic static boolean alreadyFormatedType(String typeName)
Example:
varchar(20) -> true
varchar - > false
typeName
- the type namepublic static Object array2Object(Array value)
value
- the valuepublic static String baseName2Name(String baseName)
Example:
abc.xyz - > xyz
abc.mmm.xyz - > xyz
baseName
- the base namepublic static String removeQuotes(String name, Driver driver)
name
- the namedriver
- the driverpublic static String removeQuotes(String name)
name
- the namepublic static String getNameWithoutOwnersAndWithOptionalQuotes(String name, Driver fromDriver, Driver toDriver)
Example:
abc.mnm.xyz - > xyz
abc."xyz" - > xyz
"abc.xyz" - > xyz
name
- the namefromDriver
- the from drivertoDriver
- the to driverpublic static String getNameForWildcard(String wildcard, String name, Driver fromDriver, Driver toDriver)
wildcard
- the wildcardname
- the namefromDriver
- the from drivertoDriver
- the to driverpublic static String suggestBestFieldForTheKey(List<String> fields, String tableName, Driver driver, boolean includingFirst)
fields
- the fieldstableName
- the table namedriver
- the driverincludingFirst
- if true include first fieldpublic static void cleanUpSQLData(Object caller, Statement... statements)
caller
- The object that controls the passed SQL objectsstatements
- SQL Statements to close.public static void cleanUpSQLData(Statement statement, ResultSet rs, Object caller)
statement
- A SQL Statement to close.rs
- A SQL ResultSet to close.caller
- The object that controls the passed SQL objectspublic static String convertDataType(FieldDef source, FieldDef dest)
source
- the source fielddest
- the destination fieldpublic static boolean dataTypeHasSize(int fType)
Example:
fType = Types.VARCHAR -> true
fType = Types.INTEGEr -> false
fType
- the SQL data typepublic static String date2Str(Object fieldValue, Map<String,String> params)
params
. The DATE_FORMAT_PROP is used to look
up format in the params
.fieldValue
- the field valueparams
- the parameterspublic static String dateTime2Str(Object fieldValue, Map<String,String> params)
params
. The DATE_TIME_FORMAT_PROP is
used to look up format in the params
.fieldValue
- the field valueparams
- the parameterspublic static String decimal2Str(Object value, Map<String,String> params)
value
- the valueparams
- the parameterspublic static void executeScript(Connection con, String script, boolean commit, boolean isCallable, Object... bindVariables) throws Exception
con
- the connectionscript
- the scriptcommit
- if true commit the transactionisCallable
- if true the statement is callablebindVariables
- the bind variables as an array of key/value pairsException
- in case of any errorpublic static void executeScript(Connection con, String script, boolean commit, Object... bindVariables) throws Exception
con
- the connectionscript
- the scriptcommit
- if true commit the transactionbindVariables
- the bind variables as an array of key/value pairsException
- in case of any errorpublic static void executeScript(Connection con, String script, Object... bindVariables) throws Exception
con
- the connectionscript
- the scriptbindVariables
- the bind variables as an array of key/value pairsException
- in case of any errorpublic static void executeScript(Connection con, String script, Driver driver, DataSet dataSet, OnException onException, Map<String,Object> bindVariables, String using, boolean commit, boolean isCallable, boolean supportsPrepared) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler. If
isCallable
is true the SQL statement will be executed as a
callable statement (stored procedure or anonymous sql block).con
- the conectionscript
- The sql script.driver
- the driverdataSet
- the data setonException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitisCallable
- the is callablesupportsPrepared
- the supports preparedException
- in case of any errorpublic static void executeScriptAndSaveException(Connection con, String script, Driver driver, DataSet dataSet, OnException onException, Map<String,Object> bindVariables, String using, boolean commit, boolean isCallable, boolean supportsPrepared, InfoLogger infoLogger) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler. If
isCallable
is true the SQL statement will be executed as a
callable statement (stored procedure or anonymous sql block).con
- the conectionscript
- The sql script.driver
- the driverdataSet
- the data setonException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitisCallable
- the is callablesupportsPrepared
- the supports preparedinfoLogger
- the info loggerException
- in case of any errorpublic static void executeScript(Connection con, String script, Driver driver, DataSet dataSet, OnException onException, Map<String,Object> bindVariables, String using, boolean commit, boolean isCallable) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler. If
isCallable
is true the SQL statement will be executed as a
callable statement (stored procedure or anonymous sql block).con
- the conectionscript
- The sql script.driver
- the driverdataSet
- the data setonException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitisCallable
- the is callableException
- in case of any errorpublic static void executeScriptAndSaveException(Connection con, String script, Driver driver, DataSet dataSet, OnException onException, Map<String,Object> bindVariables, String using, boolean commit, boolean isCallable, InfoLogger infoLogger) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler. If
isCallable
is true the SQL statement will be executed as a
callable statement (stored procedure or anonymous sql block).con
- the conectionscript
- The sql script.driver
- the driverdataSet
- the data setonException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitisCallable
- the is callableinfoLogger
- the info loggerException
- in case of any errorpublic static void executeScript(Connection con, String script, Driver driver, DataSet dataSet, OnException onException, Map<String,Object> bindVariables, String using, boolean commit) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler.con
- the conectionscript
- The sql script.driver
- the driverdataSet
- the data setonException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitException
- in case of any errorpublic static TypedKeyValue<ResultSet,Statement> executeScriptAndReturnResultSet(Connection con, String script, Driver driver, OnException onException, Map<String,Object> bindVariables, String using, boolean commit) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler.con
- the conectionscript
- The sql script.driver
- the driveronException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitException
- in case of any errorpublic static TypedKeyValue<ResultSet,Statement> executeScriptAndReturnResultSetAndSaveException(Connection con, String script, Driver driver, OnException onException, Map<String,Object> bindVariables, String using, boolean commit, InfoLogger infoLogger) throws Exception
bindVariables
and using
.
onException
serves as an exceptions handler.con
- the conectionscript
- The sql script.driver
- the driveronException
- The exceptions handlerbindVariables
- The bind variablesusing
- The using for bind variablescommit
- the commitinfoLogger
- the info loggerException
- in case of any errorpublic static int executeSql(Connection con, String sql, int action, String exception, Driver driver, boolean isCallable, Object... bindVars) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.driver
- the driverisCallable
- the is callablebindVars
- The bind variables0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static int executeSql(Connection con, String sql, boolean isCallable, Object... bindVars) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeisCallable
- the is callablebindVars
- The bind variables0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static int executeSql(Connection con, String sql, Object... bindVars) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executebindVars
- The bind variables0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static int executeSql(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver, boolean isCallable, boolean supportsPrepared) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driverisCallable
- the is callablesupportsPrepared
- the supports prepared0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static int executeSqlAndSaveException(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver, boolean isCallable, boolean supportsPrepared, InfoLogger infoLogger) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driverisCallable
- the is callablesupportsPrepared
- the supports preparedinfoLogger
- the info logger0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static int executeSql(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver, boolean isCallable) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driverisCallable
- the is callable0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static int executeSql(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driver0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static void executeSql(Connection con, String sql, LinkedHashMap<String,Object> params, Driver driver) throws Exception
params
and
finally executes sql. There can be multiple sql statements separated by
";". In this case statements executed one by one.con
- the consql
- the sqlparams
- the parametersdriver
- the driverException
- in case of any errorpublic static TypedKeyValue<Integer,TypedKeyValue<ResultSet,Statement>> executeSqlAndReturnResultSet(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driver0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static TypedKeyValue<Integer,TypedKeyValue<ResultSet,Statement>> executeSqlAndReturnResultSet(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver, boolean supportsPrepared) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driversupportsPrepared
- the use prepared statement0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static TypedKeyValue<Integer,TypedKeyValue<ResultSet,Statement>> executeSqlAndReturnResultSetAndSaveException(Connection con, String sql, int action, String exception, Map<String,Object> bindVars, Driver driver, boolean supportsPrepared, InfoLogger infoLogger) throws Exception
bindVars
and
finally executes sql. In case of exception returns appropriate exception
handler code.con
- the connectionsql
- the sql to executeaction
- The action. Possible actions are:
ON_EXCEPTION_RAISE, ON_EXCEPTION_IGNORE,ON_EXCEPTION_CONTINUE,
ON_EXCEPTION_MERGE
exception
- The string which can be used to mask exceptions.bindVars
- The bind variablesdriver
- the driversupportsPrepared
- the use prepared statementinfoLogger
- the info logger0 if there is no exception, otherwise one of the
ON_EXCEPTION_ values
Exception
- in case of any errorpublic static boolean fieldHasParams(int fieldType)
fieldType
- the field type Types
public static boolean fieldNameNeedQuotes(String fieldName, char quote, Driver driver)
fieldName
- the field namequote
- the quotedriver
- the driverpublic static String filter(Driver driver, String value, int fieldType)
Driver.filter(String)
if field is a flavor of char or
clob.driver
- the drivervalue
- the valuefieldType
- the field type Types
public static String fullName2BaseName(String fullName)
Example:
abc:xyz -> xyz
abc:mmm:xyz -> xyz
fullName
- the full namepublic static String fullName2Name(String fullName)
Example:
Java DB Test:APP.DATA_SOURCE -> DATA_SOURCE
fullName
- the full namepublic static String fullName2NodeName(String fullName)
Example:
Excel Test:C:/projects/toolsverse/java/data-test/test.table1$ -> Excel Test
fullName
- the full namepublic static String fullName2OwnerName(String fullName)
Example:
Excel Test:C:/projects/toolsverse/java/data-test/test.table1$ -> C:/projects/toolsverse/java/data-test/test
fullName
- the full namepublic static String getAfterFromFromSelect(String sql)
Example:
seLECt a,B,c,D fROm abc WHERE a=b and c is null order by xxx ->abc WHERE a=b and c is null order by xxx
sql
- the sqlpublic static String getAfterWhereFromSelect(String sql)
Example:
select a, b,c, d from abc where a=b and c is null ->a=b and c is null
sql
- the sqlpublic static String getSqlWithWhereClause(String sql, String condition)
sql
- the sqlcondition
- the conditionpublic static Object getBlob(ResultSet rs, int pos) throws Exception
rs
- the result setpos
- the position of the fieldException
- in case of any errorpublic static InputStream getBlobInputStreamFromResultSet(ResultSet rs, int columnNum) throws SQLException
rs
- the result setcolumnNum
- the column numberSQLException
- the SQL exceptionpublic static OutputStream getBlobOutputStream(Blob blob) throws SQLException
blob
- BlobSQLException
- the sQL exceptionpublic static String getValidSqlName(String value, String replacement, boolean withSpaces, String... additionalChars) throws Exception
value
- the valuereplacement
- the replacementwithSpaces
- if true include spacesadditionalChars
- the additional charsException
- the exceptionpublic static String getValidSqlName(String value, String replacement, String... additionalChars) throws Exception
value
- the valuereplacement
- the replacementadditionalChars
- the additional charsException
- the exceptionpublic static OutputStream getBlobOutputStreamFromResultSet(ResultSet rs, int columnNum) throws SQLException
rs
- the result setcolumnNum
- the column numberSQLException
- the SQL exceptionpublic static Object getClob(ResultSet rs, int pos) throws Exception
rs
- the result setpos
- the position of the clob fieldException
- in case of any errorpublic static String getColName(Map<String,Integer> cols, String colName)
Example:
Mapcols = new HashMap () SqlUtils.getColName(cols, "col") -> col SqlUtils.getColName(cols, "col") -> col1 SqlUtils.getColName(cols, "col") -> col2
cols
- the columnscolName
- the column namepublic static String getFieldAsText(Driver driver, String name, String fType, Object size, Object decimal, Object nullable, int javaType)
Example:
SqlUtils.getFieldAsText(driver, "test", "varchar", 100, null, "Yes", Types.VARCHAR) -> test varchar(100)
SqlUtils.getFieldAsText(driver, "test", "number", 18, 5, true, Types.NUMERIC) -> test number(18,5)
driver
- the drivername
- the field namefType
- the field typesize
- the sizedecimal
- the number of decimalsnullable
- the nullable flagjavaType
- the Types
public static String getFieldNameWithoutQuotes(String fieldName, char quote)
fieldName
- the field namequote
- the quotepublic static String encodeFieldNameWithBase64(String name)
name
- the namepublic static String decodeFieldNameWithBase64(String encoded)
encoded
- the encoded field namepublic static String getBindParameterName(String fieldName, Driver driver)
fieldName
- the field namedriver
- the driverpublic static String[] getFieldNamesWithSpaces(String[] fields, char quote, Driver driver)
fields
- the fieldsquote
- the quotedriver
- the driverpublic static String getFieldNameWithSpaces(String fieldName, char quote, Driver driver)
fieldName
- the field namequote
- the quotedriver
- the driverpublic static String getFieldNameWithSpacesForced(String fieldName, char quote, Driver driver)
fieldName
- the field namequote
- the quotedriver
- the driverpublic static String getFieldsFromSelect(String sql)
Example:
select a,b,c,d from abc -> a,b,c,d
sql
- the sqlpublic static boolean isSelectStatement(String sql)
sql
- the sqlpublic static boolean isCallStatement(String sql)
sql
- the sqlpublic static String getTableNameWithSuffix(String tableName, String suffix, Driver driver)
tableName
- the table namesuffix
- the suffixdriver
- the driverpublic static String getFieldsSql(DataSet dataSet, Driver driver, String key, FieldsRepository fieldsRepository, boolean withNotNull)
dataSet
- the data setdriver
- the driverkey
- the key fieldfieldsRepository
- the fields repositorywithNotNull
- boolean, if true and the column is not nullable add NOT NULL
constraintFieldsRepository
public static String getFieldsSql(DataSet dataSet, Driver driver, String key, FieldsRepository fieldsRepository)
dataSet
- the data setdriver
- the driverkey
- the key fieldfieldsRepository
- the fields repositoryFieldsRepository
public static String getInsertSelectSql(DataSet dataSet, Driver driver, String from, String to, boolean withTruncate)
public static int getFieldType(int newType, int existingType, boolean hasNotNullValue)
if (isCompatible(existingType, newType)) return existingType; if (isCompatible(newType, existingType)) return newType;
newType
- the new type Types
existingType
- the existing type Types
hasNotNullValue
- the "has not null value" flagTypes
public static String getFullNativeType(FieldDef fieldDef)
fieldDef
- the fieldpublic static String getIdFromPattern(String value, String delim, String idFieldName)
Example: abc#ayz#ID=12 -- 12
value
- the valuedelim
- the delimiteridFieldName
- the id field namepublic static String getJustTypeName(String typeName)
Example:
VARCHAR(100) -- VARCHAR
NUMBER(10, 20) -- NUMBER
INTEGER -- INTEGER
typeName
- the type namepublic static String getNativeTypeByJdbcType(int type)
type
- the typepublic static TypeAndValue getNumberTypeAndValue(String text)
text
- the textpublic static Object getObject(ResultSet rs, int index, FieldDef fieldDef, Driver driver) throws Exception
rs
- the result setindex
- the index of the columnfieldDef
- the fielddriver
- the driverException
- n case of any errorpublic static int[] getReplaceableTypes(int type)
type
- the type Types
public static Savepoint getSavepoint(Connection con)
con
- the connectionpublic static TypedKeyValue<Integer,Integer> getScaleAndPrecision(String nativeType)
nativeType
- the native data typepublic static String getSelectClause(Driver driver)
driver
- the driverpublic static String getSelectSql(String sql)
Example:
SqlUtils.getSelectSql("select * from abc where a=1") -> select * from abc where a=1
SqlUtils.getSelectSql("abc") -> select * from abc
sql
- the sqlpublic static String getSqlForExplainPlan(String sql, Driver driver, SqlParser sqlParser)
sql
- the sqldriver
- the driversqlParser
- the sql parserpublic static String getSqlForMetadataExtraction(String objectName, Driver driver)
objectName
- the object namedriver
- the driverpublic static String getTableNameWithSpaces(String tableName, char quote, Driver driver)
tableName
- the table namequote
- the quotedriver
- the driverpublic static TypeAndValue getTypeAndValue(String text, Map<String,String> params, boolean allStrings)
text
- the textparams
- the parameterspublic static Integer getTypeByName(String name)
Types
by type name.name
- the type namepublic static String getTypeName(Object fieldType)
Types
.fieldType
- the field typepublic static String getTypeRange(String typeName, int maxPrec, int maxScale)
Example:
SqlUtils.getTypeRange("number(100, 20)", 10, 30) -> (10,20)
typeName
- the type namemaxPrec
- the maximum precisionmaxScale
- the maximum scalepublic static String getTypeRange(String typeName, int maxPrec, int maxScale, boolean scaleLessOrEqualPrec)
Example:
SqlUtils.getTypeRange("number(100, 20)", 10, 30) -> (10,20)
typeName
- the type namemaxPrec
- the maximum precisionmaxScale
- the maximum scalescaleLessOrEqualPrec
- if true the scale should be less or equal to precisionpublic static String getTypeRange(String typeName, int maxPrec, int maxScale, boolean scaleLessOrEqualPrec, boolean forcePrecAndScale)
Example:
SqlUtils.getTypeRange("number(100, 20)", 10, 30) -> (10,20)
typeName
- the type namemaxPrec
- the maximum precisionmaxScale
- the maximum scalescaleLessOrEqualPrec
- if true the scale should be less or equal to precisionforcePrecAndScale
- if true force precision and scalepublic static String getTypeRange(String typeName, int maxPrec, int maxScale, boolean scaleLessOrEqualPrec, boolean forcePrecAndScale, int precMultiplier)
Example:
SqlUtils.getTypeRange("number(100, 20)", 10, 30) -> (10,20)
typeName
- the type namemaxPrec
- the maximum precisionmaxScale
- the maximum scalescaleLessOrEqualPrec
- if true the scale should be less or equal to precisionforcePrecAndScale
- if true force precision and scaleprecMultiplier
- the precision multiplierpublic static String getUnquotedText(Map<String,String> params, String text)
params
- the paramstext
- the textpublic static String getUrlFromWildCard(String url, String name)
url
- the original urlname
- the namepublic static String getValue(String value, int index, String delim)
Example;
SqlUtils.getValue("123;456;789", 1, ";") -> 456
value
- the valueindex
- the indexdelim
- the delimiterpublic static int getVarType(String bindVar)
bindVar
- the variable valueTypes
public static int getVarTypeByObjType(Object bindVar)
bindVar
- the bind variable valueTypes
public static String getWhereClause(Driver driver)
driver
- the driverpublic static boolean isArray(int fType)
fType
- the field type Types
public static boolean isBit(Object value)
value
- the valuepublic static boolean isBlob(int fType)
fType
- the field type Types
public static boolean isBinaryTimestamp(Object fieldValue, FieldDef fieldDef)
fieldValue
- the field valuefieldDef
- the fielddefpublic static boolean isBoolean(int fType)
fType
- the field type Types
public static boolean isChar(int fType)
fType
- the field type Types
public static boolean isCharActual(int fType)
fType
- the field type Types
public static boolean isVarChar(int fType)
fType
- the typepublic static boolean isClob(int fType)
fType
- the field type Types
public static boolean isCompatible(int original, int compareTo)
original
- the original type Types
compareTo
- the compare to type Types
public static boolean isDate(int fType)
fType
- the field type Types
public static boolean isDateOnly(int fType)
fType
- the field type Types
public static boolean isDateTime(int fType)
fType
- the field type Types
public static boolean isDecimal(int fType)
fType
- the field type Types
public static boolean isLargeObject(int fType)
fType
- the field type Types
public static boolean isNumber(int fType)
fType
- the field type Types
public static boolean isOther(int fType)
fType
- the field type Types
public static boolean isStruct(int fType)
fType
- the field type Types
public static boolean isValidPartOfSqlName(String name)
public static boolean isTableName(String sql)
sql
- the sqlpublic static boolean isTime(int fType)
fType
- the field type Types
public static boolean isTimestamp(int fType)
fType
- the field type Types
public static boolean isXml(int fType)
fType
- the data typepublic static String name2RightCase(Driver driver, String name)
driver
- the drivername
- the namepublic static String[] parseSql(String sql)
sql
- the sqlpublic static void populateDataSet(DataSet dataSet, Driver driver, ResultSet rs, ListHashMap<String,String> fieldsMapping, boolean unique, boolean checkKeyField) throws Exception
dataSet
- the data setdriver
- the driverrs
- the result setfieldsMapping
- the fields mapping. Maps original fields names to the new. Can
be null.unique
- the "is unique" flag, if true will exclude duplicated recordscheckKeyField
- the "check key field" flag, if true and dataSet.getKeyField()
!= null ignores null key fieldsException
- in case of any errorpublic static void populateDataSet(DataSet dataSet, Driver driver, ResultSet rs, ListHashMap<String,String> fieldsMapping, boolean unique, boolean checkKeyField, String filterByField, String filterByFieldValue) throws Exception
dataSet
- the data setdriver
- the driverrs
- the result setfieldsMapping
- the fields mapping. Maps original fields names to the new. Can
be null.unique
- the "is unique" flag, if true will exclude duplicated recordscheckKeyField
- the "check key field" flag, if true and dataSet.getKeyField()
!= null ignores null key fieldsfilterByField
- the field name to filter byfilterByFieldValue
- the field value to filter by. Works together with
filterByFieldException
- in case of any errorpublic static void populateDataSet(DataSet dataSet, Driver driver, ResultSet rs, ListHashMap<String,String> fieldsMapping, boolean unique, boolean checkKeyField, String filterByField, String filterByFieldValue, int maxRows, boolean ignoreGetObjectErrors) throws Exception
dataSet
- the data setdriver
- the driverrs
- the result setfieldsMapping
- the fields mapping. Maps original fields names to the new. Can
be null.unique
- the "is unique" flag, if true will exclude duplicated recordscheckKeyField
- the "check key field" flag, if true and dataSet.getKeyField()
!= null ignores null key fieldsfilterByField
- the field name to filter byfilterByFieldValue
- the field value to filter by. Works together with
filterByFieldmaxRows
- the maximum number of rows allowed for the data set. maxRows =
-1 means there is no limitationignoreGetObjectErrors
- the ignore get object errorsException
- in case of any errorpublic static String prepareParams(ResultSet rs, String params, String field) throws Exception
field
from the result set
rs
and adds it to the params
. Returns params.
Example:
abc -> "'abc'"
123 -> 'abc',123
xyz -> 'abc',123,'xyz'
rs
- the result setparams
- the existing parametersfield
- the fieldException
- in case of any errorpublic static String prepareParams(String params, Object param)
Example:
abc -> "'abc'"
123 -> 'abc',123
xyz -> 'abc',123,'xyz'
params
- the existing parametersparam
- the parameter to addpublic static String readStringFromReader(Reader r) throws IOException
r
- the readerIOException
- Signals that an I/O exception has occurred.public static void registerOutputParams(CallableStatement cst, String output, Driver driver, int start) throws Exception
cst
- the callable statementoutput
- the comma delimited output parametersdriver
- the ETL driverstart
- the start index for the output parameterException
- n case of any errorpublic static boolean requiresTranslation(int fType)
fType
requires
translation to display.fType
- the type pf the field Types
public static void setBindVar(PreparedStatement statement, Object bindVar, int index) throws Exception
statement
- the statementbindVar
- the bind variableindex
- the indexException
- in case of any errorpublic static void setNull(PreparedStatement statement, int type, int index) throws Exception
statement
- the statementtype
- the typeindex
- the indexException
- the exceptionpublic static void setBindVar(PreparedStatement statement, Object bindVar, int type, int index) throws Exception
statement
- the statementbindVar
- the bind variabletype
- the type Types
index
- the indexException
- in case of any errorpublic static void setBindVar(PreparedStatement statement, String bindVar, int index) throws Exception
statement
- the statementbindVar
- the bind variableindex
- the indexException
- in case of any errorpublic static void setBindVar(PreparedStatement statement, String bindVar, int type, int index) throws Exception
statement
- the statementbindVar
- the bind variabletype
- the typeindex
- the indexException
- in case of any errorpublic static void setBindVarAndRegisterOutputParams(PreparedStatement statement, Object bindVar, int index, Driver driver) throws Exception
statement
- the statementbindVar
- the bind variableindex
- the indexdriver
- the ETL driverException
- n case of any errorpublic static void setBlob(PreparedStatement pstmt, Object value, int pos) throws Exception
pstmt
- the prepared statementvalue
- the valuepos
- the position of the blob fieldException
- in case of any errorpublic static void setClob(PreparedStatement pstmt, Object value, int pos) throws Exception
pstmt
- the preapred statementvalue
- the valuepos
- the position of the clob fieldException
- in case of any errorpublic static Object storageValue2Value(int fieldType, Object value, Map<String,String> params)
fieldType
- the field type Types
value
- the valueparams
- the parameterspublic static Date str2Date(String text, Map<String,String> params)
params
uses default
DataSet.DATA_SET_DATE_TIME_FORMAT.text
- the textparams
- the parameterspublic static Date str2DateTime(String text, Map<String,String> params)
params
uses
default DataSet.DATA_SET_DATE_TIME_FORMAT.text
- the textparams
- the parameterspublic static Date str2Time(String text, Map<String,String> params)
params
uses default
DataSet.DATA_SET_TIME_FORMAT.text
- the textparams
- the parameterspublic static Object struct2Object(Struct value)
value
- the valuepublic static String time2Str(Object fieldValue, Map<String,String> params)
params
uses default
DataSet.DATA_SET_TIME_FORMAT.fieldValue
- the field valueparams
- the paramspublic static Object value2DisplayValue(int fieldType, Object value, Map<String,String> params)
fieldType
- the field type Types
value
- the valueparams
- the parameterspublic static Object value2StorageValue(int fieldType, Object value, Map<String,String> params, boolean booleanToNumber)
fieldType
- the field type Types
value
- the valueparams
- the parametersbooleanToNumber
- if true convert boolean true/false to 1/0public static void setBindVars(PreparedStatement statement, List<TypedKeyValue<String,Object>> fieldAndValues, Driver driver) throws Exception
statement
- the statementfieldAndValues
- the list of field names and field valuesdriver
- the etl driverException
- in case of any errorpublic static PreparedStatement getPreparedStatementAndSetBindVars(Connection con, String sql, List<TypedKeyValue<String,Object>> fieldAndValues, Driver driver, boolean useBindVariables) throws Exception
con
- the connectionsql
- the sqlfieldAndValues
- the list of field names and field valuesdriver
- the etl driveruseBindVariables
- if true the bind variables will be usedException
- in case of any errorpublic static String getConditions(List<TypedKeyValue<String,Object>> fieldAndValues, Driver driver, boolean useBindVariables)
fieldAndValues
- the list of fields and valuesdriver
- the etl driveruseBindVariables
- if true the bind variables will be usedpublic static boolean exists(Connection con, String table, Driver driver, List<TypedKeyValue<String,Object>> fieldAndValues, boolean useBindVariables) throws Exception
con
- the connectiontable
- the table namedriver
- the etl driverfieldAndValues
- the list of field names and field valuesuseBindVariables
- if true the bind variables will be usedException
- the exceptionpublic static boolean exists(Connection con, String table, Driver driver, TypedKeyValue<List<String>,List<Object>> fieldsAndValues, String fields, boolean useBindVariables) throws Exception
con
- the connectiontable
- the table namedriver
- the etl driverfieldsAndValues
- the fields and valuesfields
- the fieldsuseBindVariables
- if true the bind variables will be usedException
- in case of any errorpublic static String nameToSqlName(String name)
name
- the namepublic static String getAlterTableAddColumns(String tableName, DataSet dataSet, List<String> missingColumns, Driver driver)
tableName
- the table namedataSet
- the data setmissingColumns
- the missing columnsdriver
- the driverpublic static String getAlterTableAddColumns(String tableName, DataSet dataSet, List<String> missingColumns, Driver driver, boolean withNotNull)
tableName
- the table namedataSet
- the data setmissingColumns
- the missing columnsdriver
- the driverwithNotNull
- boolean, if true and the column is not nullable add NOT NULLCopyright © 2010-2020 Toolsverse. All Rights Reserved.