com.bristle.javalib.sql
Class ConnectionPoolUtil

java.lang.Object
  extended by com.bristle.javalib.sql.ConnectionPoolUtil
Direct Known Subclasses:
OracleConnectionPoolUtil

public class ConnectionPoolUtil
extends Object

This class contains utility routines for use with JDBC databases. Some of the methods optionally use the ConnectionPool.

 Usage:

   - Typical scenarios for using this class include:

     // ----------------------------------------------------------------------
     // To initialize for non-Oracle databases:
     // ----------------------------------------------------------------------
     ConnectionPoolUtil.setConnectionTestString
             ("some SQL string that executes quickly without throwing an" +
              " exception if the database connection is valid");

     // ----------------------------------------------------------------------
     // To use with a single database Connection, default result set type, 
     // default result set concurrency, and optionally a single Logger, 
     // specifying them each once before doing any database operations:
     // ----------------------------------------------------------------------
     ConnectionPoolUtil util = new ConnectionPoolUtil();
     util.setDefaultConnection(conn);
     util.setDefaultLogger(logger);  // optional
     util.executeSQL("delete from mytable");
     DBContext dbContext;
     try
     {
         dbContext = util.getResultSet("select * from mytable");
         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:
     // ----------------------------------------------------------------------
     ConnectionPoolUtil util = new ConnectionPoolUtil();
     util.setDefaultConnectionPool(pool);
     util.setDefaultDBConfig(config);
     util.setDefaultLogger(logger);  // optional
     util.executeSQL("delete from mytable");
     DBContext dbContext;
     try
     {
         dbContext = util.getResultSet("select * from mytable");
         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:
     // ----------------------------------------------------------------------
     ConnectionPoolUtil.executeSQL
             (conn, 
              "delete from mytable", 
              logger, // or ConnectionPoolUtil.loggerNO_LOGGING
              null, 
              null);
     DBContext dbContext;
     try
     {
         dbContext = ConnectionPoolUtil.getResultSet
             (conn,
              "select * from mytable",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY,
              logger, // or ConnectionPoolUtil.loggerNO_LOGGING
              null, 
              null);
         while (dbContext.rs.next())
         {
             String strCol1 = dbContext.rs.getString("col1");
             String strCol2 = dbContext.rs.getString("col2");
             ...
         }
     }
     finally
     {
         ConnectionPoolUtil.cleanupDBContext
             (dbContext,
              logger, // or ConnectionPoolUtil.loggerNO_LOGGING
              null);
     }
              
     // ----------------------------------------------------------------------
     // To specify a ConnectionPool, database, set of credentials, and 
     // optionally a Logger, on each operation:
     // ----------------------------------------------------------------------
     ConnectionPoolUtil.executeSQL
             (ConnectionPoolUtil.connALLOCATE_CONNECTION_FROM_POOL, 
              "delete from mytable", 
              logger, // or ConnectionPoolUtil.loggerNO_LOGGING
              pool, 
              config);
     DBContext dbContext;
     try
     {
         dbContext = ConnectionPoolUtil.getResultSet
             (ConnectionPoolUtil.connALLOCATE_CONNECTION_FROM_POOL,
              "select * from mytable",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY,
              logger, // or ConnectionPoolUtil.loggerNO_LOGGING
              pool, 
              config);
         while (dbContext.rs.next())
         {
             String strCol1 = dbContext.rs.getString("col1");
             String strCol2 = dbContext.rs.getString("col2");
             ...
         }
     }
     finally
     {
         ConnectionPoolUtil.cleanupDBContext
             (dbContext,
              logger, // or ConnectionPoolUtil.loggerNO_LOGGING
              pool);
     }
              
     // ----------------------------------------------------------------------
     // To test the validity of a database connection:
     // ----------------------------------------------------------------------
     ConnectionPoolUtil.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,
          ConnectionPoolUtil.blnCLOSE_CONNECTION,
          ConnectionPoolUtil.blnDONE_WITH_CONNECTION);
     // or
     ConnectionPoolUtil.cleanupDBContext
         (dbContext,
          ConnectionPoolUtil.blnCLOSE_CONNECTION,
          ConnectionPoolUtil.blnDONE_WITH_CONNECTION,
          logger, // or ConnectionPoolUtil.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,
          !ConnectionPoolUtil.blnCLOSE_CONNECTION,
          !ConnectionPoolUtil.blnDONE_WITH_CONNECTION);
     // or
     ConnectionPoolUtil.cleanupDBContext
         (dbContext,
          !ConnectionPoolUtil.blnCLOSE_CONNECTION,
          !ConnectionPoolUtil.blnDONE_WITH_CONNECTION,
          logger, // or ConnectionPoolUtil.loggerNO_LOGGING
          pool);
     // or:
     ConnectionPoolUtil.cleanupDBContext
         (dbContext,
          logger, // or ConnectionPoolUtil.loggerNO_LOGGING
          null);

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


Nested Class Summary
static class ConnectionPoolUtil.DBContext
          Convenience class used to pass multiple related database objects around.
static class ConnectionPoolUtil.InsertOrUpdateResult
          This class is used as the return value of insertOrUpdateReturnPK() so that it can return a composite value.
static class ConnectionPoolUtil.NoDataFoundException
          Thrown when no data is found in a situation that requires data.
static class ConnectionPoolUtil.Tester
          Each class contains a Tester inner class with a main() for easier unit testing.
 
Field Summary
static boolean blnCLOSE_CONNECTION
           
static boolean blnDONE_WITH_CONNECTION
           
static Connection connALLOCATE_CONNECTION_FROM_POOL
           
static Logger loggerNO_LOGGING
           
private  ConnectionPool.DBConfig m_config
           
private  Connection m_conn
           
private  int m_intConcurrency
           
private  int m_intLogLevelOfOperation
           
private  int m_intType
           
private  Logger m_logger
           
private  ConnectionPool m_pool
           
private static String st_strCONNECTION_TEST_STRING
           
 
Constructor Summary
ConnectionPoolUtil()
           
 
Method Summary
 String buildInsertString(String strTableName, String strCols, String strVals)
          Return an INSERT statement composed from the specified table name, columns and values.
 String buildSelectCountStarString(String strTableName, String strWhere)
          Return a SELECT COUNT(*) FROM statement composed from the specified table name and WHERE clause.
 String buildSelectString(String strTableName, String strWhere, String strCols)
          Return a SELECT statement composed from the specified table name, columns and WHERE clause.
 String buildUpdateString(String strTableName, String strWhere, String strSetColsVals)
          Return an UPDATE statement composed from the specified table name, WHERE clause, columns and values.
 void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext)
          Cleanup the database objects, leaving the database connection open, and returning it to the connection pool, if any, using the default values for Logger and ConnectionPool.
 void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext, boolean blnClose, boolean blnDoneWithConnection)
          Cleanup the database objects, optionally closing the database connection, using the default values for Logger and ConnectionPool.
static void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext, boolean blnClose, boolean blnDoneWithConnection, Logger logger, int intLogLevelOfOperation, ConnectionPool pool)
          Cleanup the database objects, optionally closing the database connection.
static void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext, Logger logger, int intLogLevelOfOperation, ConnectionPool pool)
          Cleanup the database objects, leaving the database connection open, and returning it to the connection pool, if any.
static boolean databaseConnectionIsValid(Connection conn)
          Test the specified database connection.
static int executeSQL(Connection conn, String strSQL, Logger logger, int intLogLevelOfOperation, ConnectionPool pool, ConnectionPool.DBConfig dbconfig)
          Connect to the database, execute a string of SQL, commit, and disconnect.
 void executeSQL(String strSQL)
          Connect to the database, execute a string of SQL, and disconnect, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 Integer executeSQLIfIntegerExistsThenElse(String strSelect, String strSQLThen, String strSQLElse)
          Connect to the database, execute a SELECT statement to get an Integer value, and based on its existence, execute a 2nd or 3rd string of SQL, then disconnect, returning the Integer value if the row exists, null otherwise, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 boolean executeSQLIfIntegerGreaterThanZero(String strSelect, String strSQLThen)
          Connect to the database, execute a SELECT statement to get an Integer value, and if it is greater than 0, execute a 2nd string of SQL, then disconnect, returning true if the Integer is greater than 0, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 boolean executeSQLIfIntegerGreaterThanZeroThenElse(String strSelect, String strSQLThen, String strSQLElse)
          Connect to the database, execute a SELECT statement to get an Integer value, and based on whether it is greater than 0, execute a 2nd or 3rd string of SQL, then disconnect, returning true if the Integer is greater than 0, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 boolean executeSQLIfIntegerNotGreaterThanZero(String strSelect, String strSQLThen)
          Connect to the database, execute a SELECT statement to get an Integer value, and if it is not greater than 0, execute a 2nd string of SQL, then disconnect, returning true if the Integer is not greater than 0, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 void executeSQLIfThenElse(boolean blnIf, String strSQLThen, String strSQLElse)
          Connect to the database, execute a one string of SQL or another based on the value of boolean flag, then disconnect, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
static String getConnectionTestString()
          Get the connection test string.
 Connection getDefaultConnection()
          Get the default Connection.
 ConnectionPool getDefaultConnectionPool()
          Get the default ConnectionPool.
 ConnectionPool.DBConfig getDefaultDBConfig()
          Get the default DBConfig.
 Logger getDefaultLogger()
          Get the default Logger.
 int getDefaultResultSetConcurrency()
          Get the default result set concurrency.
 int getDefaultResultSetType()
          Get the default result set type.
static int getIntValueFromDB(Connection conn, String strSQL, Logger logger, int intLogLevelOfOperation, ConnectionPool pool, ConnectionPool.DBConfig dbconfig)
          Connect to the database, get a single int value from the resultset returned by the specified SQL statement, and disconnect, returning the value.
 int getIntValueFromDB(String strSQL)
          Connect to the database, get a single int value from the resultset returned by the specified SQL statement, and disconnect, returning the value, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 int getLogLevelOfOperation()
          Get the level of logging operations done by this class.
static ConnectionPoolUtil.DBContext getResultSet(Connection conn, String strSQL, int intType, int intConcurrency, Logger logger, int intLogLevelOfOperation, ConnectionPool pool, ConnectionPool.DBConfig dbconfig)
          Connect to the database and get the data as a ResultSet with the specified type and concurrency.
 ConnectionPoolUtil.DBContext getResultSet(String strSQL)
          Connect to the database and get the data using the default values for Connection, result set type, result set concurrency, Logger, ConnectionPool, and DBConfig.
 void insert(String strTableName, String strCols, String strVals)
          Connect to the database, execute an INSERT statement, then disconnect, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 boolean insertIfNotExists(String strTableName, String strWhere, String strCols, String strVals)
          Connect to the database, execute a SELECT COUNT(*) FROM statement to decide if a row exists and if not, execute an INSERT statement, then disconnect, returning true if the row existed, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 boolean insertIfNotExistsMultiTable(String strSelectCount, String strTableName, String strCols, String strVals)
          Connect to the database, execute a SELECT COUNT(*) FROM statement to decide if a row exists and if not, execute an INSERT statement possibly into a different table, then disconnect, returning true if the row existed, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 ConnectionPoolUtil.InsertOrUpdateResult insertIfNotExistsMultiTableReturnPK(String strSelectPKExisting, String strSelectPKInserted, String strTableName, String strCols, String strVals)
          Connect to the database, execute a SELECT statement to get the primary key of an existing row, and if it doesn't exist, execute an INSERT statement possibly into a different table and execute a different SELECT statement to get a primary key, then disconnect, returning an InsertOrUpdateResult object, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 ConnectionPoolUtil.InsertOrUpdateResult insertIfNotExistsReturnPK(String strTableName, String strPKName, String strWhere, String strCols, String strVals)
          Connect to the database, execute a SELECT statement to get the primary key of an existing row, and if it doesn't exist, execute an INSERT statement into the table and re-execute the SELECT statement to get the primary key, then disconnect, returning an InsertOrUpdateResult object, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 boolean insertOrUpdate(String strTableName, String strWhere, String strCols, String strVals, String strSetColsVals)
          Connect to the database, execute a SELECT COUNT(*) FROM statement to decide if a row exists and based on its existence, execute an INSERT or UPDATE statement, then disconnect, returning true if the row existed, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 ConnectionPoolUtil.InsertOrUpdateResult insertOrUpdateReturnPK(String strTableName, String strPKName, String strWhere, String strCols, String strVals, String strSetColsVals)
          Connect to the database, execute a SELECT statement to decide if a row exists and based on its existence, execute an INSERT or UPDATE statement, then disconnect, returning an InsertOrUpdateResult object, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
static void setConnectionTestString(String strNew)
          Set the connection test string.
 void setDefaultConnection(Connection connNew)
          Set the default Connection.
 void setDefaultConnectionPool(ConnectionPool poolNew)
          Set the default ConnectionPool.
 void setDefaultDBConfig(ConnectionPool.DBConfig configNew)
          Set the default DBConfig.
 void setDefaultLogger(Logger loggerNew)
          Set the default Logger.
 void setDefaultResultSetConcurrency(int intNew)
          Set the default result set concurrency to one of: ResultSet.CONCUR_READONLY, or ResultSet.CONCUR_UPDATABLE,
 void setDefaultResultSetType(int intNew)
          Set the default result set type to one of: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
 void setLogLevelOfOperation(int intNew)
          Set the level of logging operations done by this class.
 void update(String strTableName, String strWhere, String strSetColsVals)
          Connect to the database, execute an UPDATE statement, then disconnect, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 ConnectionPoolUtil.InsertOrUpdateResult updateReturnPK(String strTableName, String strPKName, String strWhere, String strSetColsVals)
          Connect to the database, execute an UPDATE, then disconnect, returning an InsertOrUpdateResult object, using the default values for Connection, Logger, ConnectionPool, and DBConfig.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

st_strCONNECTION_TEST_STRING

private static String st_strCONNECTION_TEST_STRING

m_conn

private Connection m_conn

m_intType

private int m_intType

m_intConcurrency

private int m_intConcurrency

m_logger

private Logger m_logger

m_intLogLevelOfOperation

private int m_intLogLevelOfOperation

m_pool

private ConnectionPool m_pool

m_config

private ConnectionPool.DBConfig m_config

connALLOCATE_CONNECTION_FROM_POOL

public static final Connection connALLOCATE_CONNECTION_FROM_POOL

loggerNO_LOGGING

public static final Logger loggerNO_LOGGING

blnCLOSE_CONNECTION

public static final boolean blnCLOSE_CONNECTION
See Also:
Constant Field Values

blnDONE_WITH_CONNECTION

public static final boolean blnDONE_WITH_CONNECTION
See Also:
Constant Field Values
Constructor Detail

ConnectionPoolUtil

public ConnectionPoolUtil()
Method Detail

setConnectionTestString

public static void setConnectionTestString(String strNew)
Set the connection test string. This string of SQL is executed as necessary to test the validity of connections in the connection pool. If an exception is thrown, the connection is invalid. The default value is Oracle-specific: select sysdate from dual Override it with any other appropriate quickly-executing SQL string.

Parameters:
strNew - The new value.

getConnectionTestString

public static String getConnectionTestString()
Get the connection test string.

Returns:
The connection test string.

setDefaultConnection

public void setDefaultConnection(Connection connNew)
Set the default Connection.

Parameters:
connNew - The new value.

getDefaultConnection

public Connection getDefaultConnection()
Get the default Connection.

Returns:
The default Connection.

setDefaultResultSetType

public void setDefaultResultSetType(int intNew)
Set the default result set type to one of: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE

Parameters:
intNew - The new value.

getDefaultResultSetType

public int getDefaultResultSetType()
Get the default result set type.

Returns:
The default result set type.

setDefaultResultSetConcurrency

public void setDefaultResultSetConcurrency(int intNew)
Set the default result set concurrency to one of: ResultSet.CONCUR_READONLY, or ResultSet.CONCUR_UPDATABLE,

Parameters:
intNew - The new value.

getDefaultResultSetConcurrency

public int getDefaultResultSetConcurrency()
Get the default result set concurrency.

Returns:
The default result set concurrency.

setDefaultLogger

public void setDefaultLogger(Logger loggerNew)
Set the default Logger.

Parameters:
loggerNew - The new value.

getDefaultLogger

public Logger getDefaultLogger()
Get the default Logger.

Returns:
The default Logger.

setLogLevelOfOperation

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

Parameters:
intNew - The new value.

getLogLevelOfOperation

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

Returns:
The log level.

setDefaultConnectionPool

public void setDefaultConnectionPool(ConnectionPool poolNew)
Set the default ConnectionPool.

Parameters:
poolNew - The new value.

getDefaultConnectionPool

public ConnectionPool getDefaultConnectionPool()
Get the default ConnectionPool.

Returns:
The default ConnectionPool.

setDefaultDBConfig

public void setDefaultDBConfig(ConnectionPool.DBConfig configNew)
Set the default DBConfig.

Parameters:
configNew - The new value.

getDefaultDBConfig

public ConnectionPool.DBConfig getDefaultDBConfig()
Get the default DBConfig.

Returns:
The default DBConfig.

getResultSet

public static ConnectionPoolUtil.DBContext getResultSet(Connection conn,
                                                        String strSQL,
                                                        int intType,
                                                        int intConcurrency,
                                                        Logger logger,
                                                        int intLogLevelOfOperation,
                                                        ConnectionPool pool,
                                                        ConnectionPool.DBConfig dbconfig)
                                                 throws SQLException
Connect to the database and get the data as a ResultSet with the specified type and concurrency.

Parameters:
conn - Connection to use in database query. Optional. If null, a connection from the pool is used.
strSQL - String of SQL to use in database query.
intType - Type of result set. Must be one of: ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.TYPE_SCROLL_SENSITIVE
intConcurrency - Concurrency of result set. Must be one of: ResultSet.CONCUR_READONLY ResultSet.CONCUR_UPDATABLE
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

getResultSet

public ConnectionPoolUtil.DBContext getResultSet(String strSQL)
                                          throws SQLException
Connect to the database and get the data using the default values for Connection, result set type, result set concurrency, 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:
strSQL - String of SQL to use in database query.
Returns:
DBContext object containing returned database objects.
Throws:
SQLException

executeSQL

public static int executeSQL(Connection conn,
                             String strSQL,
                             Logger logger,
                             int intLogLevelOfOperation,
                             ConnectionPool pool,
                             ConnectionPool.DBConfig dbconfig)
                      throws SQLException
Connect to the database, execute a string of SQL, commit, and disconnect.

Parameters:
conn - Connection to use in executing SQL string. Optional. If null, a connection from the pool is used.
strSQL - String of SQL to use in database query.
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:
Number of rows affected in the database.
Throws:
SQLException

executeSQL

public void executeSQL(String strSQL)
                throws SQLException
Connect to the database, execute a string of SQL, and disconnect, 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:
strSQL - String of SQL to use in database query.
Throws:
SQLException

getIntValueFromDB

public static int getIntValueFromDB(Connection conn,
                                    String strSQL,
                                    Logger logger,
                                    int intLogLevelOfOperation,
                                    ConnectionPool pool,
                                    ConnectionPool.DBConfig dbconfig)
                             throws ConnectionPoolUtil.NoDataFoundException,
                                    SQLException
Connect to the database, get a single int value from the resultset returned by the specified SQL statement, and disconnect, returning the value.

Parameters:
conn - Connection to use in executing the SQL statement. Optional. If null, a connection from the pool is used.
strSQL - String of SQL to use in the database query.
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:
Count of rows returned.
Throws:
ConnectionPoolUtil.NoDataFoundException - when the query returns no data.
SQLException - When a SQL error occurs, including when strSQL returns a non-Integer value.

getIntValueFromDB

public int getIntValueFromDB(String strSQL)
                      throws ConnectionPoolUtil.NoDataFoundException,
                             SQLException
Connect to the database, get a single int value from the resultset returned by the specified SQL statement, and disconnect, returning the value, 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:
strSQL - String of SQL to use in the database query.
Throws:
ConnectionPoolUtil.NoDataFoundException - when the query returns no data.
SQLException - When a SQL error occurs, including when strSQL returns a non-Integer value.

executeSQLIfThenElse

public void executeSQLIfThenElse(boolean blnIf,
                                 String strSQLThen,
                                 String strSQLElse)
                          throws SQLException
Connect to the database, execute a one string of SQL or another based on the value of boolean flag, then disconnect, 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:
blnIf - The boolean to control which SQL statement to execute.
strSQLThen - String of SQL to execute if blnIf is true, or null or an empty string.
strSQLElse - String of SQL to execute if blnIf is false, or null or an empty string.
Throws:
SQLException

executeSQLIfIntegerExistsThenElse

public Integer executeSQLIfIntegerExistsThenElse(String strSelect,
                                                 String strSQLThen,
                                                 String strSQLElse)
                                          throws SQLException
Connect to the database, execute a SELECT statement to get an Integer value, and based on its existence, execute a 2nd or 3rd string of SQL, then disconnect, returning the Integer value if the row exists, null otherwise, 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:
strSelect - String of SQL to get the Integer value.
strSQLThen - String of SQL to execute if the Integer exists, or null or an empty string.
strSQLElse - String of SQL to execute if the Integer doesn't exist, or null or an empty string.
Returns:
The Integer value if it exists; null otherwise
Throws:
SQLException

executeSQLIfIntegerGreaterThanZeroThenElse

public boolean executeSQLIfIntegerGreaterThanZeroThenElse(String strSelect,
                                                          String strSQLThen,
                                                          String strSQLElse)
                                                   throws ConnectionPoolUtil.NoDataFoundException,
                                                          SQLException
Connect to the database, execute a SELECT statement to get an Integer value, and based on whether it is greater than 0, execute a 2nd or 3rd string of SQL, then disconnect, returning true if the Integer is greater than 0, 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:
strSelect - String of SQL to get the Integer value
strSQLThen - String of SQL to execute if the Integer is greater than 0, or null or an empty string.
strSQLElse - String of SQL to execute if the Integer is greater less than or equal to 0, or null or an empty string.
Returns:
True if the Integer is greater than 0; false otherwise
Throws:
ConnectionPoolUtil.NoDataFoundException - When strSelect returns no rows.
SQLException - When a SQL error occurs, including when strSelect returns a non-Integer value.

executeSQLIfIntegerGreaterThanZero

public boolean executeSQLIfIntegerGreaterThanZero(String strSelect,
                                                  String strSQLThen)
                                           throws ConnectionPoolUtil.NoDataFoundException,
                                                  SQLException
Connect to the database, execute a SELECT statement to get an Integer value, and if it is greater than 0, execute a 2nd string of SQL, then disconnect, returning true if the Integer is greater than 0, 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:
strSelect - String of SQL to get the Integer value
strSQLThen - String of SQL to execute if the Integer is greater than 0, or null or an empty string.
Returns:
True if the Integer is greater than 0; false otherwise
Throws:
ConnectionPoolUtil.NoDataFoundException - When strSelect returns no rows.
SQLException - When a SQL error occurs, including when strSelect returns a non-Integer value.

executeSQLIfIntegerNotGreaterThanZero

public boolean executeSQLIfIntegerNotGreaterThanZero(String strSelect,
                                                     String strSQLThen)
                                              throws ConnectionPoolUtil.NoDataFoundException,
                                                     SQLException
Connect to the database, execute a SELECT statement to get an Integer value, and if it is not greater than 0, execute a 2nd string of SQL, then disconnect, returning true if the Integer is not greater than 0, 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:
strSelect - String of SQL to get the Integer value
strSQLThen - String of SQL to execute if the Integer is not greater than 0, or null or an empty string.
Returns:
True if the Integer is not greater than 0; false otherwise
Throws:
ConnectionPoolUtil.NoDataFoundException - When strSelect returns no rows.
SQLException - When a SQL error occurs, including when strSelect returns a non-Integer value.

buildSelectString

public String buildSelectString(String strTableName,
                                String strWhere,
                                String strCols)
Return a SELECT statement composed from the specified table name, columns and WHERE clause.

Parameters:
strTableName - Name of the database table
strWhere - SQL WHERE clause to use in the SELECT statement to get the row count, without the keyword WHERE. Used internally as: SELECT COUNT(*) FROM strTableName WHERE strWhere
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Returns:
The SELECT statement

buildSelectCountStarString

public String buildSelectCountStarString(String strTableName,
                                         String strWhere)
Return a SELECT COUNT(*) FROM statement composed from the specified table name and WHERE clause.

Parameters:
strTableName - Name of the database table.
strWhere - SQL WHERE clause to use in the SELECT statement to get the row count, without the keyword WHERE. Used internally as: SELECT COUNT(*) FROM strTableName WHERE strWhere
Returns:
The SELECT statement

buildInsertString

public String buildInsertString(String strTableName,
                                String strCols,
                                String strVals)
Return an INSERT statement composed from the specified table name, columns and values.

Parameters:
strTableName - Name of the database table
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Returns:
The INSERT statement

insertIfNotExistsMultiTable

public boolean insertIfNotExistsMultiTable(String strSelectCount,
                                           String strTableName,
                                           String strCols,
                                           String strVals)
                                    throws ConnectionPoolUtil.NoDataFoundException,
                                           SQLException
Connect to the database, execute a SELECT COUNT(*) FROM statement to decide if a row exists and if not, execute an INSERT statement possibly into a different table, then disconnect, returning true if the row existed, 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:
strSelectCount - SQL SELECT COUNT(*) FROM statement
strTableName - Name of the database table
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Returns:
True if the test row existed; false otherwise
Throws:
ConnectionPoolUtil.NoDataFoundException - When strSelectCount returns no rows, instead of returning a row containing the count as a SELECT COUNT(*) statement does.
SQLException - When a SQL error occurs, including when strSelectCount returns a non-Integer value.

insertIfNotExists

public boolean insertIfNotExists(String strTableName,
                                 String strWhere,
                                 String strCols,
                                 String strVals)
                          throws SQLException
Connect to the database, execute a SELECT COUNT(*) FROM statement to decide if a row exists and if not, execute an INSERT statement, then disconnect, returning true if the row existed, 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:
strTableName - Name of the database table.
strWhere - SQL WHERE clause to use in the SELECT statement to get the row count, without the keyword WHERE. Used internally as: SELECT COUNT(*) FROM strTableName WHERE strWhere
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Returns:
True if the row existed; false otherwise
Throws:
SQLException

insert

public void insert(String strTableName,
                   String strCols,
                   String strVals)
            throws SQLException
Connect to the database, execute an INSERT statement, then disconnect, 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:
strTableName - Name of the database table.
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Throws:
SQLException

buildUpdateString

public String buildUpdateString(String strTableName,
                                String strWhere,
                                String strSetColsVals)
Return an UPDATE statement composed from the specified table name, WHERE clause, columns and values.

Parameters:
strTableName - Name of the database table
strWhere - SQL WHERE clause to use in the UPDATE statement, without the keyword WHERE. Used internally as: UPDATE strTableName SET strSetColsVals WHERE strWhere
strSetColsVals - String of comma-separated column=value pairs to be used on the UPDATE as: UPDATE strTableName SET strSetColsVals WHERE strWhere
Returns:
The UPDATE statement

update

public void update(String strTableName,
                   String strWhere,
                   String strSetColsVals)
            throws SQLException
Connect to the database, execute an UPDATE statement, then disconnect, 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:
strTableName - Name of the database table.
strWhere - SQL WHERE clause to use in the UPDATE statement, without the keyword WHERE. Used internally as: UPDATE strTableName SET strSetColsVals WHERE strWhere
strSetColsVals - String of comma-separated column=value pairs to be used on the UPDATE as: UPDATE strTableName SET strSetColsVals WHERE strWhere
Throws:
SQLException

insertOrUpdate

public boolean insertOrUpdate(String strTableName,
                              String strWhere,
                              String strCols,
                              String strVals,
                              String strSetColsVals)
                       throws SQLException
Connect to the database, execute a SELECT COUNT(*) FROM statement to decide if a row exists and based on its existence, execute an INSERT or UPDATE statement, then disconnect, returning true if the row existed, 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:
strTableName - Name of the database table.
strWhere - SQL WHERE clause to use in the SELECT statement to get the row count, and in the UPDATE statement, without the keyword WHERE. Used internally as: SELECT COUNT(*) FROM strTableName WHERE strWhere and: UPDATE strTableName SET strSetColsVals WHERE strWhere
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strSetColsVals - String of comma-separated column=value pairs to be used on the UPDATE as: UPDATE strTableName SET strSetColsVals WHERE strWhere
Returns:
True if the row existed; false otherwise
Throws:
SQLException

insertIfNotExistsMultiTableReturnPK

public ConnectionPoolUtil.InsertOrUpdateResult insertIfNotExistsMultiTableReturnPK(String strSelectPKExisting,
                                                                                   String strSelectPKInserted,
                                                                                   String strTableName,
                                                                                   String strCols,
                                                                                   String strVals)
                                                                            throws ConnectionPoolUtil.NoDataFoundException,
                                                                                   SQLException
Connect to the database, execute a SELECT statement to get the primary key of an existing row, and if it doesn't exist, execute an INSERT statement possibly into a different table and execute a different SELECT statement to get a primary key, then disconnect, returning an InsertOrUpdateResult object, 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:
strSelectPKExisting - SQL SELECT statement to get the primary key of the existing row.
strSelectPKInserted - SQL SELECT statement to get the primary key of the inserted row.
strTableName - Name of the database table
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Returns:
InsertOrUpdateResult object containing the primary key returned by one of the SELECT statements and a boolean flag that is true if the test row existed; false otherwise.
Throws:
ConnectionPoolUtil.NoDataFoundException - When, after successfully executing the INSERT, strSelectPKInserted returns no rows.
SQLException

insertIfNotExistsReturnPK

public ConnectionPoolUtil.InsertOrUpdateResult insertIfNotExistsReturnPK(String strTableName,
                                                                         String strPKName,
                                                                         String strWhere,
                                                                         String strCols,
                                                                         String strVals)
                                                                  throws ConnectionPoolUtil.NoDataFoundException,
                                                                         SQLException
Connect to the database, execute a SELECT statement to get the primary key of an existing row, and if it doesn't exist, execute an INSERT statement into the table and re-execute the SELECT statement to get the primary key, then disconnect, returning an InsertOrUpdateResult object, 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:
strTableName - Name of the database table
strPKName - Name of the primary key of the table.
strWhere - SQL WHERE clause to use in the SELECT statement to get the primary key after doing or not doing the INSERT, without the keyword WHERE. Used internally as: SELECT strPKName FROM strTableName WHERE strWhere
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
Returns:
InsertOrUpdateResult object containing the primary key returned from the table by strWhere, regardless of whether the INSERT was necessary, and a boolean flag that is true if the row existed; false otherwise.
Throws:
ConnectionPoolUtil.NoDataFoundException - When, after successfully executing the INSERT, strWhere still returns no rows.
SQLException

updateReturnPK

public ConnectionPoolUtil.InsertOrUpdateResult updateReturnPK(String strTableName,
                                                              String strPKName,
                                                              String strWhere,
                                                              String strSetColsVals)
                                                       throws SQLException
Connect to the database, execute an UPDATE, then disconnect, returning an InsertOrUpdateResult object, 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:
strTableName - Name of the database table.
strPKName - Name of the primary key of the table.
strWhere - SQL WHERE clause to use in the UPDATE statement, without the keyword WHERE.
strSetColsVals - String of comma-separated column=value pairs to be used on the UPDATE as: UPDATE strTableName SET strSetColsVals WHERE strWhere
Returns:
InsertOrUpdateResult object containing the primary key of the first updated row and a boolean flag that is true if such a row existed; false otherwise.
Throws:
SQLException

insertOrUpdateReturnPK

public ConnectionPoolUtil.InsertOrUpdateResult insertOrUpdateReturnPK(String strTableName,
                                                                      String strPKName,
                                                                      String strWhere,
                                                                      String strCols,
                                                                      String strVals,
                                                                      String strSetColsVals)
                                                               throws ConnectionPoolUtil.NoDataFoundException,
                                                                      SQLException
Connect to the database, execute a SELECT statement to decide if a row exists and based on its existence, execute an INSERT or UPDATE statement, then disconnect, returning an InsertOrUpdateResult object, 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:
strTableName - Name of the database table.
strPKName - Name of the primary key of the table.
strWhere - SQL WHERE clause to use in the SELECT statement to get the primary key before doing the UPDATE or after doing the INSERT, and to use in the UPDATE statement, without the keyword WHERE. Used internally as: SELECT strPKName FROM strTableName WHERE strWhere and: UPDATE strTableName SET strSetColsVals WHERE strWhere
strCols - String of comma-separated column names to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strVals - String of comma-separated values to be used on the INSERT as: INSERT INTO strTableName ( strCols ) VALUES ( strVals )
strSetColsVals - String of comma-separated column=value pairs to be used on the UPDATE as: UPDATE strTableName SET strSetColsVals WHERE strWhere
Returns:
InsertOrUpdateResult object containing the primary key of the inserted or updated row and a boolean flag that is true if the row existed; false otherwise.
Throws:
ConnectionPoolUtil.NoDataFoundException - When strWhere specifies no rows in strTableName after the INSERT or UPDATE is done.
SQLException

databaseConnectionIsValid

public static boolean databaseConnectionIsValid(Connection conn)
Test the specified database connection.

Parameters:
conn - Database connection
Returns:
True if valid; False if invalid.

cleanupDBContext

public static void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext,
                                    boolean blnClose,
                                    boolean blnDoneWithConnection,
                                    Logger logger,
                                    int intLogLevelOfOperation,
                                    ConnectionPool pool)
Cleanup the database objects, optionally closing the database connection.

Parameters:
dbContext - DBContext object containing the database objects.
blnClose - Boolean flag indicating whether to close the connection. Ignored if blnDoneWithConnection is false.
blnDoneWithConnection - Boolean flag indicating whether the caller is done with the connection. If so, it is released to the connection pool, unless blnClose indicates that it should be closed.
logger - Logger to log operations to. Optional. If null, no logging is done.
intLogLevelOfOperation - Level at which to log operations.
pool - ConnectionPool to return the pooled connection to, or null if the connection is not to be returned.

cleanupDBContext

public static void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext,
                                    Logger logger,
                                    int intLogLevelOfOperation,
                                    ConnectionPool pool)
Cleanup the database objects, leaving the database connection open, and returning it to the connection pool, if any. This is a convenience method to handle a common use case.

Parameters:
dbContext - DBContext object containing the database objects.
logger - Logger to log operations to. Optional. If null, no logging is done.
intLogLevelOfOperation - Level at which to log operations.
pool - ConnectionPool to return the pooled connection to, or null if the connection is not to be returned.

cleanupDBContext

public void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext,
                             boolean blnClose,
                             boolean blnDoneWithConnection)
Cleanup the database objects, optionally closing the database connection, using the default values for Logger and ConnectionPool. This method is useful for the when it is more convenient to set the defaults once than to specify them on each call.

Parameters:
dbContext - DBContext object containing the database objects.
blnClose - Boolean flag indicating whether to close the connection. Ignored if blnDoneWithConnection is false.
blnDoneWithConnection - Boolean flag indicating whether the caller is done with the connection. If so, it is released to the connection pool, unless blnClose indicates that it should be closed.

cleanupDBContext

public void cleanupDBContext(ConnectionPoolUtil.DBContext dbContext)
Cleanup the database objects, leaving the database connection open, and returning it to the connection pool, if any, using the default values for Logger and ConnectionPool. This method is useful when it is more convenient to set the defaults once than to specify them on each call.

Parameters:
dbContext - DBContext object containing the database objects.