Example 14-4: Handler to insert datagram into multiple tables

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();
  }
}