com.quadrique.jbuildingblocks.core.database
Class JbbDatabaseClient

java.lang.Object
  com.quadrique.jbuildingblocks.core.database.JbbDatabaseClient

public class JbbDatabaseClient
extends Object

This class encapsulates most of the JDBC api and provides a synchronous interface to any database.

Since:

01/19/2001

Version:

1.2.0

Author:

Hervé Rivere

Method Summary

void	acquireConnection(Context zContext, String zJndiDataSourceName)
void	acquireConnection(String zUrl, String zLogin, String zPassword, String zDriver, int zLoginTimeout) This method initializes the corresponding driver and establishes a connection with the database.
int[]	executeBatch(ArrayList<String> zSqlRequests, int zTimeOut) Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
int	executePreparedStatement(PreparedStatement zPreparedStatement, int zTimeOut) Executes a prepared statement which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
ResultSet	executePreparedStatementWithResultSet(PreparedStatement zPreparedStatement, int zTimeOut) Executes a prepared statement that returns a result set Please note that the call is executed in autocommit mode.
int	executeStatement(String zSqlRequest, int zTimeOut) Executes a SQL statement that does not return a result set (i.e.
ResultSet	executeStatementWithResultSet(String zSqlRequest, int zTimeOut) Executes an SQL statement that returns a single ResultSet object.
int	executeStoredProcedure(CallableStatement zStoredProcRequest, int zTimeOut) Executes a stored procedure that only returns an integer.
ResultSet	executeStoredProcedureWithResultSet(CallableStatement zStoredProcRequest, int zTimeOut) Executes a stored procedure that returns a single ResultSet object.
CallableStatement	getCallableStatement(String zCall, int zMaxNbOfRecords) This procedure creates and returns a callable statement instance so that the corresponding stored procedure input and output parameters (if any) can be set prior to performing a executeXxxxStoredProcedures() call.
Connection	getMConnection()
PreparedStatement	getPreparedStatement(String zCall, int zMaxNbOfRecords) This procedure creates and returns a prepared statement instance (i.e.
ResultSet	getResultSet()
ResultSet	getResultSet(int index)
static JbbDatabaseClient	newInstance()
void	releaseConnection() Clean up the full intance.
void	releaseResultSetAndStatementOnly() Clean up the result set and statement that were allocated.
void	setLog(String zLogFileName) This method is used to turn on the traces of the driver manager and the drivers.
void	setResultSet(ResultSet rs)

Methods inherited from class java.lang.Object

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

Method Detail

newInstance

public static JbbDatabaseClient newInstance()

Returns:

a new instance

acquireConnection

public void acquireConnection(String zUrl,
                              String zLogin,
                              String zPassword,
                              String zDriver,
                              int zLoginTimeout)
                       throws Exception

This method initializes the corresponding driver and establishes a connection with the database.

Parameters:

zUrl - the JDBC URL of the database,

zLogin - the login to use for the database connection,

zPassword - the password to use for the database connection,

zDriver - the driver class to use (e.g. "sun.database.odbc.JdbcOdbcDriver"),

zLoginTimeout - the maximum time in seconds that a driver will wait while attempting to connect to a database.

Throws:

Exception

acquireConnection

public void acquireConnection(Context zContext,
                              String zJndiDataSourceName)
                       throws Exception

Parameters:

zContext -

zJndiDataSourceName -

Throws:

Exception

setLog

public void setLog(String zLogFileName)
            throws Exception

This method is used to turn on the traces of the driver manager and the drivers.

Parameters:

zLogFileName - fully qualified name (e.g. "C\Temp\log.txt") of the file that should be used by the driver manager and all the drivers for trace/log.

Throws:

Exception

executeStatement

public int executeStatement(String zSqlRequest,
                            int zTimeOut)
                     throws Exception

Executes a SQL statement that does not return a result set (i.e. that only returns a return classLoader value or that returns nothing). This covers the SQL INSERT, UPDATE or DELETE statement. In addition, SQL statements that return nothing, such as SQL DDL statements, can be executed. Please note that the statement is executed in autocommit mode.

Parameters:

zSqlRequest - the SQL statement to execute,

zTimeOut - the timeout value to use for the request.

Returns:

the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing

Throws:

Exception

executeBatch

public int[] executeBatch(ArrayList<String> zSqlRequests,
                          int zTimeOut)
                   throws Exception

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch. The elements in the array returned by the method executeBatch may be one of the following: - A number greater than or equal to zero -- indicates that the command was processed successfully and is an update count giving the number of rows in the database that were affected by the command's execution - A value of SUCCESS_NO_INFO -- indicates that the command was processed successfully but that the number of rows affected is unknown If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following: A value of EXECUTE_FAILED -- indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails The possible implementations and return values have been modified in the Java 2 SDK, Standard Edition, version 1.3 to accommodate the option of continuing to process commands in a batch update after a BatchUpdateException object has been thrown. Returns: an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.

Throws:

Exception

executeStatementWithResultSet

public ResultSet executeStatementWithResultSet(String zSqlRequest,
                                               int zTimeOut)
                                        throws Exception

Executes an SQL statement that returns a single ResultSet object. Please note that the statement is executed in autocommit mode.

Parameters:

zSqlRequest - the SQL SELECT statement to execute,

zTimeOut - the timeout value to use for the request in seconds .

Returns:

a ResultSet object that contains the data produced by the given query; never null

Throws:

Exception

executeStoredProcedure

public int executeStoredProcedure(CallableStatement zStoredProcRequest,
                                  int zTimeOut)
                           throws Exception

Executes a stored procedure that only returns an integer.
This covers stored procedures that perform updates (INSERT, UPDATE, DELETE or any other DDL statements).
Please note that the stored procedure is executed in autocommit mode.
Here is a sample usage:
CallableStatement lStatement = lHandler.getCallableStatement("{call SubDbDeleteCallScreeningRule}",50);
lHandler.executeStoredProcedure(lStatement,10);


Here is an other example with a stored procedure that has 4 input paramters:
CallableStatement lStatement = lHandler.getCallableStatement("{call SubDbDeleteCallScreeningRule (?,?,?,?)}",50);
lStatement.setObject(1,zCountryCode,java.sql.Types.VARCHAR);
lStatement.setObject(2,zAreaCode,java.sql.Types.VARCHAR);
lStatement.setObject(3,zSubNumber,java.sql.Types.VARCHAR);
lStatement.setObject(4,zAni,java.sql.Types.VARCHAR);
lHandler.executeStoredProcedure(lStatement,10);


Here is an other example with a stored procedure that has an output parameter:
lStatement = lHandler.getCallableStatement("{? = call SubDbIsSubscrAccountCreated( ? , ? , ?)}",50);

lStatement.registerOutParameter(1, java.sql.Types.INTEGER);
lStatement.setObject(2,z_CountryCode,java.sql.Types.VARCHAR);
lStatement.setObject(3,z_AreaCode,java.sql.Types.VARCHAR);
lStatement.setObject(4,z_SubNumber,java.sql.Types.VARCHAR);

lHandler.executeStoredProcedure(lStatement,10);
lResult = lStatement.getInt(1);

Parameters:

zStoredProcRequest - the callable statement corresponding to the stored procedure to execute,

zTimeOut - the timeout value to use for the request in seconds.

Returns:

the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing

Throws:

Exception

executeStoredProcedureWithResultSet

public ResultSet executeStoredProcedureWithResultSet(CallableStatement zStoredProcRequest,
                                                     int zTimeOut)
                                              throws Exception

Executes a stored procedure that returns a single ResultSet object. Please note that the stored procedure is executed in autocommit mode.
Here is a sample application:
lStatement = lHandler.getCallableStatement("{call SubDbRetrieveCallScreeningRules( ? , ? , ?)}",50);

lStatement.setObject(1,zCountryCode,java.sql.Types.VARCHAR);
lStatement.setObject(2,zAreaCode,java.sql.Types.VARCHAR);
lStatement.setObject(3,zSubNumber,java.sql.Types.VARCHAR);

ResultSet lRs = lHandler.executeStoredProcedureWithResultSet(l_Statement,10);

Parameters:

zStoredProcRequest - the callable statement correponding to the stored procedure to execute,

zTimeOut - the timeout value to use for the request in seconds.

Returns:

a ResultSet object that contains the data produced by the given query; never null

Throws:

Exception

getCallableStatement

public CallableStatement getCallableStatement(String zCall,
                                              int zMaxNbOfRecords)
                                       throws Exception

This procedure creates and returns a callable statement instance so that the corresponding stored procedure input and output parameters (if any) can be set prior to performing a executeXxxxStoredProcedures() call.
The IN parameter values are set using the setXxxx() methods .
The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the getXxx() methods.

Here is a sample usage:

CallableStatement lStatement = lHandler.getCallableStatement("{call SubDbDeleteCallScreeningRule (?,?,?,?)}",50);
lStatement.setObject(1,zCountryCode,java.sql.Types.VARCHAR);
lStatement.setObject(2,zAreaCode,java.sql.Types.VARCHAR);
lStatement.setObject(3,zSubNumber,java.sql.Types.VARCHAR);
lStatement.setObject(4,zAni,java.sql.Types.VARCHAR);
lHandler.executeStoredProcedure(lStatement,10);

Parameters:

zCall - the following string:
"{call SpXxx}" where SpXxx is a stored procedure with no parameter,
"{call SpXxx(?,..,?)" where SpXx is a stored procedure with input parameters (need one '?' for each parameter)
"{? = call SpXxx(?,..,?)" where SpXx is a stored procedure that returns a value and that has input parameters (need one '?' for each parameter)

zMaxNbOfRecords - the maximum number of records to return

Returns:

a CallableStatement instance

Throws:

Exception

getPreparedStatement

public PreparedStatement getPreparedStatement(String zCall,
                                              int zMaxNbOfRecords)
                                       throws Exception

This procedure creates and returns a prepared statement instance (i.e. a precompiled SQL statement that is more eficient to use) that can be used multiple times (just set the corresponding dynamic values before) in a executeXxxxPreparedStatement() call.
The IN parameter values are set using the setXxxx() methods .

Here is a sample usage:

PreparedStatement lStatement = lHandler.getPreparedStatement("select * from tablex_t where userName= ?",50);
lStatement.setObject(1,zUserName,java.sql.Types.VARCHAR);
lHandler.executePreparedStatement(lStatement,10);

Parameters:

zCall - the SQL query with ? for each input parameter

zMaxNbOfRecords - the maximum number of records to return

Returns:

a PreparedStatement instance

Throws:

Exception

executePreparedStatement

public int executePreparedStatement(PreparedStatement zPreparedStatement,
                                    int zTimeOut)
                             throws Exception

Executes a prepared statement which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
Please note that the call is executed in autocommit mode.
Here is a sample usage:
PreparedStatement lStatement = lHandler.getPreparedStatement("update users_t set type=7 where user=toto",50);
lHandler.executePreparedStatement(lStatement,10);


Parameters:

zPreparedStatement - the prepared statement

zTimeOut - the timeout value to use for the request in seconds.

Returns:

the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing

Throws:

Exception

executePreparedStatementWithResultSet

public ResultSet executePreparedStatementWithResultSet(PreparedStatement zPreparedStatement,
                                                       int zTimeOut)
                                                throws Exception

Executes a prepared statement that returns a result set
Please note that the call is executed in autocommit mode.
Here is a sample usage:
PreparedStatement lStatement = lHandler.getPreparedStatement("select * from users_t",50);
lRs = lHandler.executePreparedStatement(lStatement,10);


Parameters:

zPreparedStatement - the prepared statement

zTimeOut - the timeout value to use for the request in seconds.

Returns:

the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing

Throws:

Exception

releaseResultSetAndStatementOnly

public void releaseResultSetAndStatementOnly()
                                      throws Exception

Clean up the result set and statement that were allocated. The instance can still be used to perform other calls.

Throws:

Exception

releaseConnection

public void releaseConnection()
                       throws Exception

Clean up the full intance.

Throws:

Exception

getResultSet

public ResultSet getResultSet(int index)
                       throws Exception

Parameters:

index - the index of the ResultSet instance

Returns:

the ResultSet

Throws:

Exception

getResultSet

public ResultSet getResultSet()

Returns:

the ResultSet

setResultSet

public void setResultSet(ResultSet rs)

Parameters:

rs -

getMConnection

public Connection getMConnection()

Returns:

the mConnection