0001: /**********************************************************************************
0002: * $URL: https://source.sakaiproject.org/svn/osp/tags/sakai_2-4-1/warehouse/api-impl/src/java/org/theospi/portfolio/util/db/DbLoader.java $
0003: * $Id: DbLoader.java 29129 2007-04-18 23:15:10Z ajpoland@iupui.edu $
0004: ***********************************************************************************
0005: *
0006: * Copyright (c) 2005, 2006 The Sakai Foundation.
0007: *
0008: * Licensed under the Educational Community License, Version 1.0 (the "License");
0009: * you may not use this file except in compliance with the License.
0010: * You may obtain a copy of the License at
0011: *
0012: * http://www.opensource.org/licenses/ecl1.php
0013: *
0014: * Unless required by applicable law or agreed to in writing, software
0015: * distributed under the License is distributed on an "AS IS" BASIS,
0016: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: * See the License for the specific language governing permissions and
0018: * limitations under the License.
0019: *
0020: **********************************************************************************/package org.theospi.portfolio.util.db;
0021:
0022: import java.io.BufferedWriter;
0023: import java.io.File;
0024: import java.io.FileWriter;
0025: import java.io.IOException;
0026: import java.io.InputStream;
0027: import java.io.PrintWriter;
0028: import java.io.StringReader;
0029: import java.sql.Connection;
0030: import java.sql.DatabaseMetaData;
0031: import java.sql.PreparedStatement;
0032: import java.sql.ResultSet;
0033: import java.sql.SQLException;
0034: import java.sql.Statement;
0035: import java.sql.Types;
0036: import java.util.ArrayList;
0037: import java.util.Enumeration;
0038: import java.util.Hashtable;
0039: import java.util.Iterator;
0040:
0041: import javax.xml.parsers.DocumentBuilder;
0042: import javax.xml.parsers.DocumentBuilderFactory;
0043: import javax.xml.parsers.ParserConfigurationException;
0044: import javax.xml.parsers.SAXParserFactory;
0045: import javax.xml.transform.Result;
0046: import javax.xml.transform.Source;
0047: import javax.xml.transform.Transformer;
0048: import javax.xml.transform.TransformerException;
0049: import javax.xml.transform.TransformerFactory;
0050: import javax.xml.transform.dom.DOMSource;
0051: import javax.xml.transform.sax.SAXResult;
0052: import javax.xml.transform.stream.StreamSource;
0053:
0054: import org.apache.commons.logging.Log;
0055: import org.apache.commons.logging.LogFactory;
0056: import org.sakaiproject.component.cover.ServerConfigurationService;
0057: import org.w3c.dom.Document;
0058: import org.w3c.dom.Element;
0059: import org.w3c.dom.Node;
0060: import org.w3c.dom.NodeList;
0061: import org.w3c.dom.Text;
0062: import org.xml.sax.Attributes;
0063: import org.xml.sax.InputSource;
0064: import org.xml.sax.SAXException;
0065: import org.xml.sax.XMLReader;
0066: import org.xml.sax.helpers.DefaultHandler;
0067:
0068: /**
0069: * <p>A tool to set up a OSPI database. This tool was created so that OSPI
0070: * developers would only have to maintain a single set of xml documents to define
0071: * the OSPI database schema and data. Previously it was necessary to maintain
0072: * different scripts for each database we wanted to support.</p>
0073: *
0074: * <p>DbLoader reads the generic types that are specified in tables.xml and
0075: * tries to map them to local types by querying the database metadata via methods
0076: * implemented by the JDBC driver. Fallback mappings can be supplied in
0077: * dbloader.xml for cases where the JDBC driver is not able to determine the
0078: * appropriate mapping. Such cases will be reported to standard out.</p>
0079: *
0080: * <p>An xsl transformation is used to produce the DROP TABLE and CREATE TABLE
0081: * SQL statements. These statements can be altered by modifying tables.xsl</p>
0082: *
0083: * <p> all table names should have lower case names</p>
0084: *
0085: * <p>Generic data types (as defined in java.sql.Types) which may be specified
0086: * in tables.xml include:
0087: * <code>BIT, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE,
0088: * NUMERIC, DECIMAL, CHAR, VARCHAR, LONGVARCHAR, DATE, TIME, TIMESTAMP,
0089: * BINARY, VARBINARY, LONGVARBINARY, NULL, OTHER, JAVA_OBJECT, DISTINCT,
0090: * STRUCT, ARRAY, BLOB, CLOB, REF</code>
0091: *
0092: * <p><strong>WARNING: YOU MAY WANT TO MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING DbLoader</strong></p>
0093: *
0094: * <p>DbLoader will perform the following steps:
0095: * <ol>
0096: * <li>Read configurable properties from dbloader.xml</li>
0097: * <li>Get database connection from DbService</li>
0098: * <li>Read tables.xml and issue corresponding DROP TABLE and CREATE TABLE SQL statements.</li>
0099: * <li>Read data.xml and issue corresponding INSERT/UPDATE/DELETE SQL statements.</li>
0100: * </ol>
0101: * </p>
0102: *
0103: * @author <a href="kweiner@interactivebusiness.com">Ken Weiner</a>, kweiner@interactivebusiness.com
0104: * @author modified and adapted to OSPI by <a href="felipeen@udel.edu">Luis F.C. Mendes</a> - University of Delaware
0105: * @version $Revision: 7153 $
0106: * @see java.sql.Types
0107: */
0108: public class DbLoader {
0109:
0110: protected final Log logger = LogFactory.getLog(getClass());
0111:
0112: private Connection con;
0113: private Statement stmt;
0114: private PreparedStatement pstmt;
0115: private Document tablesDoc;
0116: private Document tablesDocGeneric;
0117: private boolean createTableScript;
0118: private boolean populateTables;
0119: private PrintWriter tableScriptOut;
0120: private boolean dropTables;
0121: private boolean createTables;
0122: private String dbName;
0123: private String dbVersion;
0124: private String driverName;
0125: private String driverVersion;
0126: // Added version 1.8/1.6.2.4
0127: private boolean alterTables;
0128: private boolean indexTables;
0129: private Hashtable tableColumnTypes = new Hashtable();
0130: private PropertiesHandler propertiesHandler;
0131:
0132: public DbLoader(Connection con) {
0133: this .con = con;
0134: }
0135:
0136: public void runLoader(InputStream tables) {
0137: try {
0138:
0139: // Read in the properties
0140: XMLReader parser = getXMLReader();
0141: readProperties(parser, getClass().getResourceAsStream(
0142: "dbloader.xml"));
0143:
0144: //override default properties
0145: propertiesHandler.properties
0146: .setDropTables(ServerConfigurationService
0147: .getString(
0148: "osp.datawarehouse.dbLoader.properties.dropTables",
0149: propertiesHandler.properties
0150: .getDropTables()));
0151: propertiesHandler.properties
0152: .setCreateTables(ServerConfigurationService
0153: .getString(
0154: "osp.datawarehouse.dbLoader.properties.createTables",
0155: propertiesHandler.properties
0156: .getCreateTables()));
0157: propertiesHandler.properties
0158: .setAlterTables(ServerConfigurationService
0159: .getString(
0160: "osp.datawarehouse.dbLoader.properties.alterTables",
0161: propertiesHandler.properties
0162: .getAlterTables()));
0163: propertiesHandler.properties
0164: .setIndexTables(ServerConfigurationService
0165: .getString(
0166: "osp.datawarehouse.dbLoader.properties.indexTables",
0167: propertiesHandler.properties
0168: .getIndexTables()));
0169: propertiesHandler.properties
0170: .setPopulateTables(ServerConfigurationService
0171: .getString(
0172: "osp.datawarehouse.dbLoader.properties.populateTables",
0173: propertiesHandler.properties
0174: .getPopulateTables()));
0175: propertiesHandler.properties
0176: .setCreateTableScript(ServerConfigurationService
0177: .getString(
0178: "osp.datawarehouse.dbLoader.properties.createTableScript",
0179: propertiesHandler.properties
0180: .getCreateTableScript()));
0181:
0182: propertiesHandler.properties
0183: .setTableScriptFileName(ServerConfigurationService
0184: .getString(
0185: "osp.datawarehouse.dbLoader.properties.tableScriptFileName",
0186: propertiesHandler.properties
0187: .getTableScriptFileName()));
0188:
0189: //print db info
0190: printInfo();
0191:
0192: // Read drop/create/populate table settings
0193: dropTables = Boolean.valueOf(
0194: propertiesHandler.properties.getDropTables())
0195: .booleanValue();
0196: createTables = Boolean.valueOf(
0197: propertiesHandler.properties.getCreateTables())
0198: .booleanValue();
0199: populateTables = Boolean.valueOf(
0200: propertiesHandler.properties.getPopulateTables())
0201: .booleanValue();
0202: alterTables = Boolean.valueOf(
0203: propertiesHandler.properties.getAlterTables())
0204: .booleanValue();
0205: indexTables = Boolean.valueOf(
0206: propertiesHandler.properties.getIndexTables())
0207: .booleanValue();
0208:
0209: // Set up script
0210: createTableScript = Boolean
0211: .valueOf(
0212: propertiesHandler.properties
0213: .getCreateTableScript())
0214: .booleanValue();
0215:
0216: if (createTableScript)
0217: initTableScript();
0218:
0219: // read command line arguements to override properties in dbloader.xml
0220:
0221: boolean usetable = false;
0222: boolean usedata = false;
0223:
0224: // okay, start processing
0225:
0226: try {
0227: // Read tables.xml
0228: DocumentBuilderFactory dbf = DocumentBuilderFactory
0229: .newInstance();
0230: DocumentBuilder domParser = dbf.newDocumentBuilder();
0231:
0232: // Eventually, write and validate against a DTD
0233: //domParser.setFeature ("http://xml.org/sax/features/validation", true);
0234: //domParser.setEntityResolver(new DTDResolver("tables.dtd"));
0235: //tablesURL = DbLoader.class.getResource(PropertiesHandler.properties.getTablesUri());
0236: tablesDoc = domParser.parse(new InputSource(tables));
0237: } catch (ParserConfigurationException pce) {
0238: throw new RuntimeException(pce);
0239: } catch (Exception e) {
0240: throw new RuntimeException(e);
0241: }
0242:
0243: // Hold on to tables xml with generic types
0244: tablesDocGeneric = (Document) tablesDoc.cloneNode(true);
0245:
0246: // Replace all generic data types with local data types
0247: replaceDataTypes(tablesDoc);
0248:
0249: // tables.xml + tables.xsl --> DROP TABLE and CREATE TABLE sql statements
0250:
0251: try {
0252: Result xmlResult = new SAXResult(TableHandlerFactory
0253: .getTableHandler(this ));
0254: Source xmlSource = new DOMSource(tablesDoc);
0255: TransformerFactory tFactory = TransformerFactory
0256: .newInstance();
0257: Transformer transformer = tFactory
0258: .newTransformer(new StreamSource(getClass()
0259: .getResourceAsStream("tables.xsl")));
0260: transformer.transform(xmlSource, xmlResult);
0261: //transformer.transform(xmlSource, new StreamResult(new FileOutputStream("tables.out")));
0262: } catch (TransformerException te) {
0263: throw new RuntimeException(te);
0264: }
0265:
0266: } catch (Exception e) {
0267: throw new RuntimeException(e);
0268: }
0269: }
0270:
0271: protected XMLReader getXMLReader() throws SAXException,
0272: ParserConfigurationException {
0273: SAXParserFactory spf = SAXParserFactory.newInstance();
0274: return spf.newSAXParser().getXMLReader();
0275: }
0276:
0277: protected void printInfo() throws SQLException {
0278: DatabaseMetaData dbMetaData = con.getMetaData();
0279: dbName = dbMetaData.getDatabaseProductName();
0280: dbVersion = dbMetaData.getDatabaseProductVersion();
0281: driverName = dbMetaData.getDriverName();
0282: driverVersion = dbMetaData.getDriverVersion();
0283:
0284: logger.debug("Starting DbLoader...");
0285: logger.debug("Database name: '" + dbName + "'");
0286: logger.debug("Database version: '" + dbVersion + "'");
0287: logger.debug("Driver name: '" + driverName + "'");
0288: logger.debug("Driver version: '" + driverVersion + "'");
0289: logger.debug("Database url: '" + dbMetaData.getURL() + "'");
0290: }
0291:
0292: protected void initTableScript() throws java.io.IOException {
0293: String scriptFileName = System.getProperty("sakai.home")
0294: + propertiesHandler.properties.getTableScriptFileName();
0295: //String scriptFileName = System.getProperty("sakai.home") +propertiesHandler.properties.getScriptFileName() + "." + System.currentTimeMillis();
0296: //String scriptFileName = propertiesHandler.properties.getScriptFileName() + "." + System.currentTimeMillis();
0297: //File scriptFile = new File(scriptFileName);
0298: //if (scriptFile.exists())
0299: // scriptFile.delete();
0300: //scriptFile.createNewFile();
0301: //if (!scriptFile.exists())
0302: // scriptFile.createNewFile();
0303: String initProperty = "osp.dw.initializedTables";
0304: String inited = System.getProperty(initProperty);
0305: if (inited == null) {
0306: System.getProperties().setProperty(initProperty, "true");
0307: File scriptFile = new File(scriptFileName);
0308: if (scriptFile.exists())
0309: scriptFile.delete();
0310: }
0311: tableScriptOut = new PrintWriter(new BufferedWriter(
0312: new FileWriter(scriptFileName, true)), true);
0313: }
0314:
0315: protected void replaceDataTypes(Document tablesDoc) {
0316: Element tables = tablesDoc.getDocumentElement();
0317: NodeList types = tables.getElementsByTagName("type");
0318:
0319: for (int i = 0; i < types.getLength(); i++) {
0320: Node type = (Node) types.item(i);
0321: NodeList typeChildren = type.getChildNodes();
0322:
0323: for (int j = 0; j < typeChildren.getLength(); j++) {
0324: Node text = (Node) typeChildren.item(j);
0325: String genericType = text.getNodeValue();
0326:
0327: // Replace generic type with mapped local type
0328: text.setNodeValue(getLocalDataTypeName(genericType));
0329: }
0330: }
0331: }
0332:
0333: protected int getJavaSqlDataTypeOfColumn(Document tablesDocGeneric,
0334: String tableName, String columnName) {
0335: int dataType = 0;
0336: String hashKey = tableName + File.separator + columnName;
0337:
0338: // try to use cached version first
0339: if (tableColumnTypes.get(hashKey) != null)
0340: return ((Integer) tableColumnTypes.get(hashKey)).intValue();
0341:
0342: // Find the right table element
0343: Element table = getTableWithName(tablesDocGeneric, tableName);
0344:
0345: // Find the columns element within
0346: Element columns = getFirstChildWithName(table, "columns");
0347:
0348: // Search for the first column who's name is columnName
0349: for (Node ch = columns.getFirstChild(); ch != null; ch = ch
0350: .getNextSibling()) {
0351: if (ch instanceof Element
0352: && ch.getNodeName().equals("column")) {
0353: Element name = getFirstChildWithName((Element) ch,
0354: "name");
0355: if (getNodeValue(name).equals(columnName)) {
0356: // Get the corresponding type and return it's type code
0357: Element value = getFirstChildWithName((Element) ch,
0358: "type");
0359: dataType = getJavaSqlType(getNodeValue(value));
0360: }
0361: }
0362: }
0363:
0364: // store value is hashtable for next call to this method, prevents
0365: // repeating xml parsing which takes a very long time
0366: tableColumnTypes.put(hashKey, new Integer(dataType));
0367:
0368: return dataType;
0369: }
0370:
0371: protected Element getFirstChildWithName(Element parent, String name) {
0372: Element child = null;
0373: for (Node ch = parent.getFirstChild(); ch != null; ch = ch
0374: .getNextSibling()) {
0375: if (ch instanceof Element && ch.getNodeName().equals(name)) {
0376: child = (Element) ch;
0377: break;
0378: }
0379: }
0380:
0381: return child;
0382: }
0383:
0384: protected Element getTableWithName(Document tablesDoc,
0385: String tableName) {
0386: Element tableElement = null;
0387: NodeList tables = tablesDoc.getElementsByTagName("table");
0388:
0389: for (int i = 0; i < tables.getLength(); i++) {
0390: Node table = (Node) tables.item(i);
0391:
0392: for (Node tableChild = table.getFirstChild(); tableChild != null; tableChild = tableChild
0393: .getNextSibling()) {
0394: if (tableChild instanceof Element
0395: && tableChild.getNodeName() != null
0396: && tableChild.getNodeName().equals("name")) {
0397: if (tableName.equals(getNodeValue(tableChild))) {
0398: tableElement = (Element) table;
0399: break;
0400: }
0401: }
0402: }
0403: }
0404:
0405: return tableElement;
0406: }
0407:
0408: protected String getNodeValue(Node node) {
0409: String nodeVal = null;
0410:
0411: for (Node ch = node.getFirstChild(); ch != null; ch = ch
0412: .getNextSibling()) {
0413: if (ch instanceof Text)
0414: nodeVal = ch.getNodeValue();
0415: }
0416:
0417: return nodeVal;
0418: }
0419:
0420: protected String getLocalDataTypeName(String genericDataTypeName) {
0421:
0422: String localDataTypeName = null;
0423:
0424: try {
0425: DatabaseMetaData dbmd = con.getMetaData();
0426: String dbName = dbmd.getDatabaseProductName();
0427: String dbVersion = dbmd.getDatabaseProductVersion();
0428: String driverName = dbmd.getDriverName();
0429: String driverVersion = dbmd.getDriverVersion();
0430:
0431: // Check for a mapping in DbLoader.xml
0432: localDataTypeName = propertiesHandler.properties
0433: .getMappedDataTypeName(dbName, dbVersion,
0434: driverName, driverVersion,
0435: genericDataTypeName);
0436:
0437: if (localDataTypeName != null)
0438: return localDataTypeName;
0439:
0440: // Find the type code for this generic type name
0441: int dataTypeCode = getJavaSqlType(genericDataTypeName);
0442:
0443: // Find the first local type name matching the type code
0444: ResultSet rs = dbmd.getTypeInfo();
0445: try {
0446: while (rs.next()) {
0447: int localDataTypeCode = rs.getInt("DATA_TYPE");
0448:
0449: if (dataTypeCode == localDataTypeCode) {
0450: try {
0451: localDataTypeName = rs
0452: .getString("TYPE_NAME");
0453: } catch (SQLException sqle) {
0454: }
0455: break;
0456: }
0457: }
0458: } finally {
0459: rs.close();
0460: }
0461:
0462: if (localDataTypeName != null)
0463: return localDataTypeName;
0464:
0465: // No matching type found, report an error
0466: logger.error("Error in DbLoader.getLocalDataTypeName()");
0467: logger
0468: .error("Your database driver, '"
0469: + driverName
0470: + "', version '"
0471: + driverVersion
0472: + "', was unable to find a local type name that matches the generic type name, '"
0473: + genericDataTypeName + "'.");
0474: logger
0475: .error("Please add a mapped type for database '"
0476: + dbName
0477: + "', version '"
0478: + dbVersion
0479: + "' inside your properties file and run this program again.");
0480: logger.error("Exiting...");
0481: } catch (Exception e) {
0482: logger.error("Error in DbLoader.getLocalDataTypeName()", e);
0483: }
0484:
0485: return null;
0486: }
0487:
0488: protected int getJavaSqlType(String genericDataTypeName) {
0489: // Find the type code for this generic type name
0490: int dataTypeCode = 0;
0491:
0492: if (genericDataTypeName.equalsIgnoreCase("BIT"))
0493: dataTypeCode = Types.BIT; // -7
0494: else if (genericDataTypeName.equalsIgnoreCase("TINYINT"))
0495: dataTypeCode = Types.TINYINT; // -6
0496: else if (genericDataTypeName.equalsIgnoreCase("SMALLINT"))
0497: dataTypeCode = Types.SMALLINT; // 5
0498: else if (genericDataTypeName.equalsIgnoreCase("INTEGER"))
0499: dataTypeCode = Types.INTEGER; // 4
0500: else if (genericDataTypeName.equalsIgnoreCase("BIGINT"))
0501: dataTypeCode = Types.BIGINT; // -5
0502: else if (genericDataTypeName.equalsIgnoreCase("FLOAT"))
0503: dataTypeCode = Types.FLOAT; // 6
0504: else if (genericDataTypeName.equalsIgnoreCase("REAL"))
0505: dataTypeCode = Types.REAL; // 7
0506: else if (genericDataTypeName.equalsIgnoreCase("DOUBLE"))
0507: dataTypeCode = Types.DOUBLE; // 8
0508: else if (genericDataTypeName.equalsIgnoreCase("NUMERIC"))
0509: dataTypeCode = Types.NUMERIC; // 2
0510: else if (genericDataTypeName.equalsIgnoreCase("DECIMAL"))
0511: dataTypeCode = Types.DECIMAL; // 3
0512:
0513: else if (genericDataTypeName.equalsIgnoreCase("CHAR"))
0514: dataTypeCode = Types.CHAR; // 1
0515: else if (genericDataTypeName.equalsIgnoreCase("VARCHAR"))
0516: dataTypeCode = Types.VARCHAR; // 12
0517: else if (genericDataTypeName.equalsIgnoreCase("LONGVARCHAR"))
0518: dataTypeCode = Types.LONGVARCHAR; // -1
0519:
0520: else if (genericDataTypeName.equalsIgnoreCase("DATE"))
0521: dataTypeCode = Types.DATE; // 91
0522: else if (genericDataTypeName.equalsIgnoreCase("TIME"))
0523: dataTypeCode = Types.TIME; // 92
0524: else if (genericDataTypeName.equalsIgnoreCase("TIMESTAMP"))
0525: dataTypeCode = Types.TIMESTAMP; // 93
0526:
0527: else if (genericDataTypeName.equalsIgnoreCase("BINARY"))
0528: dataTypeCode = Types.BINARY; // -2
0529: else if (genericDataTypeName.equalsIgnoreCase("VARBINARY"))
0530: dataTypeCode = Types.VARBINARY; // -3
0531: else if (genericDataTypeName.equalsIgnoreCase("LONGVARBINARY"))
0532: dataTypeCode = Types.LONGVARBINARY; // -4
0533:
0534: else if (genericDataTypeName.equalsIgnoreCase("NULL"))
0535: dataTypeCode = Types.NULL; // 0
0536:
0537: else if (genericDataTypeName.equalsIgnoreCase("OTHER"))
0538: dataTypeCode = Types.OTHER; // 1111
0539:
0540: else if (genericDataTypeName.equalsIgnoreCase("JAVA_OBJECT"))
0541: dataTypeCode = Types.JAVA_OBJECT; // 2000
0542: else if (genericDataTypeName.equalsIgnoreCase("DISTINCT"))
0543: dataTypeCode = Types.DISTINCT; // 2001
0544: else if (genericDataTypeName.equalsIgnoreCase("STRUCT"))
0545: dataTypeCode = Types.STRUCT; // 2002
0546:
0547: else if (genericDataTypeName.equalsIgnoreCase("ARRAY"))
0548: dataTypeCode = Types.ARRAY; // 2003
0549: else if (genericDataTypeName.equalsIgnoreCase("BLOB"))
0550: dataTypeCode = Types.BLOB; // 2004
0551: else if (genericDataTypeName.equalsIgnoreCase("CLOB"))
0552: dataTypeCode = Types.CLOB; // 2005
0553: else if (genericDataTypeName.equalsIgnoreCase("REF"))
0554: dataTypeCode = Types.REF; // 2006
0555:
0556: return dataTypeCode;
0557: }
0558:
0559: protected void dropTable(String dropTableStatement) {
0560: if (createTableScript)
0561: tableScriptOut.println(dropTableStatement
0562: + propertiesHandler.properties
0563: .getStatementTerminator());
0564: else {
0565: try {
0566: stmt = con.createStatement();
0567: try {
0568: stmt.executeUpdate(dropTableStatement);
0569: } catch (SQLException sqle) {/*Table didn't exist*/
0570: }
0571: } catch (Exception e) {
0572: logger.error("Error in DbLoader.dropTable()", e);
0573: } finally {
0574: try {
0575: stmt.close();
0576: } catch (Exception e) {
0577: }
0578: }
0579: }
0580: }
0581:
0582: protected void createTable(String createTableStatement) {
0583: if (createTableScript)
0584: tableScriptOut.println(createTableStatement
0585: + propertiesHandler.properties
0586: .getStatementTerminator());
0587: else {
0588: try {
0589: stmt = con.createStatement();
0590: stmt.executeUpdate(createTableStatement);
0591: } catch (Exception e) {
0592: logger.error("error creating table with this sql: "
0593: + createTableStatement);
0594: logger.error("", e);
0595: } finally {
0596: try {
0597: stmt.close();
0598: } catch (Exception e) {
0599: }
0600: }
0601: }
0602: }
0603:
0604: protected void alterTable(String alterTableStatement) {
0605: if (createTableScript)
0606: tableScriptOut.println(alterTableStatement
0607: + propertiesHandler.properties
0608: .getStatementTerminator());
0609: else {
0610: try {
0611: stmt = con.createStatement();
0612: stmt.executeUpdate(alterTableStatement);
0613: } catch (Exception e) {
0614: logger.error("error altering table with this sql: "
0615: + alterTableStatement);
0616: logger.error("", e);
0617: } finally {
0618: try {
0619: stmt.close();
0620: } catch (Exception e) {
0621: }
0622: }
0623: }
0624: }
0625:
0626: protected void indexTable(String indexTableStatement) {
0627: if (createTableScript)
0628: tableScriptOut.println(indexTableStatement
0629: + propertiesHandler.properties
0630: .getStatementTerminator());
0631: else {
0632: try {
0633: stmt = con.createStatement();
0634: stmt.executeUpdate(indexTableStatement);
0635: } catch (Exception e) {
0636: logger.error("error indexing table with this sql: "
0637: + indexTableStatement);
0638: logger.error("", e);
0639: } finally {
0640: try {
0641: stmt.close();
0642: } catch (Exception e) {
0643: }
0644: }
0645: }
0646: }
0647:
0648: protected void readProperties(XMLReader parser,
0649: InputStream properties) throws SAXException, IOException {
0650: propertiesHandler = new PropertiesHandler();
0651: parser.setContentHandler(propertiesHandler);
0652: parser.setErrorHandler(propertiesHandler);
0653: parser.parse(new InputSource(properties));
0654: }
0655:
0656: private class PropertiesHandler extends DefaultHandler {
0657: private StringBuffer charBuff = null;
0658:
0659: private Properties properties;
0660: private DbTypeMapping dbTypeMapping;
0661: private Type type;
0662:
0663: public void startDocument() {
0664: }
0665:
0666: public void endDocument() {
0667: }
0668:
0669: public void startElement(String namespaceURI, String localName,
0670: String qName, Attributes atts) {
0671: charBuff = new StringBuffer();
0672:
0673: if (qName.equals("properties"))
0674: properties = new Properties();
0675: else if (qName.equals("db-type-mapping"))
0676: dbTypeMapping = new DbTypeMapping();
0677: else if (qName.equals("type"))
0678: type = new Type();
0679: }
0680:
0681: public void endElement(String namespaceURI, String localName,
0682: String qName) {
0683: if (qName.equals("drop-tables")) // drop tables ("true" or "false")
0684: properties.setDropTables(charBuff.toString());
0685: else if (qName.equals("create-tables")) // create tables ("true" or "false")
0686: properties.setCreateTables(charBuff.toString());
0687: else if (qName.equals("populate-tables")) // populate tables ("true" or "false")
0688: properties.setPopulateTables(charBuff.toString());
0689: else if (qName.equals("create-table-script")) // create table script ("true" or "false")
0690: properties.setCreateTableScript(charBuff.toString());
0691: else if (qName.equals("table-script-file-name")) // script file name
0692: properties.setTableScriptFileName(charBuff.toString());
0693: else if (qName.equals("statement-terminator")) // statement terminator
0694: properties.setStatementTerminator(charBuff.toString());
0695: else if (qName.equals("db-type-mapping"))
0696: properties.addDbTypeMapping(dbTypeMapping);
0697: else if (qName.equals("db-name")) // database name
0698: dbTypeMapping.setDbName(charBuff.toString());
0699: else if (qName.equals("db-version")) // database version
0700: dbTypeMapping.setDbVersion(charBuff.toString());
0701: else if (qName.equals("driver-name")) // driver name
0702: dbTypeMapping.setDriverName(charBuff.toString());
0703: else if (qName.equals("driver-version")) // driver version
0704: dbTypeMapping.setDriverVersion(charBuff.toString());
0705: else if (qName.equals("type"))
0706: dbTypeMapping.addType(type);
0707: else if (qName.equals("generic")) // generic type
0708: type.setGeneric(charBuff.toString());
0709: else if (qName.equals("local")) // local type
0710: type.setLocal(charBuff.toString());
0711: else if (qName.equals("alter-tables")) // alter tables ("true" or "false")
0712: properties.setAlterTables(charBuff.toString());
0713: else if (qName.equals("index-tables")) // index tables ("true" or "false")
0714: properties.setIndexTables(charBuff.toString());
0715: }
0716:
0717: public void characters(char ch[], int start, int length) {
0718: charBuff.append(ch, start, length);
0719: }
0720:
0721: class Properties {
0722: private String dropTables;
0723: private String createTables;
0724: private String populateTables;
0725: private String createTableScript;
0726: private String tableScriptFileName;
0727: private String statementTerminator;
0728: private ArrayList dbTypeMappings = new ArrayList();
0729:
0730: private String alterTables;
0731: private String indexTables;
0732:
0733: public String getDropTables() {
0734: return dropTables;
0735: }
0736:
0737: public String getCreateTables() {
0738: return createTables;
0739: }
0740:
0741: public String getPopulateTables() {
0742: return populateTables;
0743: }
0744:
0745: public String getCreateTableScript() {
0746: return createTableScript;
0747: }
0748:
0749: public String getTableScriptFileName() {
0750: return tableScriptFileName;
0751: }
0752:
0753: public String getStatementTerminator() {
0754: return statementTerminator;
0755: }
0756:
0757: public ArrayList getDbTypeMappings() {
0758: return dbTypeMappings;
0759: }
0760:
0761: public void setDropTables(String dropTables) {
0762: this .dropTables = dropTables;
0763: }
0764:
0765: public void setCreateTables(String createTables) {
0766: this .createTables = createTables;
0767: }
0768:
0769: public void setPopulateTables(String populateTables) {
0770: this .populateTables = populateTables;
0771: }
0772:
0773: public void setCreateTableScript(String createTableScript) {
0774: this .createTableScript = createTableScript;
0775: }
0776:
0777: public void setTableScriptFileName(
0778: String tableScriptFileName) {
0779: this .tableScriptFileName = tableScriptFileName;
0780: }
0781:
0782: public void setStatementTerminator(
0783: String statementTerminator) {
0784: this .statementTerminator = statementTerminator;
0785: }
0786:
0787: public void addDbTypeMapping(DbTypeMapping dbTypeMapping) {
0788: dbTypeMappings.add(dbTypeMapping);
0789: }
0790:
0791: public String getAlterTables() {
0792: return alterTables;
0793: }
0794:
0795: public void setAlterTables(String alterTables) {
0796: this .alterTables = alterTables;
0797: }
0798:
0799: public String getIndexTables() {
0800: return indexTables;
0801: }
0802:
0803: public void setIndexTables(String indexTables) {
0804: this .indexTables = indexTables;
0805: }
0806:
0807: public String getMappedDataTypeName(String dbName,
0808: String dbVersion, String driverName,
0809: String driverVersion, String genericDataTypeName) {
0810: String mappedDataTypeName = null;
0811: Iterator iterator = dbTypeMappings.iterator();
0812:
0813: while (iterator.hasNext()) {
0814: DbTypeMapping dbTypeMapping = (DbTypeMapping) iterator
0815: .next();
0816: String dbNameProp = dbTypeMapping.getDbName();
0817: String dbVersionProp = dbTypeMapping.getDbVersion();
0818: String driverNameProp = dbTypeMapping
0819: .getDriverName();
0820: String driverVersionProp = dbTypeMapping
0821: .getDriverVersion();
0822:
0823: if (dbNameProp.equalsIgnoreCase(dbName)
0824: && dbVersionProp
0825: .equalsIgnoreCase(dbVersion)
0826: && driverNameProp
0827: .equalsIgnoreCase(driverName)
0828: && driverVersionProp
0829: .equalsIgnoreCase(driverVersion)) {
0830: // Found a matching database/driver combination
0831: mappedDataTypeName = dbTypeMapping
0832: .getMappedDataTypeName(genericDataTypeName);
0833: }
0834: }
0835: return mappedDataTypeName;
0836: }
0837:
0838: }
0839:
0840: class DbTypeMapping {
0841: String dbName;
0842: String dbVersion;
0843: String driverName;
0844: String driverVersion;
0845: ArrayList types = new ArrayList();
0846:
0847: public String getDbName() {
0848: return dbName;
0849: }
0850:
0851: public String getDbVersion() {
0852: return dbVersion;
0853: }
0854:
0855: public String getDriverName() {
0856: return driverName;
0857: }
0858:
0859: public String getDriverVersion() {
0860: return driverVersion;
0861: }
0862:
0863: public ArrayList getTypes() {
0864: return types;
0865: }
0866:
0867: public void setDbName(String dbName) {
0868: this .dbName = dbName;
0869: }
0870:
0871: public void setDbVersion(String dbVersion) {
0872: this .dbVersion = dbVersion;
0873: }
0874:
0875: public void setDriverName(String driverName) {
0876: this .driverName = driverName;
0877: }
0878:
0879: public void setDriverVersion(String driverVersion) {
0880: this .driverVersion = driverVersion;
0881: }
0882:
0883: public void addType(Type type) {
0884: types.add(type);
0885: }
0886:
0887: public String getMappedDataTypeName(
0888: String genericDataTypeName) {
0889: String mappedDataTypeName = null;
0890: Iterator iterator = types.iterator();
0891:
0892: while (iterator.hasNext()) {
0893: Type type = (Type) iterator.next();
0894:
0895: if (type.getGeneric().equalsIgnoreCase(
0896: genericDataTypeName))
0897: mappedDataTypeName = type.getLocal();
0898: }
0899: return mappedDataTypeName;
0900: }
0901: }
0902:
0903: class Type {
0904: String genericType; // "generic" is a Java reserved word
0905: String local;
0906:
0907: public String getGeneric() {
0908: return genericType;
0909: }
0910:
0911: public String getLocal() {
0912: return local;
0913: }
0914:
0915: public void setGeneric(String genericType) {
0916: this .genericType = genericType;
0917: }
0918:
0919: public void setLocal(String local) {
0920: this .local = local;
0921: }
0922: }
0923: }
0924:
0925: class DataHandler extends DefaultHandler {
0926: protected StringBuffer charBuff = null;
0927:
0928: protected boolean insideData = false;
0929: private boolean insideTable = false;
0930: private boolean insideName = false;
0931: private boolean insideRow = false;
0932: private boolean insideColumn = false;
0933: private boolean insideValue = false;
0934: private boolean supportsPreparedStatements = false;
0935:
0936: Table table;
0937: Row row;
0938: Column column;
0939: String action; //determines sql function for a table row
0940: String type; //determines type of column
0941:
0942: public void startDocument() {
0943: logger.debug("Populating tables...");
0944:
0945: if (!populateTables)
0946: logger.debug("disabled.");
0947:
0948: supportsPreparedStatements = supportsPreparedStatements();
0949: }
0950:
0951: public void endDocument() {
0952: //logger.debug("");
0953: }
0954:
0955: public void startElement(String namespaceURI, String localName,
0956: String qName, Attributes atts) {
0957: charBuff = new StringBuffer();
0958:
0959: if (qName.equals("data"))
0960: insideData = true;
0961: else if (qName.equals("table")) {
0962: insideTable = true;
0963: table = new Table();
0964: action = atts.getValue("action");
0965: } else if (qName.equals("name"))
0966: insideName = true;
0967: else if (qName.equals("row")) {
0968: insideRow = true;
0969: row = new Row();
0970: } else if (qName.equals("column")) {
0971: insideColumn = true;
0972: column = new Column();
0973: type = atts.getValue("type");
0974: } else if (qName.equals("value"))
0975: insideValue = true;
0976: }
0977:
0978: public void endElement(String namespaceURI, String localName,
0979: String qName) {
0980: if (qName.equals("data"))
0981: insideData = false;
0982: else if (qName.equals("table"))
0983: insideTable = false;
0984: else if (qName.equals("name")) {
0985: insideName = false;
0986:
0987: if (!insideColumn) // table name
0988: table.setName(charBuff.toString().toLowerCase());
0989: else
0990: // column name
0991: column.setName(charBuff.toString());
0992: } else if (qName.equals("row")) {
0993: insideRow = false;
0994:
0995: if (action != null) {
0996: if (action.equals("delete"))
0997: executeSQL(table, row, "delete");
0998: else if (action.equals("modify"))
0999: executeSQL(table, row, "modify");
1000: else if (action.equals("add"))
1001: executeSQL(table, row, "insert");
1002: } else if (populateTables)
1003: executeSQL(table, row, "insert");
1004: } else if (qName.equals("column")) {
1005: insideColumn = false;
1006: if (type != null)
1007: column.setType(type);
1008: row.addColumn(column);
1009: } else if (qName.equals("value")) {
1010: insideValue = false;
1011:
1012: if (insideColumn) // column value
1013: column.setValue(charBuff.toString());
1014: }
1015: }
1016:
1017: public void characters(char ch[], int start, int length) {
1018: charBuff.append(ch, start, length);
1019: }
1020:
1021: private String prepareInsertStatement(Row row,
1022: boolean preparedStatement) {
1023: StringBuffer sb = new StringBuffer("INSERT INTO ");
1024: sb.append(table.getName()).append(" (");
1025:
1026: ArrayList columns = row.getColumns();
1027: Iterator iterator = columns.iterator();
1028:
1029: while (iterator.hasNext()) {
1030: Column column = (Column) iterator.next();
1031: sb.append(column.getName()).append(", ");
1032: }
1033:
1034: // Delete comma and space after last column name (kind of sloppy, but it works)
1035: sb.deleteCharAt(sb.length() - 1);
1036: sb.deleteCharAt(sb.length() - 1);
1037:
1038: sb.append(") VALUES (");
1039: iterator = columns.iterator();
1040:
1041: while (iterator.hasNext()) {
1042: Column column = (Column) iterator.next();
1043:
1044: if (preparedStatement)
1045: sb.append("?");
1046: else {
1047: String value = column.getValue();
1048:
1049: if (value != null) {
1050: if (value.equals("SYSDATE"))
1051: sb.append(value);
1052: else if (value.equals("NULL"))
1053: sb.append(value);
1054: else if (getJavaSqlDataTypeOfColumn(
1055: tablesDocGeneric, table.getName(),
1056: column.getName()) == Types.INTEGER)
1057: // this column is an integer, so don't put quotes (Sybase cares about this)
1058: sb.append(value);
1059: else {
1060: sb.append("'");
1061: sb.append(sqlEscape(value.trim()));
1062: sb.append("'");
1063: }
1064: } else
1065: sb.append("''");
1066: }
1067:
1068: sb.append(", ");
1069: }
1070:
1071: // Delete comma and space after last value (kind of sloppy, but it works)
1072: sb.deleteCharAt(sb.length() - 1);
1073: sb.deleteCharAt(sb.length() - 1);
1074:
1075: sb.append(")");
1076:
1077: return sb.toString();
1078: }
1079:
1080: private String prepareDeleteStatement(Row row,
1081: boolean preparedStatement) {
1082:
1083: StringBuffer sb = new StringBuffer("DELETE FROM ");
1084: sb.append(table.getName()).append(" WHERE ");
1085:
1086: ArrayList columns = row.getColumns();
1087: Iterator iterator = columns.iterator();
1088: Column column;
1089:
1090: while (iterator.hasNext()) {
1091: column = (Column) iterator.next();
1092: if (preparedStatement)
1093: sb.append(column.getName() + " = ? and ");
1094: else if (getJavaSqlDataTypeOfColumn(tablesDocGeneric,
1095: table.getName(), column.getName()) == Types.INTEGER)
1096: sb.append(column.getName() + " = "
1097: + sqlEscape(column.getValue().trim())
1098: + " and ");
1099: else
1100: sb.append(column.getName() + " = " + "'"
1101: + sqlEscape(column.getValue().trim())
1102: + "' and ");
1103: }
1104:
1105: sb.deleteCharAt(sb.length() - 1);
1106: sb.deleteCharAt(sb.length() - 1);
1107: sb.deleteCharAt(sb.length() - 1);
1108: sb.deleteCharAt(sb.length() - 1);
1109:
1110: if (!preparedStatement)
1111: sb.deleteCharAt(sb.length() - 1);
1112:
1113: return sb.toString();
1114:
1115: }
1116:
1117: private String prepareUpdateStatement(Row row,
1118: boolean preparedStatement) {
1119:
1120: StringBuffer sb = new StringBuffer("UPDATE ");
1121: sb.append(table.getName()).append(" SET ");
1122:
1123: ArrayList columns = row.getColumns();
1124: Iterator iterator = columns.iterator();
1125:
1126: Hashtable setPairs = new Hashtable();
1127: Hashtable wherePairs = new Hashtable();
1128: String type;
1129: Column column;
1130:
1131: while (iterator.hasNext()) {
1132: column = (Column) iterator.next();
1133: type = column.getType();
1134:
1135: if (type != null && type.equals("select")) {
1136: if (getJavaSqlDataTypeOfColumn(tablesDocGeneric,
1137: table.getName(), column.getName()) == Types.INTEGER)
1138: wherePairs.put(column.getName(), column
1139: .getValue().trim());
1140: else
1141: wherePairs.put(column.getName(), "'"
1142: + column.getValue().trim() + "'");
1143: } else {
1144: if (getJavaSqlDataTypeOfColumn(tablesDocGeneric,
1145: table.getName(), column.getName()) == Types.INTEGER)
1146: setPairs.put(column.getName(), column
1147: .getValue().trim());
1148: else
1149: setPairs.put(column.getName(), "'"
1150: + column.getValue().trim() + "'");
1151: }
1152: }
1153:
1154: String nm;
1155: String val;
1156:
1157: Enumeration sKeys = setPairs.keys();
1158: while (sKeys.hasMoreElements()) {
1159: nm = (String) sKeys.nextElement();
1160: val = (String) setPairs.get(nm);
1161: sb.append(nm + " = " + sqlEscape(val) + ", ");
1162: }
1163: sb.deleteCharAt(sb.length() - 1);
1164: sb.deleteCharAt(sb.length() - 1);
1165:
1166: sb.append(" WHERE ");
1167:
1168: Enumeration wKeys = wherePairs.keys();
1169: while (wKeys.hasMoreElements()) {
1170: nm = (String) wKeys.nextElement();
1171: val = (String) wherePairs.get(nm);
1172: sb.append(nm + "=" + sqlEscape(val) + " and ");
1173: }
1174: sb.deleteCharAt(sb.length() - 1);
1175: sb.deleteCharAt(sb.length() - 1);
1176: sb.deleteCharAt(sb.length() - 1);
1177: sb.deleteCharAt(sb.length() - 1);
1178: sb.deleteCharAt(sb.length() - 1);
1179:
1180: return sb.toString();
1181:
1182: }
1183:
1184: /**
1185: * Make a string SQL safe
1186: * @param sql the string that is not necessarily safe
1187: * @return SQL safe string
1188: */
1189: public final String sqlEscape(String sql) {
1190: if (sql == null) {
1191: return "";
1192: } else {
1193: int primePos = sql.indexOf("'");
1194: if (primePos == -1) {
1195: return sql;
1196: } else {
1197: StringBuffer sb = new StringBuffer(sql.length() + 4);
1198: int startPos = 0;
1199: do {
1200: sb
1201: .append(sql.substring(startPos,
1202: primePos + 1));
1203: sb.append("'");
1204: startPos = primePos + 1;
1205: primePos = sql.indexOf("'", startPos);
1206: } while (primePos != -1);
1207: sb.append(sql.substring(startPos));
1208: return sb.toString();
1209: }
1210: }
1211: }
1212:
1213: private void executeSQL(Table table, Row row, String action) {
1214: if (createTableScript) {
1215: if (action.equals("delete"))
1216: tableScriptOut.println(prepareDeleteStatement(row,
1217: false)
1218: + propertiesHandler.properties
1219: .getStatementTerminator());
1220: else if (action.equals("modify"))
1221: tableScriptOut.println(prepareUpdateStatement(row,
1222: false)
1223: + propertiesHandler.properties
1224: .getStatementTerminator());
1225: else if (action.equals("insert"))
1226: tableScriptOut.println(prepareInsertStatement(row,
1227: false)
1228: + propertiesHandler.properties
1229: .getStatementTerminator());
1230: }
1231:
1232: if (supportsPreparedStatements) {
1233: String preparedStatement = "";
1234: try {
1235: if (action.equals("delete"))
1236: preparedStatement = prepareDeleteStatement(row,
1237: true);
1238: else if (action.equals("modify"))
1239: preparedStatement = prepareUpdateStatement(row,
1240: true);
1241: else if (action.equals("insert"))
1242: preparedStatement = prepareInsertStatement(row,
1243: true);
1244: //System.out.println(preparedStatement);
1245: pstmt = con.prepareStatement(preparedStatement);
1246: pstmt.clearParameters();
1247:
1248: // Loop through parameters and set them, checking for any that excede 4k
1249: ArrayList columns = row.getColumns();
1250: Iterator iterator = columns.iterator();
1251:
1252: for (int i = 1; iterator.hasNext(); i++) {
1253: Column column = (Column) iterator.next();
1254: String value = column.getValue();
1255:
1256: // Get a java sql data type for column name
1257: int javaSqlDataType = getJavaSqlDataTypeOfColumn(
1258: tablesDocGeneric, table.getName(),
1259: column.getName());
1260: if (value == null
1261: || (value != null && value
1262: .equalsIgnoreCase("NULL")))
1263: pstmt.setNull(i, javaSqlDataType);
1264: else if (javaSqlDataType == Types.TIMESTAMP) {
1265: if (value.equals("SYSDATE"))
1266: pstmt.setTimestamp(i,
1267: new java.sql.Timestamp(System
1268: .currentTimeMillis()));
1269: else
1270: pstmt.setTimestamp(i,
1271: java.sql.Timestamp
1272: .valueOf(value));
1273: } else {
1274: value = value.trim(); // can't read xml properly without this, don't know why yet
1275: int valueLength = value.length();
1276: //System.out.println("Value: " + value);
1277: //System.out.println("Value.length: " + value.length());
1278: //System.out.println("SQL DATATPYE: " + javaSqlDataType);
1279: //System.out.println("For loop I: " + i);
1280: if (valueLength <= 4000) {
1281: try {
1282: // Needed for Sybase and maybe others
1283: pstmt.setObject(i, value,
1284: javaSqlDataType);
1285: } catch (Exception e) {
1286: // Needed for Oracle and maybe others
1287: pstmt.setObject(i, value);
1288: }
1289: } else {
1290: try {
1291: try {
1292: // Needed for Sybase and maybe others
1293: pstmt.setObject(i, value,
1294: javaSqlDataType);
1295: } catch (Exception e) {
1296: // Needed for Oracle and maybe others
1297: pstmt.setObject(i, value);
1298: }
1299: } catch (SQLException sqle) {
1300: // For Oracle and maybe others
1301: pstmt.setCharacterStream(i,
1302: new StringReader(value),
1303: valueLength);
1304: }
1305: }
1306: }
1307: }
1308: pstmt.executeUpdate();
1309: } catch (SQLException sqle) {
1310: logger
1311: .error(
1312: "Error in DbLoader.DataHandler.executeSQL()",
1313: sqle);
1314: logger
1315: .error("Error in DbLoader.DataHandler.executeSQL(): "
1316: + preparedStatement);
1317: } catch (Exception e) {
1318: logger
1319: .error(
1320: "Error in DbLoader.DataHandler.executeSQL()",
1321: e);
1322: } finally {
1323: try {
1324: pstmt.close();
1325: } catch (Exception e) {
1326: }
1327: }
1328: } else {
1329:
1330: // If prepared statements aren't supported, try a normal sql statement
1331: String statement = "";
1332: if (action.equals("delete"))
1333: statement = prepareDeleteStatement(row, false);
1334: else if (action.equals("modify"))
1335: statement = prepareUpdateStatement(row, false);
1336: else if (action.equals("insert"))
1337: statement = prepareInsertStatement(row, false);
1338: //System.out.println(statement);
1339:
1340: try {
1341: stmt = con.createStatement();
1342: stmt.executeUpdate(statement);
1343: } catch (Exception e) {
1344: logger
1345: .error(
1346: "Error in DbLoader.DataHandler.executeSQL()",
1347: e);
1348: logger
1349: .error("Error in DbLoader.DataHandler.executeSQL(): "
1350: + statement);
1351: } finally {
1352: try {
1353: stmt.close();
1354: } catch (Exception e) {
1355: }
1356: }
1357: }
1358: }
1359:
1360: private boolean supportsPreparedStatements() {
1361: boolean supportsPreparedStatements = true;
1362:
1363: try {
1364: // Issue a prepared statement to see if database/driver accepts them.
1365: // The assumption is that if a SQLException is thrown, it doesn't support them.
1366: // I don't know of any other way to check if the database/driver accepts
1367: // prepared statements. If you do, please change this method!
1368: Statement stmt;
1369: stmt = con.createStatement();
1370: try {
1371: stmt
1372: .executeUpdate("CREATE TABLE PREP_TEST (A VARCHAR(1))");
1373: } catch (Exception e) {/* Assume it already exists */
1374: } finally {
1375: try {
1376: stmt.close();
1377: } catch (Exception e) {
1378: }
1379: }
1380:
1381: pstmt = con
1382: .prepareStatement("SELECT A FROM PREP_TEST WHERE A=?");
1383: pstmt.clearParameters();
1384: pstmt.setString(1, "D");
1385: ResultSet rs = pstmt.executeQuery();
1386: rs.close();
1387: } catch (SQLException sqle) {
1388: supportsPreparedStatements = false;
1389: logger
1390: .error(
1391: "Error in DbLoader.DataHandler.supportsPreparedStatements()",
1392: sqle);
1393: } finally {
1394: try {
1395: stmt = con.createStatement();
1396: stmt.executeUpdate("DROP TABLE PREP_TEST");
1397: } catch (Exception e) {/* Assume it already exists */
1398: } finally {
1399: try {
1400: stmt.close();
1401: } catch (Exception e) {
1402: }
1403: }
1404:
1405: try {
1406: pstmt.close();
1407: } catch (Exception e) {
1408: }
1409: }
1410: return supportsPreparedStatements;
1411: }
1412:
1413: class Table {
1414: private String name;
1415:
1416: public String getName() {
1417: return name;
1418: }
1419:
1420: public void setName(String name) {
1421: this .name = name;
1422: }
1423: }
1424:
1425: class Row {
1426: ArrayList columns = new ArrayList();
1427:
1428: public ArrayList getColumns() {
1429: return columns;
1430: }
1431:
1432: public void addColumn(Column column) {
1433: columns.add(column);
1434: }
1435: }
1436:
1437: class Column {
1438: private String name;
1439: private String value;
1440: private String type;
1441:
1442: public String getName() {
1443: return name;
1444: }
1445:
1446: public String getValue() {
1447: return value;
1448: }
1449:
1450: public String getType() {
1451: return type;
1452: }
1453:
1454: public void setName(String name) {
1455: this .name = name;
1456: }
1457:
1458: public void setValue(String value) {
1459: this .value = value;
1460: }
1461:
1462: public void setType(String type) {
1463: this .type = type;
1464: }
1465: }
1466: }
1467:
1468: public Connection getCon() {
1469: return con;
1470: }
1471:
1472: public void setCon(Connection con) {
1473: this .con = con;
1474: }
1475:
1476: public Statement getStmt() {
1477: return stmt;
1478: }
1479:
1480: public void setStmt(Statement stmt) {
1481: this .stmt = stmt;
1482: }
1483:
1484: public PreparedStatement getPstmt() {
1485: return pstmt;
1486: }
1487:
1488: public void setPstmt(PreparedStatement pstmt) {
1489: this .pstmt = pstmt;
1490: }
1491:
1492: public Document getTablesDoc() {
1493: return tablesDoc;
1494: }
1495:
1496: public void setTablesDoc(Document tablesDoc) {
1497: this .tablesDoc = tablesDoc;
1498: }
1499:
1500: public Document getTablesDocGeneric() {
1501: return tablesDocGeneric;
1502: }
1503:
1504: public void setTablesDocGeneric(Document tablesDocGeneric) {
1505: this .tablesDocGeneric = tablesDocGeneric;
1506: }
1507:
1508: public boolean isCreateTableScript() {
1509: return createTableScript;
1510: }
1511:
1512: public void setCreateTableScript(boolean createTableScript) {
1513: this .createTableScript = createTableScript;
1514: }
1515:
1516: public boolean isPopulateTables() {
1517: return populateTables;
1518: }
1519:
1520: public void setPopulateTables(boolean populateTables) {
1521: this .populateTables = populateTables;
1522: }
1523:
1524: public PrintWriter getTableScriptOut() {
1525: return tableScriptOut;
1526: }
1527:
1528: public void setTableScriptOut(PrintWriter tableScriptOut) {
1529: this .tableScriptOut = tableScriptOut;
1530: }
1531:
1532: public boolean isDropTables() {
1533: return dropTables;
1534: }
1535:
1536: public void setDropTables(boolean dropTables) {
1537: this .dropTables = dropTables;
1538: }
1539:
1540: public boolean isCreateTables() {
1541: return createTables;
1542: }
1543:
1544: public void setCreateTables(boolean createTables) {
1545: this .createTables = createTables;
1546: }
1547:
1548: public String getDbName() {
1549: return dbName;
1550: }
1551:
1552: public void setDbName(String dbName) {
1553: this .dbName = dbName;
1554: }
1555:
1556: public String getDbVersion() {
1557: return dbVersion;
1558: }
1559:
1560: public void setDbVersion(String dbVersion) {
1561: this .dbVersion = dbVersion;
1562: }
1563:
1564: public String getDriverName() {
1565: return driverName;
1566: }
1567:
1568: public void setDriverName(String driverName) {
1569: this .driverName = driverName;
1570: }
1571:
1572: public String getDriverVersion() {
1573: return driverVersion;
1574: }
1575:
1576: public void setDriverVersion(String driverVersion) {
1577: this .driverVersion = driverVersion;
1578: }
1579:
1580: public boolean isAlterTables() {
1581: return alterTables;
1582: }
1583:
1584: public void setAlterTables(boolean alterTables) {
1585: this .alterTables = alterTables;
1586: }
1587:
1588: public boolean isIndexTables() {
1589: return indexTables;
1590: }
1591:
1592: public void setIndexTables(boolean indexTables) {
1593: this .indexTables = indexTables;
1594: }
1595:
1596: public Hashtable getTableColumnTypes() {
1597: return tableColumnTypes;
1598: }
1599:
1600: public void setTableColumnTypes(Hashtable tableColumnTypes) {
1601: this .tableColumnTypes = tableColumnTypes;
1602: }
1603:
1604: public PropertiesHandler getPropertiesHandler() {
1605: return propertiesHandler;
1606: }
1607:
1608: public void setPropertiesHandler(PropertiesHandler propertiesHandler) {
1609: this.propertiesHandler = propertiesHandler;
1610: }
1611: }
|