com.ibm.ws.extensionhelper

Interface DatabaseHelper


  1. 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

Modifier and Type Field and Description
  1. static
  2. java.lang.Integer
AUTH_APPLICATION
Resource authorization type: Application
  1. static
  2. java.lang.Integer
AUTH_CONTAINER
Resource authorization type: Container
  1. static
  2. java.lang.String
DBTYPE_DB2
DB2 Database on a Non i-series or z-series platform
  1. static
  2. java.lang.String
DBTYPE_DB2ISERIES
DB2 Database on a i-series
  1. static
  2. java.lang.String
DBTYPE_DB2j
Deprecated. Please switch to the Derby database and use DBTYPE_DERBY instead.
  1. static
  2. java.lang.String
DBTYPE_DB2ZSERIES
DB2 Database on a z-series
  1. static
  2. java.lang.String
DBTYPE_DERBY
Derby Database
  1. static
  2. java.lang.String
DBTYPE_INFORMIX
Informix Database
  1. static
  2. java.lang.String
DBTYPE_INFORMIX7
Deprecated. Informix version 7 is not supported.
  1. static
  2. java.lang.String
DBTYPE_MSSQL
Microsoft SQL 2000 (or higher) Database
  1. static
  2. java.lang.String
DBTYPE_MSSQL7
Deprecated. Microsoft SQL Server version 7 is not supported.
  1. static
  2. java.lang.String
DBTYPE_ORACLE
Oracle Database
  1. static
  2. java.lang.String
DBTYPE_ORACLETHICK
Oracle Database using Native Driver
  1. static
  2. java.lang.String
DBTYPE_SYBASE
Sybase Database
  1. static
  2. java.lang.String
KEY_DATASOURCE_CLASSNAME
The key to the MAP to specify the implementation class name of the datasource.
  1. static
  2. java.lang.String
KEY_DATASOURCE_CLASSPATH
Key for the Map to specify the classpath to the database implementation classes.
  1. static
  2. java.lang.String
KEY_DATASOURCE_JNDI_NAME
Key for Data Source JNDI Name to lookup.
  1. static
  2. java.lang.String
KEY_DATASOURCE_PASSWORD
Key for password to use with user name in a getConnection call.
  1. static
  2. 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.
  1. static
  2. java.lang.String
KEY_DATASOURCE_USERNAME
Key for user name to use for a getConnection call.
  1. static
  2. java.lang.String
KEY_REPLACEMENT_MAP
Key for Map to use for replacing various strings in SQL statements, depending on database.
  1. static
  2. java.lang.String
KEY_STATEMENTS_FILE
Key for file name to load SQL Statements from.
  1. static
  2. java.lang.String
KEY_TABLE_PREFIX
Key for table prefix to use.
  1. static
  2. java.lang.String
RESREF_AUTH
Key for type of authorization to use for Resource.
  1. static
  2. java.lang.String
RESREF_SHARING
Key for type of Resource Sharing to use.
  1. static
  2. java.lang.String
RESREF_TRANSACTION
Key for type of transaction to use.
  1. static
  2. java.lang.Integer
SHARING_SHAREABLE
Resource sharing type: Shareable
  1. static
  2. java.lang.Integer
SHARING_UNSHAREABLE
Resource sharing type: Unshareable
  1. static
  2. java.lang.Integer
TRANSACTION_NONE
Resource transaction isolation level: None
  1. static
  2. java.lang.Integer
TRANSACTION_READ_COMMITTED
Resource transaction isolation level: Comitted Read
  1. static
  2. java.lang.Integer
TRANSACTION_READ_UNCOMMITTED
Resource transaction isolation level: Uncommitted Read
  1. static
  2. java.lang.Integer
TRANSACTION_REPEATABLE_READ
Resource transaction isolation level: Repeatable Read
  1. static
  2. java.lang.Integer
TRANSACTION_SERIALIZABLE
Resource transaction isolation level: Serializable
  1. static
  2. java.lang.Integer
TRANSACTION_SNAPSHOT
Resource transaction isolation level: Snapshot (Microsoft SQL Server 2005 & up only).
  1. static
  2. java.lang.Integer
TRANSACTION_SS_SNAPSHOT
Resource transaction isolation level: Snapshot (Microsoft SQL Server 2005 & up only).

Method Summary

Modifier and Type Method and Description
  1. void
dumpNestedSQLException(java.sql.SQLException sqle)
Utility function to Print a SQLException and any nested exceptions
  1. void
dumpSQLExceptionInfo(java.lang.Throwable e)
Utility function to Print a SQLException and any nested exceptions
  1. boolean
existsTable(java.lang.String tableName)
Check whether the table name exists in current database.
  1. 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.
  1. java.sql.Connection
getConnection()
Get a connection from Database
  1. java.sql.Connection
getConnection(TxHandle txHandle)
Creates a connection to the database using a specific txHandle.
  1. java.lang.String
getDatabaseType()
Get Type of Database in use.
  1. javax.sql.DataSource
getDataSource()
Returns current Datasource in use
  1. java.lang.String
getRealTableName(java.lang.String tableKey)
Returns table name this appeds table prefix to provided table key.
  1. 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.
  1. 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
  1. java.lang.String
getTablePrefixName()
Return the table prefix name.
  1. TransactionControl
getTransactionControl()
Retrieve the TransactionControl associated with this DatabaseHelper.
  1. void
returnConnection(java.sql.Connection con)
Return Connection.

Field Detail

RESREF_AUTH

  1. 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:

RESREF_SHARING

  1. 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:

RESREF_TRANSACTION

  1. 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:

AUTH_APPLICATION

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

AUTH_CONTAINER

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

SHARING_SHAREABLE

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

SHARING_UNSHAREABLE

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

TRANSACTION_NONE

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

TRANSACTION_READ_UNCOMMITTED

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

TRANSACTION_READ_COMMITTED

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

TRANSACTION_REPEATABLE_READ

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

TRANSACTION_SERIALIZABLE

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

TRANSACTION_SNAPSHOT

  1. 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

  1. 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

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

DBTYPE_DB2ISERIES

  1. static final java.lang.String DBTYPE_DB2ISERIES
DB2 Database on a i-series
See Also:

DBTYPE_DB2ZSERIES

  1. static final java.lang.String DBTYPE_DB2ZSERIES
DB2 Database on a z-series
See Also:

DBTYPE_DB2j

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

DBTYPE_DERBY

  1. static final java.lang.String DBTYPE_DERBY
Derby Database
See Also:

DBTYPE_ORACLE

  1. static final java.lang.String DBTYPE_ORACLE
Oracle Database
See Also:

DBTYPE_ORACLETHICK

  1. static final java.lang.String DBTYPE_ORACLETHICK
Oracle Database using Native Driver
See Also:

DBTYPE_SYBASE

  1. static final java.lang.String DBTYPE_SYBASE
Sybase Database
See Also:

DBTYPE_MSSQL

  1. static final java.lang.String DBTYPE_MSSQL
Microsoft SQL 2000 (or higher) Database
See Also:

DBTYPE_MSSQL7

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

DBTYPE_INFORMIX

  1. static final java.lang.String DBTYPE_INFORMIX
Informix Database
See Also:

DBTYPE_INFORMIX7

  1. static final java.lang.String DBTYPE_INFORMIX7
Deprecated. Informix version 7 is not supported.
Informix 7 Database
See Also:

KEY_DATASOURCE_JNDI_NAME

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

KEY_DATASOURCE_USERNAME

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

KEY_DATASOURCE_PASSWORD

  1. 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:

KEY_STATEMENTS_FILE

  1. 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:

KEY_TABLE_PREFIX

  1. 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:

  • There is a SQL statement such as "SELECT ID FROM <table_prefix>TBL" table will result
  • The TABLE_PREFIX_KEY is set to "TEST"
The resulting SQL statement will be "SELECT ID FROM TESTTBL"
See Also:

KEY_REPLACEMENT_MAP

  1. 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:

KEY_DATASOURCE_CLASSPATH

  1. 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:

KEY_DATASOURCE_CLASSNAME

  1. 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:

KEY_DATASOURCE_PROPERTIES

  1. 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:

Method Detail

getDatabaseType

  1. 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

  1. java.sql.Connection getConnection( )
  2. throws java.sql.SQLException
Get a connection from Database
Returns:
JDBC Connection
Throws:
java.sql.SQLException - If a exception occurs during getConnection

returnConnection

  1. void returnConnection(java.sql.Connection con)
  2. throws java.sql.SQLException
Return Connection.
Parameters:
con - JDBC Connection in use to return.
Throws:
java.sql.SQLException - If a Exception occurs during

getRealTableName

  1. 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

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

getSQLStatement

  1. java.lang.String getSQLStatement( java.lang.String statementType,
  2. 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

  1. java.lang.String getSQLStatement( java.lang.String statementType,
  2. java.lang.String tableKey,
  3. 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

  1. void dumpNestedSQLException(java.sql.SQLException sqle)
Utility function to Print a SQLException and any nested exceptions
Parameters:
sqle - Exception to print

dumpSQLExceptionInfo

  1. void dumpSQLExceptionInfo(java.lang.Throwable e)
Utility function to Print a SQLException and any nested exceptions
Parameters:
e - Throwable to print

existsTable

  1. 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

  1. boolean existsTable(java.lang.String tableName,
  2. 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

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

getConnection

  1. java.sql.Connection getConnection( TxHandle txHandle)
  2. 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

  1. TransactionControl getTransactionControl( )
Retrieve the TransactionControl associated with this DatabaseHelper.
Returns:
the TransactionControl associated with this DatabaseHelper.