Overview  Package   Class  Use  Tree  Deprecated  Index  Help 
 PREV CLASS   NEXT CLASS FRAMES    NO FRAMES    
SUMMARY: NESTED | FIELD | CONSTR | METHOD DETAIL: FIELD | CONSTR | METHOD

com.sibvisions.rad.persist.jdbc
Class DBAccess

java.lang.Object
  extended by com.sibvisions.rad.persist.jdbc.DBAccess
All Implemented Interfaces:
IDBAccess
Direct Known Subclasses:
DB2DBAccess, DerbyDBAccess, HSQLDBAccess, MSSQLDBAccess, MySQLDBAccess, OracleDBAccess, PostgreSQLDBAccess
public class DBAccess
extends Object
implements IDBAccess

The DBAccess is the implementation for most used SQL databases.
Standard ANSI SQL Databases are anyways supported.
Its used to read/write data between the storage and the DataBook/DataPage. It has database type specific implementations.


Example: 

 
 DBAccess dba = new DBAccess();
 
 // set connect properties
 dba.setDriver("org.hsqldb.jdbcDriver");
 dba.setUrl("jdbc:hsqldb:file:testdbs/test/testdb");
 dba.setUsername("sa");
 dba.setPassword("");
 
 Properties pDBProperties = new Properties();
 pDBProperties.put("shutdown", true);
 dba.setDBProperties(pDBProperties);
 
 // open
 dba.open();
 
 To get Database independent DBAccess, it is better to use:
 
 DBAccess dba = DBAccess.getDBAccess("jdbc:hsqldb:file:testdbs/test/testdb");
 
 // insert data into test table
 PreparedStatement psPreparedStatement = dba.getPreparedStatement(
                     "insert into test (id, name) values(?,?)", false);
 psPreparedStatement.setInt(1, 1);
 psPreparedStatement.setString(2, "projectX");
 dba.executeUpdate(psPreparedStatement);
 
 // select data from test table
 psPreparedStatement = dba.getPreparedStatement("select * from test", false);
 ResultSet rs = dba.executeQuery(psPreparedStatement);
 
 while (rs.next())
 {
    System.out.println(rs.getInt("id") + "," + rs.getString("name"));
 }

See Also:
IDBAccess, RemoteDataBook

Nested Class Summary
static class DBAccess.ColumnMetaDataInfo
          The ColumnMetaDataInfo is caches information about column meta data requests.
 
Field Summary
protected static ILogger logger
          The logger for protocol the performance.
static int LONGNVARCHAR
          A jdbc VARCHAR DB column data type constant.
static int NCHAR
          A jdbc VARCHAR DB column data type constant.
static int NCLOB
          A jdbc BLOB DB column data type constant.
static int NVARCHAR
          A jdbc VARCHAR DB column data type constant.
static String QUOTE
          JVx generell DB quote character. will be translated in the DB specific.
static int SQLXML
          A jdbc XML DB column data type constant.
 
Constructor Summary
DBAccess()
          Constructs a new DBAccess Object.
 
Method Summary
 void close()
          Closes the database Connection and releases all memory.
 void commit()
          Commits the DB transaction.
protected  Object convertDatabaseSpecificObjectToValue(ServerColumnMetaData pColumnMetaData, Object pObject)
          Enables the database specific implementation to handle/convert special objects.
protected  Object convertValueToDatabaseSpecificObject(Object pValue)
          Converts an object to a standard value for the specific database.
protected  String createReplace(String pSource, String pOld, String pNew)
          Create an DB specific replace command, which replacs in the pSource all pOld to pNew.
protected  String createWhereColumn(ServerMetaData pServerMetaData, CompareCondition pCompare, String pColumnName)
          Creates the where column.
protected  String createWhereParam(ServerMetaData pServerMetaData, CompareCondition pCompare)
          Creates the where parameter.
 void delete(String pWriteBackTable, ServerMetaData pServerMetaData, Object[] pDelete)
          Deletes the specified row.
 Object executeFunction(String pFunctionName, int pReturnType, Object... pParameters)
          Executes a DB function with the specified parameters and return the result.
 void executeProcedure(String pProcedureName, Object... pParameters)
          Executes a DB procedure with the specified parameters.
 List<Object> executeSql(String pStatement, Object... pParameters)
          Executes a SQL command as prepared statement.
 void executeStatement(String pStatement)
          Executes a DDL or DML Statement.
 int executeUpdate(PreparedStatement pSqlStatement)
          Calls executesUpdate() for specified PreparedStatement and returns the number of updated rows.
 List<Object[]> fetch(ServerMetaData pServerMetaData, String pBeforeQueryColumns, String[] pQueryColumns, String pFromClause, ICondition pFilter, String pLastWhereCondition, String pAfterWhereClause, SortDefinition pSort, int pFromRow, int pMinimumRowCount)
          Returns the List of fetched rows (as List of Object[]) for the specified query tables and parameters.
protected  SQLException formatSQLException(SQLException pSqlException)
          Adds the SQL Error Code into the message of the SQL Exception.
protected  SQLException formatSQLException(SQLException pSqlException, String pMessage, String pCode)
          Adds the SQL Error Code into the message of the SQL Exception.
 Hashtable<String,Object[]> getAllowedValues(String pCatalog, String pSchema, String pTable)
          Gets the allowed values from a specific table.
protected  String getAutomaticLinkColumnName(ForeignKey pForeignKey)
          Returns the automatic link column name for the specified ForeignKey and FK Column in the master table.
static TranslationMap getAutomaticLinkColumnNameTranslation()
          Returns the TranslationMap for the automatic link column name custom Translation. its used in the getAutomaticLinkColName(ForeignKey pForeignKey) to change the column, thats determined.
 DBAccess.ColumnMetaDataInfo getColumnMetaData(String pFromClause, String[] pQueryColumns, String pBeforeQueryColumns, String pWhereClause, String pAfterWhereClause, String pWriteBackTable, String[] pWriteBackColumns)
          Returns the meta data information for the specified query, and configures all columns with defaults.
 Connection getConnection()
          Returns the conncetion to the database.
 String getDatabaseSpecificLockStatement(String pWriteBackTable, ServerMetaData pServerMetaData, ICondition pPKFilter)
          Returns the database specific statement to lock the specified row in the database.
static DBAccess getDBAccess(Connection pConnection)
          Gets the suitable DBAccess for the given connection.
static DBAccess getDBAccess(DBCredentials pCredentials)
          Gets the suitable DBAccess for the given DBCredentials.
static DBAccess getDBAccess(String pJdbcUrl)
          Gets the suitable DBAcces for the given JdbcUrl.
 Properties getDBProperties()
          Returns the DB specific initial parameters for the Connection creation.
 String getDBProperty(String pName)
          Gets the value for a specific database property.
 Object[] getDefaultAllowedValues(String pCatalog, String pSchema, String pTable, ServerColumnMetaData pMetaData)
          Gets the default allowed values for a given column from a given table.
 String getDefaultSchema()
          Gets the default schema name, if it was set manually.
 Hashtable<String,Object> getDefaultValues(String pCatalog, String pSchema, String pTable)
          Gets all default column values of a specific table.
 String getDriver()
          Gets the database driver name as String.
 List<ForeignKey> getFKs(String pCatalog, String pSchema, String pTable)
          Returns all Foreign Keys for the specified table.
protected  String getFromClause(String pWriteBackTable, List<ForeignKey> auForeignKeys)
          It creates and returns a from clause with joins over all foreign tables.
 int getMaxTime()
          Returns the maximum time in miliseconds to use, to try to fetch all rows. reduce open cursors, and increase performance.
 String getPassword()
          Sets the password to use for the connection to the database.
 Key getPK(String pCatalog, String pSchema, String pTable)
          Gets all Primary Key columns and return it as list of Strings.
 PreparedStatement getPreparedStatement(String pSqlStatement, boolean pReturnKey)
          Return a PreparedStatement for the given SQL statement.
protected  String[] getQueryColumns(String pWriteBackTable, List<ForeignKey> pForeignKeys, String[] pWriteBackColumns)
          It creates and returns the query columns, with all write back columns and includes the columns for the LinkReferences (automatic link celleditors).
 String getSelectStatement(ICondition pFilter, String pFromClause, String[] pQueryColumns, String pBeforeQueryColumns, String pWhereClause, String pAfterWhereClause, ServerMetaData pServerMetaData)
          It initialize the select for a specified storage unit and return the SELECT statement.
 String getTableForSynonym(String pSynomyn)
          Returns the full qualified table name incl. schema/catalog/db link for the given synonym.
 List<Key> getUKs(String pCatalog, String pSchema, String pTable)
          It gets all columns for each Unique Key and return it.
 String getUrl()
          Gets the jdbc url String for this database.
 String getUsername()
          Gets the user name to connect with.
 String getWHEREClause(ICondition pFilter, ServerMetaData pServerMetaData, boolean pUsePrefix)
          Returns the WHERE clause for a UPDATE, DELETE or SELECT statement specified with a Filter.
 Object[] insert(String pWriteBackTable, ServerMetaData pServerMetaData, Object[] pNewDataRow)
          Returns the newly inserted row from the write back table.
 Object[] insertAnsiSQL(String pWriteBackTable, String pInsertStatement, ServerMetaData pServerMetaData, Object[] pNewDataRow, String pDummyColumn)
          Returns the newly inserted row from a Ansi SQL Database.
 Object[] insertDatabaseSpecific(String pWriteBackTable, String pInsertStatement, ServerMetaData pServerMetaData, Object[] pNewDataRow, String pDummyColumn)
          Returns the newly inserted row from an Database specific insert statement.
 boolean isAutoQuote(String pName)
          It returns true if this name should be automated quoted.
 boolean isOpen()
          Returns true, if the database is still open.
 boolean isTypeEqual(ServerColumnMetaData pServerColumnMetaData, CompareCondition pCompare)
          Check if the Type of the column and the value to compare is equal.
 void lockRow(String pWriteBackTable, ServerMetaData pServerMetaData, ICondition pPKFilter)
          It locks the specified row in the storage.
protected  int lockRowInternal(String pWriteBackTable, ServerMetaData pServerMetaData, ICondition pPKFilter)
          It locks the current row and return how many rows are affected.
 void open()
          It opens the database and stores the Connection object.
 String quote(String pName)
          Quotes a named DB object with our internal quote character, if it should be quoted in this database.
 String quoteAllways(String pName)
          Quotes a named DB object with the JVx DB QUOTE character.
static void registerDBAccessClass(String pJdbcUrlPrefix, Class<? extends DBAccess> pClass)
          Registers the Class for the jdbc url prefix.
 String removeDBSpecificQuotes(String pName)
          Removes the DB specific quotes of a named DB object.
static String removeQuotes(String pName)
          Removes the JVx DB Quotes of a named DB object.
 void rollback()
          Rollback the DB transaction.
static void setAutomaticLinkColumnNameTranslation(TranslationMap pTranslationMap)
          Sets the TranslationMap for the automatic link column name custom Translation. its used in the getAutomaticLinkColName(ForeignKey pForeignKey) to change the column, thats determined.
protected  int setColumnsToStore(PreparedStatement pInsert, ServerColumnMetaData[] pServerColumnMetaData, int[] iaWriteables, Object[] pNew, Object[] pOld)
          Sets the values of all changed columns to store from the value Object[]s into the PreparedStatement and returns the last used parameter index.
protected  void setConnection(Connection pConnection)
          Sets the internal conncetion to the database.
protected  boolean setDatabaseSpecificType(ResultSetMetaData pMetaData, int pColumnIndex, ServerColumnMetaData pColumnMetaData)
          Enables the database specific implementation to change the metadata before the default settings are made.
 void setDBProperties(Properties pProperties)
          Sets DB specific initial parameters for the Connection creation.
 void setDBProperty(String pName, String pValue)
          Sets a specific database property.
 void setDefaultSchema(String pSchema)
          Sets the user-defined default schema.
 void setDriver(String pDriver)
          Sets the database driver name as String.
protected  void setFilterParameter(int iStartParameterIndex, PreparedStatement pStatement, ICondition pFilter)
          Sets all Filter parameter values into the PreparedStatement.
 void setMaxTime(int pMaxTime)
          Sets the maximum time in miliseconds to use, to try to fetch all rows. reduce open cursors, and increase performance.
 void setPassword(String pPassword)
          Sets the password to use for the connection to the database.
 void setQueryTimeOut(int pQueryTimeOut)
          Sets the query time out.
protected  void setQuoteCharacters(String pOpen, String pClose)
          Sets the database specific quote characters.
 void setUrl(String pUrl)
          Sets the url String for this database.
 void setUsername(String pUsername)
          Sets the user name to connect with.
protected  String[] splitSchemaTable(String pFromClause)
          Separates the schema and table from the given from clause.
 boolean supportsGetGeneratedKeys()
          Returns if this Database specific supports generated keys.
 String toString()
          
protected  Object translateDefaultValue(String pColumnName, int pDataType, String pDefaultValue)
          Translates a default value from a column to the datatype object.
 String translateQuotes(String pStatement)
          It replaces all JVx quotes with the database specific quote.
protected  Object translateValue(int pDataType, String pValue)
          Translates an object value to the datatype object.
 Object[] update(String pWriteBackTable, ServerMetaData pServerMetaData, Object[] pOld, Object[] pNew)
          Return the updated row.
 int updateAnsiSQL(String pWriteBackTable, String sUpdateStatement, ServerMetaData pServerMetaData, Object[] pOld, Object[] pNew, ICondition pPKFilter)
          Updates the specified row and return the count of affected rows.
 int updateDatabaseSpecific(String pWriteBackTable, String sUpdateStatement, ServerMetaData pServerMetaData, Object[] pOld, Object[] pNew, ICondition pPKFilter)
          Updates the specified row and return the count of affected rows.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 

Field Detail

NCHAR

public static final int NCHAR
A jdbc VARCHAR DB column data type constant.

See Also:
Constant Field Values

NVARCHAR

public static final int NVARCHAR
A jdbc VARCHAR DB column data type constant.

See Also:
Constant Field Values

LONGNVARCHAR

public static final int LONGNVARCHAR
A jdbc VARCHAR DB column data type constant.

See Also:
Constant Field Values

SQLXML

public static final int SQLXML
A jdbc XML DB column data type constant.

See Also:
Constant Field Values

NCLOB

public static final int NCLOB
A jdbc BLOB DB column data type constant.

See Also:
Constant Field Values

QUOTE

public static final String QUOTE
JVx generell DB quote character. will be translated in the DB specific.

See Also:
Constant Field Values

logger

protected static ILogger logger
The logger for protocol the performance.

Constructor Detail

DBAccess

public DBAccess()
Constructs a new DBAccess Object.

Method Detail

getDBAccess

public static DBAccess getDBAccess(String pJdbcUrl)
                            throws DataSourceException
Gets the suitable DBAcces for the given JdbcUrl.

Parameters:
pJdbcUrl - the JdbcUrl.
Returns:
the suitable DBAccess.
Throws:
DataSourceException - if the instance name is unknown or the DBAccess object cannot be created.

getDBAccess

public static DBAccess getDBAccess(DBCredentials pCredentials)
                            throws DataSourceException
Gets the suitable DBAccess for the given DBCredentials.

Parameters:
pCredentials - the database credentials.
Returns:
the suitable DBAccess.
Throws:
DataSourceException - if the instance name is unknown or the DBAccess object cannot be created.

getDBAccess

public static DBAccess getDBAccess(Connection pConnection)
                            throws DataSourceException
Gets the suitable DBAccess for the given connection.

Parameters:
pConnection - the database connection.
Returns:
the suitable DBAccess.
Throws:
DataSourceException - if the instance name is unknown or the DBAccess object cannot be created.

registerDBAccessClass

public static void registerDBAccessClass(String pJdbcUrlPrefix,
                                         Class<? extends DBAccess> pClass)
Registers the Class for the jdbc url prefix.

Parameters:
pJdbcUrlPrefix - the jdbc url prefix.
pClass - the Class.

setAutomaticLinkColumnNameTranslation

public static void setAutomaticLinkColumnNameTranslation(TranslationMap pTranslationMap)
Sets the TranslationMap for the automatic link column name custom Translation. its used in the getAutomaticLinkColName(ForeignKey pForeignKey) to change the column, thats determined. Default is *_ID to *, and *_id to *.

Parameters:
pTranslationMap - the TranslationMap to use.

getAutomaticLinkColumnNameTranslation

public static TranslationMap getAutomaticLinkColumnNameTranslation()
Returns the TranslationMap for the automatic link column name custom Translation. its used in the getAutomaticLinkColName(ForeignKey pForeignKey) to change the column, thats determined. Default is *_ID to *, and *_id to *.

Returns:
the TranslationMap for the automatic link column name custom Translation.

getFromClause

protected String getFromClause(String pWriteBackTable,
                               List<ForeignKey> auForeignKeys)
                        throws DataSourceException
It creates and returns a from clause with joins over all foreign tables.

Parameters:
pWriteBackTable - the write back table to use
auForeignKeys - the ForeignKey array to use
Returns:
a from clause with joins over all foreign tables.
Throws:
DataSourceException - if an error occur in determining the from clause and query columns.

getAutomaticLinkColumnName

protected String getAutomaticLinkColumnName(ForeignKey pForeignKey)
Returns the automatic link column name for the specified ForeignKey and FK Column in the master table. It uses the ForeignKey column name, or if more then one column exists, all of it combined with an "_". If the PrimaryKey only exists out of one column and the ForeignKey uses more then one (Unique Key and PrimaryKey columns), then its only the ForeignKey column corresponding to this one PrimaryKey column used. After the AutomaticLinkColumnName is created, the defined TranslationMap is used to change the name.

Parameters:
pForeignKey - the ForeignKEy to use.
Returns:
the automatc link column name for the specified ForeignKey and FK Column in the master table.
See Also:
setAutomaticLinkColumnNameTranslation(TranslationMap)

getQueryColumns

protected String[] getQueryColumns(String pWriteBackTable,
                                   List<ForeignKey> pForeignKeys,
                                   String[] pWriteBackColumns)
                            throws DataSourceException
It creates and returns the query columns, with all write back columns and includes the columns for the LinkReferences (automatic link celleditors). It also removes all intersecting and unused ForeignKeys from the list of ForeignKeys!

Parameters:
pWriteBackTable - the write back table to use
pForeignKeys - the ForeignKey array to use
pWriteBackColumns - the write back table columns to use.
Returns:
the query columns, with all write back columns and includs the columns for the LinkReferences (automatic link celleditors).
Throws:
DataSourceException - if an error occur in determining the from clause and query columns.

getUKs

public List<Key> getUKs(String pCatalog,
                        String pSchema,
                        String pTable)
                 throws DataSourceException
It gets all columns for each Unique Key and return it.

Parameters:
pCatalog - the catalog to use
pSchema - the schema to use
pTable - the table to use
Returns:
all columns for each Unique Key.
Throws:
DataSourceException - if an error occur during UK search process.

getPK

public Key getPK(String pCatalog,
                 String pSchema,
                 String pTable)
          throws DataSourceException
Gets all Primary Key columns and return it as list of Strings.

Parameters:
pCatalog - the catalog to use
pSchema - the schema to use
pTable - the table to use
Returns:
all Primary Key columns and return it as String[].
Throws:
DataSourceException - if an error occur during PK search process.

getFKs

public List<ForeignKey> getFKs(String pCatalog,
                               String pSchema,
                               String pTable)
                        throws DataSourceException
Returns all Foreign Keys for the specified table.

Parameters:
pCatalog - the catalog to use
pSchema - the schema to use
pTable - the table to use as base table.
Returns:
all Foreign Keys for the specified table.
Throws:
DataSourceException - if an error occur in determining the ForeignKeys.

getTableForSynonym

public String getTableForSynonym(String pSynomyn)
                          throws DataSourceException
Returns the full qualified table name incl. schema/catalog/db link for the given synonym. If pSynomyn is no synonym, then the pSynomyn string is returned.

Parameters:
pSynomyn - the synonym to use.
Returns:
the full qualified table name incl. schema/catalog/db link for the given synonym.
Throws:
DataSourceException - if an error occur in determining the synonyms.

fetch

public List<Object[]> fetch(ServerMetaData pServerMetaData,
                            String pBeforeQueryColumns,
                            String[] pQueryColumns,
                            String pFromClause,
                            ICondition pFilter,
                            String pLastWhereCondition,
                            String pAfterWhereClause,
                            SortDefinition pSort,
                            int pFromRow,
                            int pMinimumRowCount)
                     throws DataSourceException
Returns the List of fetched rows (as List of Object[]) for the specified query tables and parameters. It fetch's the the rows from pFromRow row index a minimum amount of pMinimumRowCount rows. Implementations should fetch as much rows as possible in a proper amount of time, to get less requests from the client model to the server IDBAccess. Implementation can cache the select cursor and reuse it for the next fetch operation, but they should take care, that's a state less call and in a fall over case it maybe loose on the fail over system the cursor.

Specified by:
fetch in interface IDBAccess
Parameters:
pServerMetaData - the MetaDataColumn array to use.
pBeforeQueryColumns - the before query columns
pQueryColumns - the query columns
pFromClause - the from clause with query tables and join definitions
pFilter - the filter to use
pLastWhereCondition - the last where condition in query
pAfterWhereClause - the after where clause in query
pSort - the sort order to use
pFromRow - the row index from to fetch
pMinimumRowCount - the minimum count row to fetch
Returns:
the List of fetched rows (as List of Object[]) for the specified query tables and parameters.
Throws:
DataSourceException - if the fetch fails.

lockRow

public void lockRow(String pWriteBackTable,
                    ServerMetaData pServerMetaData,
                    ICondition pPKFilter)
             throws DataSourceException
It locks the specified row in the storage.

Specified by:
lockRow in interface IDBAccess
Parameters:
pWriteBackTable - the storage unit to use
pServerMetaData - the MetaDataColumn array to use.
pPKFilter - the PrimaryKey in as an ICondition to identify the row to lock
Throws:
DataSourceException - if an Exception occur during interacting with the storage

lockRowInternal

protected int lockRowInternal(String pWriteBackTable,
                              ServerMetaData pServerMetaData,
                              ICondition pPKFilter)
                       throws DataSourceException
It locks the current row and return how many rows are affected.

Parameters:
pWriteBackTable - the storage unit to use
pPKFilter - the PrimaryKey in as an ICondition to identify the row to lock
pServerMetaData - the MetaDataColumn array to use.
Returns:
the counts of affected rows
Throws:
DataSourceException - if an Exception occur during interacting with the storage

insert

public Object[] insert(String pWriteBackTable,
                       ServerMetaData pServerMetaData,
                       Object[] pNewDataRow)
                throws DataSourceException
Returns the newly inserted row from the write back table.

Specified by:
insert in interface IDBAccess
Parameters:
pWriteBackTable - the write back table to use.
pServerMetaData - the meta data to use.
pNewDataRow - the new values Object[] to insert.
Returns:
the newly inserted row as Object[] from the write back table.
Throws:
DataSourceException - if an Exception occur during insert the row to the table

update

public Object[] update(String pWriteBackTable,
                       ServerMetaData pServerMetaData,
                       Object[] pOld,
                       Object[] pNew)
                throws DataSourceException
Return the updated row.

Specified by:
update in interface IDBAccess
Parameters:
pWriteBackTable - the write back table to use.
pServerMetaData - the meta data to use.
pOld - the old values of the row
pNew - the new values of the row
Returns:
the updated row.
Throws:
DataSourceException - if an Exception occur during update.

updateDatabaseSpecific

public int updateDatabaseSpecific(String pWriteBackTable,
                                  String sUpdateStatement,
                                  ServerMetaData pServerMetaData,
                                  Object[] pOld,
                                  Object[] pNew,
                                  ICondition pPKFilter)
                           throws DataSourceException
Updates the specified row and return the count of affected rows.
Database specific implementation should override this method to implement the specific update code.

Parameters:
pWriteBackTable - the table to use for the update
sUpdateStatement - the SQL Statement to use for the update
pServerMetaData - the meta data to use.
pOld - the old row (values) to use.
pNew - the new row (values) to use.
pPKFilter - the PrimaryKey equals filter to use.
Returns:
the count of updates rows from a Ansi SQL Database.
Throws:
DataSourceException - if an Exception occur during update to the storage

updateAnsiSQL

public int updateAnsiSQL(String pWriteBackTable,
                         String sUpdateStatement,
                         ServerMetaData pServerMetaData,
                         Object[] pOld,
                         Object[] pNew,
                         ICondition pPKFilter)
                  throws DataSourceException
Updates the specified row and return the count of affected rows.

Parameters:
pWriteBackTable - the table to use for the update
sUpdateStatement - the SQL Statement to use for the update
pServerMetaData - the meta data to use.
pOld - the old row (values) to use.
pNew - the new row (values) to use.
pPKFilter - the PrimaryKey equals filter to use.
Returns:
the count of updates rows from a Ansi SQL Database.
Throws:
DataSourceException - if an Exception occur during update to the storage

delete

public void delete(String pWriteBackTable,
                   ServerMetaData pServerMetaData,
                   Object[] pDelete)
            throws DataSourceException
Deletes the specified row.

Specified by:
delete in interface IDBAccess
Parameters:
pWriteBackTable - the write back table to use.
pServerMetaData - the meta data to use.
pDelete - the row to delete.
Throws:
DataSourceException - if an Exception occur during delete.

toString

public String toString()

Overrides:
toString in class Object

setQuoteCharacters

protected void setQuoteCharacters(String pOpen,
                                  String pClose)
Sets the database specific quote characters.

Parameters:
pOpen - the open quote character
pClose - the close quote character

isAutoQuote

public boolean isAutoQuote(String pName)
It returns true if this name should be automated quoted. e.g. in Oracle default all isUppercase(), so if the name has one loweCase character, then AutoQuote is true to quote this name.

Parameters:
pName - the name to quote.
Returns:
true if this name should be automated quoted.

quote

public String quote(String pName)
Quotes a named DB object with our internal quote character, if it should be quoted in this database. Thats the case if the name != to the default case sensitivness. e.g. in Oracle != upperCase()

Parameters:
pName - the name to use.
Returns:
the quoted name if quoting in this database is necessary or otherwise just the name.

quoteAllways

public String quoteAllways(String pName)
Quotes a named DB object with the JVx DB QUOTE character.

Parameters:
pName - the name to use.
Returns:
the name quoted.

removeQuotes

public static String removeQuotes(String pName)
Removes the JVx DB Quotes of a named DB object.

Parameters:
pName - the name to use.
Returns:
the unquoted name.

removeDBSpecificQuotes

public String removeDBSpecificQuotes(String pName)
Removes the DB specific quotes of a named DB object.

Parameters:
pName - the name to use.
Returns:
the unquoted name.

translateQuotes

public String translateQuotes(String pStatement)
It replaces all JVx quotes with the database specific quote.

Parameters:
pStatement - the statement to use.
Returns:
the database specific quoted statement.

open

public void open()
          throws DataSourceException
It opens the database and stores the Connection object.

Throws:
DataSourceException - if the database couldn't opened

isOpen

public boolean isOpen()
               throws DataSourceException
Returns true, if the database is still open.

Returns:
true, if the database is still open.
Throws:
DataSourceException - if isClosed() on the DB Connection throws an Exception

close

public void close()
           throws DataSourceException
Closes the database Connection and releases all memory.

Throws:
DataSourceException - if database couldn't closed.

rollback

public void rollback()
              throws DataSourceException
Rollback the DB transaction.

Throws:
DataSourceException - if the transaction couldn't rollback

commit

public void commit()
            throws DataSourceException
Commits the DB transaction.

Throws:
DataSourceException - if the transaction couldn't commit

setQueryTimeOut

public void setQueryTimeOut(int pQueryTimeOut)
Sets the query time out.

Parameters:
pQueryTimeOut - the timeout in miliseconds

getConnection

public Connection getConnection()
Returns the conncetion to the database.

Returns:
the conncetion to the database.

setConnection

protected void setConnection(Connection pConnection)
Sets the internal conncetion to the database.

Parameters:
pConnection - the conncetion to the database

getDriver

public String getDriver()
Gets the database driver name as String.

Returns:
pDriverName the database driver name

setDriver

public void setDriver(String pDriver)
Sets the database driver name as String.

Parameters:
pDriver - the database driver name

getUrl

public String getUrl()
Gets the jdbc url String for this database.

Returns:
the jdbc url String.

setUrl

public void setUrl(String pUrl)
Sets the url String for this database.

Parameters:
pUrl - the jdbc url String.

getUsername

public String getUsername()
Gets the user name to connect with.

Returns:
pUser the user name

setUsername

public void setUsername(String pUsername)
Sets the user name to connect with.

Parameters:
pUsername - the user name

getPassword

public String getPassword()
Sets the password to use for the connection to the database.

Returns:
pPassword the password to use for the database

setPassword

public void setPassword(String pPassword)
Sets the password to use for the connection to the database.

Parameters:
pPassword - the password to use for the database

setDBProperty

public void setDBProperty(String pName,
                          String pValue)
Sets a specific database property.

Parameters:
pName - the property name
pValue - th value

getDBProperty

public String getDBProperty(String pName)
Gets the value for a specific database property.

Parameters:
pName - the property name
Returns:
the value or null if the property was not found

setDBProperties

public void setDBProperties(Properties pProperties)
Sets DB specific initial parameters for the Connection creation.

Parameters:
pProperties - DB specific initial parameters

getDBProperties

public Properties getDBProperties()
Returns the DB specific initial parameters for the Connection creation.

Returns:
the DB specific initial parameters for the Connection creation.

getMaxTime

public int getMaxTime()
Returns the maximum time in miliseconds to use, to try to fetch all rows. reduce open cursors, and increase performance.

Returns:
the iMaxTime.

setMaxTime

public void setMaxTime(int pMaxTime)
Sets the maximum time in miliseconds to use, to try to fetch all rows. reduce open cursors, and increase performance.

Parameters:
pMaxTime - the iMaxTime to set

executeProcedure

public void executeProcedure(String pProcedureName,
                             Object... pParameters)
                      throws DataSourceException
Executes a DB procedure with the specified parameters.

Parameters:
pProcedureName - the procedure (optional with package) name.
pParameters - the parameters to use with the correct and corresponding java type.
Throws:
DataSourceException - if the call failed.

executeFunction

public Object executeFunction(String pFunctionName,
                              int pReturnType,
                              Object... pParameters)
                       throws DataSourceException
Executes a DB function with the specified parameters and return the result.

Parameters:
pFunctionName - the function (optional with package) name.
pReturnType - the return SQL Type (see Types
pParameters - the parameters to use with the correct and corresponding java type.
Returns:
the result of the DB function call.
Throws:
DataSourceException - if the call failed.

executeStatement

public void executeStatement(String pStatement)
                      throws SQLException
Executes a DDL or DML Statement.

Parameters:
pStatement - the statement.
Throws:
SQLException - if an error occur during execution.

executeSql

public List<Object> executeSql(String pStatement,
                               Object... pParameters)
                        throws SQLException
Executes a SQL command as prepared statement.

Parameters:
pStatement - the statement with or without parameters
pParameters - the parameters to use
Returns:
the first parameter from the result set, for every row, or null if the command returned no result
Throws:
SQLException - if an error occurs during execution

getPreparedStatement

public PreparedStatement getPreparedStatement(String pSqlStatement,
                                              boolean pReturnKey)
                                       throws DataSourceException
Return a PreparedStatement for the given SQL statement.

Parameters:
pSqlStatement - the SQL statement to prepare
pReturnKey - if the generated key in insert statements should returned
Returns:
a PreparedStatement for the given SQL statement.
Throws:
DataSourceException - if the statement couldn't prepared.

executeUpdate

public int executeUpdate(PreparedStatement pSqlStatement)
                  throws DataSourceException
Calls executesUpdate() for specified PreparedStatement and returns the number of updated rows.

Parameters:
pSqlStatement - the PreparedStatement to use
Returns:
number of updated rows.
Throws:
DataSourceException - if the statement couldn't updated.

formatSQLException

protected SQLException formatSQLException(SQLException pSqlException)
Adds the SQL Error Code into the message of the SQL Exception.

Parameters:
pSqlException - the SQL Exception to use.
Returns:
the SQLException with the modified error message with SQL Error Code.

formatSQLException

protected SQLException formatSQLException(SQLException pSqlException,
                                          String pMessage,
                                          String pCode)
Adds the SQL Error Code into the message of the SQL Exception.

Parameters:
pSqlException - the SQL Exception to use.
pMessage - the message to use
pCode - the detected error code
Returns:
the SQLException with the modified error message with SQL Error Code.

getSelectStatement

public String getSelectStatement(ICondition pFilter,
                                 String pFromClause,
                                 String[] pQueryColumns,
                                 String pBeforeQueryColumns,
                                 String pWhereClause,
                                 String pAfterWhereClause,
                                 ServerMetaData pServerMetaData)
                          throws DataSourceException
It initialize the select for a specified storage unit and return the SELECT statement.
It doesn't add the ORDER BY clause.

Parameters:
pFilter - the Filter to use
pFromClause - the list of query tables to use in the SELECT statement.
pQueryColumns - the list of query columns to use in the SELECT statement.
pBeforeQueryColumns - the string to place in the SELECT statement between the SELECT and the first query column.
pWhereClause - the string to place in the SELECT statement after the last WHERE condition from the Filter or MasterReference (Master-Detail Condition).
pAfterWhereClause - the string to place in the SELECT statement after the WHERE clause and before the ORDER BY clause.
pServerMetaData - the MetaDataColumn array to use.
Returns:
the SELECT statement as String.
Throws:
DataSourceException - if it's not possible to build the select statement in fact of missing elements

getDatabaseSpecificLockStatement

public String getDatabaseSpecificLockStatement(String pWriteBackTable,
                                               ServerMetaData pServerMetaData,
                                               ICondition pPKFilter)
                                        throws DataSourceException
Returns the database specific statement to lock the specified row in the database.

Parameters:
pWriteBackTable - the table to use.
pPKFilter - the PK filter with the values to use.
pServerMetaData - the MetaDataColumn array to use.
Returns:
the database specific statement to lock the specified row in the database.
Throws:
DataSourceException - if some parts are missing for the statement

insertDatabaseSpecific

public Object[] insertDatabaseSpecific(String pWriteBackTable,
                                       String pInsertStatement,
                                       ServerMetaData pServerMetaData,
                                       Object[] pNewDataRow,
                                       String pDummyColumn)
                                throws DataSourceException
Returns the newly inserted row from an Database specific insert statement.
Database specific derivations of DBAcces need to implement it database specific.

Parameters:
pWriteBackTable - the table to use for the insert
pInsertStatement - the SQL Statement to use for the insert
pServerMetaData - the meta data to use.
pNewDataRow - the new row (Object[]) with the values to insert
pDummyColumn - null, if all writeable columns are null, but for a correct INSERT it have to be minimum one column to use in the syntax.
Returns:
the newly inserted row from an Oracle Database.
Throws:
DataSourceException - if an Exception occur during insert to the storage

supportsGetGeneratedKeys

public boolean supportsGetGeneratedKeys()
Returns if this Database specific supports generated keys. Because of Derby bug!

Returns:
if this Database specific supports generated keys.

insertAnsiSQL

public Object[] insertAnsiSQL(String pWriteBackTable,
                              String pInsertStatement,
                              ServerMetaData pServerMetaData,
                              Object[] pNewDataRow,
                              String pDummyColumn)
                       throws DataSourceException
Returns the newly inserted row from a Ansi SQL Database.
It uses getGeneratedKeys to get the primary key values back from the database. Its recommend that the jdbc driver of the database support that clean.

Parameters:
pWriteBackTable - the table to use for the insert
pInsertStatement - the SQL Statement to use for the insert
pServerMetaData - the meta data to use.
pNewDataRow - the new row (Object[]) with the values to insert
pDummyColumn - true, if all writeable columns are null, but for a correct INSERT it have to be minimum one column to use in the syntax.
Returns:
the newly inserted row from a Ansi SQL Database.
Throws:
DataSourceException - if an Exception occur during insert to the storage

getColumnMetaData

public DBAccess.ColumnMetaDataInfo getColumnMetaData(String pFromClause,
                                                     String[] pQueryColumns,
                                                     String pBeforeQueryColumns,
                                                     String pWhereClause,
                                                     String pAfterWhereClause,
                                                     String pWriteBackTable,
                                                     String[] pWriteBackColumns)
                                              throws DataSourceException
Returns the meta data information for the specified query, and configures all columns with defaults.

Parameters:
pBeforeQueryColumns - the before query columns
pQueryColumns - the query columns
pFromClause - the from clause with query tables and join definitions
pWhereClause - the last where condition in query
pAfterWhereClause - the after where clause in query
pWriteBackTable - the write back table to use for the isWriteable() state (Optional)
pWriteBackColumns - the write back columns to use for the isWriteable() state (Optional)
Returns:
the meta data for the specified query, and initials all columns.
Throws:
DataSourceException - if an Exception occur during getting the meta data or if the storage is not opened or if one columns SQL type is not supported

getWHEREClause

public String getWHEREClause(ICondition pFilter,
                             ServerMetaData pServerMetaData,
                             boolean pUsePrefix)
                      throws DataSourceException
Returns the WHERE clause for a UPDATE, DELETE or SELECT statement specified with a Filter.

Parameters:
pFilter - the Filter to use
pServerMetaData - the MetaData to use
pUsePrefix - true to use the prefixed column (real column name) and false to ignore prefixes (the simple name of the column)
Returns:
the WHERE clause for a UPDATE, DELETE or SELECT statement specified with a Filter.
Throws:
DataSourceException - if columns not existing

setFilterParameter

protected void setFilterParameter(int iStartParameterIndex,
                                  PreparedStatement pStatement,
                                  ICondition pFilter)
                           throws DataSourceException
Sets all Filter parameter values into the PreparedStatement.

Parameters:
iStartParameterIndex - the start index for the parameters to set.
pStatement - the PreparedStatement to initialize
pFilter - the Filter to get the values
Throws:
DataSourceException - if the values can't set into the PreparedStatement

setColumnsToStore

protected int setColumnsToStore(PreparedStatement pInsert,
                                ServerColumnMetaData[] pServerColumnMetaData,
                                int[] iaWriteables,
                                Object[] pNew,
                                Object[] pOld)
                         throws DataSourceException
Sets the values of all changed columns to store from the value Object[]s into the PreparedStatement and returns the last used parameter index.

Parameters:
pInsert - the PreparedStatement to initialize
pServerColumnMetaData - the column meta data to use.
iaWriteables - the writable columns as int index array
pNew - the new values Object[]
pOld - the old values Object[]
Returns:
the last used parameter index of the PreparedStatement.
Throws:
DataSourceException - if the values can't set into the PreparedStatement

getDefaultValues

public Hashtable<String,Object> getDefaultValues(String pCatalog,
                                                 String pSchema,
                                                 String pTable)
                                          throws DataSourceException
Gets all default column values of a specific table.

Parameters:
pCatalog - the catalog name
pSchema - the schema name
pTable - the table name
Returns:
a Hashtable with the column name as key and the default value as value. It only contains columns with a default value
Throws:
DataSourceException - if the database access throws an exception

translateDefaultValue

protected Object translateDefaultValue(String pColumnName,
                                       int pDataType,
                                       String pDefaultValue)
                                throws Exception
Translates a default value from a column to the datatype object.

Parameters:
pColumnName - the column name to translate
pDataType - the datatype of the column
pDefaultValue - the original default value from the database
Returns:
the default value with the datatype of the column or null if the default value is not valid
Throws:
Exception - if the type translation causes an error or the datatype is not supported

translateValue

protected Object translateValue(int pDataType,
                                String pValue)
                         throws Exception
Translates an object value to the datatype object.

Parameters:
pDataType - the datatype of the column
pValue - the value from the database
Returns:
the value with the specified datatype or null if the value is not valid
Throws:
Exception - if the type translation causes an error or the datatype is not supported

getAllowedValues

public Hashtable<String,Object[]> getAllowedValues(String pCatalog,
                                                   String pSchema,
                                                   String pTable)
                                            throws DataSourceException
Gets the allowed values from a specific table. The allowed values are defined through check constraints or other table related restrictions.

Parameters:
pCatalog - the catalog name
pSchema - the schema name
pTable - the table to check
Returns:
a Hashtable with a column name as key and the allowed values as array of Objects or null if there are no allowed values
Throws:
DataSourceException - if the database access throws an exception

getDefaultAllowedValues

public Object[] getDefaultAllowedValues(String pCatalog,
                                        String pSchema,
                                        String pTable,
                                        ServerColumnMetaData pMetaData)
Gets the default allowed values for a given column from a given table. This method will be called if no other default values for the given column are available.

Parameters:
pCatalog - the catalog name
pSchema - the schema name
pTable - the table to check
pMetaData - the column meta data
Returns:
the default allowed values or null if there are no default values

createReplace

protected String createReplace(String pSource,
                               String pOld,
                               String pNew)
Create an DB specific replace command, which replacs in the pSource all pOld to pNew. This implementation use the ANSI SQL REPLACE command.

Parameters:
pSource - the source to replace.
pOld - the old value.
pNew - the new value.
Returns:
the SQL command to to this.

createWhereParam

protected String createWhereParam(ServerMetaData pServerMetaData,
                                  CompareCondition pCompare)
Creates the where parameter. That is normally a single question mark, but it depends on the database if conversions are needed.

Parameters:
pServerMetaData - the server metadata
pCompare - the compare condition
Returns:
the parameter representation for where clause

createWhereColumn

protected String createWhereColumn(ServerMetaData pServerMetaData,
                                   CompareCondition pCompare,
                                   String pColumnName)
Creates the where column. That is normally just the column name, but it depends on the database if conversions are needed.

Parameters:
pServerMetaData - the server metadata
pCompare - the compare condition
pColumnName - the column name to use
Returns:
the column name representation for where clause

isTypeEqual

public boolean isTypeEqual(ServerColumnMetaData pServerColumnMetaData,
                           CompareCondition pCompare)
Check if the Type of the column and the value to compare is equal.

Parameters:
pServerColumnMetaData - the server column meta data for the column to compare.
pCompare - the compare condition to use.
Returns:
false if the type isn't equal.

splitSchemaTable

protected String[] splitSchemaTable(String pFromClause)
Separates the schema and table from the given from clause. The separation is only possible if the from clause is simple, e.g. it does not contain any quote characters and does not contain separators (',').

Parameters:
pFromClause - the from clause
Returns:
[0]...schema, [1]...table

setDefaultSchema

public void setDefaultSchema(String pSchema)
Sets the user-defined default schema.

Parameters:
pSchema - the schema name
See Also:
getDefaultSchema()

getDefaultSchema

public String getDefaultSchema()
Gets the default schema name, if it was set manually.

Returns:
the user-defined default schema name
See Also:
setDefaultSchema(String)

convertDatabaseSpecificObjectToValue

protected Object convertDatabaseSpecificObjectToValue(ServerColumnMetaData pColumnMetaData,
                                                      Object pObject)
Enables the database specific implementation to handle/convert special objects. Some databases have datatypes that are not defined in the standard. This datatypes have specific classes in the JDBC drivers. With this method it is possible to convert the values of specific JDBC driver classes into usable objects. We can not send any JDBC object to the client!

Parameters:
pColumnMetaData - the column metadata
pObject - the read object
Returns:
the value to use

convertValueToDatabaseSpecificObject

protected Object convertValueToDatabaseSpecificObject(Object pValue)
Converts an object to a standard value for the specific database. Not all values are supported from the underlying database, e.g. java.sql.Timestamp is preferred instead of java.util.Date.

Parameters:
pValue - any value
Returns:
the database specific value

setDatabaseSpecificType

protected boolean setDatabaseSpecificType(ResultSetMetaData pMetaData,
                                          int pColumnIndex,
                                          ServerColumnMetaData pColumnMetaData)
                                   throws SQLException
Enables the database specific implementation to change the metadata before the default settings are made.

Parameters:
pMetaData - the metadata from the resultset
pColumnIndex - the column index in the resultset metadata
pColumnMetaData - the newly created and pre-configured column metadata
Returns:
true if column metadata are changed and the default settings should not be applied, false to apply the standard settings
Throws:
SQLException - if metadata access fails
Overview  Package   Class  Use  Tree  Deprecated  Index  Help 
 PREV CLASS   NEXT CLASS FRAMES    NO FRAMES    
SUMMARY: NESTED | FIELD | CONSTR | METHOD DETAIL: FIELD | CONSTR | METHOD
Copyright © 2009 SIB Visions GmbH. All Rights Reserved.