soap tutorial

adriani.jws.template.db
Class GeneralDAO

java.lang.Object
  extended by adriani.jws.template.db.GeneralDAO
Direct Known Subclasses:
ServiceDAO

public abstract class GeneralDAO
extends java.lang.Object

GeneralDAO - general class used to provide some common methods to all database accessors (DAO classes).
This class defines some general purposes objects: class fields, double quoting methods, introspection methods, standard accessors and benchmark analysis. The core utilities of this class is a set of methods wrapping the more common SQL statements: retrieving one single record, retrieving a result set, calling a PL/SQL function or procedure, calling a pipelined PL/SQL function etc. All connections issues SHOULD NOT be implemented at this level, but should be left to the children classes, in order to reflect the detailed requirements of the business logic.


Field Summary
static int AUTO
          Reference to the "auto-type" feature allowing to inspect column's datatypes.
protected  long chronStart
          Long timestamp used to evaluate performance time when the ANALYSIS mode is used.
protected static java.text.SimpleDateFormat db_timestamp
          DateFormat static reference for conversion from the format YYYY-MM-DD HH-MM-SS.M.
static java.lang.String DOUBLE_QUOTES
          Static final String value stored as class fields to improve performances.
protected static java.lang.String EMPTY
          Static final String value stored as class fields to improve performances.
protected static java.lang.String ERROR
          The message to print in the case of a general Java Exception .
protected  java.lang.String leadingName
          The leading name of each message logged by this class.
protected static WSLogger logger
          Retrieving the logger instance to be used for this class.
protected static int NO_DATA_FOUND
          The integer storing the Oracle error code ORA-01403: no data found.
protected static java.lang.String NO_DATA_MSG
          The message to print if the query returned no data at all.
static java.lang.String PERCENT
          Static final String value stored as class fields to improve performances.
protected static java.lang.String PSTMT_NOK
          The message to print if preparation of the prepared statement failed.
protected static java.lang.String QUERY_NOK
          The message to print in the case of a SQLException .
static java.lang.String QUOTE
          Static final String value stored as class fields to improve performances.
protected static java.lang.String ROLLBACK_NOK
          The message to print in the case of a rollback failure.
protected  int status
          Status of the DAO after execution of the last SQL statement.
static int VARCHAR
          Reference to the "auto-type" feature allowing to inspect column's datatypes (default value)
 
Constructor Summary
GeneralDAO()
          Empty constructor.
 
Method Summary
 void collectStatus(int statusValue)
          Subclasses SHOULD override this method in order to log the final execution status of a general SQL statement.
 boolean compareSB(java.lang.StringBuffer first, java.lang.StringBuffer second)
          Helper method, used to compare StringBuffer contents.
 java.lang.String doubleQuote(java.lang.String input)
          Adds double quotation marks to the input String to avoid SQL error.
 void endQuery(java.lang.String msg)
          Log performances data on the Performances Analyser and flush the relative Handler.
 int executeUpdate(int targetDB, java.lang.String label, java.lang.String query)
          Execute the general UPDATE query passed in input.
 int executeUpdate(java.sql.PreparedStatement pstmt, java.lang.String label)
          Executes a PreparedStatement to the target database, returning an integer number describing the number of affected rows.
 java.lang.String fetchColumnData(java.sql.ResultSet rs, int col)
          Fetch the column data automatically.
 void finalize()
          Method useful for debug and testing, used to verify the scope of the children classes.
 void flushLogger(java.util.logging.Logger logger)
          Flush the handler bound to the given Logger.
 java.lang.String getColumnData(java.sql.ResultSet rs, int col, int type)
          Return an String containing data of the input indexed column, read using the proper method for the column java.sql.Types type.
 int getColumnType(java.sql.ResultSet rs, int col)
          Inspect the ResultSetMetaData for the input indexed column, and return a formatted StringBuffer object containing the column's metadata.
abstract  java.sql.Connection getConnection(int targetDB)
          Return the connection currently opened through the target database, for example:

targetDB = DB_01 --> get the connection opened to the DB_01 database.
 java.lang.StringBuffer[] getFunction(int targetDB, java.lang.String label, java.lang.String query, java.lang.Object[] param)
          Executes a PL/SQL FUNCTION on the target database, returning a single record wrapped by a StringBuffer array.
 java.lang.StringBuffer[] getFunction(int targetDB, java.lang.String label, java.lang.String query, java.lang.Object[] param, int type)
          Executes a PL/SQL FUNCTION on the target database, returning a single record wrapped by a StringBuffer array.
 java.lang.StringBuffer[][] getFunctionPipe(int targetDB, java.lang.String label, java.lang.String query, int cols)
          Executes a PL/SQL FUNCTION on the target database, returning a set of records described by an array whose elements are others StringBuffer arrays:

StringBuffer[n][i]

contains the i-th column of the n-th record.
 java.lang.StringBuffer[] getPreparedRecord(java.sql.PreparedStatement pstmt, java.lang.String label, int cols)
          Executes a PreparedStatement to the target database, returning a single record described by a StringBuffer array.
 java.lang.StringBuffer[][] getPreparedRecordSet(java.sql.PreparedStatement pstmt, java.lang.String label, int cols)
          Executes a PreparedStatement to the target database, returning a set of records described by an array which elements are others StringBuffer arrays, where:

StringBuffer[n][i]

contains the i-th column of the n-th record.
 java.lang.StringBuffer[] getProcedure(int targetDB, java.lang.String label, java.lang.String query, java.lang.Object[] param, int cols, int[] inout)
          Executes a PL/SQL PROCEDURE call to the target database, returning a single record described by a StringBuffer array.
 java.lang.StringBuffer[] getRecord(int targetDB, java.lang.String label, java.lang.String query, int cols)
          Executes a SELECT query to the target database, returning a single record described by a StringBuffer array.
 java.lang.StringBuffer[] getRecord(int targetDB, java.lang.String label, java.lang.String query, int cols, int type)
          Executes a SELECT query to the target database, returning a single record described by a StringBuffer array.
 java.lang.StringBuffer[][] getRecordSet(int targetDB, java.lang.String label, java.lang.String query, int cols)
          Executes a SELECT query to the target database, returning a set of records described by an array whose elements are others StringBuffer arrays:

StringBuffer[n][i]

contains the i-th column of the n-th record.
 java.lang.StringBuffer[][] getRecordSet(int targetDB, java.lang.String label, java.lang.String query, int cols, int type)
          Executes a SELECT query to the target database, returning a set of records described by an array whose elements are others StringBuffer arrays:

StringBuffer[n][i]

contains the i-th column of the n-th record.
static int getRowCount(java.sql.ResultSet set)
          Determines the number of rows in a ResultSet.
 int getStatus()
          Return an int code describing the status of the DAO after execution of the last SQL-oriented label.
 java.lang.StringBuffer inspectColumn(java.sql.ResultSet rs, int col)
          Inspect the ResultSetMetaData for the input indexed column, and return a formatted StringBuffer object containing the column's metadata.
 java.lang.StringBuffer inspectResultSet(java.sql.ResultSet rs)
          This method can be invoked immediately after the stmt.executeQuery(query) call to inspect the retrieved ResultSet.
 java.lang.String quote(java.lang.String input)
          Execute a doubleQuote() method on the input string, then surround the result with a pair of single quotes.
 void startQuery(java.lang.String msg)
          Log performances data on the Performances Analyser and flush the relative Handler.
protected  int valueOfString(java.lang.String string, int no_value)
          Parse the string value of the input String into an integer value.
protected  java.sql.Timestamp valueOfTimestamp(java.lang.String string)
          Parse the string value of the input String into a timestamp value.
 
Methods inherited from class java.lang.Object
clone, equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

status

protected int status
Status of the DAO after execution of the last SQL statement.


chronStart

protected long chronStart
Long timestamp used to evaluate performance time when the ANALYSIS mode is used.


EMPTY

protected static final java.lang.String EMPTY
Static final String value stored as class fields to improve performances.

See Also:
Constant Field Values

QUOTE

public static final java.lang.String QUOTE
Static final String value stored as class fields to improve performances.

See Also:
Constant Field Values

PERCENT

public static final java.lang.String PERCENT
Static final String value stored as class fields to improve performances.

See Also:
Constant Field Values

DOUBLE_QUOTES

public static final java.lang.String DOUBLE_QUOTES
Static final String value stored as class fields to improve performances.

See Also:
Constant Field Values

ERROR

protected static final java.lang.String ERROR
The message to print in the case of a general Java Exception .

See Also:
Constant Field Values

QUERY_NOK

protected static final java.lang.String QUERY_NOK
The message to print in the case of a SQLException .

See Also:
Constant Field Values

ROLLBACK_NOK

protected static final java.lang.String ROLLBACK_NOK
The message to print in the case of a rollback failure.

See Also:
Constant Field Values

PSTMT_NOK

protected static final java.lang.String PSTMT_NOK
The message to print if preparation of the prepared statement failed.

See Also:
Constant Field Values

NO_DATA_MSG

protected static final java.lang.String NO_DATA_MSG
The message to print if the query returned no data at all.

See Also:
Constant Field Values

NO_DATA_FOUND

protected static final int NO_DATA_FOUND
The integer storing the Oracle error code ORA-01403: no data found.

See Also:
Constant Field Values

VARCHAR

public static final int VARCHAR
Reference to the "auto-type" feature allowing to inspect column's datatypes (default value)

See Also:
Constant Field Values

AUTO

public static final int AUTO
Reference to the "auto-type" feature allowing to inspect column's datatypes.

See Also:
Constant Field Values

db_timestamp

protected static java.text.SimpleDateFormat db_timestamp
DateFormat static reference for conversion from the format YYYY-MM-DD HH-MM-SS.M.


logger

protected static WSLogger logger
Retrieving the logger instance to be used for this class.


leadingName

protected java.lang.String leadingName
The leading name of each message logged by this class.

Constructor Detail

GeneralDAO

public GeneralDAO()
Empty constructor.

Method Detail

finalize

public void finalize()
Method useful for debug and testing, used to verify the scope of the children classes.

Overrides:
finalize in class java.lang.Object

getConnection

public abstract java.sql.Connection getConnection(int targetDB)
Return the connection currently opened through the target database, for example:

targetDB = DB_01 --> get the connection opened to the DB_01 database.
targetDB = DB_02 --> get the connection opened to the DB_02 database.
etc...

It's up to the specific subclass decide how to obtain and return database connections, usually using one of the JDBCConnectionPool included in this package.

Parameters:
targetDB - the number of the database offering the connection
Returns:
the currently opened connection to the target database

startQuery

public void startQuery(java.lang.String msg)
Log performances data on the Performances Analyser and flush the relative Handler. This method automatically adds a detailed timestamp to the logged message, and store the starting timestamp in the chronStart class field.

Parameters:
msg - the string text name of the query that is going to be executed
See Also:
Settings.BENCHMARK

endQuery

public void endQuery(java.lang.String msg)
Log performances data on the Performances Analyser and flush the relative Handler. This mehod compares the query end time with the query starting time and log the query duration time in the performances logger.

Parameters:
msg - the string text name of the query that has just been terminated
See Also:
Settings.BENCHMARK

flushLogger

public void flushLogger(java.util.logging.Logger logger)
Flush the handler bound to the given Logger. Notice that the benchmark feature relies always on the SystemLogger class, never on the Log4j mechanism.

Parameters:
logger - the key string reference to the logger object to be flushed
See Also:
SystemLogger

collectStatus

public void collectStatus(int statusValue)
Subclasses SHOULD override this method in order to log the final execution status of a general SQL statement. For example, if the subclass relies on the ServiceAnalyzer mechanism for collecting statistics about SQL execution status, implementation of this method could be something like:

serviceMacro.getServiceAnalyzer().collect(status) ;

Current implementation (of this class) does nothing.

Parameters:
statusValue - the integer status as defined by the ErrorMessage class fields
See Also:
ServiceAnalyzer, ErrorMessage

getRecord

public java.lang.StringBuffer[] getRecord(int targetDB,
                                          java.lang.String label,
                                          java.lang.String query,
                                          int cols)
Executes a SELECT query to the target database, returning a single record described by a StringBuffer array. The length of the array is defined by the cols input parameter, representing the number of columns to be retrieved from the database. This method just forward action to the full-signature getRecord() method, assuming as default return types the VARCHAR type (for all columns).

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the SQL statement to be executed, already filled with parameters
cols - the number of columns to be retrieved from the database
Returns:
a StringBuffer array containing the selected columns (from the same record)
See Also:
getRecord(int targetDB, String label, String query, int cols, int type)

getRecordSet

public java.lang.StringBuffer[][] getRecordSet(int targetDB,
                                               java.lang.String label,
                                               java.lang.String query,
                                               int cols)
Executes a SELECT query to the target database, returning a set of records described by an array whose elements are others StringBuffer arrays:

StringBuffer[n][i]

contains the i-th column of the n-th record.

This method just forward action to the full-signature getRecordSet() method, assuming as default return types the VARCHAR type (for all columns).

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the SQL statement to be executed, already filled with parameters
cols - the number of columns to be retrieved from the database
Returns:
a StringBuffer 2D array containing all the matching records

getRecord

public java.lang.StringBuffer[] getRecord(int targetDB,
                                          java.lang.String label,
                                          java.lang.String query,
                                          int cols,
                                          int type)
Executes a SELECT query to the target database, returning a single record described by a StringBuffer array. The length of the array is defined by the cols input parameter, representing the number of columns to be retrieved from the database.

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the SQL statement to be executed, already filled with parameters
cols - the number of columns to be retrieved from the database
type - a reference to the criterion used to evaluate columns types
Returns:
a StringBuffer array containing the selected columns (from the same record)

getRecordSet

public java.lang.StringBuffer[][] getRecordSet(int targetDB,
                                               java.lang.String label,
                                               java.lang.String query,
                                               int cols,
                                               int type)
Executes a SELECT query to the target database, returning a set of records described by an array whose elements are others StringBuffer arrays:

StringBuffer[n][i]

contains the i-th column of the n-th record.

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the SQL statement to be executed, already filled with parameters
cols - the number of columns to be retrieved from the database
type - a reference to the criterion used to evaluate columns types
Returns:
a StringBuffer 2D array containing all the matching records

getFunction

public java.lang.StringBuffer[] getFunction(int targetDB,
                                            java.lang.String label,
                                            java.lang.String query,
                                            java.lang.Object[] param)
Executes a PL/SQL FUNCTION on the target database, returning a single record wrapped by a StringBuffer array.
This method just forward action to the full-signature getFunction() method, assuming as default return type the VARCHAR type.

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the prepared call statement to be executed, not yet filled with parameters
param - the Object array containing the input parameters
Returns:
a StringBuffer array containing the selected columns
See Also:
getFunction(int targetDB, String label, String query, Object[] param, int type)

getFunction

public java.lang.StringBuffer[] getFunction(int targetDB,
                                            java.lang.String label,
                                            java.lang.String query,
                                            java.lang.Object[] param,
                                            int type)
Executes a PL/SQL FUNCTION on the target database, returning a single record wrapped by a StringBuffer array.
Current implementation relies on the {? = call FUNCTION(?, ?, ?, ....)} syntax, because is generally more performing than the alternative approaches:

SELECT FUNCTION(x, y, z, ...) FROM DUAL (ordinary plain old java).
SELECT FUNCTION(?, ?, ?, ...) FROM DUAL (JDBC prepared statement).

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the prepared call statement to be executed, not yet filled with parameters
param - the Object array containing the input parameters
type - the java.sql.Types output type
Returns:
a StringBuffer array containing the selected columns

getFunctionPipe

public java.lang.StringBuffer[][] getFunctionPipe(int targetDB,
                                                  java.lang.String label,
                                                  java.lang.String query,
                                                  int cols)
Executes a PL/SQL FUNCTION on the target database, returning a set of records described by an array whose elements are others StringBuffer arrays:

StringBuffer[n][i]

contains the i-th column of the n-th record.

Current implementation is just a wrapper to the getRecordSet() method, since the best way to execute a function returning a pipe is to use an ordinary SELECT statement.

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the SQL statement to be executed, already filled with parameters
cols - the number of columns to be retrieved from the database
Returns:
a StringBuffer 2D array containing all the matching records

getProcedure

public java.lang.StringBuffer[] getProcedure(int targetDB,
                                             java.lang.String label,
                                             java.lang.String query,
                                             java.lang.Object[] param,
                                             int cols,
                                             int[] inout)
Executes a PL/SQL PROCEDURE call to the target database, returning a single record described by a StringBuffer array. The length of the output array is defined by the cols input parameter (number of columns to be retrieved from the record).

If the procedure does not require any IN parameter the input param array must be null. If the procedure requires some IN OUT parameters, the index of these parameters MUST BE specified in the inout input integer array. If there are no IN OUT parameters, the inout array MUST BE null.

Example: to call the PL/SQL procedure FOO(a IN, b IN OUT, c IN, d IN OUT, e OUT) this method must be invoked using:
param = {1,2,3,4} ; cols = 3 ; inout = {2,4}.

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the SQL statement to be executed, not yet filled with parameters
param - the Object array containing the input parameters
cols - the number of output columns to be retrieved from the database
inout - an int array specifying the index of the IN OUT parameters (null if there are no IN OUT params)
Returns:
a StringBuffer array containing the selected columns

getPreparedRecord

public java.lang.StringBuffer[] getPreparedRecord(java.sql.PreparedStatement pstmt,
                                                  java.lang.String label,
                                                  int cols)
Executes a PreparedStatement to the target database, returning a single record described by a StringBuffer array. The length of the array is defined by the cols input parameter, i.e. the number of columns to be retrieved from the database.

Remark: if the database rely on the mock connection pooling, this method will not close the prepared statement, in order to leave to the Database class the choice to eventually close or cache the prepared statement (after usage).

Parameters:
pstmt - the PreparedStatement to be executed, already filled with parameters
label - the logical name of the statement, only for logging purposes
cols - the number of columns to be retrieved from the database
Returns:
a StringBuffer array containing the selected columns

getPreparedRecordSet

public java.lang.StringBuffer[][] getPreparedRecordSet(java.sql.PreparedStatement pstmt,
                                                       java.lang.String label,
                                                       int cols)
Executes a PreparedStatement to the target database, returning a set of records described by an array which elements are others StringBuffer arrays, where:

StringBuffer[n][i]

contains the i-th column of the n-th record.

Remark: if the database relies on the mock connection pooling, this method will not close the prepared statement, in order to leave to the Database class the choice to eventually close or cache the prepared statement (after usage).

Parameters:
pstmt - the PreparedStatement to be executed, already filled with parameters
label - the logical name of the statement, only for logging purposes
cols - the number of columns to be retrieved from the database
Returns:
a StringBuffer double array containing all the matching records

executeUpdate

public int executeUpdate(int targetDB,
                         java.lang.String label,
                         java.lang.String query)
Execute the general UPDATE query passed in input. This method can be used any time the update is performed using normal statements.

Parameters:
targetDB - the number of the database where to execute the query
label - the logical name of the statement, only for logging purposes
query - the query that must be executed
Returns:
the number of rows inserted in the database

executeUpdate

public int executeUpdate(java.sql.PreparedStatement pstmt,
                         java.lang.String label)
Executes a PreparedStatement to the target database, returning an integer number describing the number of affected rows.

Parameters:
pstmt - the PreparedStatement to be executed, already filled with parameters
label - the logical name of the statement, only for logging purposes
Returns:
the number of rows inserted in the database

getStatus

public int getStatus()
Return an int code describing the status of the DAO after execution of the last SQL-oriented label.

Returns:
an integer code describing the status of the DAO after execution of the last SQL statement

fetchColumnData

public java.lang.String fetchColumnData(java.sql.ResultSet rs,
                                        int col)
                                 throws java.sql.SQLException
Fetch the column data automatically. This is achieved by inspecting the ResultSetMetaData for the input indexed column, and returning a formatted StringBuffer object containing the column's data. This is just a shortcut to invocation of following methods:

 int colType = getColumnType(result, n) ;
return getColumnData(result, n, colType) ;

Parameters:
rs - the ResultSet as returned by the database
col - the index of the column to be inspected
Returns:
a String representation of the column's content
Throws:
java.sql.SQLException
See Also:
getColumnType(ResultSet rs, int col), throws SQLException

getColumnType

public int getColumnType(java.sql.ResultSet rs,
                         int col)
Inspect the ResultSetMetaData for the input indexed column, and return a formatted StringBuffer object containing the column's metadata.

Parameters:
rs - the ResultSet as returned by the database
col - the index of the column to be inspected
Returns:
the integer reference to the java.sql.Types column type

getColumnData

public java.lang.String getColumnData(java.sql.ResultSet rs,
                                      int col,
                                      int type)
                               throws java.sql.SQLException
Return an String containing data of the input indexed column, read using the proper method for the column java.sql.Types type.

Parameters:
rs - the ResultSet as returned by the database
col - the index of the column to be read
type - the java.sql.Types describing the column
Returns:
a String representation of the column's content
Throws:
java.sql.SQLException

doubleQuote

public java.lang.String doubleQuote(java.lang.String input)
Adds double quotation marks to the input String to avoid SQL error. In example, if the input String is like Video's, the returned String will be: Video''s.

Parameters:
input - the string that can contain single quote characters
Returns:
the same string with all single quotes converted to double quotes

quote

public java.lang.String quote(java.lang.String input)
Execute a doubleQuote() method on the input string, then surround the result with a pair of single quotes.
Example: a string like Video's will become 'Video''s'.

Parameters:
input - the string that can contain single quote characters
Returns:
the same string with all single quotes converted to double quotes, enclosed within quotes

valueOfString

protected int valueOfString(java.lang.String string,
                            int no_value)
Parse the string value of the input String into an integer value. The no_value parameter represent the default integer value to be returned if the parsing is not successful.

Parameters:
string - the input string object
no_value - the integer value to be returned if conversion fails
Returns:
the integer value of the input string (or the no_value number)

valueOfTimestamp

protected java.sql.Timestamp valueOfTimestamp(java.lang.String string)
Parse the string value of the input String into a timestamp value.

Parameters:
string - the input string object
Returns:
the timestamp value of the input string

compareSB

public boolean compareSB(java.lang.StringBuffer first,
                         java.lang.StringBuffer second)
Helper method, used to compare StringBuffer contents.

Parameters:
first - a StringBuffer object
second - another StringBuffer object
Returns:
true if the two string object contains the same text

inspectResultSet

public java.lang.StringBuffer inspectResultSet(java.sql.ResultSet rs)
This method can be invoked immediately after the stmt.executeQuery(query) call to inspect the retrieved ResultSet. The obtained meta data are returned as a StringBuffer object.

Parameters:
rs - the ResultSet as returned by the database
Returns:
a StringBuffer object containing meta data information about the result set

getRowCount

public static int getRowCount(java.sql.ResultSet set)
                       throws java.sql.SQLException
Determines the number of rows in a ResultSet. Upon exit, if the cursor was not currently on a row, it is just before the first row in the result set (a call to ResultSet.next() will go to the first row).

Parameters:
set - The ResultSet to check (must be scrollable).
Returns:
The number of rows.
Throws:
java.sql.SQLException - If the ResultSet is not scrollable.

inspectColumn

public java.lang.StringBuffer inspectColumn(java.sql.ResultSet rs,
                                            int col)
Inspect the ResultSetMetaData for the input indexed column, and return a formatted StringBuffer object containing the column's metadata.

Parameters:
rs - the ResultSet as returned by the database
col - the index of the column to be inspected
Returns:
a StringBuffer object containing meta data information about the column

Build 18/09/2012

Morpheus Quality Technologies - Making the Zion software
Copyright © 2009-2013 Morpheus Quality Technologies. All Rights Reserved