org.osesb.configuration
Class SQLStatementListConfiguration

java.lang.Object
  org.osesb.configuration.XMLConfiguration
      org.osesb.configuration.SQLStatementListConfiguration

public class SQLStatementListConfiguration
extends XMLConfiguration

SQLStatementListConfiguration extends XMLConfiguration to support a List of named, org.osesb.utilities.jdbc.SQLStatement(s).

Each SQLStatement includes:

• A name, which is used to retrieve the SQLStatement. The name is specified as an attribute to the <statement> element. see getSQLStatement().
• The sql string, which is specified as the value of the <statement> element.
• Optional SQL parameters, which are enclosed within the <params> content model, which is a child of a <statement> Element. <param> Elements are specified as children of <params>. Elements are processed into org.osesb.utilities.jdbc.SQLParameter objects by the SQLStatementListConfiguration Class.

For example, the following SQLStatementListConfiguration, which is a fragment from a DatabasePollerServiceConfiguration, illustrates a SQLStatementListConfiguration for a org.osesb.service.services.databasepoller.DatabasePollerService, which scans integration tables for changes, transforms new or modified rows to XML, and sends the XML to osESB for processing. The 'selectStatement' scans the integration table and the 'postSelectUpdateStatement' is a prepared update statement with parameters that check-points processed rows with a timestamp so that rows are not re-processed during subsequent scans of the integration table. Note the use of the ${now} scripted variable, which provides the run-time value of the timestamp.

<sqlStatements>
              <!-- Generate a DatabaseServiceEvent for all the rows since we last processed. -->
              <statement name="selectStatement">
                  SELECT * from ADMINISTRATOR.DBPOLLER_PROJECT WHERE EAI_CHKPOINT IS NULL
              </statement>
              <!-- Check-point all the rows we just processed.  All rows
              are processed  into the same XML message. All rows get the same
              values for EAI_CHKPOINT and EAI_MSGID and can be processed by a
              single UPDATE statement.-->
              <statement name="postSelectUpdateStatement">
                  UPDATE ADMINISTRATOR.DBPOLLER_PROJECT SET EAI_CHKPOINT=? WHERE EAI_CHKPOINT IS NULL
                  <params>
                      <param
                          name="eai-chkpoint"
                          type="input"
                          sqlType="java.sql.Types.TIMESTAMP"
                          value="${now}">
                      </param>
                  </params>
              </statement>
          </sqlStatements>
 

The following descibes the required and optional parts of a element. A SQLStatement must have a sql string and a name.

          1.  The SQL statement, which is the value of the Element. (required)

          2.  a 'name' Attribute, which facilitates retrieval of the SQLStatement
          from the SQLStatementListConfiguration. (required)

          3. 'resultSetType' and 'resultSetConcurrency' Attributes for specifying
          scrollable and updatable ResultSets. The resultSetType and resultSetConcurrency
          attributes must be valid field names of java.sql.ResultSet. These parameters
          are passed as arguments to the createStatement(), createPreparedStatement(), and
          createCallableStatement() methods of the SQLStatement Class.

          Optional SQL parameters are enclosed within the <params> content model, which is a
          child of a <statement> Element.  <param> Elements are specified as
          children of <params>.  Elements are processed into
          org.osesb.utilities.jdbc.SQLParameter objects by the SQLStatementListConfiguration Class.
          <param> Elements include only Attributes, which are:

          1.  a 'name' Attribute, which facilitates access to the parameter
          by a TableSetAdapter.

          2. a 'type' attribute, which must be 'input' (default) for parameters, which
          are used as input parameters to SELECT, UPDATE, INSERT, DELETE, and Call
          statements or 'scrollUpdate' parameters, which are used as input to
          scrollable, updatable ResultSet(s).

          The values of 'input' parameters are used as input to java.sql.PreparedStatement
          and java.sql.CallableStatement setXXX() methods.

          The values of 'scrollUpdate' parameters are used as input to ResultSet
          updateXXX methods.

          3. a 'columnName' Attribute that associates a column name with a 'scrollUpdate'
          parameter value.

          4. a 'value' Attribute, which may be a literal value or a scripted value.
          For parameters that should be evaluated at run-time, the framework supports the
          following scripted variables:

              1. for java.sql.Types.DATE: ${now}, ${week-before}, ${week-after},...

              2. ${msgid} for a UUID message identifier that is propagated in the message that
              is sent to the destination.

              See the See the org.osesb.scriptedvariables package.

          5. a 'sqlType' Attribute, which specifies the java.sql.Type of the 'value'
          Attribute.  'sqlType' must be specified as a java.sql.Types field name like
          'java.sql.Types.DATE' or 'DATE'

          6. a 'dateTimeFormat' Attribute for specifiying the format of a parameter that is
          given as a literal date.

SQLStatements are retrieved from a SQLStatementListConfiguration using:

• getSQLStatement(), which returns the named SQLStatement.
• getSQLStatements(), which returns all SQLStatement(s) as a Map with the statement name as the key and the SQLStatement as the value.
• iterator(), which returns an Iterator over the SQLStatement(s). The Iterator retains the order of addition of the SQLStatement(s).

Since:

Version .9

Field Summary

static java.lang.String	SQLSTATEMENT_TAGNAME the repeating SQL statement element tag names in the SQLStatementListConfiguration configuration.
static java.lang.String	SQLSTATEMENTLISTCONFIG_CONTENTMODEL SQLStatementListConfiguration content model tag.
protected org.apache.commons.collections.map.ListOrderedMap	sqlStatements ListOrderedMap of org.osesb.utilities.jdbc.SQLStatement(s).

Fields inherited from class org.osesb.configuration.XMLConfiguration

configFilePath, configurationProperties, constructedFromConfigFile, contentModelElement, ftpClientConfiguration, jaxrpcClientConfiguration, jdbcConfiguration, jmsClientConfiguration, log, rootConfigurationAttributes, smtpClientConfiguration, systemProperties, thisClassName

Constructor Summary

SQLStatementListConfiguration()
Creates a new empty instance of SQLStatementListConfiguration.

SQLStatementListConfiguration(org.w3c.dom.Element contentModelElement)
Creates a new instance of SQLStatementListConfiguration given the content model Element that encloses the <statement> Elements.

SQLStatementListConfiguration(java.lang.String configFile)
Creates a new instance of SQLStatementListConfiguration given a XML configuration file.

SQLStatementListConfiguration(java.lang.String configFile, java.lang.String xpathExpression)
Creates a new instance of SQLStatementListConfiguration given a XML configuration file and a XPath expression that evaluates to the element that marks the beginning of the configuration.

Method Summary

void	addSQLStatement(java.lang.String name, SQLStatement sqlStatement) Adds a named SQLStatement.
void	clear() Clear the SQLStatements(s) in this instance of SQLStatementListConfiguration.
boolean	containsSQLStatement(java.lang.String name) Returns true if this SQLStatementListConfiguration contains a SQLStatement with the specified name.
SQLStatement	getSQLStatement(java.lang.String statementName) Returns a SQLStatement by name.
org.apache.commons.collections.map.ListOrderedMap	getSQLStatements() Returns all SQLStatements as a org.apache.commons.collections.map.ListOrderedMap with the SQLStatement name as the key and the SQLStatement as the value.
boolean	isEmpty() Return true if this instance of SQLStatementListConfiguration is empty.
java.util.Iterator	iterator() Return an Iterator over the SQLStatements(s).
protected java.util.List	loadSQLParameters(org.w3c.dom.Element sqlStatementElement) Load SQLStatement parameters for a sqlStatementElement as a List of org.osesb.utilities.jdbc.SQLInputParameter(s).
protected void	loadSQLStatements() Loads the <sqlStatements> content model, creating a List of org.osesb.utilities.jdbc.SQLStatement(s).
static void	main(java.lang.String[] args) main() For testing and sandboxing.
SQLStatement	removeSQLStatement(java.lang.String name) Removes a named SQLStatement.
void	setDebugFlag(boolean debugFlag) Set the debugFlag for enclosed SQLStatements.
protected void	setResultSetProperties(org.w3c.dom.Element sqlStatementElement, SQLStatement sqlStatement) Sets ResultSet properties for a for a sqlStatementElement.
int	size() Return the number of SQLStatements(s) in this instance of SQLStatementListConfiguration.
java.lang.String	toString() Returns the String representation of this SQLStatementListConfiguration.

Methods inherited from class org.osesb.configuration.XMLConfiguration

checkXMLConfigurationClass, doEncryptionInstructions, getBooleanConfigurationProperty, getBooleanRootConfigurationAttribute, getConfigFilePath, getConfigurationAttribute, getConfigurationAttributes, getConfigurationElement, getConfigurationElements, getConfigurationProperties, getConfigurationProperty, getConfigurationProperty, getContentModelElement, getDebugFlag, getDocument, getEncryptionInstructions, getFTPClientConfiguration, getIntConfigurationProperty, getIntRootConfigurationAttribute, getJAXRPCClientConfiguration, getJDBCConfiguration, getJMSClientConfiguration, getLongConfigurationProperty, getLongRootConfigurationAttribute, getName, getRootConfigurationAttribute, getRootConfigurationAttributes, getSMTPClientConfiguration, getSystemProperties, getSystemProperty, isConfigurationElementEmpty, loadConfiguration, loadExternalConfiguration, loadFTPClientConfiguration, loadJAXRPCClientConfiguration, loadJDBCConfiguration, loadJMSClientConfiguration, loadPropertiesFromContentModel, loadSMTPClientConfiguration, loadSystemProperties, mergeConfigurationProperties, setConfigFilePath, setConfigurationProperty, setFTPClientConfiguration, setJAXRPCClientConfiguration, setJDBCConfiguration, setJMSClientConfiguration, setName, setRootConfigurationAttribute, setSMTPClientConfiguration, setSystemProperties, setSystemProperty, writeConfigurationFile, writeConfigurationFile

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

sqlStatements

protected org.apache.commons.collections.map.ListOrderedMap sqlStatements

ListOrderedMap of org.osesb.utilities.jdbc.SQLStatement(s). A ListOrderedMap maintains objects in the map in the order that they are 'put'.

SQLSTATEMENTLISTCONFIG_CONTENTMODEL

public static final java.lang.String SQLSTATEMENTLISTCONFIG_CONTENTMODEL

SQLStatementListConfiguration content model tag.

See Also:

Constant Field Values

SQLSTATEMENT_TAGNAME

public static final java.lang.String SQLSTATEMENT_TAGNAME

the repeating SQL statement element tag names in the SQLStatementListConfiguration configuration.

See Also:

Constant Field Values

Constructor Detail

SQLStatementListConfiguration

public SQLStatementListConfiguration()

Creates a new empty instance of SQLStatementListConfiguration.

Use addStatement() to add a org.osesb.utilities.jdbc.SQLStatement to SQLStatementListConfiguration.

SQLStatementListConfiguration

public SQLStatementListConfiguration(java.lang.String configFile)
                              throws java.lang.Exception

Creates a new instance of SQLStatementListConfiguration given a XML configuration file. The root element of the XML configuration file is the starting element for the configuration.

Parameters:

configFile - The path to the XML configuration file, which is specified as a resource path or as a file system path that is relative to the path specified by the ConfigurationConstants.CONFIG_PATH_SYSTEMPROPERTY System Property.

Throws:

java.lang.Exception - if there is a problem reading or parsing the configuration file.

SQLStatementListConfiguration

public SQLStatementListConfiguration(java.lang.String configFile,
                                     java.lang.String xpathExpression)
                              throws java.lang.Exception

Creates a new instance of SQLStatementListConfiguration given a XML configuration file and a XPath expression that evaluates to the element that marks the beginning of the configuration.

An empty xpathExpression implies that the root element of the XML configuration file is the starting element for the configuration.

Parameters:

configFile - The path to the XML configuration file, which is specified as a resource path or as a file system path that is relative to the path specified by the ConfigurationConstants.CONFIG_PATH_SYSTEMPROPERTY System Property.

xpathExpression - A XPath expression that evaluates to the element that marks the beginning of the configuration. If following the framework convention for SQLStatementListConfiguration(s), xpathExpression should have 'sqlStatements' as the last Element in the path.

Throws:

java.lang.Exception - if there is a problem reading or parsing the configuration file.

SQLStatementListConfiguration

public SQLStatementListConfiguration(org.w3c.dom.Element contentModelElement)
                              throws java.lang.Exception

Creates a new instance of SQLStatementListConfiguration given the content model Element that encloses the <statement> Elements.

Parameters:

contentModelElement - the content model Element that encloses the <statement> Elements.

Throws:

java.lang.Exception - if there is a fatal error transversing the configuration content model enclosed by contentModelElement.

Method Detail

loadSQLStatements

protected final void loadSQLStatements()
                                throws java.lang.Exception

Loads the <sqlStatements> content model, creating a List of org.osesb.utilities.jdbc.SQLStatement(s). SQLStatement(s) are placed in the List in the order that they appear in the configuration. Example configuration:

<sqlStatements>
      <statement name="postSelectUpdateStatement">
          UPDATE MYSCHEMA.DBPOLLER_PROJECT SET EAI_CHKPOINT=?, EAI_MSGID=? WHERE EAI_CHKPOINT IS NULL
          <params>
              <param
                  type="input"
                  sqlType="java.sql.Types.TIMESTAMP"
                  value="${now}">
              </param>
              <param
                  type="input"
                  sqlType="java.sql.Types.VARCHAR"
                  value="${msgid}">
              </param>
          </params>
      </statement>
  </sqlStatements>
 

Note that this method should not be overridden as it is invoked from this class's constructor.

Throws:

java.lang.Exception - if there is a fatal error transversing the SQLStatement content models.

clear

public void clear()

Clear the SQLStatements(s) in this instance of SQLStatementListConfiguration.

Overrides:

clear in class XMLConfiguration

loadSQLParameters

protected final java.util.List loadSQLParameters(org.w3c.dom.Element sqlStatementElement)
                                          throws java.lang.Exception

Load SQLStatement parameters for a sqlStatementElement as a List of org.osesb.utilities.jdbc.SQLInputParameter(s).

Note that this method should not be overridden as it is invoked from this class's constructor.

Parameters:

sqlStatementElement - The conetnt model Element for the SQL statement.

Returns:

A List of org.osesb.utilities.jdbc.SQLInputParameter(s).

Throws:

java.lang.Exception - if there is a fatal error transversing the content model of a SQLStatement.

setResultSetProperties

protected final void setResultSetProperties(org.w3c.dom.Element sqlStatementElement,
                                            SQLStatement sqlStatement)

Sets ResultSet properties for a for a sqlStatementElement. Note that this method should not be overridden as it is invoked from this class's constructor.

Parameters:

sqlStatementElement - The conetnt model Element for the SQL statement.

getSQLStatements

public org.apache.commons.collections.map.ListOrderedMap getSQLStatements()

Returns all SQLStatements as a org.apache.commons.collections.map.ListOrderedMap with the SQLStatement name as the key and the SQLStatement as the value.

An iterator over the returned ListOrderedMap retains the order of addition of the SQLStatements(s).

Returns:

a org.apache.commons.collections.map.ListOrderedMap of all SQLStatement(s).

getSQLStatement

public SQLStatement getSQLStatement(java.lang.String statementName)

Returns a SQLStatement by name.

Parameters:

statementName - The name of the SQLStatement, which is specified using the 'name' attribute of the <statement> Element.

Returns:

the SQLStatement with the specified name or null if the named SQLStatement is not in this SQLStatementListConfiguration.

addSQLStatement

public void addSQLStatement(java.lang.String name,
                            SQLStatement sqlStatement)
                     throws java.lang.Exception

Adds a named SQLStatement.

Parameters:

name - The name of the SQLStatement.

sqlStatement - The SQLStatement.

Throws:

java.lang.Exception - If 'name' is empty or if SQLStatement is null.

removeSQLStatement

public SQLStatement removeSQLStatement(java.lang.String name)

Removes a named SQLStatement.

Parameters:

name - The name of the SQLSatement to remove.

Returns:

The SQLStatement that was removed or null if 'name' is not associated with a SQLStatement.

containsSQLStatement

public boolean containsSQLStatement(java.lang.String name)

Returns true if this SQLStatementListConfiguration contains a SQLStatement with the specified name.

Parameters:

name - The name whose presence in this SQLStatementListConfiguration is to be tested.

Returns:

true if the named SQLStatement is present in this SQLStatementListConfiguration.

setDebugFlag

public void setDebugFlag(boolean debugFlag)

Set the debugFlag for enclosed SQLStatements. This method does not override a Element. It sets the debugFlag in the enclosed XMLConfigurations that do not define a Element.

Overrides:

setDebugFlag in class XMLConfiguration

Parameters:

debugFlag - for this SQLStatementListConfiguration.

isEmpty

public boolean isEmpty()

Return true if this instance of SQLStatementListConfiguration is empty.

Returns:

true if this instance of SQLStatementListConfiguration is empty.

iterator

public java.util.Iterator iterator()

Return an Iterator over the SQLStatements(s). Iterator() retains the order of addition of the SQLStatements(s).

Returns:

an Iterator over the SQLStatements(s).

size

public int size()

Return the number of SQLStatements(s) in this instance of SQLStatementListConfiguration.

Returns:

he number of SQLStatements(s) in this instance of SQLStatementListConfiguration.

toString

public java.lang.String toString()

Returns the String representation of this SQLStatementListConfiguration.

Overrides:

toString in class XMLConfiguration

Returns:

the String representation of this SQLStatementListConfiguration.

main

public static void main(java.lang.String[] args)

main() For testing and sandboxing.

Parameters:

args - commandline arguments