com.bristle.javalib.sql.oracle
Class OracleConnectionPoolUtil

java.lang.Object
  extended by com.bristle.javalib.sql.ConnectionPoolUtil
      extended by com.bristle.javalib.sql.oracle.OracleConnectionPoolUtil

public class OracleConnectionPoolUtil
extends ConnectionPoolUtil

This class contains utility routines for use with an Oracle database. Some of the methods optionally use the ConnectionPool.

 Usage:

   - Typical scenarios for using this class include:

     // ----------------------------------------------------------------------
     // To use with a single database Connection, and optionally a single 
     // Logger, specifying them each once before doing any database operations:
     // ----------------------------------------------------------------------
     OracleConnectionPoolUtil util = new OracleConnectionPoolUtil();
     util.setDefaultConnection(conn);
     util.setDefaultLogger(logger);  // optional
     util.executeSQL("begin dbuser1.package1.proc1(123,'abc',null,true); end;");
     DBContext dbContext;
     try
     {
         dbContext = util.getResultSetFromOracleFunction
                       ("dbuser1.package1.function1(123,'abc',null,true)");
         while (dbContext.rs.next())
         {
             String strCol1 = dbContext.rs.getString("col1");
             String strCol2 = dbContext.rs.getString("col2");
             ...
         }
     }
     finally
     {
         util.cleanupDBContext(dbContext);
     }
              
     // ----------------------------------------------------------------------
     // To use with a single ConnectionPool, single database, single set of 
     // credentials, default result set type, default result set concurrency,
     // and optionally a single Logger, specifying them each once before 
     // doing any database operations:
     // ----------------------------------------------------------------------
     OracleConnectionPoolUtil util = new OracleConnectionPoolUtil();
     util.setDefaultConnectionPool(pool);
     util.setDefaultDBConfig(config);
     util.setDefaultLogger(logger);  // optional
     util.executeSQL("begin dbuser1.package1.proc1(123,'abc',null,true); end;");
     DBContext dbContext;
     try
     {
         dbContext = util.getResultSetFromOracleFunction
                       ("dbuser1.package1.function1(123,'abc',null,true)");
         while (dbContext.rs.next())
         {
             String strCol1 = dbContext.rs.getString("col1");
             String strCol2 = dbContext.rs.getString("col2");
             ...
         }
     }
     finally
     {
         util.cleanupDBContext(dbContext);
     }
              
     // ----------------------------------------------------------------------
     // To specify a database Connection, and optionally a Logger, on each 
     // operation:
     // ----------------------------------------------------------------------
     OracleConnectionPoolUtil.executeSQL
             (conn, 
              "begin dbuser1.package1.proc1(123,'abc',null,true); end;", 
              logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
              null, 
              null);
     DBContext dbContext;
     try
     {
         dbContext = OracleConnectionPoolUtil.getResultSetFromOracleFunction
             (conn,
              "dbuser1.package1.function1(123,'abc',null,true)",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY,
              logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
              null, 
              null);
         while (dbContext.rs.next())
         {
             String strCol1 = dbContext.rs.getString("col1");
             String strCol2 = dbContext.rs.getString("col2");
             ...
         }
     }
     finally
     {
         OracleConnectionPoolUtil.cleanupDBContext
             (dbContext,
              logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
              null);
     }
              
     // ----------------------------------------------------------------------
     // To specify a ConnectionPool, database, set of credentials, and 
     // optionally a Logger, on each operation:
     // ----------------------------------------------------------------------
     OracleConnectionPoolUtil.executeSQL
             (OracleConnectionPoolUtil.connALLOCATE_CONNECTION_FROM_POOL, 
              "begin dbuser1.package1.proc1(123,'abc',null,true); end;", 
              logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
              pool, 
              config);
     DBContext dbContext;
     try
     {
         dbContext = OracleConnectionPoolUtil.getResultSetFromOracleFunction
             (OracleConnectionPoolUtil.connALLOCATE_CONNECTION_FROM_POOL,
              "dbuser1.package1.function1(123,'abc',null,true)",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY,
              logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
              pool, 
              config);
         while (dbContext.rs.next())
         {
             String strCol1 = dbContext.rs.getString("col1");
             String strCol2 = dbContext.rs.getString("col2");
             ...
         }
     }
     finally
     {
         OracleConnectionPoolUtil.cleanupDBContext
             (dbContext,
              logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
              pool);
     }
              
     // ----------------------------------------------------------------------
     // To test the validity of a database connection:
     // ----------------------------------------------------------------------
     OracleConnectionPoolUtil.databaseConnectionIsValid(conn);
              
     // ----------------------------------------------------------------------
     // To close a Connection and drop it from the ConnectionPool, perhaps 
     // because the Connection has gone bad in some way:
     // ----------------------------------------------------------------------
     util.cleanupDBContext
         (dbContext,
          OracleConnectionPoolUtil.blnCLOSE_CONNECTION,
          OracleConnectionPoolUtil.blnDONE_WITH_CONNECTION);
     // or
     OracleConnectionPoolUtil.cleanupDBContext
         (dbContext,
          OracleConnectionPoolUtil.blnCLOSE_CONNECTION,
          OracleConnectionPoolUtil.blnDONE_WITH_CONNECTION,
          logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
          pool);

     // ----------------------------------------------------------------------
     // To hold on to a pooled Connection, preventing it from being returned 
     // to the ConnectionPool, but still releasing the other database objects
     // (Statement and ResultSet): 
     // ----------------------------------------------------------------------
     util.cleanupDBContext
         (dbContext,
          !OracleConnectionPoolUtil.blnCLOSE_CONNECTION,
          !OracleConnectionPoolUtil.blnDONE_WITH_CONNECTION);
     // or
     OracleConnectionPoolUtil.cleanupDBContext
         (dbContext,
          !OracleConnectionPoolUtil.blnCLOSE_CONNECTION,
          !OracleConnectionPoolUtil.blnDONE_WITH_CONNECTION,
          logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
          pool);
     // or:
     OracleConnectionPoolUtil.cleanupDBContext
         (dbContext,
          logger, // or OracleConnectionPoolUtil.loggerNO_LOGGING
          null);

 Assumptions:
 Effects:
       - None.
 Anticipated Changes:
 Notes:
 Implementation Notes:
 Portability Issues:
 Revision History:
   $Log$
 


Nested Class Summary
static class OracleConnectionPoolUtil.Tester
          Each class contains a Tester inner class with a main() for easier unit testing.
 
Nested classes/interfaces inherited from class com.bristle.javalib.sql.ConnectionPoolUtil
ConnectionPoolUtil.DBContext, ConnectionPoolUtil.InsertOrUpdateResult, ConnectionPoolUtil.NoDataFoundException
 
Field Summary
private  int m_intLogLevelOfOperation
           
 
Fields inherited from class com.bristle.javalib.sql.ConnectionPoolUtil
blnCLOSE_CONNECTION, blnDONE_WITH_CONNECTION, connALLOCATE_CONNECTION_FROM_POOL, loggerNO_LOGGING
 
Constructor Summary
OracleConnectionPoolUtil()
           
 
Method Summary
 int getLogLevelOfOperation()
          Get the level of logging operations done by this class.
static ConnectionPoolUtil.DBContext getResultSetFromOracleFunction(Connection conn, String strCall, Logger logger, int intLogLevelOfOperation, ConnectionPool pool, ConnectionPool.DBConfig dbconfig)
          Connect to the database and get the data as a readonly forward-only ResultSet by calling an Oracle stored function that returns a ResultSet.
 ConnectionPoolUtil.DBContext getResultSetFromOracleFunction(String strCall)
          Connect to the database and get the data as a readonly forward-only ResultSet by calling an Oracle stored function that returns a ResultSet, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 void setLogLevelOfOperation(int intNew)
          Set the level of logging operations done by this class.
 
Methods inherited from class com.bristle.javalib.sql.ConnectionPoolUtil
buildInsertString, buildSelectCountStarString, buildSelectString, buildUpdateString, cleanupDBContext, cleanupDBContext, cleanupDBContext, cleanupDBContext, databaseConnectionIsValid, executeSQL, executeSQL, executeSQLIfIntegerExistsThenElse, executeSQLIfIntegerGreaterThanZero, executeSQLIfIntegerGreaterThanZeroThenElse, executeSQLIfIntegerNotGreaterThanZero, executeSQLIfThenElse, getConnectionTestString, getDefaultConnection, getDefaultConnectionPool, getDefaultDBConfig, getDefaultLogger, getDefaultResultSetConcurrency, getDefaultResultSetType, getIntValueFromDB, getIntValueFromDB, getResultSet, getResultSet, insert, insertIfNotExists, insertIfNotExistsMultiTable, insertIfNotExistsMultiTableReturnPK, insertIfNotExistsReturnPK, insertOrUpdate, insertOrUpdateReturnPK, setConnectionTestString, setDefaultConnection, setDefaultConnectionPool, setDefaultDBConfig, setDefaultLogger, setDefaultResultSetConcurrency, setDefaultResultSetType, update, updateReturnPK
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

m_intLogLevelOfOperation

private int m_intLogLevelOfOperation
Constructor Detail

OracleConnectionPoolUtil

public OracleConnectionPoolUtil()
Method Detail

setLogLevelOfOperation

public void setLogLevelOfOperation(int intNew)
Set the level of logging operations done by this class.

Overrides:
setLogLevelOfOperation in class ConnectionPoolUtil
Parameters:
intNew - The new value.

getLogLevelOfOperation

public int getLogLevelOfOperation()
Get the level of logging operations done by this class.

Overrides:
getLogLevelOfOperation in class ConnectionPoolUtil
Returns:
The log level.

getResultSetFromOracleFunction

public static ConnectionPoolUtil.DBContext getResultSetFromOracleFunction(Connection conn,
                                                                          String strCall,
                                                                          Logger logger,
                                                                          int intLogLevelOfOperation,
                                                                          ConnectionPool pool,
                                                                          ConnectionPool.DBConfig dbconfig)
                                                                   throws SQLException
Connect to the database and get the data as a readonly forward-only ResultSet by calling an Oracle stored function that returns a ResultSet.

Parameters:
conn - Connection to use in database query. Optional. If null, a connection from the pool is used.
strCall - String of SQL to call the Oracle stored function. Example: "Function1('abc', 1, null, 'xyz', true)"
logger - Logger to log operations to. Optional. If null, no logging is done.
intLogLevelOfOperation - Level at which to log operations.
pool - ConnectionPool to use to obtain a connection if conn is null. Optional and ignored if conn is not null.
dbconfig - Info used to choose a pooled connection when the pool is used. Optional and ignored if the pool is not used.
Returns:
DBContext object containing returned database objects.
Throws:
SQLException

getResultSetFromOracleFunction

public ConnectionPoolUtil.DBContext getResultSetFromOracleFunction(String strCall)
                                                            throws SQLException
Connect to the database and get the data as a readonly forward-only ResultSet by calling an Oracle stored function that returns a ResultSet, using the default values for Connection, Logger, ConnectionPool, and DBConfig. This method is useful when it is more convenient to set the defaults once than to specify them on each call.

Parameters:
strCall - String of SQL to call the Oracle stored function. Example: "Function1('abc', 1, null, 'xyz', true)"
Returns:
Context object containing returned database objects.
Throws:
SQLException