import oracle.xml.parser.v2.*; import org.w3c.dom.*; import oracle.xml.sql.dml.OracleXMLSave; import java.sql.*; import java.net.URL; public class MultiTableInsertHandler implements XMLDocumentHandler { private XMLDocument categories = null; private String path = null; private String table = null; private XSLStylesheet sheet = null; private String dateFormat = null; private int itemsHandled = 0; private int itemsInserted = 0; private boolean ignoreErrors = false; Connection conn; // Provide a JDBC Connection and optionally an XSL Transformation // to be used to transform the XML Datagram into multi-table insert format // If 'ignoreErrors' is true, then individual inserts can succeed even if // some fail due to errors. If false, any error on insert does a rollback. public MultiTableInsertHandler( Connection conn, XSLStylesheet sheet, boolean ignoreErrors ) { this.conn = conn; this.sheet = sheet; this.ignoreErrors = ignoreErrors; } // Process an XML datagram for multi-table insert public void handleDocument( Document d, URL u ) throws Exception { try { itemsHandled++; handle(d,u); conn.commit(); itemsInserted++; System.out.println(formatted(itemsHandled)+": Inserted."); } catch (oracle.xml.sql.OracleXMLSQLException ex) { try { conn.rollback(); } catch (SQLException s) {} System.out.println(formatted(itemsHandled)+": Failed, ORA-" + formatted(ex.getErrorCode())); } } // Handle the transformation and multi-table inserting private void handle(Document d, URL u) throws Exception { XMLDocument result = null; NodeList nl = null; // If a transformation has been specified, do the transformation if (sheet != null) { XSLProcessor processor = new XSLProcessor(); DocumentFragment df = processor.processXSL(sheet, (XMLDocument)d); result = new XMLDocument(); result.appendChild(df); } else { result = (XMLDocument)d; } // First check if document element is ROWSET. If present, only one ROWSET nl = result.selectNodes("/ROWSET"); if (nl != null && nl.getLength() == 0) { XMLElement e = (XMLElement) result.getDocumentElement(); // If ROWSET is not Doc Element, Search for ROWSET children elements nl = e.selectNodes("ROWSET"); } String table = null; int rowsets = nl != null ? nl.getLength() : 0; // Loop over all the ROWSET elements we found. for (int z = 0; z < rowsets; z++ ) { // Create a new document with current ROWSET as doc element. XMLDocument insDoc = new XMLDocument(); XMLElement curElt = (XMLElement)nl.item(z); curElt.getParentNode().removeChild(curElt); insDoc.appendChild(curElt); // Pickup the target tablename from the table attribute of ROWSET tag table = curElt.valueOf("@table"); // If table name was given and ROWSET element has some child elements if (table != null && insDoc.getDocumentElement().getFirstChild() != null) { try { // Create the XMLSave object and pass current ROWSET doc and tablename OracleXMLSave xs = new OracleXMLSave(conn,table); xs.setCommitBatch(1); xs.setIgnoreCase(true); if (dateFormat != null) { xs.setDateFormat(dateFormat); } int rows = xs.insertXML(insDoc); } catch (oracle.xml.sql.OracleXMLSQLException ex) { // If we're ignoring errors, then note the error and continue. if (ignoreErrors) { System.out.println(formatted(itemsHandled)+": Ignoring, ORA-" + formatted(ex.getErrorCode()) + " on table " + table); } else { throw ex; } } } } } public int getItemsHandled() { return itemsHandled; }; public void setDateFormat(String format) { dateFormat = format; } private String formatted(long n) { java.text.DecimalFormat df = new java.text.DecimalFormat(); df.applyPattern("00000"); return df.format(n).toString(); } } |