com.ibm.etools.xmltosql
Class XMLToSQL

java.lang.Object
  |
  +--com.ibm.etools.xmltosql.XMLToSQL

public class XMLToSQL
extends java.lang.Object

XMLToSQL is used to insert, update, or delete rows in a database table using an XML document. The mapping between the XML structure to the table structure is based on a set of simple mapping rules. The following XML fragment illustrates the mapping rules:

   <rootElement>
     <EMPLOYEE>
       <FIRSTNAME>CHRISTINE</FIRSTNAME>
       <LASTNAME>HAAS</LASTNAME>
     </EMPLOYEE>
     <EMPLOYEE>
       <FIRSTNAME>MICHAEL</FIRSTNAME>
       <LASTNAME>THOMPSON</LASTNAME>
     </EMPLOYEE> 
     ...
   </rootElement>
 
A good way to create a valid XML document is to first use the SQLToXML library to generate one, then modify it to supply new values for update. You can also use the XML to SQL wizard to unit test the XML document for validity before writing your code that will use the XMLToSQL library.


Constructor Summary
XMLToSQL(SQLProperties sqlProperties)
          This is the only constructor.
 
Method Summary
 void addToKeyColumns(java.lang.String columnName)
          Adds to the list of the key column names that will be used in building the where-clause for UPDATE or DELETE.
 void addToUpdateColumns(java.lang.String columnName)
          Adds to the list of the column names that will be updated or inserted.
 void execute(org.w3c.dom.Document doc, boolean continueOnSQLError)
          Updates a table from a DOM Document.
 void execute(java.io.InputStream inputStream)
          Updates a table from an XML input stream.
 void execute(java.io.InputStream inputStream, boolean continueOnSQLError)
          Updates a table from an XML input stream.
 void execute(java.lang.String filename)
          Updates a table from an XML file.
 void execute(java.lang.String filename, boolean continueOnSQLError)
          Updates a table from an XML file.
 void finalize()
          Closes the connection if this was created internally.
 java.util.Vector getFailedStatements()
          Returns a collection of SQL statements that have been failed during execution.
 java.lang.String getTableName(java.io.InputStream inputStream)
          Returns the database table name to be updated.
 java.lang.String getTableName(java.lang.String filename)
          Returns the database table name to be updated.
 void setConnection(java.sql.Connection jdbcConnection)
          Provides a JDBC connection to be used for updating the tables.
 void setTrace(boolean flag)
          Sets the trace flag.
 void setTrace(boolean flag, java.io.PrintWriter writer)
          Sets the trace flag.
 boolean updateMultipleRows(java.lang.String uri, java.util.Vector keys)
          Determines if multiple rows would be updated/deleted once any of the execute() method was called.
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

XMLToSQL

public XMLToSQL(SQLProperties sqlProperties)
This is the only constructor. Any information necessary for updating a database table is provided through SQLProperties.
Parameters:
sqlProperties - Contains information for the update.
See Also:
SQLProperties
Method Detail

execute

public void execute(java.lang.String filename)
             throws java.sql.SQLException,
                    java.io.FileNotFoundException,
                    java.lang.ClassNotFoundException,
                    java.io.IOException,
                    org.xml.sax.SAXException,
                    javax.xml.parsers.ParserConfigurationException
Updates a table from an XML file. If an error occurs while updating a row, rollback is performed for all the rows processed so far.
Parameters:
filename - XML filename.
Throws:
java.sql.SQLException - DDL execution failed.
java.io.FileNotFoundException - Input XML file was not found.
java.lang.ClassNotFoundException - JDBC driver could not be found.
java.io.IOException - Corrupted XML file.
SAXException - The XML content of the input file may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.

execute

public void execute(java.lang.String filename,
                    boolean continueOnSQLError)
             throws java.sql.SQLException,
                    java.io.FileNotFoundException,
                    java.lang.ClassNotFoundException,
                    java.io.IOException,
                    org.xml.sax.SAXException,
                    javax.xml.parsers.ParserConfigurationException
Updates a table from an XML file. Different transaction mode is applied depending on the boolean value of coninueOnSQLError. When this is true, updating continues for the rest of the rows even if an error occurs. To see which rows have been failed in this case, you need to call getFailedStatements(). If continueOnSQLErros is false, rollback is done on all of the rows processed should an error occurs.
Parameters:
filename - XML filename.
continueOnSQLError - Used to choose a transaction mode.
Throws:
java.sql.SQLException - DDL execution failed.
java.io.FileNotFoundException - Input XML file was not found.
java.lang.ClassNotFoundException - JDBC driver could not be found.
java.io.IOException - Corrupted XML file
SAXException - The XML content of the input file may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.
See Also:
getFailedStatements()

execute

public void execute(java.io.InputStream inputStream)
             throws java.sql.SQLException,
                    java.lang.ClassNotFoundException,
                    java.io.IOException,
                    org.xml.sax.SAXException,
                    javax.xml.parsers.ParserConfigurationException
Updates a table from an XML input stream. If an error occurs while updating a row, rollback is performed for all of the rows processed so far.
Parameters:
inputStream - XML input stream.
Throws:
java.sql.SQLException - DDL execution failed.
java.lang.ClassNotFoundException - JDBC driver could not be found.
java.io.IOException - Corrupted XML stream
SAXException - The XML content of the input stream may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.

execute

public void execute(java.io.InputStream inputStream,
                    boolean continueOnSQLError)
             throws java.sql.SQLException,
                    java.lang.ClassNotFoundException,
                    java.io.IOException,
                    org.xml.sax.SAXException,
                    javax.xml.parsers.ParserConfigurationException
Updates a table from an XML input stream. Different transaction mode is applied depending on the boolean value of coninueOnSQLError. When this is true, updating continues for the rest of the rows even if an error occurs. To see which rows have been failed in this case, you need to call getFailedStatements(). If continueOnSQLErros is false, rollback is done on all of the rows processed should an error occurs.
Parameters:
inputStream - XML input stream.
continueOnSQLError - Used to choose a transaction mode.
Throws:
java.sql.SQLException - DDL execution failed.
java.lang.ClassNotFoundException - JDBC driver could not be found.
java.io.IOException - Corrupted XML stream
SAXException - The XML content of the input stream may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.
See Also:
getFailedStatements()

execute

public void execute(org.w3c.dom.Document doc,
                    boolean continueOnSQLError)
             throws java.sql.SQLException,
                    java.lang.ClassNotFoundException,
                    java.io.IOException,
                    org.xml.sax.SAXException,
                    javax.xml.parsers.ParserConfigurationException
Updates a table from a DOM Document. Different transaction mode is applied depending on the boolean value of coninueOnSQLError. When this is true, updating continues for the rest of the rows even if an error occurs. To see which rows have been failed in this case, you need to call getFailedStatements(). If continueOnSQLErros is false, rollback is done on all of the rows processed should an error occurs.
Parameters:
doc - DOM Document.
continueOnSQLError - Used to choose a transaction mode.
Throws:
java.sql.SQLException - DDL execution failed.
java.lang.ClassNotFoundException - JDBC driver could not be found.
java.io.IOException - Corrupted XML stream
SAXException - The XML content of the input stream may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.
See Also:
getFailedStatements()

setConnection

public void setConnection(java.sql.Connection jdbcConnection)
Provides a JDBC connection to be used for updating the tables. This method becomes useful when an application program wants to use its own connection management mechanism such as connection pooling. If this method is not called before any of the execute() methods, XMLToSQL creates its own connection using the values from SQLProperties.
Parameters:
jdbcConnection - A JDBC connection

getFailedStatements

public java.util.Vector getFailedStatements()
Returns a collection of SQL statements that have been failed during execution. This method is only meaningful when the continueOnError flag is true.
Returns:
A (String) Vector of SQL statements that have failed to execute.

finalize

public void finalize()
Closes the connection if this was created internally. If the connection was supplied externally (by using setConnection()), nothing is done. This method is called by the JVM when XMLToSQL is garbage-collected.
See Also:
setConnection(java.sql.Connection)

setTrace

public void setTrace(boolean flag)
Sets the trace flag. The SQL statements being executed is printed out to the console when flag is true. By default, trace is turned off.
Parameters:
flag - true to show the trace, false otherwise.

setTrace

public void setTrace(boolean flag,
                     java.io.PrintWriter writer)
Sets the trace flag. The SQL statements being executed is printed out to the writer when flag is true. By default, trace is turned off.
Parameters:
flag - true to show the trace, false otherwise.
writer - A PrintWriter where the trace output is directed to.

getTableName

public java.lang.String getTableName(java.lang.String filename)
                              throws java.io.IOException,
                                     org.xml.sax.SAXException,
                                     javax.xml.parsers.ParserConfigurationException
Returns the database table name to be updated. This method is provided to determine the table name from the XML input stream before executing a SQL statement. This is for the convenience of any user interface components.
Parameters:
filename - XML file name
Returns:
A table name retrieved from the XML file.
Throws:
java.io.IOException - Corrupted XML stream
SAXException - The XML content of the input stream may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.

getTableName

public java.lang.String getTableName(java.io.InputStream inputStream)
                              throws java.io.IOException,
                                     org.xml.sax.SAXException,
                                     javax.xml.parsers.ParserConfigurationException
Returns the database table name to be updated. This method is provided to determine the table name from the XML input stream before executing a SQL statement. This is for the convenience of any user interface components.
Parameters:
inputStream - XML input stream
Returns:
A table name retrieved from the XML stream.
Throws:
java.io.IOException - Corrupted XML stream
SAXException - The XML content of the input stream may not be well-formed.
javax.xml.parsers.ParserConfigurationException - Could not find an XML parser.

addToKeyColumns

public void addToKeyColumns(java.lang.String columnName)
                     throws java.lang.Exception
Adds to the list of the key column names that will be used in building the where-clause for UPDATE or DELETE. If the table has primary keys, those primary key columns are used instead. This method is useful if a table does not have any primary key columns defined, but requires caution in this case since multiple rows can be updated unintentionally. A column cannot be both a key column and an update column. This method does not have any effect for INSERT.
Parameters:
columnName - A column name to be used in the where-clause for UPDATE or DELETE.
Throws:
java.lang.Exception - The column in the update column list cannot be used as a key column.
See Also:
addToUpdateColumns(java.lang.String)

addToUpdateColumns

public void addToUpdateColumns(java.lang.String columnName)
                        throws java.lang.Exception
Adds to the list of the column names that will be updated or inserted. If this method is not called before any of the execute() methods, all of the columns excluding any key columns are added to the list by XMLToSQL for UPDATE or INSERT. A column cannot be both a key column and an update column. This method does not have any effect for DELETE.
Parameters:
columnName - A column name to be updated or inserted in the table.
Throws:
java.lang.Exception - The column in the key column list cannot be used as an update column.
See Also:
addToKeyColumns(java.lang.String)

updateMultipleRows

public boolean updateMultipleRows(java.lang.String uri,
                                  java.util.Vector keys)
                           throws java.lang.Exception
Determines if multiple rows would be updated/deleted once any of the execute() method was called. There is a chance of multiple rows being affected in UPDATE/DELETE mode when the operation is performed against a table that does not have any primary keys. This method returns false if a single row is going to be affected from its corresponding source XML element, and true otherwise.
Parameters:
uri - A source XML filename
keys - The key column names to be used in the where-clause for UPDATE/DELETE.
Returns:
false for single row update, true otherwise.
Throws:
java.lang.Exception - Failed while processing the source XML file.