Java tutorial
/********************************************************************************** * $URL: https://source.sakaiproject.org/svn/warehouse/trunk/warehouse-impl/impl/src/java/org/sakaiproject/warehouse/util/db/DbLoader.java $ * $Id: DbLoader.java 105080 2012-02-24 23:10:31Z ottenhoff@longsight.com $ *********************************************************************************** * * Copyright (c) 2005, 2006, 2007, 2008 The Sakai Foundation * * Licensed under the Educational Community License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.opensource.org/licenses/ECL-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * **********************************************************************************/ package org.sakaiproject.warehouse.util.db; import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.PrintWriter; import java.io.StringReader; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.Enumeration; import java.util.Hashtable; import java.util.Iterator; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory; import javax.xml.parsers.ParserConfigurationException; import javax.xml.parsers.SAXParserFactory; import javax.xml.transform.Result; import javax.xml.transform.Source; import javax.xml.transform.Transformer; import javax.xml.transform.TransformerException; import javax.xml.transform.TransformerFactory; import javax.xml.transform.dom.DOMSource; import javax.xml.transform.sax.SAXResult; import javax.xml.transform.stream.StreamSource; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.sakaiproject.component.cover.ServerConfigurationService; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.w3c.dom.Text; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; /** * <p>A tool to set up a OSPI database. This tool was created so that OSPI * developers would only have to maintain a single set of xml documents to define * the OSPI database schema and data. Previously it was necessary to maintain * different scripts for each database we wanted to support.</p> * * <p>DbLoader reads the generic types that are specified in tables.xml and * tries to map them to local types by querying the database metadata via methods * implemented by the JDBC driver. Fallback mappings can be supplied in * dbloader.xml for cases where the JDBC driver is not able to determine the * appropriate mapping. Such cases will be reported to standard out.</p> * * <p>An xsl transformation is used to produce the DROP TABLE and CREATE TABLE * SQL statements. These statements can be altered by modifying tables.xsl</p> * * <p> all table names should have lower case names</p> * * <p>Generic data types (as defined in java.sql.Types) which may be specified * in tables.xml include: * <code>BIT, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE, * NUMERIC, DECIMAL, CHAR, VARCHAR, LONGVARCHAR, DATE, TIME, TIMESTAMP, * BINARY, VARBINARY, LONGVARBINARY, NULL, OTHER, JAVA_OBJECT, DISTINCT, * STRUCT, ARRAY, BLOB, CLOB, REF</code> * * <p><strong>WARNING: YOU MAY WANT TO MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING DbLoader</strong></p> * * <p>DbLoader will perform the following steps: * <ol> * <li>Read configurable properties from dbloader.xml</li> * <li>Get database connection from DbService</li> * <li>Read tables.xml and issue corresponding DROP TABLE and CREATE TABLE SQL statements.</li> * <li>Read data.xml and issue corresponding INSERT/UPDATE/DELETE SQL statements.</li> * </ol> * </p> * * @author <a href="kweiner@interactivebusiness.com">Ken Weiner</a>, kweiner@interactivebusiness.com * @author modified and adapted to OSPI by <a href="felipeen@udel.edu">Luis F.C. Mendes</a> - University of Delaware * @version $Revision: 105080 $ * @see java.sql.Types */ public class DbLoader { protected final Log logger = LogFactory.getLog(getClass()); private Connection con; private Statement stmt; private PreparedStatement pstmt; private Document tablesDoc; private Document tablesDocGeneric; private boolean createTableScript; private boolean populateTables; private PrintWriter tableScriptOut; private boolean dropTables; private boolean createTables; private String dbName; private String dbVersion; private String driverName; private String driverVersion; // Added version 1.8/1.6.2.4 private boolean alterTables; private boolean indexTables; private Hashtable tableColumnTypes = new Hashtable(); private PropertiesHandler propertiesHandler; public DbLoader(Connection con) { this.con = con; } public void runLoader(InputStream tables) { try { // Read in the properties XMLReader parser = getXMLReader(); readProperties(parser, getClass().getResourceAsStream("dbloader.xml")); //override default properties propertiesHandler.properties.setDropTables( ServerConfigurationService.getString("sakai.datawarehouse.dbLoader.properties.dropTables", propertiesHandler.properties.getDropTables())); propertiesHandler.properties.setCreateTables( ServerConfigurationService.getString("sakai.datawarehouse.dbLoader.properties.createTables", propertiesHandler.properties.getCreateTables())); propertiesHandler.properties.setAlterTables( ServerConfigurationService.getString("sakai.datawarehouse.dbLoader.properties.alterTables", propertiesHandler.properties.getAlterTables())); propertiesHandler.properties.setIndexTables( ServerConfigurationService.getString("sakai.datawarehouse.dbLoader.properties.indexTables", propertiesHandler.properties.getIndexTables())); propertiesHandler.properties.setPopulateTables( ServerConfigurationService.getString("sakai.datawarehouse.dbLoader.properties.populateTables", propertiesHandler.properties.getPopulateTables())); propertiesHandler.properties.setCreateTableScript(ServerConfigurationService.getString( "sakai.datawarehouse.dbLoader.properties.createTableScript", propertiesHandler.properties.getCreateTableScript())); propertiesHandler.properties.setTableScriptFileName(ServerConfigurationService.getString( "sakai.datawarehouse.dbLoader.properties.tableScriptFileName", propertiesHandler.properties.getTableScriptFileName())); //print db info printInfo(); // Read drop/create/populate table settings dropTables = Boolean.valueOf(propertiesHandler.properties.getDropTables()).booleanValue(); createTables = Boolean.valueOf(propertiesHandler.properties.getCreateTables()).booleanValue(); populateTables = Boolean.valueOf(propertiesHandler.properties.getPopulateTables()).booleanValue(); alterTables = Boolean.valueOf(propertiesHandler.properties.getAlterTables()).booleanValue(); indexTables = Boolean.valueOf(propertiesHandler.properties.getIndexTables()).booleanValue(); // Set up script createTableScript = Boolean.valueOf(propertiesHandler.properties.getCreateTableScript()).booleanValue(); if (createTableScript) initTableScript(); // read command line arguements to override properties in dbloader.xml boolean usetable = false; boolean usedata = false; // okay, start processing try { // Read tables.xml DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance(); DocumentBuilder domParser = dbf.newDocumentBuilder(); // Eventually, write and validate against a DTD //domParser.setFeature ("http://xml.org/sax/features/validation", true); //domParser.setEntityResolver(new DTDResolver("tables.dtd")); //tablesURL = DbLoader.class.getResource(PropertiesHandler.properties.getTablesUri()); tablesDoc = domParser.parse(new InputSource(tables)); } catch (ParserConfigurationException pce) { throw new RuntimeException(pce); } catch (Exception e) { throw new RuntimeException(e); } // Hold on to tables xml with generic types tablesDocGeneric = (Document) tablesDoc.cloneNode(true); // Replace all generic data types with local data types replaceDataTypes(tablesDoc); // tables.xml + tables.xsl --> DROP TABLE and CREATE TABLE sql statements try { Result xmlResult = new SAXResult(TableHandlerFactory.getTableHandler(this)); Source xmlSource = new DOMSource(tablesDoc); TransformerFactory tFactory = TransformerFactory.newInstance(); Transformer transformer = tFactory .newTransformer(new StreamSource(getClass().getResourceAsStream("tables.xsl"))); transformer.transform(xmlSource, xmlResult); //transformer.transform(xmlSource, new StreamResult(new FileOutputStream("tables.out"))); } catch (TransformerException te) { throw new RuntimeException(te); } } catch (Exception e) { throw new RuntimeException(e); } } protected XMLReader getXMLReader() throws SAXException, ParserConfigurationException { SAXParserFactory spf = SAXParserFactory.newInstance(); return spf.newSAXParser().getXMLReader(); } protected void printInfo() throws SQLException { DatabaseMetaData dbMetaData = con.getMetaData(); dbName = dbMetaData.getDatabaseProductName(); dbVersion = dbMetaData.getDatabaseProductVersion(); driverName = dbMetaData.getDriverName(); driverVersion = dbMetaData.getDriverVersion(); logger.debug("Starting DbLoader..."); logger.debug("Database name: '" + dbName + "'"); logger.debug("Database version: '" + dbVersion + "'"); logger.debug("Driver name: '" + driverName + "'"); logger.debug("Driver version: '" + driverVersion + "'"); logger.debug("Database url: '" + dbMetaData.getURL() + "'"); } protected void initTableScript() throws java.io.IOException { String scriptFileName = System.getProperty("sakai.home") + propertiesHandler.properties.getTableScriptFileName(); //String scriptFileName = System.getProperty("sakai.home") +propertiesHandler.properties.getScriptFileName() + "." + System.currentTimeMillis(); //String scriptFileName = propertiesHandler.properties.getScriptFileName() + "." + System.currentTimeMillis(); //File scriptFile = new File(scriptFileName); //if (scriptFile.exists()) // scriptFile.delete(); //scriptFile.createNewFile(); //if (!scriptFile.exists()) // scriptFile.createNewFile(); String initProperty = "sakai.dw.initializedTables"; String inited = System.getProperty(initProperty); if (inited == null) { System.getProperties().setProperty(initProperty, "true"); File scriptFile = new File(scriptFileName); if (scriptFile.exists()) scriptFile.delete(); } tableScriptOut = new PrintWriter(new BufferedWriter(new FileWriter(scriptFileName, true)), true); } protected void replaceDataTypes(Document tablesDoc) { Element tables = tablesDoc.getDocumentElement(); NodeList types = tables.getElementsByTagName("type"); for (int i = 0; i < types.getLength(); i++) { Node type = (Node) types.item(i); NodeList typeChildren = type.getChildNodes(); for (int j = 0; j < typeChildren.getLength(); j++) { Node text = (Node) typeChildren.item(j); String genericType = text.getNodeValue(); // Replace generic type with mapped local type text.setNodeValue(getLocalDataTypeName(genericType)); } } } protected int getJavaSqlDataTypeOfColumn(Document tablesDocGeneric, String tableName, String columnName) { int dataType = 0; String hashKey = tableName + File.separator + columnName; // try to use cached version first if (tableColumnTypes.get(hashKey) != null) return ((Integer) tableColumnTypes.get(hashKey)).intValue(); // Find the right table element Element table = getTableWithName(tablesDocGeneric, tableName); // Find the columns element within Element columns = getFirstChildWithName(table, "columns"); // Search for the first column who's name is columnName for (Node ch = columns.getFirstChild(); ch != null; ch = ch.getNextSibling()) { if (ch instanceof Element && ch.getNodeName().equals("column")) { Element name = getFirstChildWithName((Element) ch, "name"); if (getNodeValue(name).equals(columnName)) { // Get the corresponding type and return it's type code Element value = getFirstChildWithName((Element) ch, "type"); dataType = getJavaSqlType(getNodeValue(value)); } } } // store value is hashtable for next call to this method, prevents // repeating xml parsing which takes a very long time tableColumnTypes.put(hashKey, new Integer(dataType)); return dataType; } protected Element getFirstChildWithName(Element parent, String name) { Element child = null; for (Node ch = parent.getFirstChild(); ch != null; ch = ch.getNextSibling()) { if (ch instanceof Element && ch.getNodeName().equals(name)) { child = (Element) ch; break; } } return child; } protected Element getTableWithName(Document tablesDoc, String tableName) { Element tableElement = null; NodeList tables = tablesDoc.getElementsByTagName("table"); for (int i = 0; i < tables.getLength(); i++) { Node table = (Node) tables.item(i); for (Node tableChild = table.getFirstChild(); tableChild != null; tableChild = tableChild .getNextSibling()) { if (tableChild instanceof Element && tableChild.getNodeName() != null && tableChild.getNodeName().equals("name")) { if (tableName.equals(getNodeValue(tableChild))) { tableElement = (Element) table; break; } } } } return tableElement; } protected String getNodeValue(Node node) { String nodeVal = null; for (Node ch = node.getFirstChild(); ch != null; ch = ch.getNextSibling()) { if (ch instanceof Text) nodeVal = ch.getNodeValue(); } return nodeVal; } protected String getLocalDataTypeName(String genericDataTypeName) { String localDataTypeName = null; try { DatabaseMetaData dbmd = con.getMetaData(); String dbName = dbmd.getDatabaseProductName(); String dbVersion = dbmd.getDatabaseProductVersion(); String driverName = dbmd.getDriverName(); String driverVersion = dbmd.getDriverVersion(); // Check for a mapping in DbLoader.xml localDataTypeName = propertiesHandler.properties.getMappedDataTypeName(dbName, dbVersion, driverName, driverVersion, genericDataTypeName); if (localDataTypeName != null) return localDataTypeName; // Find the type code for this generic type name int dataTypeCode = getJavaSqlType(genericDataTypeName); // Find the first local type name matching the type code ResultSet rs = dbmd.getTypeInfo(); try { while (rs.next()) { int localDataTypeCode = rs.getInt("DATA_TYPE"); if (dataTypeCode == localDataTypeCode) { try { localDataTypeName = rs.getString("TYPE_NAME"); } catch (SQLException sqle) { } break; } } } finally { rs.close(); } if (localDataTypeName != null) return localDataTypeName; // No matching type found, report an error logger.error("Error in DbLoader.getLocalDataTypeName()"); logger.error("Your database driver, '" + driverName + "', version '" + driverVersion + "', was unable to find a local type name that matches the generic type name, '" + genericDataTypeName + "'."); logger.error("Please add a mapped type for database '" + dbName + "', version '" + dbVersion + "' inside your properties file and run this program again."); logger.error("Exiting..."); } catch (Exception e) { logger.error("Error in DbLoader.getLocalDataTypeName()", e); } return null; } protected int getJavaSqlType(String genericDataTypeName) { // Find the type code for this generic type name int dataTypeCode = 0; if (genericDataTypeName.equalsIgnoreCase("BIT")) dataTypeCode = Types.BIT; // -7 else if (genericDataTypeName.equalsIgnoreCase("TINYINT")) dataTypeCode = Types.TINYINT; // -6 else if (genericDataTypeName.equalsIgnoreCase("SMALLINT")) dataTypeCode = Types.SMALLINT; // 5 else if (genericDataTypeName.equalsIgnoreCase("INTEGER")) dataTypeCode = Types.INTEGER; // 4 else if (genericDataTypeName.equalsIgnoreCase("BIGINT")) dataTypeCode = Types.BIGINT; // -5 else if (genericDataTypeName.equalsIgnoreCase("FLOAT")) dataTypeCode = Types.FLOAT; // 6 else if (genericDataTypeName.equalsIgnoreCase("REAL")) dataTypeCode = Types.REAL; // 7 else if (genericDataTypeName.equalsIgnoreCase("DOUBLE")) dataTypeCode = Types.DOUBLE; // 8 else if (genericDataTypeName.equalsIgnoreCase("NUMERIC")) dataTypeCode = Types.NUMERIC; // 2 else if (genericDataTypeName.equalsIgnoreCase("DECIMAL")) dataTypeCode = Types.DECIMAL; // 3 else if (genericDataTypeName.equalsIgnoreCase("CHAR")) dataTypeCode = Types.CHAR; // 1 else if (genericDataTypeName.equalsIgnoreCase("VARCHAR")) dataTypeCode = Types.VARCHAR; // 12 else if (genericDataTypeName.equalsIgnoreCase("LONGVARCHAR")) dataTypeCode = Types.LONGVARCHAR; // -1 else if (genericDataTypeName.equalsIgnoreCase("DATE")) dataTypeCode = Types.DATE; // 91 else if (genericDataTypeName.equalsIgnoreCase("TIME")) dataTypeCode = Types.TIME; // 92 else if (genericDataTypeName.equalsIgnoreCase("TIMESTAMP")) dataTypeCode = Types.TIMESTAMP; // 93 else if (genericDataTypeName.equalsIgnoreCase("BINARY")) dataTypeCode = Types.BINARY; // -2 else if (genericDataTypeName.equalsIgnoreCase("VARBINARY")) dataTypeCode = Types.VARBINARY; // -3 else if (genericDataTypeName.equalsIgnoreCase("LONGVARBINARY")) dataTypeCode = Types.LONGVARBINARY; // -4 else if (genericDataTypeName.equalsIgnoreCase("NULL")) dataTypeCode = Types.NULL; // 0 else if (genericDataTypeName.equalsIgnoreCase("OTHER")) dataTypeCode = Types.OTHER; // 1111 else if (genericDataTypeName.equalsIgnoreCase("JAVA_OBJECT")) dataTypeCode = Types.JAVA_OBJECT; // 2000 else if (genericDataTypeName.equalsIgnoreCase("DISTINCT")) dataTypeCode = Types.DISTINCT; // 2001 else if (genericDataTypeName.equalsIgnoreCase("STRUCT")) dataTypeCode = Types.STRUCT; // 2002 else if (genericDataTypeName.equalsIgnoreCase("ARRAY")) dataTypeCode = Types.ARRAY; // 2003 else if (genericDataTypeName.equalsIgnoreCase("BLOB")) dataTypeCode = Types.BLOB; // 2004 else if (genericDataTypeName.equalsIgnoreCase("CLOB")) dataTypeCode = Types.CLOB; // 2005 else if (genericDataTypeName.equalsIgnoreCase("REF")) dataTypeCode = Types.REF; // 2006 return dataTypeCode; } protected void dropTable(String dropTableStatement) { if (createTableScript) tableScriptOut.println(dropTableStatement + propertiesHandler.properties.getStatementTerminator()); else { try { stmt = con.createStatement(); try { stmt.executeUpdate(dropTableStatement); } catch (SQLException sqle) {/*Table didn't exist*/ } } catch (Exception e) { logger.error("Error in DbLoader.dropTable()", e); } finally { try { stmt.close(); } catch (Exception e) { } } } } protected void createTable(String createTableStatement) { if (createTableScript) tableScriptOut.println(createTableStatement + propertiesHandler.properties.getStatementTerminator()); else { try { stmt = con.createStatement(); stmt.executeUpdate(createTableStatement); } catch (Exception e) { logger.error("error creating table with this sql: " + createTableStatement); logger.error("", e); } finally { try { stmt.close(); } catch (Exception e) { } } } } protected void alterTable(String alterTableStatement) { if (createTableScript) tableScriptOut.println(alterTableStatement + propertiesHandler.properties.getStatementTerminator()); else { try { stmt = con.createStatement(); stmt.executeUpdate(alterTableStatement); } catch (Exception e) { logger.error("error altering table with this sql: " + alterTableStatement); logger.error("", e); } finally { try { stmt.close(); } catch (Exception e) { } } } } protected void indexTable(String indexTableStatement) { if (createTableScript) tableScriptOut.println(indexTableStatement + propertiesHandler.properties.getStatementTerminator()); else { try { stmt = con.createStatement(); stmt.executeUpdate(indexTableStatement); } catch (Exception e) { logger.error("error indexing table with this sql: " + indexTableStatement); logger.error("", e); } finally { try { stmt.close(); } catch (Exception e) { } } } } protected void readProperties(XMLReader parser, InputStream properties) throws SAXException, IOException { propertiesHandler = new PropertiesHandler(); parser.setContentHandler(propertiesHandler); parser.setErrorHandler(propertiesHandler); parser.parse(new InputSource(properties)); } private class PropertiesHandler extends DefaultHandler { private StringBuilder charBuff = null; private Properties properties; private DbTypeMapping dbTypeMapping; private Type type; public void startDocument() { } public void endDocument() { } public void startElement(String namespaceURI, String localName, String qName, Attributes atts) { charBuff = new StringBuilder(); if (qName.equals("properties")) properties = new Properties(); else if (qName.equals("db-type-mapping")) dbTypeMapping = new DbTypeMapping(); else if (qName.equals("type")) type = new Type(); } public void endElement(String namespaceURI, String localName, String qName) { if (qName.equals("drop-tables")) // drop tables ("true" or "false") properties.setDropTables(charBuff.toString()); else if (qName.equals("create-tables")) // create tables ("true" or "false") properties.setCreateTables(charBuff.toString()); else if (qName.equals("populate-tables")) // populate tables ("true" or "false") properties.setPopulateTables(charBuff.toString()); else if (qName.equals("create-table-script")) // create table script ("true" or "false") properties.setCreateTableScript(charBuff.toString()); else if (qName.equals("table-script-file-name")) // script file name properties.setTableScriptFileName(charBuff.toString()); else if (qName.equals("statement-terminator")) // statement terminator properties.setStatementTerminator(charBuff.toString()); else if (qName.equals("db-type-mapping")) properties.addDbTypeMapping(dbTypeMapping); else if (qName.equals("db-name")) // database name dbTypeMapping.setDbName(charBuff.toString()); else if (qName.equals("db-version")) // database version dbTypeMapping.setDbVersion(charBuff.toString()); else if (qName.equals("driver-name")) // driver name dbTypeMapping.setDriverName(charBuff.toString()); else if (qName.equals("driver-version")) // driver version dbTypeMapping.setDriverVersion(charBuff.toString()); else if (qName.equals("type")) dbTypeMapping.addType(type); else if (qName.equals("generic")) // generic type type.setGeneric(charBuff.toString()); else if (qName.equals("local")) // local type type.setLocal(charBuff.toString()); else if (qName.equals("alter-tables")) // alter tables ("true" or "false") properties.setAlterTables(charBuff.toString()); else if (qName.equals("index-tables")) // index tables ("true" or "false") properties.setIndexTables(charBuff.toString()); } public void characters(char ch[], int start, int length) { charBuff.append(ch, start, length); } class Properties { private String dropTables; private String createTables; private String populateTables; private String createTableScript; private String tableScriptFileName; private String statementTerminator; private ArrayList dbTypeMappings = new ArrayList(); private String alterTables; private String indexTables; public String getDropTables() { return dropTables; } public String getCreateTables() { return createTables; } public String getPopulateTables() { return populateTables; } public String getCreateTableScript() { return createTableScript; } public String getTableScriptFileName() { return tableScriptFileName; } public String getStatementTerminator() { return statementTerminator; } public ArrayList getDbTypeMappings() { return dbTypeMappings; } public void setDropTables(String dropTables) { this.dropTables = dropTables; } public void setCreateTables(String createTables) { this.createTables = createTables; } public void setPopulateTables(String populateTables) { this.populateTables = populateTables; } public void setCreateTableScript(String createTableScript) { this.createTableScript = createTableScript; } public void setTableScriptFileName(String tableScriptFileName) { this.tableScriptFileName = tableScriptFileName; } public void setStatementTerminator(String statementTerminator) { this.statementTerminator = statementTerminator; } public void addDbTypeMapping(DbTypeMapping dbTypeMapping) { dbTypeMappings.add(dbTypeMapping); } public String getAlterTables() { return alterTables; } public void setAlterTables(String alterTables) { this.alterTables = alterTables; } public String getIndexTables() { return indexTables; } public void setIndexTables(String indexTables) { this.indexTables = indexTables; } public String getMappedDataTypeName(String dbName, String dbVersion, String driverName, String driverVersion, String genericDataTypeName) { String mappedDataTypeName = null; Iterator iterator = dbTypeMappings.iterator(); while (iterator.hasNext()) { DbTypeMapping dbTypeMapping = (DbTypeMapping) iterator.next(); String dbNameProp = dbTypeMapping.getDbName(); String dbVersionProp = dbTypeMapping.getDbVersion(); String driverNameProp = dbTypeMapping.getDriverName(); String driverVersionProp = dbTypeMapping.getDriverVersion(); if (dbNameProp.equalsIgnoreCase(dbName) && dbVersionProp.equalsIgnoreCase(dbVersion) && driverNameProp.equalsIgnoreCase(driverName) && driverVersionProp.equalsIgnoreCase(driverVersion)) { // Found a matching database/driver combination mappedDataTypeName = dbTypeMapping.getMappedDataTypeName(genericDataTypeName); } } return mappedDataTypeName; } } class DbTypeMapping { String dbName; String dbVersion; String driverName; String driverVersion; ArrayList types = new ArrayList(); public String getDbName() { return dbName; } public String getDbVersion() { return dbVersion; } public String getDriverName() { return driverName; } public String getDriverVersion() { return driverVersion; } public ArrayList getTypes() { return types; } public void setDbName(String dbName) { this.dbName = dbName; } public void setDbVersion(String dbVersion) { this.dbVersion = dbVersion; } public void setDriverName(String driverName) { this.driverName = driverName; } public void setDriverVersion(String driverVersion) { this.driverVersion = driverVersion; } public void addType(Type type) { types.add(type); } public String getMappedDataTypeName(String genericDataTypeName) { String mappedDataTypeName = null; Iterator iterator = types.iterator(); while (iterator.hasNext()) { Type type = (Type) iterator.next(); if (type.getGeneric().equalsIgnoreCase(genericDataTypeName)) mappedDataTypeName = type.getLocal(); } return mappedDataTypeName; } } class Type { String genericType; // "generic" is a Java reserved word String local; public String getGeneric() { return genericType; } public String getLocal() { return local; } public void setGeneric(String genericType) { this.genericType = genericType; } public void setLocal(String local) { this.local = local; } } } class DataHandler extends DefaultHandler { protected StringBuilder charBuff = null; protected boolean insideData = false; private boolean insideTable = false; private boolean insideName = false; private boolean insideRow = false; private boolean insideColumn = false; private boolean insideValue = false; private boolean supportsPreparedStatements = false; Table table; Row row; Column column; String action; //determines sql function for a table row String type; //determines type of column public void startDocument() { logger.debug("Populating tables..."); if (!populateTables) logger.debug("disabled."); supportsPreparedStatements = supportsPreparedStatements(); } public void endDocument() { //logger.debug(""); } public void startElement(String namespaceURI, String localName, String qName, Attributes atts) { charBuff = new StringBuilder(); if (qName.equals("data")) insideData = true; else if (qName.equals("table")) { insideTable = true; table = new Table(); action = atts.getValue("action"); } else if (qName.equals("name")) insideName = true; else if (qName.equals("row")) { insideRow = true; row = new Row(); } else if (qName.equals("column")) { insideColumn = true; column = new Column(); type = atts.getValue("type"); } else if (qName.equals("value")) insideValue = true; } public void endElement(String namespaceURI, String localName, String qName) { if (qName.equals("data")) insideData = false; else if (qName.equals("table")) insideTable = false; else if (qName.equals("name")) { insideName = false; if (!insideColumn) // table name table.setName(charBuff.toString().toLowerCase()); else // column name column.setName(charBuff.toString()); } else if (qName.equals("row")) { insideRow = false; if (action != null) { if (action.equals("delete")) executeSQL(table, row, "delete"); else if (action.equals("modify")) executeSQL(table, row, "modify"); else if (action.equals("add")) executeSQL(table, row, "insert"); } else if (populateTables) executeSQL(table, row, "insert"); } else if (qName.equals("column")) { insideColumn = false; if (type != null) column.setType(type); row.addColumn(column); } else if (qName.equals("value")) { insideValue = false; if (insideColumn) // column value column.setValue(charBuff.toString()); } } public void characters(char ch[], int start, int length) { charBuff.append(ch, start, length); } private String prepareInsertStatement(Row row, boolean preparedStatement) { StringBuilder sb = new StringBuilder("INSERT INTO "); sb.append(table.getName()).append(" ("); ArrayList columns = row.getColumns(); Iterator iterator = columns.iterator(); while (iterator.hasNext()) { Column column = (Column) iterator.next(); sb.append(column.getName()).append(", "); } // Delete comma and space after last column name (kind of sloppy, but it works) sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.append(") VALUES ("); iterator = columns.iterator(); while (iterator.hasNext()) { Column column = (Column) iterator.next(); if (preparedStatement) sb.append("?"); else { String value = column.getValue(); if (value != null) { if (value.equals("SYSDATE")) sb.append(value); else if (value.equals("NULL")) sb.append(value); else if (getJavaSqlDataTypeOfColumn(tablesDocGeneric, table.getName(), column.getName()) == Types.INTEGER) // this column is an integer, so don't put quotes (Sybase cares about this) sb.append(value); else { sb.append("'"); sb.append(sqlEscape(value.trim())); sb.append("'"); } } else sb.append("''"); } sb.append(", "); } // Delete comma and space after last value (kind of sloppy, but it works) sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.append(")"); return sb.toString(); } private String prepareDeleteStatement(Row row, boolean preparedStatement) { StringBuilder sb = new StringBuilder("DELETE FROM "); sb.append(table.getName()).append(" WHERE "); ArrayList columns = row.getColumns(); Iterator iterator = columns.iterator(); Column column; while (iterator.hasNext()) { column = (Column) iterator.next(); if (preparedStatement) sb.append(column.getName() + " = ? and "); else if (getJavaSqlDataTypeOfColumn(tablesDocGeneric, table.getName(), column.getName()) == Types.INTEGER) sb.append(column.getName() + " = " + sqlEscape(column.getValue().trim()) + " and "); else sb.append(column.getName() + " = " + "'" + sqlEscape(column.getValue().trim()) + "' and "); } sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); if (!preparedStatement) sb.deleteCharAt(sb.length() - 1); return sb.toString(); } private String prepareUpdateStatement(Row row, boolean preparedStatement) { StringBuilder sb = new StringBuilder("UPDATE "); sb.append(table.getName()).append(" SET "); ArrayList columns = row.getColumns(); Iterator iterator = columns.iterator(); Hashtable setPairs = new Hashtable(); Hashtable wherePairs = new Hashtable(); String type; Column column; while (iterator.hasNext()) { column = (Column) iterator.next(); type = column.getType(); if (type != null && type.equals("select")) { if (getJavaSqlDataTypeOfColumn(tablesDocGeneric, table.getName(), column.getName()) == Types.INTEGER) wherePairs.put(column.getName(), column.getValue().trim()); else wherePairs.put(column.getName(), "'" + column.getValue().trim() + "'"); } else { if (getJavaSqlDataTypeOfColumn(tablesDocGeneric, table.getName(), column.getName()) == Types.INTEGER) setPairs.put(column.getName(), column.getValue().trim()); else setPairs.put(column.getName(), "'" + column.getValue().trim() + "'"); } } String nm; String val; Enumeration sKeys = setPairs.keys(); while (sKeys.hasMoreElements()) { nm = (String) sKeys.nextElement(); val = (String) setPairs.get(nm); sb.append(nm + " = " + sqlEscape(val) + ", "); } sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.append(" WHERE "); Enumeration wKeys = wherePairs.keys(); while (wKeys.hasMoreElements()) { nm = (String) wKeys.nextElement(); val = (String) wherePairs.get(nm); sb.append(nm + "=" + sqlEscape(val) + " and "); } sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); sb.deleteCharAt(sb.length() - 1); return sb.toString(); } /** * Make a string SQL safe * @param sql the string that is not necessarily safe * @return SQL safe string */ public final String sqlEscape(String sql) { if (sql == null) { return ""; } else { int primePos = sql.indexOf("'"); if (primePos == -1) { return sql; } else { StringBuilder sb = new StringBuilder(sql.length() + 4); int startPos = 0; do { sb.append(sql.substring(startPos, primePos + 1)); sb.append("'"); startPos = primePos + 1; primePos = sql.indexOf("'", startPos); } while (primePos != -1); sb.append(sql.substring(startPos)); return sb.toString(); } } } private void executeSQL(Table table, Row row, String action) { if (createTableScript) { if (action.equals("delete")) tableScriptOut.println(prepareDeleteStatement(row, false) + propertiesHandler.properties.getStatementTerminator()); else if (action.equals("modify")) tableScriptOut.println(prepareUpdateStatement(row, false) + propertiesHandler.properties.getStatementTerminator()); else if (action.equals("insert")) tableScriptOut.println(prepareInsertStatement(row, false) + propertiesHandler.properties.getStatementTerminator()); } if (supportsPreparedStatements) { String preparedStatement = ""; try { if (action.equals("delete")) preparedStatement = prepareDeleteStatement(row, true); else if (action.equals("modify")) preparedStatement = prepareUpdateStatement(row, true); else if (action.equals("insert")) preparedStatement = prepareInsertStatement(row, true); //System.out.println(preparedStatement); pstmt = con.prepareStatement(preparedStatement); pstmt.clearParameters(); // Loop through parameters and set them, checking for any that excede 4k ArrayList columns = row.getColumns(); Iterator iterator = columns.iterator(); for (int i = 1; iterator.hasNext(); i++) { Column column = (Column) iterator.next(); String value = column.getValue(); // Get a java sql data type for column name int javaSqlDataType = getJavaSqlDataTypeOfColumn(tablesDocGeneric, table.getName(), column.getName()); if (value == null || (value != null && value.equalsIgnoreCase("NULL"))) pstmt.setNull(i, javaSqlDataType); else if (javaSqlDataType == Types.TIMESTAMP) { if (value.equals("SYSDATE")) pstmt.setTimestamp(i, new java.sql.Timestamp(System.currentTimeMillis())); else pstmt.setTimestamp(i, java.sql.Timestamp.valueOf(value)); } else { value = value.trim(); // can't read xml properly without this, don't know why yet int valueLength = value.length(); //System.out.println("Value: " + value); //System.out.println("Value.length: " + value.length()); //System.out.println("SQL DATATPYE: " + javaSqlDataType); //System.out.println("For loop I: " + i); if (valueLength <= 4000) { try { // Needed for Sybase and maybe others pstmt.setObject(i, value, javaSqlDataType); } catch (Exception e) { // Needed for Oracle and maybe others pstmt.setObject(i, value); } } else { try { try { // Needed for Sybase and maybe others pstmt.setObject(i, value, javaSqlDataType); } catch (Exception e) { // Needed for Oracle and maybe others pstmt.setObject(i, value); } } catch (SQLException sqle) { // For Oracle and maybe others pstmt.setCharacterStream(i, new StringReader(value), valueLength); } } } } pstmt.executeUpdate(); } catch (SQLException sqle) { logger.error("Error in DbLoader.DataHandler.executeSQL()", sqle); logger.error("Error in DbLoader.DataHandler.executeSQL(): " + preparedStatement); } catch (Exception e) { logger.error("Error in DbLoader.DataHandler.executeSQL()", e); } finally { try { pstmt.close(); } catch (Exception e) { } } } else { // If prepared statements aren't supported, try a normal sql statement String statement = ""; if (action.equals("delete")) statement = prepareDeleteStatement(row, false); else if (action.equals("modify")) statement = prepareUpdateStatement(row, false); else if (action.equals("insert")) statement = prepareInsertStatement(row, false); //System.out.println(statement); try { stmt = con.createStatement(); stmt.executeUpdate(statement); } catch (Exception e) { logger.error("Error in DbLoader.DataHandler.executeSQL()", e); logger.error("Error in DbLoader.DataHandler.executeSQL(): " + statement); } finally { try { stmt.close(); } catch (Exception e) { } } } } private boolean supportsPreparedStatements() { boolean supportsPreparedStatements = true; try { // Issue a prepared statement to see if database/driver accepts them. // The assumption is that if a SQLException is thrown, it doesn't support them. // I don't know of any other way to check if the database/driver accepts // prepared statements. If you do, please change this method! Statement stmt; stmt = con.createStatement(); try { stmt.executeUpdate("CREATE TABLE PREP_TEST (A VARCHAR(1))"); } catch (Exception e) {/* Assume it already exists */ } finally { try { stmt.close(); } catch (Exception e) { } } pstmt = con.prepareStatement("SELECT A FROM PREP_TEST WHERE A=?"); pstmt.clearParameters(); pstmt.setString(1, "D"); ResultSet rs = pstmt.executeQuery(); rs.close(); } catch (SQLException sqle) { supportsPreparedStatements = false; logger.error("Error in DbLoader.DataHandler.supportsPreparedStatements()", sqle); } finally { try { stmt = con.createStatement(); stmt.executeUpdate("DROP TABLE PREP_TEST"); } catch (Exception e) {/* Assume it already exists */ } finally { try { stmt.close(); } catch (Exception e) { } } try { pstmt.close(); } catch (Exception e) { } } return supportsPreparedStatements; } class Table { private String name; public String getName() { return name; } public void setName(String name) { this.name = name; } } class Row { ArrayList columns = new ArrayList(); public ArrayList getColumns() { return columns; } public void addColumn(Column column) { columns.add(column); } } class Column { private String name; private String value; private String type; public String getName() { return name; } public String getValue() { return value; } public String getType() { return type; } public void setName(String name) { this.name = name; } public void setValue(String value) { this.value = value; } public void setType(String type) { this.type = type; } } } public Connection getCon() { return con; } public void setCon(Connection con) { this.con = con; } public Statement getStmt() { return stmt; } public void setStmt(Statement stmt) { this.stmt = stmt; } public PreparedStatement getPstmt() { return pstmt; } public void setPstmt(PreparedStatement pstmt) { this.pstmt = pstmt; } public Document getTablesDoc() { return tablesDoc; } public void setTablesDoc(Document tablesDoc) { this.tablesDoc = tablesDoc; } public Document getTablesDocGeneric() { return tablesDocGeneric; } public void setTablesDocGeneric(Document tablesDocGeneric) { this.tablesDocGeneric = tablesDocGeneric; } public boolean isCreateTableScript() { return createTableScript; } public void setCreateTableScript(boolean createTableScript) { this.createTableScript = createTableScript; } public boolean isPopulateTables() { return populateTables; } public void setPopulateTables(boolean populateTables) { this.populateTables = populateTables; } public PrintWriter getTableScriptOut() { return tableScriptOut; } public void setTableScriptOut(PrintWriter tableScriptOut) { this.tableScriptOut = tableScriptOut; } public boolean isDropTables() { return dropTables; } public void setDropTables(boolean dropTables) { this.dropTables = dropTables; } public boolean isCreateTables() { return createTables; } public void setCreateTables(boolean createTables) { this.createTables = createTables; } public String getDbName() { return dbName; } public void setDbName(String dbName) { this.dbName = dbName; } public String getDbVersion() { return dbVersion; } public void setDbVersion(String dbVersion) { this.dbVersion = dbVersion; } public String getDriverName() { return driverName; } public void setDriverName(String driverName) { this.driverName = driverName; } public String getDriverVersion() { return driverVersion; } public void setDriverVersion(String driverVersion) { this.driverVersion = driverVersion; } public boolean isAlterTables() { return alterTables; } public void setAlterTables(boolean alterTables) { this.alterTables = alterTables; } public boolean isIndexTables() { return indexTables; } public void setIndexTables(boolean indexTables) { this.indexTables = indexTables; } public Hashtable getTableColumnTypes() { return tableColumnTypes; } public void setTableColumnTypes(Hashtable tableColumnTypes) { this.tableColumnTypes = tableColumnTypes; } public PropertiesHandler getPropertiesHandler() { return propertiesHandler; } public void setPropertiesHandler(PropertiesHandler propertiesHandler) { this.propertiesHandler = propertiesHandler; } }