dbsql2xml / Discussion / Help: Change to use XMLStreamWriter

FWIW, after making the changes mentioned earlier so it would work better with MySQL, I've also modified it so it uses XMLStreamWriter, allowing it to process larger files. Here's the updated Dbsql2xml.java file containing the changes for both MySQL and XMLStreamWriter:
 * The "dbsql2xml" is Java tool (class) for transforming (export) relational databases into hierarchical XML. Copyright (C)
 * 2005�2006 Stepan Rybar This library is free software; you can redistribute it and/or modify it under the terms of the GNU
 * Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your
 * option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without
 * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
 * for more details. You should have received a copy of the GNU Lesser General Public License along with this library; if not,
 * write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA For more information, see
 * http://sourceforge.net/projects/dbsql2xml/ Stepan RYBAR Modra 6 Stodulky 1979 Praha the Czech Republic xrybs01@seznam.cz
 * http://sourceforge.net/projects/dbsql2xml/
 */
package net.sf.dbsql2xml;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.OutputStream;
import java.io.FileOutputStream;
import java.io.ByteArrayOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamWriter;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.w3c.dom.ProcessingInstruction;
import org.w3c.dom.Text;
/**
 * <code>dbsql2xml</code> <br />
 * "dbsql2xml" is Java tool (class) for transformation (export, convert)
 * relational database into hierarchical XML. It requires JRE 5.0, JDBC and SQL
 * DBMS. "dbsql2xml" uses XML document for mapping database tables and columns
 * into elements of tree XML. <br />
 * For more information see "https://sourceforge.net/projects/dbsql2xml/".
 * 
 * @version 0.21 2006-01-08 - modified by Dallan Quass 2010-02-02
 */
public class Dbsql2xml {
  private Log               log                     = LogFactory.getLog(Dbsql2xml.class);
  private boolean           connnectionPassed       = true;
  private String            applicationInfo         = null;
  private String            pathToMappingXmlFile    = null;
  private String            pathToXsdFile           = null;
  private String            namesOfGlobalVariables  = null;
  private String            valuesOfGlobalVariables = null;
  private String            pathToOutputXmlFile     = null;
  private String            globalCharsetName       = null;
  private HashMap           globalVariables         = new HashMap();
  private Connection        connection              = null;
  private Statement         globalStatement         = null;
  private XPathHelper       xPathHelper             = new XPathHelper();
  private XMLStreamWriter   writer                  = null;
  private boolean           multiStatementDriver    = true;
  private long              totalTime               = 0;
  private long              xmlTime                 = 0;
  private String            jdbcDriver              = null;
  private String            jdbcURL                 = null;
  private String            jdbcUserName            = null;
  private String            jdbcPassword            = null;
  private String            connUrl                 = null;
  /**
   * Creates a <code>dbsql2xml</code> for output to <code>File</code>.
   * 
   * @param pathToMappingXmlFile
   *          e.g "treeXMLMapping.xml"
   * @param pathToXsdFile
   *          e.g. "dbsql2xml.xsd"
   * @param namesOfGlobalVariables
   *          e.g. "$gvG01#$gvG02#$gvG03" or "myVarX#myVarY#myVarZ"
   * @param valuesOfGlobalVariables
   *          e.f "01#John Smith#2005-05-10"
   * @param pathToOutputXmlFile
   *          e.g "treeXMLOut.xml" <br />
   *          <br />
   *          token, which delimites names and values of global variables, is
   *          set in mapping XML file inside element
   *          <code>tokenForCommandLineStringArrays</code> <br />
   *          it can be single or multiple character <br />
   *          token is the same for names and values of global variables <br />
   *          <br />
   *          So example of creating <code>dbsql2xml</code> object is: <br />
   *          <code>dbsql2xml myDbSql2Xml = new dbsql2xml("treeXMLMapping.xml", "dbsql2xml.xsd", "$gvG01", "001", "treeXMLOut.xml");</code>
   *          <br />
   *          See example in distribution (standalone application and servlet).
   */
  public Dbsql2xml(String pathToMappingXmlFile, String pathToXsdFile, String namesOfGlobalVariables, String valuesOfGlobalVariables,
      String pathToOutputXmlFile) {
    this.pathToMappingXmlFile = pathToMappingXmlFile;
    this.pathToXsdFile = pathToXsdFile;
    this.namesOfGlobalVariables = namesOfGlobalVariables;
    this.valuesOfGlobalVariables = valuesOfGlobalVariables;
    this.pathToOutputXmlFile = pathToOutputXmlFile;
    this.connection = null;
    this.connnectionPassed = false;
    log.debug("Creating Dbsql2xml with: " + "\npathToMappingXmlFile = " + pathToMappingXmlFile + "\npathToXsdFile = " + pathToXsdFile
        + "\nnamesOfGlobalVariables = " + namesOfGlobalVariables + "\nvaluesOfGlobalVariables = " + valuesOfGlobalVariables
        + "\npathToOutputXmlFile = " + pathToOutputXmlFile);
  }
  public Dbsql2xml(String pathToMappingXmlFile, String pathToXsdFile, String namesOfGlobalVariables, String valuesOfGlobalVariables,
      String pathToOutputXmlFile, Connection con) {
    this.pathToMappingXmlFile = pathToMappingXmlFile;
    this.pathToXsdFile = pathToXsdFile;
    this.namesOfGlobalVariables = namesOfGlobalVariables;
    this.valuesOfGlobalVariables = valuesOfGlobalVariables;
    this.pathToOutputXmlFile = pathToOutputXmlFile;
    this.connection = con;
    log.debug("Creating Dbsql2xml with: " + "\npathToMappingXmlFile = " + pathToMappingXmlFile + "\npathToXsdFile = " + pathToXsdFile
        + "\nnamesOfGlobalVariables = " + namesOfGlobalVariables + "\nvaluesOfGlobalVariables = " + valuesOfGlobalVariables
        + "\npathToOutputXmlFile = " + pathToOutputXmlFile);
  }
  /**
   * Creates a <code>dbsql2xml</code> for output to <code>String</code>,
   * <code>Document</code> and <code>System.out</code>.
   * 
   * @param pathToMappingXmlFile
   *          e.g "treeXMLMapping.xml"
   * @param pathToXsdFile
   *          e.g. "dbsql2xml.xsd"
   * @param namesOfGlobalVariables
   *          e.g. "$gvG01#$gvG02#$gvG03" or "myVarX#myVarY#myVarZ"
   * @param valuesOfGlobalVariables
   *          e.f "01#John Smith#2005-05-10" <br />
   *          <br />
   *          token, which delimites names and values of global variables, is
   *          set in mapping XML file inside element
   *          <code>tokenForCommandLineStringArrays</code> <br />
   *          it can be single or multiple character <br />
   *          token is the same for names and values of global variables <br />
   *          <br />
   *          So example of creating <code>dbsql2xml</code> object is: <br />
   *          <code>dbsql2xml myDbSql2Xml = new dbsql2xml("treeXMLMapping.xml", "dbsql2xml.xsd", "$gvG01", "001");</code>
   *          <br />
   *          See example in distribution (standalone application and servlet).
   */
  public Dbsql2xml(String pathToMappingXmlFile, String pathToXsdFile, String namesOfGlobalVariables, String valuesOfGlobalVariables) {
    this.pathToMappingXmlFile = pathToMappingXmlFile;
    this.pathToXsdFile = pathToXsdFile;
    this.namesOfGlobalVariables = namesOfGlobalVariables;
    this.valuesOfGlobalVariables = valuesOfGlobalVariables;
    this.pathToOutputXmlFile = null;
    this.connection = null;
    this.connnectionPassed = false;
    log.debug("Creating Dbsql2xml with: " + "\npathToMappingXmlFile = " + pathToMappingXmlFile + "\npathToXsdFile = " + pathToXsdFile
        + "\nnamesOfGlobalVariables = " + namesOfGlobalVariables + "\nvaluesOfGlobalVariables = " + valuesOfGlobalVariables
        + "\npathToOutputXmlFile = " + pathToOutputXmlFile);
  }
  /**
   * Creates a <code>dbsql2xml</code> for output to <code>String</code>,
   * <code>Document</code> and <code>System.out</code> with
   * <code>Connection</code> already defined.
   * 
   * @param pathToMappingXmlFile
   *          e.g "treeXMLMapping.xml"
   * @param pathToXsdFile
   *          e.g. "dbsql2xml.xsd"
   * @param namesOfGlobalVariables
   *          e.g. "$gvG01#$gvG02#$gvG03" or "myVarX#myVarY#myVarZ"
   * @param valuesOfGlobalVariables
   *          e.g. "01#John Smith#2005-05-10"
   * @param connection
   *          <br />
   *          <br />
   *          token, which delimites names and values of global variables, is
   *          set in mapping XML file inside element
   *          <code>tokenForCommandLineStringArrays</code> <br />
   *          it can be single or multiple character <br />
   *          token is the same for names and values of global variables <br />
   *          <br />
   *          So example of creating <code>dbsql2xml</code> object is: <br />
   *          <code>dbsql2xml myDbSql2Xml = new dbsql2xml("treeXMLMapping.xml", "dbsql2xml.xsd", "$gvG01", "001");</code>
   *          <br />
   *          See example in distribution (standalone application and servlet).
   */
  public Dbsql2xml(String pathToMappingXmlFile, String pathToXsdFile, String namesOfGlobalVariables, String valuesOfGlobalVariables,
      Connection connection) throws Exception {
    this.pathToMappingXmlFile = pathToMappingXmlFile;
    this.pathToXsdFile = pathToXsdFile;
    this.namesOfGlobalVariables = namesOfGlobalVariables;
    this.valuesOfGlobalVariables = valuesOfGlobalVariables;
    this.pathToOutputXmlFile = null;
    this.connection = connection;
    log.debug("Creating Dbsql2xml with: " + "\npathToMappingXmlFile = " + pathToMappingXmlFile + "\npathToXsdFile = " + pathToXsdFile
        + "\nnamesOfGlobalVariables = " + namesOfGlobalVariables + "\nvaluesOfGlobalVariables = " + valuesOfGlobalVariables
        + "\npathToOutputXmlFile = " + pathToOutputXmlFile);
  }
  private void doExport(OutputStream out) {
    long startTime = System.currentTimeMillis();
    try {
      checkInputParameters();
      // load mapping (configuration) XML file
      Document mapping = createMappingFromFile();
      // initialize XPath for future use
      xPathHelper = new XPathHelper();
      // load global variables from constructors parameters and mapping XML file
      loadGlobalVariables(namesOfGlobalVariables, valuesOfGlobalVariables, mapping);
      // create output XML writer
      writer = XMLOutputFactory.newInstance().createXMLStreamWriter(out);
      writer.writeStartDocument();
      // create root element of output XML document
      String rootElementName = xPathHelper.getString("config/rootElementName", mapping);
      if (rootElementName == null || rootElementName.trim().length() == 0) {
        rootElementName = "dbsql2xml";
      }
      writer.writeStartElement(rootElementName);
      applicationInfo = initApplicationInfo();
      writer.writeComment(applicationInfo);
      globalCharsetName = xPathHelper.getString("config/globalCharsetName", mapping);
      if (connection == null) {
        if (jdbcDriver == null || jdbcURL == null || jdbcUserName == null || jdbcPassword == null) {
          jdbcDriver = xPathHelper.getString("config/connectionProperties/jdbcDriver", mapping);
          jdbcURL = xPathHelper.getString("config/connectionProperties/jdbcURL", mapping);
          jdbcUserName = xPathHelper.getString("config/connectionProperties/jdbcUserName", mapping);
          jdbcPassword = xPathHelper.getString("config/connectionProperties/jdbcPassword", mapping);
        }
        connection = getConnection(jdbcDriver, jdbcURL, jdbcUserName, jdbcPassword);
      }
      multiStatementDriver = xPathHelper.getString("config/connectionProperties/multiStatementDriver", mapping).equalsIgnoreCase("true");
      // load data from database to hierarchical XML document
      Element topLevelTableElement = xPathHelper.getElement("/config/table", mapping);
      processTableElement(topLevelTableElement);
      // end XML document
      writer.writeEndElement();
      writer.writeEndDocument();
      writer.flush();
      writer.close();
      // append to or process output XML document XSLT or CSS (if any) in
      // mapping file
// broken - we'd need to write the document to a byte array, then convert the byte array to a Document before calling this function
//      Element processingInstructionsElement = xPathHelper.getElement("config/processingInstructions", mapping);
//      doProcessingInstructions(processingInstructionsElement, rootElement);
      // If we didn't get a connection from the client then close it.
      if (!this.connnectionPassed) {
        connection.close();
        connection = null;
      }
    }
    catch (Exception e) {
      log.error("Error!", e);
    }
    finally {
      System.gc();
    }
    log.debug("It took " + (System.currentTimeMillis() - startTime) + " ms to create the xml");
    xmlTime += (System.currentTimeMillis() - startTime);
  }
  private String initApplicationInfo() {
    String retVal = "";
    retVal = "The \"dbsql2xml\" is Java tool (class) for transforming (export) relational databases into hierarchical XML.\nFor more information, see http://sourceforge.net/projects/dbsql2xml/.\n" +
            "It has been modified by Dallan Quass to work for very large documents and MySQL";
    return retVal;
  }
  private void checkInputParameters() throws Exception {
    try {
      if (pathToMappingXmlFile == null | pathToMappingXmlFile == "") {
        throw new Exception("No mapping XML file specified.");
      }
      if (pathToXsdFile == null | pathToXsdFile == "") {
        throw new Exception("No XML Schema specified.");
      }
      if (namesOfGlobalVariables == null | valuesOfGlobalVariables == null) {
        namesOfGlobalVariables = "";
        valuesOfGlobalVariables = "";
      }
    }
    catch (Exception e) {
      log.error("pathToMappingXmlFile=\"" + pathToMappingXmlFile + "\", pathToXsdFile=\"" + pathToXsdFile + "\", namesOfGlobalVariables=\""
          + namesOfGlobalVariables + "\", valuesOfGlobalVariables=\"" + valuesOfGlobalVariables + "\"");
      log.error("error in checkInputParameters");
      throw e;
    }
  }
  /**
   * Output results to <code>File</code>. It is intended for standalone
   * export (transformation, extraction) application. Output encoding is in
   * UTF-8.
   */
  public void doExportIntoFile() throws Exception {
    try {
      if (pathToOutputXmlFile == null | pathToOutputXmlFile == "") {
        throw new Exception("It looks like not four parameters was given - no file for output");
      }
      else {
        long startTime = System.currentTimeMillis();
        OutputStream out = new FileOutputStream(pathToOutputXmlFile);
        doExport(out);
        out.close();
//        TransformerFactory transformerFactory = TransformerFactory.newInstance();
//        Transformer transformer = transformerFactory.newTransformer();
//        Source source = new DOMSource(outDocument);
//        StreamResult streamResult = new StreamResult(new File(pathToOutputXmlFile));
//        transformer.transform(source, streamResult);
        log.debug("Wrote file to " + pathToOutputXmlFile);
        log.debug("It took " + (System.currentTimeMillis() - startTime) + " ms to process the xml");
        totalTime += (System.currentTimeMillis() - startTime);
      }
    }
    catch (Exception e) {
      log.error("error in doExportIntoFile", e);
      throw e;
    }
  }
  /**
   * Output results to <code>String</code>. It is intended for Java servlets.
   * Output encoding is in UTF-8.
   */
  public String doExportIntoString() throws Exception {
    try {
      ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
      doExport(byteArrayOutputStream);
//      TransformerFactory transformerFactory = TransformerFactory.newInstance();
//      Transformer transformer = transformerFactory.newTransformer();
//      Source source = new DOMSource(outDocument);
//      StreamResult streamResult = new StreamResult(byteArrayOutputStream);
//      transformer.transform(source, streamResult);
      return byteArrayOutputStream.toString("UTF-8");// output is always in
      // UTF-8
    }
    catch (Exception e) {
      log.error("error in doExportIntoString", e);
      throw e;
    }
  }
  /**
   * Output results to <code>System.out</code>. It is intended for debugging
   * purposes on systems with console. Output encoding is in UTF-8.
   */
  public void doExportIntoStandardOutput() throws Exception {
    try {
      System.out.println(doExportIntoString());
    }
    catch (Exception e) {
      log.error("error in doExportIntoStandardOutput", e);
      throw e;
    }
  }
  /**
   * Output results to <code>Document</code>. It is intended for application
   * integration into other XML related ones. Output encoding is in UTF-8.
   */
// broken - we need to write the document to a byte array, then convert the byte array to a Document
//  public Document doExportIntoDocument() throws Exception {
//    try {
//      doExport();
//      return (outDocument);
//    }
//    catch (Exception e) {
//      log.error("error in doExportIntoString", e);
//      throw e;
//    }
//  }
  private void loadGlobalVariables(String namesOfGlobalVariables, String valuesOfGlobalVariables, Document document) throws Exception {
    try {
      // load global variables given by mapping file
      NodeList nodeList = xPathHelper.getNodeList("config/globalVariables/globalVariable", document);
      for (int i = 0; i < nodeList.getLength(); i++) {
        globalVariables.put(xPathHelper.getString("@name", (Node) nodeList.item(i)), xPathHelper.getString("self::node()", (Node) nodeList.item(i)));
      }
      // load global variables given by constructor
      String token = xPathHelper.getString("config/tokenForCommandLineStringArrays", document);
      String[] globalVariablesNames = namesOfGlobalVariables.split(token);
      String[] globalVariablesValues = valuesOfGlobalVariables.split(token);
      if (globalVariablesNames.length == globalVariablesValues.length) {
        for (int i = 0; i < globalVariablesNames.length; i++) {
          globalVariables.put(globalVariablesNames[i], globalVariablesValues[i]);// global
                                                                                  // variables
                                                                                  // is
                                                                                  // HashMap
        }
      }
      else {
        throw new Exception("Constructor parameters global variables names and values does not match in count.");
      }
    }
    catch (Exception e) {
      log.error("error in loadGlobalVariables", e);
      throw e;
    }
  }
  private Document createMappingFromFile() throws Exception {// path to file is
    // global
    String JAXP_SCHEMA_LANGUAGE = "http://java.sun.com/xml/jaxp/properties/schemaLanguage";
    String W3C_XML_SCHEMA = "http://www.w3.org/2001/XMLSchema";
    String JAXP_SCHEMA_SOURCE = "http://java.sun.com/xml/jaxp/properties/schemaSource";
    try {
      DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
      // validate against XML Schema in dbsql2xml.xsd
      documentBuilderFactory.setNamespaceAware(true);
      String version = System.getProperty("java.version");
      //Essentially make sure we are jdk 1.5 or above to do the special stuff. 
      if (isJaxpIncluded(version)) {
        documentBuilderFactory.setValidating(false);// mapping file will be validated against definition in XML Schema Unfortunately saxon doesn't support this. BEWARE
        documentBuilderFactory.setXIncludeAware(true);// mapping file can use XInclude
        documentBuilderFactory.setAttribute(JAXP_SCHEMA_LANGUAGE, W3C_XML_SCHEMA);
        documentBuilderFactory.setAttribute(JAXP_SCHEMA_SOURCE, new File(pathToXsdFile));
      }
      DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
      Document document = documentBuilder.parse(new File(pathToMappingXmlFile));
      return document;
    }
    catch (Exception e) {
      e.printStackTrace();
      log.error("error in createDocumentFromFile", e);
      throw e;
    }
  }
//  private Document createNewDocument() throws Exception {
//    try {
//      DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
//      DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
//      Document document = documentBuilder.newDocument();
//      return document;
//    }
//    catch (Exception e) {
//      log.error("error in createNewDocument", e);
//      throw e;
//    }
//  }
//  private void doProcessingInstructions(Element processingInstructionsElement, Element rootElement) throws Exception {
//    try {
//      String xslt = xPathHelper.getString("xslt", processingInstructionsElement);
//      String css = xPathHelper.getString("css", processingInstructionsElement);
//      if (!xslt.equals("")) {
//        if (0 == 1) {
//          // not now - to be done processing XML using XSLT in this program
//        }
//        else {
//          ProcessingInstruction xmlstylesheet = outDocument.createProcessingInstruction("xml-stylesheet", "type=\"text/xsl\" href=\"" + xslt + "\"");
//          outDocument.insertBefore(xmlstylesheet, rootElement);
//        }
//      }
//      if (!css.equals("")) {
//        ProcessingInstruction xmlstylesheet = outDocument.createProcessingInstruction("xml-stylesheet", "type=\"text/css\" href=\"" + css + "\"");
//        outDocument.insertBefore(xmlstylesheet, rootElement);
//      }
//    }
//    catch (Exception e) {
//      log.error("error in doProcessingInstructions", e);
//      throw e;
//    }
//  }
  // process TABLE elements should be better commented
  private void processTableElement(Element tableElement) throws Exception {
    // What should this element be named.
    String tableElementName = tableElement.getAttribute("xmlName");
    // get SQL SELECT statement to be used for query column values
    String tableSql = tableElement.getAttribute("sql");
    String prepared = tableElement.getAttribute("isPrepared");
    boolean isPrepared = false;
    ArrayList paramVals = new ArrayList();
    if (prepared != null && prepared.trim().length() > 0) {
      isPrepared = Boolean.parseBoolean(prepared);
    }
    if (isPrepared) {
      // Process all the <prepared...> elements to create replacements
      NodeList nl = tableElement.getChildNodes();
      for (int i = 0; i < nl.getLength(); i++) {
        if (nl.item(i).getNodeName() == "replacement") {
          Element elem = (Element) nl.item(i);
          String order = elem.getAttribute("order");
          String type = elem.getAttribute("paramType");
          // Not really a value yet, but the global variable to use.
          String value = elem.getAttribute("parameter");
          String template = elem.getAttribute("conversionTemplate");
          ReplacementValue rv = new ReplacementValue();
          rv.setOrder(Integer.parseInt(order));
          rv.setType(type);
          rv.setValue(value);
          rv.setConversionTemplate(template);
          paramVals.add(rv);
        }
      }
    }
    // replace global variables names by their values
    Statement statement = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      if (isPrepared) {
        preparedStatement = getPreparedStatement(connection, tableSql);
        resultSet = getResultSet(preparedStatement, paramVals);
      }
      else {
        tableSql = replaceGlobalVariable(tableSql);
        statement = getStatement(connection);
        resultSet = getResultSet(statement, tableSql);
      }
      while (resultSet.next()) {
        // each record is encapsulated by table xmlName
        writer.writeStartElement(tableElementName);
//        Element newElement = outDocument.createElement(tableElementName);
        NodeList nl = tableElement.getChildNodes();
        // walk through each column of table element
        for (int i = 0; i < nl.getLength(); i++) {
          // XSD ensures columns first, table second
          if (nl.item(i).getNodeName() == "column") {
            processColumnElement((Element) nl.item(i), resultSet);
          }
          if (nl.item(i).getNodeName() == "replacement") {
          }
          if (nl.item(i).getNodeName() == "table") {
            processTableElement((Element) nl.item(i));
          }
        }
        writer.writeEndElement();
//        parentElement.appendChild(newElement);
      }
    }
    catch (Exception e) {
      log.error("tableSql=\"" + tableSql + "\", multiStatementDriver=\"" + multiStatementDriver + "\"");
      log.error("error in processTableElement", e);
      throw e;
    }
    finally {
      // Do Some cleanup
      if (resultSet != null) {
         try {
           resultSet.close();
         }
         catch (Exception ignored) {
         }
      }
      if (statement != null && !multiStatementDriver) {
        try {
          statement.close();
        }
        catch (Exception ignored) {
        }
      }
      if (preparedStatement != null) {
        try {
          preparedStatement.close();
        }
        catch (Exception ignored) {
        }
      }
    }
  }
  // process COLUMN elements should be better commented
  private void processColumnElement(Element columnElement, ResultSet resultSet) throws Exception {
    String columnElementName = columnElement.getAttribute("xmlName");
    String columnElementValue = columnElement.getAttribute("sqlName");
    String columnGlobalVariableName = columnElement.getAttribute("globalVariableName");
    String charsetName = columnElement.getAttribute("charsetName");
    if (charsetName == null | charsetName == "") {
      charsetName = globalCharsetName;
    }
    try {
      int columnIndex = resultSet.findColumn(columnElementValue);// index of this attribute in the ResultSet
      String columnValue = getColumnValueAsString(resultSet, columnIndex, charsetName);
      // is value of this column used inside following SQL SELECT statements? as global variable...
      if (columnGlobalVariableName != null) {
        globalVariables.put(columnGlobalVariableName, columnValue);
      }
//      Element newElement = outDocument.createElement(columnElementName);
//       Text newText = outDocument.createTextNode(columnValue);
//       newElement.appendChild(newText);
//       parentElement.appendChild(newElement);
      writer.writeStartElement(columnElementName);
      writer.writeCharacters(columnValue);
      writer.writeEndElement();
    }
    catch (Exception e) {
      log.error("xmlName=\"" + columnElementName + "\", sqlName=\"" + columnElementValue + "\", globalVariableName=\"" + columnGlobalVariableName
          + "\", charsetName=\"" + charsetName + "\"");
      log.error("error in processColumnElement", e);
      throw e;
    }
  }
  private void setReplacementValue(PreparedStatement ps, int order, String type, String conversionTemplate, String rValue) throws Exception {
    String value = (String) globalVariables.get(rValue);
    if (type.toLowerCase().trim().equals("string")) {
      ps.setString(order, value);
    }
    else if (type.toLowerCase().trim().equals("int")) {
      ps.setInt(order, Integer.parseInt(value));
    }
    else if (type.toLowerCase().trim().equals("long")) {
      ps.setLong(order, Long.parseLong(value));
    }
    else if (type.toLowerCase().trim().equals("double")) {
      ps.setDouble(order, Double.parseDouble(value));
    }
    else if (type.toLowerCase().trim().equals("float")) {
      ps.setFloat(order, Float.parseFloat(value));
    }
    else if (type.toLowerCase().trim().equals("date")) {
      SimpleDateFormat sdf = new SimpleDateFormat(conversionTemplate);
      Date d = sdf.parse(value);
      ps.setDate(order, new java.sql.Date(d.getTime()));
      sdf = null;
    }
  }
  // RegExp change global variables in SQL SELECT statement using regular expression
  private String replaceGlobalVariable(String tableSql) {
    Set set = globalVariables.keySet();
    Iterator iterator = set.iterator();
    while (iterator.hasNext()) {// walk through each global variables pair
      String key = (String) iterator.next();
      String value = (String) globalVariables.get(key);
      // here can be an error in RegEx - should be tested by users
      tableSql = tableSql.replaceAll("\\" + key + "{1,}", value);// replace key
      // by value
    }// end_while
    return tableSql;
  }
  // SQL generic functions
  private Connection getConnection(String jdbcDriver, String jdbcUrl, String jdbcUserName, String jdbcPassword) throws Exception {
    try {
      Class.forName(jdbcDriver);
      Connection connection = DriverManager.getConnection(jdbcUrl, jdbcUserName, jdbcPassword);
      return connection;
    }
    catch (Exception e) {
      log.error("jdbcDriver=\"" + jdbcDriver + "\", jdbcUrl=\"" + jdbcUrl + "\", jdbcUserName and jdbcPassword are not logged");
      log.error("error in getConnection", e);
      throw e;
    }
  }
   private Statement getStatement(Connection connection) throws Exception {
     try {
       // if-else for drivers, which can not have multiple ResultSet-s per one Statement
       if (!multiStatementDriver) {
         return connection.createStatement();
        }
        else {
          if (globalStatement == null) {
            globalStatement = connection.createStatement();
          }
          return globalStatement;
        }
     }
     catch (Exception e) {
       e.printStackTrace();
       log.error("connection=\"" + connection + "\"");
       log.error("error in getStatement", e);
       throw e;
     }
   }
   private PreparedStatement getPreparedStatement(Connection connection, String sqlSelect) throws Exception {
     try {
       return connection.prepareStatement(sqlSelect);
     }
     catch (Exception e) {
       e.printStackTrace();
       log.error("connection=\"" + connection + "\", sqlSelect=\"" + sqlSelect + "\"");
       log.error("error in getPreparedStatement", e);
       throw e;
     }
   }
  /**
   * 
   * The standard for using regular statements
   * 
   * @param connection the sql connection to use
   * @param sqlSelect  the sql to execute - already replaced from global vals
   * @return           the result set from executing the statement
   * @throws Exception
   */
  private ResultSet getResultSet(Statement statement, String sqlSelect) throws Exception {
    ResultSet retVal = null;
    try {
      retVal = statement.executeQuery(sqlSelect);
    }
    catch (Exception e) {
      log.error("statement=\"" + statement + "\", sqlSelect=\"" + sqlSelect + "\", multiStatementDriver=\"" + multiStatementDriver + "\"");
      log.error("error in getResultSet", e);
      throw e;
    }
    return retVal;
  }
  /**
   * 
   * This implementation is specifically for using prepared statements. 
   * 
   * @param connection The sql connection to use
   * @param sqlSelect  the sql to execute
   * @param paramVals  the arraylist of parameters
   * @return           the result set from executing the query
   * @throws Exception
   */
  private ResultSet getResultSet(PreparedStatement preparedStatement, ArrayList paramVals) throws Exception {
    ResultSet retVal = null;
    try {
      if (paramVals != null && paramVals.size() > 0) {
        for (int i = 0; i < paramVals.size(); i++) {
          ReplacementValue rv = (ReplacementValue) paramVals.get(i);
          this.setReplacementValue(preparedStatement, rv.getOrder(), rv.getType(), rv.getConversionTemplate(), (String) rv.getValue());
        }
      }
      retVal = preparedStatement.executeQuery();
    }
    catch (Exception e) {
      log.error("connection=\"" + connection + "\", multiStatementDriver=\"" + multiStatementDriver + "\"");
      log.error("error in getResultSet", e);
      throw e;
    }
    return retVal;
  }
  // TODO allow different types mainly boolean for char(1) that is "Y", "y", "N", "n", "0", "1", "T", "t", "F" or "f" for instance
  private String getColumnValueAsString(ResultSet resultSet, int columnIndex, String charsetName) throws Exception {
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    try {
      switch (resultSetMetaData.getColumnType(columnIndex)) {
        case java.sql.Types.VARCHAR:
        case java.sql.Types.CHAR:
        case java.sql.Types.LONGVARCHAR:
// not used - seems like the JDBC driver should return the string in the correct character set
//          byte[] byteArray = resultSet.getBytes(columnIndex);
//          if (byteArray == null || resultSet.wasNull()) {
//            return "null";
//          }
//          else {
//            String stringFromChar = new String(byteArray, charsetName);
//            return stringFromChar;
//          }// end_if-else
           String string = resultSet.getString(columnIndex);
           if (resultSet.wasNull()) {
             return "";
           }
           else {
             return string;
           }// end_if-else
        case java.sql.Types.DOUBLE:
        case java.sql.Types.INTEGER:
        case java.sql.Types.BIGINT:
        case java.sql.Types.TINYINT:
        case java.sql.Types.SMALLINT:
        case java.sql.Types.REAL:
        case java.sql.Types.FLOAT:
        case java.sql.Types.DECIMAL:
        case java.sql.Types.NUMERIC:
        case java.sql.Types.BIT:
          String stringFromNumeric = resultSet.getString(columnIndex);
          if (resultSet.wasNull()) {
            return "";
          }
          else {
            return stringFromNumeric;
          }// end_if-else
        case java.sql.Types.BINARY:
        case java.sql.Types.VARBINARY:
        case java.sql.Types.LONGVARBINARY:
          String stringFromBinary = resultSet.getString(columnIndex);
          if (resultSet.wasNull()) {
            return "";
          }
          else {
            return stringFromBinary;
          }// end_if-else
        case java.sql.Types.DATE:
          java.sql.Date date = resultSet.getDate(columnIndex);
          if (resultSet.wasNull()) {
            return "";
          }
          else {
            return date.toString();
          }// end_if-else
        case java.sql.Types.TIME:
          java.sql.Time time = resultSet.getTime(columnIndex);
          if (resultSet.wasNull()) {
            return "";
          }
          else {
            return time.toString();
          }// end_if-else
        case java.sql.Types.TIMESTAMP:
          java.sql.Timestamp timestamp = resultSet.getTimestamp(columnIndex);
          if (resultSet.wasNull()) {
            return "";
          }
          else {
            return timestamp.toString();
          }// end_if-else
        default:
          return "unsupported data type (CLOB and BLOB should be, ARRAY, REF, STRUCT, JAVA OBJECT hard to be)";
      }// end_switch
    }
    catch (Exception e) {
      log.error("resultSet=\"" + resultSet + "\", column index=\"" + columnIndex + "\", column type=\""
          + resultSetMetaData.getColumnType(columnIndex) + "\", charsetName=\"" + charsetName + "\"");
      log.error("error in getColumnValueAsString", e);
      throw e;
    }
  }
  public String getValuesOfGlobalVariables() {
    return valuesOfGlobalVariables;
  }
  public void setValuesOfGlobalVariables(String valuesOfGlobalVariables) {
    this.valuesOfGlobalVariables = valuesOfGlobalVariables;
  }
  public String getPathToOutputXmlFile() {
    return pathToOutputXmlFile;
  }
  public void setPathToOutputXmlFile(String pathToOutputXmlFile) {
    this.pathToOutputXmlFile = pathToOutputXmlFile;
    if (log.isDebugEnabled()) {
      log.debug("Path to output xml is " + pathToOutputXmlFile);
    }
  }
  public long getTotalTime() {
    return totalTime;
  }
  public void setTotalTime(long totaltime) {
    this.totalTime = totaltime;
  }
  public long getXmlTime() {
    return xmlTime;
  }
  public void setXmlTime(long xmltime) {
    this.xmlTime = xmltime;
  }
  private static boolean isJaxpIncluded(String versionString) {
    boolean retVal = true;
    if (versionString != null && versionString.trim().length() >= 3) {
      versionString = versionString.substring(2, 3);
      int version = Integer.parseInt(versionString);
      if (version >= 5) {
        retVal = true;
      }
    }
    return retVal;
  }
  class ReplacementValue {
    private int    order;
    private String type;
    private Object value;
    private String conversionTemplate;
    public void setOrder(int order) {
      this.order = order;
    }
    public int getOrder() {
      return this.order;
    }
    public void setType(String type) {
      this.type = type;
    }
    public String getType() {
      return this.type;
    }
    public void setValue(Object value) {
      this.value = value;
    }
    public Object getValue() {
      return this.value;
    }
    public void setConversionTemplate(String template) {
      this.conversionTemplate = template;
    }
    public String getConversionTemplate() {
      return this.conversionTemplate;
    }
  }
}
Change to use XMLStreamWriter

Forums

Help

Change to use XMLStreamWriter document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Change to use XMLStreamWriter
