IBM WebSphere Application ServerTM
Release 7

com.ibm.ws.extensionhelper
Interface DatabaseHelper


public interface DatabaseHelper

The DatabaseHelper is a simple utility interface for looking-up a datasource, retrieving a connection and performing SQL statement retrieval based on key replacements.

Use the ExtensionHelper.getDatabaseHelper(java.util.Map) method to retrieve an instance of this interface.

Since:
5.0.2

Field Summary
static java.lang.Integer AUTH_APPLICATION
          Resource authorization type: Application
static java.lang.Integer AUTH_CONTAINER
          Resource authorization type: Container
static java.lang.String DBTYPE_DB2
          DB2 Database on a Non i-series or z-series platform
static java.lang.String DBTYPE_DB2ISERIES
          DB2 Database on a i-series
static java.lang.String DBTYPE_DB2j
          Deprecated. Please switch to the Derby database and use DBTYPE_DERBY instead.
static java.lang.String DBTYPE_DB2ZSERIES
          DB2 Database on a z-series
static java.lang.String DBTYPE_DERBY
          Derby Database
static java.lang.String DBTYPE_INFORMIX
          Informix Database
static java.lang.String DBTYPE_INFORMIX7
          Deprecated. Informix version 7 is not supported.
static java.lang.String DBTYPE_MSSQL
          Microsoft SQL 2000 (or higher) Database
static java.lang.String DBTYPE_MSSQL7
          Deprecated. Microsoft SQL Server version 7 is not supported.
static java.lang.String DBTYPE_ORACLE
          Oracle Database
static java.lang.String DBTYPE_ORACLETHICK
          Oracle Database using Native Driver
static java.lang.String DBTYPE_SYBASE
          Sybase Database
static java.lang.String KEY_DATASOURCE_CLASSNAME
          The key to the MAP to specify the implementation class name of the datasource.
static java.lang.String KEY_DATASOURCE_CLASSPATH
          Key for the Map to specify the classpath to the database implementation classes.
static java.lang.String KEY_DATASOURCE_JNDI_NAME
          Key for Data Source JNDI Name to lookup.
static java.lang.String KEY_DATASOURCE_PASSWORD
          Key for password to use with user name in a getConnection call.
static java.lang.String KEY_DATASOURCE_PROPERTIES
          Key for the Map to specify a properties object which contains additional, database implmentation specific settings for the database driver.
static java.lang.String KEY_DATASOURCE_USERNAME
          Key for user name to use for a getConnection call.
static java.lang.String KEY_REPLACEMENT_MAP
          Key for Map to use for replacing various strings in SQL statements, depending on database.
static java.lang.String KEY_STATEMENTS_FILE
          Key for file name to load SQL Statements from.
static java.lang.String KEY_TABLE_PREFIX
          Key for table prefix to use.
static java.lang.String RESREF_AUTH
          Key for type of authorization to use for Resource.
static java.lang.String RESREF_SHARING
          Key for type of Resource Sharing to use.
static java.lang.String RESREF_TRANSACTION
          Key for type of transaction to use.
static java.lang.Integer SHARING_SHAREABLE
          Resource sharing type: Shareable
static java.lang.Integer SHARING_UNSHAREABLE
          Resource sharing type: Unshareable
static java.lang.Integer TRANSACTION_NONE
          Resource transaction isolation level: None
static java.lang.Integer TRANSACTION_READ_COMMITTED
          Resource transaction isolation level: Comitted Read
static java.lang.Integer TRANSACTION_READ_UNCOMMITTED
          Resource transaction isolation level: Uncommitted Read
static java.lang.Integer TRANSACTION_REPEATABLE_READ
          Resource transaction isolation level: Repeatable Read
static java.lang.Integer TRANSACTION_SERIALIZABLE
          Resource transaction isolation level: Serializable
static java.lang.Integer TRANSACTION_SNAPSHOT
          Resource transaction isolation level: Snapshot (Microsoft SQL Server 2005 & up only).
static java.lang.Integer TRANSACTION_SS_SNAPSHOT
          Resource transaction isolation level: Snapshot (Microsoft SQL Server 2005 & up only).
 
Method Summary
 void dumpNestedSQLException(java.sql.SQLException sqle)
          Utility function to Print a SQLException and any nested exceptions
 void dumpSQLExceptionInfo(java.lang.Throwable e)
          Utility function to Print a SQLException and any nested exceptions
 boolean existsTable(java.lang.String tableName)
          Check whether the table name exists in current database.
 boolean existsTable(java.lang.String tableName, java.sql.Connection con)
          Check whether the table name exists in current database If the table exists, return true.
 java.sql.Connection getConnection()
          Get a connection from Database
 java.sql.Connection getConnection(TxHandle txHandle)
          Creates a connection to the database using a specific txHandle.
 java.lang.String getDatabaseType()
          Get Type of Database in use.
 javax.sql.DataSource getDataSource()
          Returns current Datasource in use
 java.lang.String getRealTableName(java.lang.String tableKey)
          Returns table name this appeds table prefix to provided table key.
 java.lang.String getSQLStatement(java.lang.String statementType, java.lang.String tableKey)
          Returns a sql statement loaded from statements file using the parameter as hints.
 java.lang.String getSQLStatement(java.lang.String statementType, java.lang.String tableKey, java.lang.String databaseType)
          Returns a sql statement loaded from statements file using the parameter as hints
 java.lang.String getTablePrefixName()
          Return the table prefix name.
 TransactionControl getTransactionControl()
          Retrieve the TransactionControl associated with this DatabaseHelper.
 void returnConnection(java.sql.Connection con)
          Return Connection.
 

Field Detail

RESREF_AUTH

static final java.lang.String RESREF_AUTH
Key for type of authorization to use for Resource. This is optional. The default value is AUTH_APPLICATION.

See Also:
Constant Field Values

RESREF_SHARING

static final java.lang.String RESREF_SHARING
Key for type of Resource Sharing to use. This is optional. The default value is SHARING_SHAREABLE.

See Also:
Constant Field Values

RESREF_TRANSACTION

static final java.lang.String RESREF_TRANSACTION
Key for type of transaction to use. This is optional. The default value is TRANSACTION_READ_COMMITTED.

See Also:
Constant Field Values

AUTH_APPLICATION

static final java.lang.Integer AUTH_APPLICATION
Resource authorization type: Application


AUTH_CONTAINER

static final java.lang.Integer AUTH_CONTAINER
Resource authorization type: Container


SHARING_SHAREABLE

static final java.lang.Integer SHARING_SHAREABLE
Resource sharing type: Shareable


SHARING_UNSHAREABLE

static final java.lang.Integer SHARING_UNSHAREABLE
Resource sharing type: Unshareable


TRANSACTION_NONE

static final java.lang.Integer TRANSACTION_NONE
Resource transaction isolation level: None


TRANSACTION_READ_UNCOMMITTED

static final java.lang.Integer TRANSACTION_READ_UNCOMMITTED
Resource transaction isolation level: Uncommitted Read


TRANSACTION_READ_COMMITTED

static final java.lang.Integer TRANSACTION_READ_COMMITTED
Resource transaction isolation level: Comitted Read


TRANSACTION_REPEATABLE_READ

static final java.lang.Integer TRANSACTION_REPEATABLE_READ
Resource transaction isolation level: Repeatable Read


TRANSACTION_SERIALIZABLE

static final java.lang.Integer TRANSACTION_SERIALIZABLE
Resource transaction isolation level: Serializable


TRANSACTION_SNAPSHOT

static final java.lang.Integer TRANSACTION_SNAPSHOT

Resource transaction isolation level: Snapshot (Microsoft SQL Server 2005 & up only). The value of 16 is taken from the DataDirect Connect JDBC driver constant,

   com.ddtek.jdbc.extensions.ExtConstants.TRANSACTION_SNAPSHOT
 


TRANSACTION_SS_SNAPSHOT

static final java.lang.Integer TRANSACTION_SS_SNAPSHOT

Resource transaction isolation level: Snapshot (Microsoft SQL Server 2005 & up only). The value of 4096 is taken from the Microsoft SQL Server JDBC driver constant,

   com.microsoft.sqlserver.jdbc.SQLServerConnection.TRANSACTION_SNAPSHOT
 


DBTYPE_DB2

static final java.lang.String DBTYPE_DB2
DB2 Database on a Non i-series or z-series platform

See Also:
Constant Field Values

DBTYPE_DB2ISERIES

static final java.lang.String DBTYPE_DB2ISERIES
DB2 Database on a i-series

See Also:
Constant Field Values

DBTYPE_DB2ZSERIES

static final java.lang.String DBTYPE_DB2ZSERIES
DB2 Database on a z-series

See Also:
Constant Field Values

DBTYPE_DB2j

static final java.lang.String DBTYPE_DB2j
Deprecated. Please switch to the Derby database and use DBTYPE_DERBY instead.
CloudScape Database

See Also:
Constant Field Values

DBTYPE_DERBY

static final java.lang.String DBTYPE_DERBY
Derby Database

See Also:
Constant Field Values

DBTYPE_ORACLE

static final java.lang.String DBTYPE_ORACLE
Oracle Database

See Also:
Constant Field Values

DBTYPE_ORACLETHICK

static final java.lang.String DBTYPE_ORACLETHICK
Oracle Database using Native Driver

See Also:
Constant Field Values

DBTYPE_SYBASE

static final java.lang.String DBTYPE_SYBASE
Sybase Database

See Also:
Constant Field Values

DBTYPE_MSSQL

static final java.lang.String DBTYPE_MSSQL
Microsoft SQL 2000 (or higher) Database

See Also:
Constant Field Values

DBTYPE_MSSQL7

static final java.lang.String DBTYPE_MSSQL7
Deprecated. Microsoft SQL Server version 7 is not supported.
Microsoft SQL 7 Database

See Also:
Constant Field Values

DBTYPE_INFORMIX

static final java.lang.String DBTYPE_INFORMIX
Informix Database

See Also:
Constant Field Values

DBTYPE_INFORMIX7

static final java.lang.String DBTYPE_INFORMIX7
Deprecated. Informix version 7 is not supported.
Informix 7 Database

See Also:
Constant Field Values

KEY_DATASOURCE_JNDI_NAME

static final java.lang.String KEY_DATASOURCE_JNDI_NAME
Key for Data Source JNDI Name to lookup. This is required.

See Also:
Constant Field Values

KEY_DATASOURCE_USERNAME

static final java.lang.String KEY_DATASOURCE_USERNAME
Key for user name to use for a getConnection call. This is optional.

See Also:
Constant Field Values

KEY_DATASOURCE_PASSWORD

static final java.lang.String KEY_DATASOURCE_PASSWORD
Key for password to use with user name in a getConnection call. This is optional.

See Also:
Constant Field Values

KEY_STATEMENTS_FILE

static final java.lang.String KEY_STATEMENTS_FILE
Key for file name to load SQL Statements from. This is optional.

Provides the name of a file to store SQL statements in. This file is a standard properties file. The format of this file is: <TABLE_KEY><DBTYPE><STATEMENT_TYPE>

 PROJECT1_DEFAULT_FINDBYPRIMARYKEY= SELECT * FROM <table_prefix>PRJ1 WHERE 
 
 PROJECT1_DEFAULT_FINDBYPKRANGE=    SELECT * FROM <table_prefix>PRJ1 WHERE PK BETWEEN ? AND ?
 PROJECT1_INFORMIX7_FINDBYPKRANGE=  SELECT * FROM <table_prefix>PRJ1 WHERE PK BETWEEN  AND 
 

To retrieve the digested version of the SQL string, use the following syntax: databaseHelper.getSQLStatement("FINDPKRANGE", "PROJECT1");

This will automatically use the INFORMIX7 SQL string if the defined DatabaseHelper instance has a databsase type of DBTYPE_INFORMIX7

See Also:
Constant Field Values

KEY_TABLE_PREFIX

static final java.lang.String KEY_TABLE_PREFIX
Key for table prefix to use. This is optional.

If the SQL Statements have a <table_prefix> string, it is replaced with the value associated with this key.
For example:

The resulting SQL statement will be "SELECT ID FROM TESTTBL"

See Also:
Constant Field Values

KEY_REPLACEMENT_MAP

static final java.lang.String KEY_REPLACEMENT_MAP
Key for Map to use for replacing various strings in SQL statements, depending on database. This is optional.

For example: If you have a list of strings to be replaced for the supplied SQL statements:
Map replMap = new HashMap(); replMap.put("", "GREEN"); replMap.put("", "SQUARE"); Map dbHelperMap = new HashMap(); dbHelperMap.put(REPLACEMENT_MAP_KEY, replMap);

See Also:
Constant Field Values

KEY_DATASOURCE_CLASSPATH

static final java.lang.String KEY_DATASOURCE_CLASSPATH
Key for the Map to specify the classpath to the database implementation classes. This key is optional and only used when KEY_DATASOURCE_CLASSNAME is specified.

See Also:
Constant Field Values

KEY_DATASOURCE_CLASSNAME

static final java.lang.String KEY_DATASOURCE_CLASSNAME
The key to the MAP to specify the implementation class name of the datasource. When specified, the database helper code will connect directly to the database using this implmentation class. If this is not specified, then the database helper uses the JNDI name and looks up the datasource in JNDI.

See Also:
Constant Field Values

KEY_DATASOURCE_PROPERTIES

static final java.lang.String KEY_DATASOURCE_PROPERTIES
Key for the Map to specify a properties object which contains additional, database implmentation specific settings for the database driver. This key is optional and is only used when KEY_DATA_SOURCE_CLASSNAME is specifed.

See Also:
Constant Field Values
Method Detail

getDatabaseType

java.lang.String getDatabaseType()
Get Type of Database in use. See the DBTYPE_ constants within this interface for the possible values here.

Returns:
Current Database type

getConnection

java.sql.Connection getConnection()
                                  throws java.sql.SQLException
Get a connection from Database

Returns:
JDBC Connection
Throws:
java.sql.SQLException - If a exception occurs during getConnection

returnConnection

void returnConnection(java.sql.Connection con)
                      throws java.sql.SQLException
Return Connection.

Parameters:
con - JDBC Connection in use to return.
Throws:
java.sql.SQLException - If a Exception occurs during

getRealTableName

java.lang.String getRealTableName(java.lang.String tableKey)
Returns table name this appeds table prefix to provided table key.

Parameters:
tableKey - Key to use for obtaining real table name
Returns:
String with a Complete table name

getDataSource

javax.sql.DataSource getDataSource()
Returns current Datasource in use

Returns:
Datasource in use

getSQLStatement

java.lang.String getSQLStatement(java.lang.String statementType,
                                 java.lang.String tableKey)
Returns a sql statement loaded from statements file using the parameter as hints. This will use the appropriate database type-specific SQL string

Parameters:
statementType - Type of Statement insert, update etc
tableKey - Unique key for the table
Returns:
A well formed SQL String, null if nothing matches

getSQLStatement

java.lang.String getSQLStatement(java.lang.String statementType,
                                 java.lang.String tableKey,
                                 java.lang.String databaseType)
Returns a sql statement loaded from statements file using the parameter as hints

Parameters:
statementType - Type of Statement insert, update etc
databaseType - the database type
tableKey - Unique key for the table
Returns:
A well formed SQL String, null if nothing matches

dumpNestedSQLException

void dumpNestedSQLException(java.sql.SQLException sqle)
Utility function to Print a SQLException and any nested exceptions

Parameters:
sqle - Exception to print

dumpSQLExceptionInfo

void dumpSQLExceptionInfo(java.lang.Throwable e)
Utility function to Print a SQLException and any nested exceptions

Parameters:
e - Throwable to print

existsTable

boolean existsTable(java.lang.String tableName)
Check whether the table name exists in current database. If the table exists, return true.

Parameters:
tableName - Name of table to check
Returns:
true if table exists false otherwise

existsTable

boolean existsTable(java.lang.String tableName,
                    java.sql.Connection con)
Check whether the table name exists in current database If the table exists, return true.

Parameters:
tableName - name of the table to check
con - the connection to use to test the table with.

getTablePrefixName

java.lang.String getTablePrefixName()
Return the table prefix name.

Returns:
the table prefix name.

getConnection

java.sql.Connection getConnection(TxHandle txHandle)
                                  throws java.sql.SQLException
Creates a connection to the database using a specific txHandle. When using the database helper from a node agent, the normal WAS transaction service is not available. In this case, we use the transaction service provided by the database connection itself and we set the connection object in the txHandle so the Transaction implementation can commit or rollback the transaction.

Parameters:
txHandle -
Returns:
a Connection to the database
Throws:
java.sql.SQLException

getTransactionControl

TransactionControl getTransactionControl()
Retrieve the TransactionControl associated with this DatabaseHelper.

Returns:
the TransactionControl associated with this DatabaseHelper.

IBM WebSphere Application ServerTM
Release 7