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

      Fields 
      Modifier and Type Field and Description
      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

      Methods 
      Modifier and Type Method and Description
      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_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:

        • 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:
        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 8.5