com.bristle.javalib.sql
Class ConnectionPoolUtil

java.lang.Object
  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

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

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

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.