org.opensubsystems.core.persist.db.mysql
Class MySQLDatabaseImpl

java.lang.Object
  org.opensubsystems.core.persist.db.DatabaseImpl
      org.opensubsystems.core.persist.db.mysql.MySQLDatabaseImpl

All Implemented Interfaces:

Database

public class MySQLDatabaseImpl
extends DatabaseImpl

Management layer for My SQL database (www.mysql.com) TODO: Improve: Consider storing all data in UTF-8 See http://uwstopia.nl/blog/2007/01/simple-utf-8-and-mysql-how-to - Make sure you append CHARACTER SET 'UTF8' to each CREATE TABLE statement. Example: CREATE TABLE test (id SERIAL PRIMARY KEY, somefield VARCHAR(4)) CHARACTER SET 'UTF8';. This can be done at the database level as well (when using CREATE DATABASE), but most of the times this is done for you by an external party (hoster, sysadmins, ...) - Issue the following query immediately after you established a database connection (most likely somewhere in your initialization routines): SET NAMES='UTF8'

Version:

$Id: MySQLDatabaseImpl.java,v 1.25 2007/01/24 06:12:31 bastafidli Exp $

Author:

Julo Legeny

Code reviewer:

Miro Halas

Code reviewed:

1.20 2006/04/05 05:03:08 bastafidli

Field Summary

Fields inherited from class org.opensubsystems.core.persist.db.DatabaseImpl

m_bDatabaseSchemaInitialized, m_bDatabaseStarted, m_bDatabaseStartInProgress, m_vdsSchema, MAX_SAFE_LENGTH

Fields inherited from interface org.opensubsystems.core.persist.db.Database

BATCH_ITERATOR, DB2_DATABASE_TYPE, DB2_DATABASE_TYPE_IDENTIFIER, HSQLDB_DATABASE_TYPE, HSQLDB_DATABASE_TYPE_IDENTIFIER, MAXDB_DATABASE_TYPE, MAXDB_DATABASE_TYPE_IDENTIFIER, MSSQL_DATABASE_TYPE, MSSQL_DATABASE_TYPE_IDENTIFIER, MYSQL_DATABASE_TYPE, MYSQL_DATABASE_TYPE_IDENTIFIER, ORACLE_DATABASE_TYPE, ORACLE_DATABASE_TYPE_IDENTIFIER, POSTGRESQL_DATABASE_TYPE, POSTGRESQL_DATABASE_TYPE_IDENTIFIER, SAPDB_DATABASE_TYPE, SAPDB_DATABASE_TYPE_IDENTIFIER, SYBASE_DATABASE_TYPE, SYBASE_DATABASE_TYPE_IDENTIFIER

Constructor Summary

MySQLDatabaseImpl()
Default constructor for empty database.

Method Summary

void	createDatabaseInstance() Method for creating database instance.
protected void	createUser(java.sql.Connection cntAdminDBConnection) Create database user which will be used by connection pool to access the database.
java.lang.String	getCurrentTimestampFunctionCall() Get string which can be used in SQL queries to retrieve timestamp representing current time.
int	getDatabaseType() Returns the type of database currently in use.
java.lang.String	getDatabaseTypeIdentifier() Returns the identifier for the type of database currently in use which can be used to construct package and class names.
java.lang.Object[]	getSQLAnalyzeFunctionCall(java.util.Map mapTableNames) Get DB specific SQL queries for analyzing tables or updating statistics on the tables (it is good for performance).
java.lang.String	getSQLCountFunctionCall() Get string which can be used in SQL queries to retrieve record count.
boolean	hasSelectListRangeSupport() Find out if database allows us to support rows limitation. This means that database has to provide a way how to construct EFFICIENT SQL which allows us to retrieve items starting from row X and ending at row Y.
void	insertAndFetchGeneratedValues(java.sql.Connection dbConnection, java.sql.PreparedStatement insertStatement, boolean bIsInDomain, java.lang.String strTableName, int iIndex, BasicDataObject data) Insert the data, fetch from the database id and generated creation and optionally modification timestamps for the newly created data object.
boolean	isCallableStatement(java.lang.String strQuery) Test if the specified query invokes stored procedure or if it is just a regular prepared statement.
boolean	preferCountToLast() Find out if when trying to find out size of the result set we should use rather count(*)/count(1)/count(id) instead of using hasAbsolutePositioningSupport.
void	startDatabaseServer() Method for starting database instance.
void	stop() Stop the database. After the database is stopped, no more requests can be issued until the database is started again.
void	updatedAndFetchGeneratedValues(java.lang.String strDataName, java.sql.Connection dbConnection, java.sql.PreparedStatement updateStatement, boolean bIsInDomain, java.lang.String strTableName, int iIndex, ModifiableDataObject data) Update the data, check for errors and fetch from the database generated modification timestamps for the updated data object.

Methods inherited from class org.opensubsystems.core.persist.db.DatabaseImpl

add, add, getAdminConnection, getConnectionTestStatement, getInstance, getInstanceIfStarted, getSelectListResultSetConcurrency, getSelectListResultSetType, getTransactionIsolation, hasAbsolutePositioningSupport, isStarted, setInstance, start

Methods inherited from class java.lang.Object

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

Constructor Detail

MySQLDatabaseImpl

public MySQLDatabaseImpl()
                  throws OSSException

Default constructor for empty database.

Throws:

OSSException - - problem connecting to database

Method Detail

createUser

protected void createUser(java.sql.Connection cntAdminDBConnection)
                   throws OSSException

Create database user which will be used by connection pool to access the database.

Specified by:

createUser in class DatabaseImpl

Parameters:

cntAdminDBConnection - - connection with rights to create users

Throws:

OSSException - - cannot create user

startDatabaseServer

public void startDatabaseServer()
                         throws OSSException

Method for starting database instance. For HSQL it is not needed because the database is started (created) when the administrator is connected to. For HSQL will be empty implementation of this method. For SAPDB is neccessary to start DB instance before connecting to.

Specified by:

startDatabaseServer in class DatabaseImpl

Throws:

OSSException - - problem starting the server

createDatabaseInstance

public void createDatabaseInstance()
                            throws OSSException

Method for creating database instance. For HSQL it is not needed because the database is created when the administrator is connected to. For HSQL will be empty implementation of this method. For SAPDB is neccessary to create DB instance before connecting to.

Specified by:

createDatabaseInstance in class DatabaseImpl

Throws:

OSSException - - problem creating the database instance

stop

public void stop()
          throws OSSException

Stop the database. After the database is stopped, no more requests can be issued until the database is started again.

Specified by:

stop in interface Database

Overrides:

stop in class DatabaseImpl

Throws:

OSSException - - problems stopping the database

getDatabaseType

public int getDatabaseType()

Returns the type of database currently in use. It will be one of the constants defined in this class (SAPDB, HSQLDB, etc.).

Returns:

int - database type

getDatabaseTypeIdentifier

public java.lang.String getDatabaseTypeIdentifier()

Returns the identifier for the type of database currently in use which can be used to construct package and class names. It will be one of the constants defined in this class (SAPDB, HSQLDB, etc.).

Returns:

String - database type

preferCountToLast

public boolean preferCountToLast()

Find out if when trying to find out size of the result set we should use rather count(*)/count(1)/count(id) instead of using hasAbsolutePositioningSupport.

Specified by:

preferCountToLast in interface Database

Overrides:

preferCountToLast in class DatabaseImpl

Returns:

boolean - true if should use count(x) instead of last()

getCurrentTimestampFunctionCall

public java.lang.String getCurrentTimestampFunctionCall()

Get string which can be used in SQL queries to retrieve timestamp representing current time.

Returns:

String - SQL representation of function call to get current timestamp

getSQLCountFunctionCall

public java.lang.String getSQLCountFunctionCall()

Get string which can be used in SQL queries to retrieve record count.

Returns:

String - SQL representation of function call to get record count

hasSelectListRangeSupport

public boolean hasSelectListRangeSupport()

Find out if database allows us to support rows limitation. This means that database has to provide a way how to construct EFFICIENT SQL which allows us to retrieve items starting from row X and ending at row Y.

Specified by:

hasSelectListRangeSupport in interface Database

Overrides:

hasSelectListRangeSupport in class DatabaseImpl

Returns:

boolean - true if it is possible to support row limitation

getSQLAnalyzeFunctionCall

public java.lang.Object[] getSQLAnalyzeFunctionCall(java.util.Map mapTableNames)

Get DB specific SQL queries for analyzing tables or updating statistics on the tables (it is good for performance).

Parameters:

mapTableNames - - map of table names the update statistics will be processed on. Key is the data object data of which are stored in the table and value is te name of the table

Returns:

Object[] - index 0 - String[] - SQL representation of function calls to analyze tables and indexes (array of SQL commands for each table the update statistics will be processed) - index 1 - Boolean flag signaling if the autocommit when executing these statements should be true or false

isCallableStatement

public boolean isCallableStatement(java.lang.String strQuery)

Test if the specified query invokes stored procedure or if it is just a regular prepared statement.

Parameters:

strQuery - - query to test

Returns:

boolean - true if query invokes stored procedure false otherwise

insertAndFetchGeneratedValues

public void insertAndFetchGeneratedValues(java.sql.Connection dbConnection,
                                          java.sql.PreparedStatement insertStatement,
                                          boolean bIsInDomain,
                                          java.lang.String strTableName,
                                          int iIndex,
                                          BasicDataObject data)
                                   throws java.sql.SQLException,
                                          OSSException

Insert the data, fetch from the database id and generated creation and optionally modification timestamps for the newly created data object. Note: Since the caller created the prepared (or callable) statement, the caller is responsible for its closing.

Parameters:

dbConnection - - connection to use to access the database

insertStatement - - statement used to insert the data. This can be CallableStatement if stored procedure is used.

bIsInDomain - - are the data objects maintained in domains

strTableName - - name of the table where the data are being inserted

iIndex - - 1 based index of the next parameter value of which can be set on the statement (last parameter set by caller + 1)

data - - data object to update with the fetched values

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

updatedAndFetchGeneratedValues

public void updatedAndFetchGeneratedValues(java.lang.String strDataName,
                                           java.sql.Connection dbConnection,
                                           java.sql.PreparedStatement updateStatement,
                                           boolean bIsInDomain,
                                           java.lang.String strTableName,
                                           int iIndex,
                                           ModifiableDataObject data)
                                    throws java.sql.SQLException,
                                           OSSException

Update the data, check for errors and fetch from the database generated modification timestamps for the updated data object. Note: Since the caller created the prepared statement, the caller is responsible for its closing.

Parameters:

strDataName - - name of the data object

dbConnection - - connection to use to access the datavase

updateStatement - - statement to update data in the database. This can be CallableStatement if stored procedure is used.

bIsInDomain - - are the data objects maintained in domains

strTableName - - name of the table

iIndex - - 1 based index of the next parameter value of which can be set on the statement (last parameter set by caller + 1)

data - - data object to update

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured