com.bristle.javalib.sql.oracle
Class OracleConnectionPoolUtil
java.lang.Object
com.bristle.javalib.sql.ConnectionPoolUtil
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$
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 |
OracleConnectionPoolUtil
public OracleConnectionPoolUtil()
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