de.must.dataobj

Class SqlDialect

java.lang.Object

de.must.dataobj.SqlDialect

Direct Known Subclasses:

Db2400Dialect, DbaseDialect, DerbyDialect, FirebirdDialect, MariaDbDialect, MSAccessDialect, MSSQLServerDialect, MysqlDialect, OracleDialect, PostgreSQLDialect, SQLBaseDialect, SQLiteDialect, UCanAccessDialect

public abstract class SqlDialect
extends java.lang.Object

Super class for all database specific operations. Each database defines its own subclass to handle their typical SQL dialects. The specific dialect is loaded when DataObject is created.

See Also:

ConnectionHolder, DataObject

Field Summary

Fields

Modifier and Type	Field and Description
static java.lang.String	BOOLEAN_FALSE_INT
static java.lang.String	BOOLEAN_TRUE_INT
protected DataObject	dataObject
protected java.text.SimpleDateFormat	timestampFormat

Constructor Summary

Constructors

Constructor and Description
SqlDialect()

Method Summary

Modifier and Type	Method and Description
protected java.lang.String	compress(java.lang.String columnName, java.lang.String columnValue) Compresses the column value to its maximum length as specified in database.
protected void	extendCreateStatement(java.lang.StringBuilder createStatement, AbstractAttribute attribute)
protected void	extendInsertStatementWithValues(int i, java.sql.PreparedStatement statementForInsertion, java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, java.lang.String columnName, int columnType, int columnScale, java.lang.String columnNameOfRecordingDate, java.lang.String columnNameOfRecordingUser, java.lang.String recordingUser)
protected java.lang.String	extendToLength(java.lang.String value, int length) Extend a String by spaces up to the length as specified to fit the minimum length of char columns as requested by e.g ucanaccess.
protected int	extendUpdatePhrase(int countUpdateColumns, java.lang.StringBuilder updateStatement, java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, java.lang.String columnName, int columnType, int columnScale)
void	fillInsertStatementWithValues(java.sql.PreparedStatement statementForInsertion, java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, int columnCount, java.lang.String[] columnNames, int[] columnType, int[] columnScale, java.lang.String columnNameOfRecordingDate, java.lang.String columnNameOfRecordingUser, java.lang.String recordingUser)
boolean	getBoolean(java.lang.String columnName, java.util.HashMap<java.lang.String,java.lang.Object> original) Returns the value of a column as boolean.
java.lang.String	getCreateStatement(EntityInfo entityInfo) Returns the specific create statement string of the specified table
java.lang.String	getCreateStatement(java.lang.String tableName, AbstractAttribute[] attributes, Index[] indices) Returns the specific create statement string of the specified table
java.lang.String	getCreateStatement(java.lang.String tableName, AbstractAttribute[] attributes, Index[] indices, ForeignKey[] foreignKeys) Returns the specific create statement string of the specified table
static int	getIntDateValue(java.sql.Date dateValue)
protected java.lang.String	getLimitationFragment(int limit, int offset) Returns the limitation fragment of the SQL statement.
protected boolean	getRowBoolean(java.sql.ResultSet rs, int columnNbr) Returns the boolean value of the specified column.
protected boolean	getRowBoolean(java.sql.ResultSet rs, java.lang.String columnName) Returns the boolean value of the specified column.
protected java.sql.Date	getRowDate(java.sql.ResultSet rs, int columnNbr) Returns the date value of the specified column.
protected java.sql.Date	getRowDate(java.sql.ResultSet rs, java.lang.String columnName) Returns the date value of the specified column.
protected double	getRowDouble(java.sql.ResultSet rs, int columnNbr) Returns the double value of the specified column.
protected double	getRowDouble(java.sql.ResultSet rs, java.lang.String columnName) Returns the double value of the specified column.
protected float	getRowFloat(java.sql.ResultSet rs, int columnNbr) Returns the float value of the specified column.
protected float	getRowFloat(java.sql.ResultSet rs, java.lang.String columnName) Returns the float value of the specified column.
protected int	getRowInt(java.sql.ResultSet rs, int columnNbr) Returns the int value of the specified column.
protected int	getRowInt(java.sql.ResultSet rs, java.lang.String columnName) Returns the int value of the specified column.
protected long	getRowLong(java.sql.ResultSet rs, int columnNbr) Returns the long value of the specified column.
protected long	getRowLong(java.sql.ResultSet rs, java.lang.String columnName) Returns the long value of the specified column.
protected java.lang.String	getRowString(java.sql.ResultSet rs, int columnNbr) Returns the String value of the specified column.
protected java.lang.String	getRowString(java.sql.ResultSet rs, java.lang.String columnName) Returns the String value of the specified column.
protected java.sql.Time	getRowTime(java.sql.ResultSet rs, int columnNbr) Returns the time value of the specified column.
protected java.sql.Time	getRowTime(java.sql.ResultSet rs, java.lang.String columnName) Returns the time value of the specified column.
protected java.sql.Timestamp	getRowTimestamp(java.sql.ResultSet rs, java.lang.String columnName) Returns the timestamp value of the specified column.
java.lang.String	getSqlCompareString(boolean b) Returns the specific variant how boolean values are compared.
abstract java.lang.String	getSqlCompareString(java.sql.Date date) Returns the specific variant how date values are compared.
abstract java.lang.String	getSqlCompareString(DateString dateString) Returns the specific variant how date values are compared.
java.lang.String	getSqlCompareString(java.sql.Timestamp timestamp)
java.lang.String	getSQLFormat(java.lang.Double doubleObj) Returns a double as a string in a way it is accepted at sql updates.
java.lang.String	getUpdatePhrase(java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, java.lang.String entity, int columnCount, java.lang.String[] columnNames, int[] columnType, int[] columnScale) Returns the update statement string.
java.lang.String	getWhereConditionFragementForExactDateComparison(java.lang.String columnName, java.sql.Date date) Returns the compare fragment for exact date comparison regardless their time parts in where conditions.
protected void	initColumn(java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, java.lang.String columnName, int columnType, int columnScale)
void	initRow(java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, int columnCount, java.lang.String[] columnNames, int[] columnType, int[] columnScale) Initializes a row.
protected boolean	isKnownAsBoolean(java.lang.String columnName) Checks whether or not the specified column is of type boolean by evaluating the attributes of the data object if available.
protected static boolean	isPartOfUniqueIndex(java.lang.String fieldName, Index[] indices) Returns true if fieldName is part of one of the unique indices.
protected void	loadColumn(java.sql.ResultSet rs, java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, java.lang.String columnName, int columnType, int columnScale)
void	loadRow(java.sql.ResultSet rs, java.util.HashMap<java.lang.String,java.lang.Object> original, java.util.HashMap<java.lang.String,java.lang.Object> workedOn, int columnCount, java.lang.String[] columnNames, int[] columnType, int[] columnScale) Loads the row's values into buffers for random access.
protected void	setAttributes(AbstractAttribute[] attributes) Sets the list of attributes.
void	setBoolean(java.lang.String columnName, boolean boolValue, java.util.HashMap<java.lang.String,java.lang.Object> workedOn) Sets the value of the specified column.
protected void	setDataObject(DataObject dataObject)
protected java.lang.String	sqlSecure(java.lang.String stringField) Replaces ' by '' to avoid SQL error while inserting or updating.
boolean	supportsForeignKey()
boolean	supportsPrimaryKey()
java.lang.String	transformStringWhenSaving(java.lang.String columnName, java.lang.String value) Converts String values before sending to database.
abstract java.lang.String	unifyColumnName(java.lang.String columnName) Unifies the column name.
abstract java.lang.String	unifyTableName(java.lang.String tableName) Unifies the table name.

Methods inherited from class java.lang.Object

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

Field Detail

BOOLEAN_TRUE_INT

public static final java.lang.String BOOLEAN_TRUE_INT

See Also:

Constant Field Values

BOOLEAN_FALSE_INT

public static final java.lang.String BOOLEAN_FALSE_INT

See Also:

Constant Field Values

dataObject

protected DataObject dataObject

timestampFormat

protected java.text.SimpleDateFormat timestampFormat

Constructor Detail

SqlDialect

public SqlDialect()

Method Detail

isPartOfUniqueIndex

protected static boolean isPartOfUniqueIndex(java.lang.String fieldName,
                                             Index[] indices)

Returns true if fieldName is part of one of the unique indices.

Parameters:

fieldName - the name of the field to check

indices - the indices to look for

Returns:

true if fieldName is part of one of the unique indices

setDataObject

protected void setDataObject(DataObject dataObject)

supportsPrimaryKey

public boolean supportsPrimaryKey()

supportsForeignKey

public boolean supportsForeignKey()

getCreateStatement

public java.lang.String getCreateStatement(EntityInfo entityInfo)

Returns the specific create statement string of the specified table

Parameters:

entityInfo - informations about the table to be created

Returns:

the create statement string

getCreateStatement

public java.lang.String getCreateStatement(java.lang.String tableName,
                                           AbstractAttribute[] attributes,
                                           Index[] indices)

Returns the specific create statement string of the specified table

Parameters:

tableName - the name of the table to be created

attributes - the attribute of the table to be created

indices - the indices to be created

Returns:

the create statement string

getCreateStatement

public java.lang.String getCreateStatement(java.lang.String tableName,
                                           AbstractAttribute[] attributes,
                                           Index[] indices,
                                           ForeignKey[] foreignKeys)

Returns the specific create statement string of the specified table

Parameters:

tableName - the name of the table to be created

attributes - the attribute of the table to be created

indices - the indices to be created

foreignKeys - the foreign keys of the table to be created

Returns:

the create statement string

extendCreateStatement

protected void extendCreateStatement(java.lang.StringBuilder createStatement,
                                     AbstractAttribute attribute)

initRow

public void initRow(java.util.HashMap<java.lang.String,java.lang.Object> original,
                    java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                    int columnCount,
                    java.lang.String[] columnNames,
                    int[] columnType,
                    int[] columnScale)

Initializes a row. Needed to prepare an insert statement.

Parameters:

original - the buffer of original values (before changing values)

workedOn - the buffer of worked on values (after changing values)

columnCount - the number of the regarded columns

columnNames - the names of the regarded columns

columnType - the types of the regarded columns

columnScale - the scales of the regarded columns

initColumn

protected void initColumn(java.util.HashMap<java.lang.String,java.lang.Object> original,
                          java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                          java.lang.String columnName,
                          int columnType,
                          int columnScale)

loadRow

public void loadRow(java.sql.ResultSet rs,
                    java.util.HashMap<java.lang.String,java.lang.Object> original,
                    java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                    int columnCount,
                    java.lang.String[] columnNames,
                    int[] columnType,
                    int[] columnScale)

Loads the row's values into buffers for random access.

Parameters:

rs - the used result set

original - the buffer of original values (before changing values)

workedOn - the buffer of worked on values (after changing values)

columnCount - the number of the regarded columns

columnNames - the names of the regarded columns

columnType - the types of the regarded columns

columnScale - the scales of the regarded columns

loadColumn

protected void loadColumn(java.sql.ResultSet rs,
                          java.util.HashMap<java.lang.String,java.lang.Object> original,
                          java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                          java.lang.String columnName,
                          int columnType,
                          int columnScale)

fillInsertStatementWithValues

public void fillInsertStatementWithValues(java.sql.PreparedStatement statementForInsertion,
                                          java.util.HashMap<java.lang.String,java.lang.Object> original,
                                          java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                                          int columnCount,
                                          java.lang.String[] columnNames,
                                          int[] columnType,
                                          int[] columnScale,
                                          java.lang.String columnNameOfRecordingDate,
                                          java.lang.String columnNameOfRecordingUser,
                                          java.lang.String recordingUser)
                                   throws java.sql.SQLException

Throws:

java.sql.SQLException

extendInsertStatementWithValues

protected void extendInsertStatementWithValues(int i,
                                               java.sql.PreparedStatement statementForInsertion,
                                               java.util.HashMap<java.lang.String,java.lang.Object> original,
                                               java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                                               java.lang.String columnName,
                                               int columnType,
                                               int columnScale,
                                               java.lang.String columnNameOfRecordingDate,
                                               java.lang.String columnNameOfRecordingUser,
                                               java.lang.String recordingUser)
                                        throws java.sql.SQLException

Throws:

java.sql.SQLException

getUpdatePhrase

public java.lang.String getUpdatePhrase(java.util.HashMap<java.lang.String,java.lang.Object> original,
                                        java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                                        java.lang.String entity,
                                        int columnCount,
                                        java.lang.String[] columnNames,
                                        int[] columnType,
                                        int[] columnScale)

Returns the update statement string.

Parameters:

original - the buffer of original values (before changing values)

workedOn - the buffer of worked on values (after changing values)

entity - the table name

columnCount - the number of the regarded columns

columnNames - the names of the regarded columns

columnType - the types of the regarded columns

columnScale - the scales of the regarded columns

Returns:

the update statement string

extendUpdatePhrase

protected int extendUpdatePhrase(int countUpdateColumns,
                                 java.lang.StringBuilder updateStatement,
                                 java.util.HashMap<java.lang.String,java.lang.Object> original,
                                 java.util.HashMap<java.lang.String,java.lang.Object> workedOn,
                                 java.lang.String columnName,
                                 int columnType,
                                 int columnScale)

unifyTableName

public abstract java.lang.String unifyTableName(java.lang.String tableName)

Unifies the table name.

Parameters:

tableName - the table name to unify

Returns:

the unified table name

unifyColumnName

public abstract java.lang.String unifyColumnName(java.lang.String columnName)

Unifies the column name.

Parameters:

columnName - the column name to unify

Returns:

the unified column name

getSqlCompareString

public abstract java.lang.String getSqlCompareString(DateString dateString)

Returns the specific variant how date values are compared.

Parameters:

dateString - the DateString to be prepared

Returns:

the prepared String for data comparison

getSqlCompareString

public abstract java.lang.String getSqlCompareString(java.sql.Date date)

Returns the specific variant how date values are compared.

Parameters:

date - the date value to be prepared

Returns:

the prepared String for data comparison

getWhereConditionFragementForExactDateComparison

public java.lang.String getWhereConditionFragementForExactDateComparison(java.lang.String columnName,
                                                                         java.sql.Date date)

Returns the compare fragment for exact date comparison regardless their time parts in where conditions. In case of overriding, Be advised that using date functions may cause performance leaks, therefore prefer BETWEEN.

Parameters:

columnName - the name of the column containing the date value (which may include time parts!)

date - the date to compare to

Returns:

the applicable fragment for exact comparison regardless their time parts

getSqlCompareString

public java.lang.String getSqlCompareString(java.sql.Timestamp timestamp)

getSqlCompareString

public java.lang.String getSqlCompareString(boolean b)

Returns the specific variant how boolean values are compared.

Parameters:

b - the boolean value to be prepared

Returns:

the prepared String for boolean comparison

getBoolean

public boolean getBoolean(java.lang.String columnName,
                          java.util.HashMap<java.lang.String,java.lang.Object> original)

Returns the value of a column as boolean.

Parameters:

columnName - the name of the column

original - the buffer of original values of the row

Returns:

the value of a column

setBoolean

public void setBoolean(java.lang.String columnName,
                       boolean boolValue,
                       java.util.HashMap<java.lang.String,java.lang.Object> workedOn)

Sets the value of the specified column.

Parameters:

columnName - the name of the column to be set

boolValue - the value the column shall be set to

workedOn - the buffer of worked on values of the row

transformStringWhenSaving

public java.lang.String transformStringWhenSaving(java.lang.String columnName,
                                                  java.lang.String value)

Converts String values before sending to database. Needed if databases comes in trouble with control bytes like e.g. "\n", Override it if necessary.

Parameters:

value - the value to transform

Returns:

the transformed value

getRowBoolean

protected boolean getRowBoolean(java.sql.ResultSet rs,
                                int columnNbr)

Returns the boolean value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowFloat

protected float getRowFloat(java.sql.ResultSet rs,
                            int columnNbr)

Returns the float value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowInt

protected int getRowInt(java.sql.ResultSet rs,
                        int columnNbr)

Returns the int value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowLong

protected long getRowLong(java.sql.ResultSet rs,
                          int columnNbr)

Returns the long value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowDate

protected java.sql.Date getRowDate(java.sql.ResultSet rs,
                                   int columnNbr)

Returns the date value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowTime

protected java.sql.Time getRowTime(java.sql.ResultSet rs,
                                   int columnNbr)

Returns the time value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowString

protected java.lang.String getRowString(java.sql.ResultSet rs,
                                        int columnNbr)

Returns the String value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowDouble

protected double getRowDouble(java.sql.ResultSet rs,
                              int columnNbr)

Returns the double value of the specified column.

Parameters:

rs - the result set

columnNbr - the number of the column

Returns:

the value of the column

getRowBoolean

protected boolean getRowBoolean(java.sql.ResultSet rs,
                                java.lang.String columnName)

Returns the boolean value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowFloat

protected float getRowFloat(java.sql.ResultSet rs,
                            java.lang.String columnName)

Returns the float value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowInt

protected int getRowInt(java.sql.ResultSet rs,
                        java.lang.String columnName)

Returns the int value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowLong

protected long getRowLong(java.sql.ResultSet rs,
                          java.lang.String columnName)

Returns the long value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowDate

protected java.sql.Date getRowDate(java.sql.ResultSet rs,
                                   java.lang.String columnName)

Returns the date value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowTime

protected java.sql.Time getRowTime(java.sql.ResultSet rs,
                                   java.lang.String columnName)

Returns the time value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowTimestamp

protected java.sql.Timestamp getRowTimestamp(java.sql.ResultSet rs,
                                             java.lang.String columnName)

Returns the timestamp value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowString

protected java.lang.String getRowString(java.sql.ResultSet rs,
                                        java.lang.String columnName)

Returns the String value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getRowDouble

protected double getRowDouble(java.sql.ResultSet rs,
                              java.lang.String columnName)

Returns the double value of the specified column.

Parameters:

rs - the result set

columnName - the name of the column

Returns:

the value of the column

getSQLFormat

public java.lang.String getSQLFormat(java.lang.Double doubleObj)

Returns a double as a string in a way it is accepted at sql updates.

Parameters:

doubleObj - the double value to be formatted

Returns:

a double as a string in a way it is accepted at SQL updates

sqlSecure

protected java.lang.String sqlSecure(java.lang.String stringField)

Replaces ' by '' to avoid SQL error while inserting or updating.

Parameters:

stringField - the String to modify

Returns:

the modified String

extendToLength

protected java.lang.String extendToLength(java.lang.String value,
                                          int length)

Extend a String by spaces up to the length as specified to fit the minimum length of char columns as requested by e.g ucanaccess.

Parameters:

value - the value to be extended

length - the required length of the char column

Returns:

the extended value

compress

protected java.lang.String compress(java.lang.String columnName,
                                    java.lang.String columnValue)

Compresses the column value to its maximum length as specified in database.

Parameters:

columnName - the name of the column to be compressed

columnValue - the column value to be compressed

Returns:

the compressed value

getLimitationFragment

protected java.lang.String getLimitationFragment(int limit,
                                                 int offset)

Returns the limitation fragment of the SQL statement.

Parameters:

limit - maximum of entries to be put into the result set

offset - the number of records that don't have to be read anymore

Returns:

the limitation fragment

setAttributes

protected void setAttributes(AbstractAttribute[] attributes)

Sets the list of attributes. Helpful to determine things that may not be determined by meta data like column type.

Parameters:

the - attributes of the data object.

isKnownAsBoolean

protected boolean isKnownAsBoolean(java.lang.String columnName)

Checks whether or not the specified column is of type boolean by evaluating the attributes of the data object if available.

Parameters:

columnName - the name of the column to be checked as boolean type

Returns:

true if the specified column is of type boolean

getIntDateValue

public static int getIntDateValue(java.sql.Date dateValue)