Java tutorial
/** * This class controls the process of persisting XML into a relational database. * * Copyright (C) 2007 Stephen Harding * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program 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 General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * * Please send inquiries to; steve@inverse2.com * * $Revision: 1.12 $ * * $Log: XMLtoSQL.java,v $ * Revision 1.12 2008/08/28 14:12:41 stevewdh * Added a method to allow you to get the defintion of a parsed script. * * Revision 1.11 2008/07/01 17:26:47 stevewdh * Changed logging so that user can specify the type they want (Java, Log4J or HTML). * * Revision 1.10 2008/04/17 13:31:32 stevewdh * Add methods getLastException() and getScriptMessage() which allow an application to know why a script failed to generate any XML. * You can now specify data from the input message as a substitution variable. * * Revision 1.9 2008/03/05 10:46:34 stevewdh * Fixed issue where if key columns where not above the data in the * XML heirachy the script would not work... (it seemed that UID worked ok). * * Made DB2 compatible by not putting ";" on end of XMLtoSQL statements. * * Fixed issue where if a column was not present in the XML the XMLtoSQL * class put in a blank value for it. * * Revision 1.8 2007/11/17 12:30:21 stevewdh * Fixed bug 1823431: Spurious message output by XMLtoSQL if "update" attribute set to true but "insert" attribute to false on a "statement" element. * * Revision 1.7 2007/10/24 17:16:53 stevewdh * Fixed an issue where if a UID element was blank the toaster would not recognise it as a UID column. * * Revision 1.6 2007/10/24 15:26:16 stevewdh * Implemented volatile attribute on the mapping. This will force the toaster to retrieve data from the database after it has inserted/updated it. This is useful when a trigger in the database may update the column. * * The ifnull attribute on a mapping will now be used when no data exists in the input XML. * * The class now outputs SQL statements generated by the mapping script as default. * * Revision 1.5 2007/10/19 11:39:04 stevewdh * Fixed bug 1816459: XMLtoSQL will handle data where everything is specified as attributes of an element and there are no child elements. Before this fix, only the first element would have been updated/created in the database. * * Revision 1.4 2007/10/18 17:27:25 stevewdh * Fixed bug 1815837: XMLtoSQL will not throw an null pointer exception if the abort or transaction attributes are not specified on the onError or onWarning elements. * * Fixed bug 1815917: XMLtoSQL will now not attempt to insert a row into the target table if no data was selected by any of the the mapping XPath expressions. * * Revision 1.3 2007/10/16 14:23:57 stevewdh * Removed some unwanted output messages. * * Revision 1.2 2007/10/16 14:13:34 stevewdh * Fixed bug 1814498: The toaster now allows you to map an attribute to a UID column in the database. * * Revision 1.1 2007/10/04 11:06:36 stevewdh * *** empty log message *** * * Revision 1.3 2007/09/30 13:09:05 stephen harding * Added contact details to license header. * * Revision 1.2 2007/09/22 16:38:28 stephen harding * Changed some log levels and made UID element namespace unimportant. * * Revision 1.1 2007/09/15 16:09:06 stephen harding * Added header. * * Revision 1.20 2007/07/07 11:25:46 stephen harding * *** empty log message *** * * Revision 1.19 2007/01/13 18:01:09 stephen harding * *** empty log message *** * * Revision 1.18 2007/01/11 13:56:14 stephen harding * Delete statement will use ALL key column values. If not specified in the XML the value ~~NOXMLDATA~~ will be used, which will probably mean the delete fails. * * Revision 1.17 2007/01/09 22:25:19 stephen harding * Fixed problem where missing data could cause an invalid SQL statement. * * Revision 1.16 2007/01/09 10:51:46 stephen harding * Fixed problem in attribute processing - if attribute has more than one key above it it now updates the database correctly. * * Revision 1.15 2007/01/08 19:06:49 stephen harding * Changed the way that data from attributes is grouped under a key. The set of child elements below the attributes element are included in the key elements... * * Revision 1.14 2007/01/07 13:43:34 stephen harding * Don't call License.summariseLicense() - it is already called by the License class. This stops the license summary from being shown twice. * * Revision 1.13 2007/01/06 19:03:05 stephen harding * Switching from update to insert for UID generation is not done with the 'insert' boolean. This could have caused problems if multiple rows needed to be inserted (some with existing UIDs). * * Revision 1.12 2007/01/06 17:49:06 stephen harding * Protect connection.rollback() wit a try/catch. * * Revision 1.11 2007/01/06 17:40:09 stephen harding * Fixed script abort processing. * * Revision 1.10 2007/01/06 17:22:33 stephen harding * Removed debug message. * * Revision 1.9 2007/01/06 17:13:10 stephen harding * If an XML item is not found by the xpath query then it will not be included in the SQL statement (unless it is a UID column...). * * Revision 1.8 2007/01/06 12:59:13 stephen harding * Public key for license decryption is now held in the License class. * * Revision 1.7 2007/01/06 11:17:23 stephen harding * Added new public key to the source code. * * Revision 1.6 2006/12/30 14:15:43 stephen harding * Changed CVS keyword id to revision. * * Revision 1.5 2006/12/30 14:13:16 stephen harding * Logging changes and add CVS keywords. * */ package com.init.octo; import java.io.ByteArrayInputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.StringReader; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; import org.apache.tika.Tika; import org.jdom2.Document; import org.jdom2.Element; import org.jdom2.JDOMException; import org.jdom2.input.SAXBuilder; import com.init.octo.parser.ParseException; import com.init.octo.parser.XMLtoSQLScript; import com.init.octo.util.Logger; public class XMLtoSQL { private static Logger log = Logger.getLogger(XMLtoSQL.class.getName()); private Document xmlInput; // the XML document that the data for the SQL // will be extracted from private Connection connection; // database connection private Properties parameters; // setup by user of the class and are used to // setup the working variables private StringBuffer errorMessages; // error message buffer private Element scriptRoot; // root element of the XML to SQL script private String rootDirectory; // directory where files are relative to private Exception lastException; /** * Construct an XMLToSQLMapping object. * */ public XMLtoSQL() { log.debug("Constructing XMLToSQL object..."); parameters = new Properties(); } public String getScriptMessage() { return (errorMessages == null ? "" : errorMessages.toString()); } public Exception getLastException() { return (lastException); } public Element getScriptDefinition() { return (scriptRoot); } public void setRootDirectory(String directory) { rootDirectory = directory; } public void setDatabaseConnection(Connection connection) { this.connection = connection; } public void setParameter(String paramName, String paramValue) { parameters.setProperty(paramName, paramValue); } public void setInputXML(String inxml) throws JDOMException, IOException { // convert the XML into a jdom structure... SAXBuilder xmlReader = new SAXBuilder(); xmlInput = xmlReader.build(new ByteArrayInputStream(inxml.getBytes())); } public boolean setInputXMLFile(String filename) { try { setInputXML(cacheFile(filename)); } catch (Exception ex) { return (false); } return (true); } private String cacheFile(String filename) throws Exception { if (rootDirectory != null && filename.indexOf(File.separator) == -1) { filename = rootDirectory + File.separator + filename; } FileInputStream fis = new FileInputStream(filename); StringBuffer tmp = new StringBuffer(); byte[] buf = new byte[1024]; int len; fis.close(); while ((len = fis.read(buf)) != -1) { tmp.append(new String(buf, 0, len)); } return (tmp.toString()); } private String getFileType(String script) { String mimeType = ""; Tika tika = new Tika(); mimeType = tika.detect(script); return mimeType.toLowerCase(); } public Element setScript(String script) throws JDOMException, IOException, ParseException { Element scriptRoot = null; if (getFileType(script).equals("application/xml")) { SAXBuilder xmlReader = new SAXBuilder(); Document doc = xmlReader.build(new ByteArrayInputStream(script.getBytes())); scriptRoot = doc.getRootElement(); } else { XMLtoSQLScript parser = new XMLtoSQLScript(new StringReader(script)); // parser.parseUpdateScript(); scriptRoot = parser.getParsedDocumentRoot(); } return scriptRoot; } public Element setScriptFromFile(String filename) throws JDOMException, IOException, ParseException, Exception { return setScript(cacheFile(filename)); } public Document runScript() throws JDOMException, SQLException { ScriptRunner scriptRunner = new ScriptRunner(connection, scriptRoot, xmlInput, parameters, errorMessages, lastException); return scriptRunner.run(); } } // end class XMLToSQLMapping