net.sourceforge.esw.collection
Class JDBCTransducer

java.lang.Object
  |
  +--net.sourceforge.esw.collection.ATransducer
        |
        +--net.sourceforge.esw.collection.AJDBCTransducer
              |
              +--net.sourceforge.esw.collection.JDBCTransducer

All Implemented Interfaces:

ITransactionalTransducer, ITransducer, java.io.Serializable

public class JDBCTransducer

extends AJDBCTransducer

Performs transduction between a JDBC data source and an IMetaCollection instance.

This transduction occurs both ways; put() takes data from the IMetaCollection instance to which this ITransducer instance is attached and places it into the data source represented by this ITransducer instance. The get() method takes data from the data source represented by this ITransducer instance and places it into the IMetaCollection instance to which this ITransducer instance is attached.

This class supports transactions. By default, get and put automatically commit at the end of their operation. However, you can change this behavior via the setAutoCommit method, allowing multi-step transactions. In this case your code must explicitly call commit to complete the transaction.

This JDBCTransducer instance is the access point to JDBC data sources. It encapsulates any JDBC calls and transduces the row and column based data from JDBC to the INode and IMetaCollection data structure.

This is one of the more complicated ITransducer implementors, but can be one of the most rewarding in terms of time saved.

The get method operates against any SQL statements that retrieve data from the JDBC data source, like SELECT statements. The put method operates against any data to be put into the JDBC data source, like INSERT statements.

The transduction is performed against an SQL statement that is passed into this JDBCTransducer instance. Remember to keep track of whether the data is going into the JDBC data source, in which case the put is invoked, or if data is being pulled from the JDBC data source, in which case get should be invoked. Also, the SQL statement added to this JDBCTransducer instance is added to the method specific to the get or put methods.

The SQL statement for put requires more than just a SQL statement. To put data into the JDBC data source, there must be a way to specify what values from the IMetaCollection instance with which to interact. As such, ESW framework has a concept of "replaceable parameters"; a special character combination to specifiy which data from which named INode instance should be put into the JDBC data source. The example below shows how to specify replaceable parameters.

All JDBC data sources are a grid based data structure with rows and columns. These rows and columns must be mapped to the IMetaCollection instance. If we consider the children of the IMetaCollection instance being the rows of a JDBC data source, and the children's children, or the IMetaCollection instance's grandchildren to be the columns, we have mapped the JDBC data source to a graph structure.

For example, if you wanted to put data into a JDBC data source that contained two columns, FIRST_NAME and LAST_NAME, then you would sepecify a SQL statement of the form INSERT INTO MYTABLE VALUES ($FIRST_NAME$, $LAST_NAME$) where the $ is the delimiter for the replaceable parameters, or names of the INode instance fields. This JDBCTransducer instance would look at the first child of the IMetaCollection instance being transduced and would look for INode instances with the identifer FIRST_NAME. If it found an INode instance with the identifier of FIRST_NAME, this JDBCTransducer instance would replace the $FIRST_NAME$ parameter in the SQL statement with the INode instance's value. It would then look for an INode instance with an identifier of LAST_NAME and replace that value. Once all the replaceable parameters are filled in, the SQL statement is executed. This JDBCTransducer instance would then go to the second child of the IMetaCollection instance and then execute the SQL statement against that child's INode instances and continue in this manner for all the children of the IMetaCollection instance.

Since there is no data being pulled from the IMetaCollection instance during a get method invocation, there are no replaceable parameter considerations. A straight SQL SQLECT statement is all that is required. This JDBCTransducer instance get method invocation only works against SQL SELECT statements.

This JDBCTransducer instance put method invocation only works against SQL INSERT, UPDATE, and DELETE statements.

JDBC data sources use four elements, outside of the SQL statement to work. For most, all four are required, but sometimes, only the JDBCDriver and URL are required, the others are optional. These four are:

• JDBCDriver: the fully qualified class name of the JDBC driver for your JDBC data source.
• URL: Each JDBC vendor defines a URL for access to their data source.
• User Name: The name of an authoized user of the database. Optional.
• Password: The password for the specified authorized user. Optional.

All must be set on this JDBCTransducer instance before transduction.

This JDBCTransducer instance pools the connections to the JDBC data source. See the net.sourceforge.esw.util.database.DBConnectionPoolManager class for more details of how to use the pooling outside of the JDBCTransducer.

Examples:

    // for get operations
    IMetaCollection meta = MetaFactory.createMetaCollection();
    JDBCTransducer transducer = new JDBCTransducer( getMyJDBCDriver(), // the JDBC driver
                                                    getMyURL(), // the URL for the data source
                                                    getMyUsername(), // the authorized username
                                                    getMyPassword() ); // the password for the user
    transducer.setGetStatement( "SELECT * FROM MYTABLE" );
    try {
      meta.get();
    }
    catch ( TransducerException te ) {
      te.printStackTrace();
    }

    // the same transducer for put operations
    transducer.setPutStatement( "INSERT INTO MYTABLE VALUES ( $FIRST_NAME$, $LAST_NAME$ )" );
    try {
      meta.put();
    }
    catch ( TransducerException te ) {
      te.printStackTrace();
    }
 

See Also:

DBConnectionPoolManager, IMetaCollection, INodeContextFactory, Serialized Form

Field Summary

protected java.lang.String	getStatement The get method's SQL statement.
protected java.lang.String	putStatement The put method's SQL statement.

Fields inherited from class net.sourceforge.esw.collection.AJDBCTransducer

bJDBCDriverAutoCommit, bTransducerAutoCommit, connection, DELIMITER, password, QUESTION, url, userid

Fields inherited from class net.sourceforge.esw.collection.ATransducer

contextFactory

Constructor Summary

JDBCTransducer(java.lang.String aDriver, java.lang.String aURL)
Creates a new JDBCTransducer instance with all the JDBC paramters neccessary to create a JDBC connection.

JDBCTransducer(java.lang.String aDriver, java.lang.String aURL, java.lang.String aUser, java.lang.String aPassword)
Creates a new JDBCTransducer instance with all the JDBC parameters neccessary to create a JDBC connection.

Method Summary

void	get(IMetaCollection aCollection) Gets the data source's data represented by this ITransducer instance into the referenced IMetaCollection instance.
java.lang.String	getGetStatement() Returns the get SQL statement for this JDBCTransducer instance.
java.lang.String	getPutStatement() Returns the put SQL statement for this JDBCTransducer instance.
void	put(IMetaCollection aCollection) Puts the referenced IMetaCollection instance's data into the data source represented by this ITransducer instance.
void	setGetStatement(java.lang.String aStatement) Sets the get statement for this JDBCTransducer instance.
void	setPutStatement(java.lang.String aStatement) Sets the put statement for putting data from the IMetaCollection instance into the specified JDBC data source.

Methods inherited from class net.sourceforge.esw.collection.AJDBCTransducer

commit, getAutoCommit, getConnection, getJDBCDriverAutoCommit, getReplaceableParameterNames, putResultSetIntoMetaCollection, removeReplaceableParameters, rollback, setAutoCommit, setJDBCDriverAutoCommit, setLog

Methods inherited from class net.sourceforge.esw.collection.ATransducer

getContextFactory, setContextFactory

Methods inherited from class java.lang.Object

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

Methods inherited from interface net.sourceforge.esw.collection.ITransducer

getContextFactory, setContextFactory

Field Detail

getStatement

protected java.lang.String getStatement

The get method's SQL statement.

putStatement

protected java.lang.String putStatement

The put method's SQL statement.

Constructor Detail

JDBCTransducer

public JDBCTransducer(java.lang.String aDriver,
                      java.lang.String aURL,
                      java.lang.String aUser,
                      java.lang.String aPassword)
               throws java.lang.IllegalArgumentException

Creates a new JDBCTransducer instance with all the JDBC parameters neccessary to create a JDBC connection.

Parameters:

aDriver - the fully-qualified JDBC Driver class name.

aURL - the URL for the JDBC driver.

aUser - the authorized user for the JDBC data source.

aPassword - the password for the authoized user.

Throws:

java.lang.IllegalArgumentException - if the JDBC driver does not exist or does not properly register.

JDBCTransducer

public JDBCTransducer(java.lang.String aDriver,
                      java.lang.String aURL)
               throws java.lang.IllegalArgumentException

Creates a new JDBCTransducer instance with all the JDBC paramters neccessary to create a JDBC connection.

Parameters:

aDriver - the fully-qualifed JDBC Driver class name.

aURL - the URL for the JDBC driver.

Throws:

java.lang.IllegalArgumentException - if the JDBC driver does not exist or does not properly register.

Method Detail

setGetStatement

public void setGetStatement(java.lang.String aStatement)

Sets the get statement for this JDBCTransducer instance. The get statement is a valid SQL statement that will be executed against the specified JDBC data source during a get method invocation.

This SQL statement should only be an SELECT statement.

Parameters:

aStatement - a valid SQL SELECT statement.

setPutStatement

public void setPutStatement(java.lang.String aStatement)

Sets the put statement for putting data from the IMetaCollection instance into the specified JDBC data source.

This SQL statement should only be an INSERT, UPDATE or DELETE statement.

Identifiers contained within DELIMITER chraracters: $likeThis$ are replaceable parameters. This JDBCTransducer instance will examine the children of the IMetaCollection instance looking for INode instances with identifiers matching the replaceable parameters and will substitute their value into the SQL statement.

Parameters:

aStatement - a valid SQL INSERT, UPDATE, or DELETE statement.

getGetStatement

public java.lang.String getGetStatement()

Returns the get SQL statement for this JDBCTransducer instance.

getPutStatement

public java.lang.String getPutStatement()

Returns the put SQL statement for this JDBCTransducer instance.

get

public void get(IMetaCollection aCollection)
         throws TransducerException

Gets the data source's data represented by this ITransducer instance into the referenced IMetaCollection instance.

This implementation will map the rows of the returned JDBC data source to children within the specified IMetaCollection instance, and will map columns to children within the rows of the IMetaCollection instance. This means that rows within the JDBC data source become the IMetaCollection instance's children, and that columns within the JDBC rows become the IMetaCollection instance's grandchildren.

Parameters:

aCollection - the IMetaCollection instance into which to read.

Throws:

TransducerException - if an error occurs during transduction.

See Also:

put( IMetaCollection )

put

public void put(IMetaCollection aCollection)
         throws TransducerException

Puts the referenced IMetaCollection instance's data into the data source represented by this ITransducer instance.

This implementation will, for each child of the specified IMetaCollection instance, execute the put statement against the JDBC data source this JDBCTransducer represents, replacing parameters from each IMetaCollection child's INode instance children. The operation is symmetric to the get method: the IMetaCollection instance's children become rows in the JDBC data source, and the IMetaCollection instance's grandchildren become columns within the row.

Parameters:

aCollection - the IMetaCollection instance from which to take data.

Throws:

TransducerException - if an error occurs during transduction.

See Also:

get( IMetaCollection )