Java tutorial
/** * '$RCSfile: DatabaseAdapter.java,v $' * * '$Author: leinfelder $' * '$Date: 2008/06/17 23:21:28 $' * '$Revision: 1.21 $' * * For Details: http://kepler.ecoinformatics.org * * Copyright (c) 2003 The Regents of the University of California. * All rights reserved. * * Permission is hereby granted, without written agreement and without * license or royalty fees, to use, copy, modify, and distribute this * software and its documentation for any purpose, provided that the * above copyright notice and the following two paragraphs appear in * all copies of this software. * * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN * IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY * OF SUCH DAMAGE. * * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY * OF CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, * UPDATES, ENHANCEMENTS, OR MODIFICATIONS. */ package edu.lternet.pasta.dml.database; import java.sql.SQLException; import java.util.ArrayList; import java.util.TreeMap; import java.util.Vector; import java.util.Map; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import edu.lternet.pasta.dml.parser.Attribute; import edu.lternet.pasta.dml.parser.AttributeList; import edu.lternet.pasta.dml.parser.DateTimeDomain; import edu.lternet.pasta.dml.parser.Domain; import edu.lternet.pasta.dml.parser.StorageType; /** * This class provide a bridge between DatabaseHandler and a specific db. * * @author Jing Tao */ public abstract class DatabaseAdapter { /* * Class fields */ public static Log log = LogFactory.getLog(DatabaseAdapter.class); public static final String HSQL_ADAPTER = "HSQLAdapter"; public static final String ORACLE_ADAPTER = "OracleAdapter"; public static final String POSTGRES_ADAPTER = "PostgresAdapter"; public static final String COMMA = ","; public static final String SPACE = " "; public static final String INSERT = "INSERT INTO"; public static final String LEFTPARENTH = "("; public static final String RIGHTPARENTH = ")"; public static final String SEMICOLON = ";"; public static final String DOUBLEQUOTE = "\""; public static final String SINGLEQUOTE = "'"; public static final String VALUES = "VALUES"; public static final String NULL = "null"; public static final String UNDERSCORE = "_"; /* * Instance fields */ //subclasses can override this rather than reimplementing entire methods protected String TO_DATE_FUNCTION = "to_timestamp"; private final String XML_SCHEMA_DATATYPES = "http://www.w3.org/2001/XMLSchema-datatypes"; protected static final int DEFAULT_TABLE_NAME_MAX_LENGTH = 30; /* * Constructors */ /* * Class methods */ public static int getTableNameMaxLength() { return DEFAULT_TABLE_NAME_MAX_LENGTH; } /** * Given an entity name, return a well-formed table name. This is a generic * implementation that should work for most databases. This method should be * overridden by a database adapter subclass if it has special rules for the * well-formedness of a table name. This method simply looks for illegal * table name characters in the entity name and replaces them with underscore * characters. * * @param entityName the entity name * @return a well-formed table name corresponding to the entity\ * name */ public static String getLegalDBTableName(String entityName) { final int tableNameMaxLength = getTableNameMaxLength(); String legalName = null; char[] badChars = { ' ', '-', '.', '/', ',', '(', ')', '<', '>' }; char goodChar = '_'; if (entityName != null) { int entityNameLength = entityName.length(); int legalNameLength = Math.min(entityNameLength, tableNameMaxLength); legalName = entityName.substring(0, legalNameLength); } if (legalName != null) { for (int i = 0; i < badChars.length; i++) { legalName = legalName.replace(badChars[i], goodChar); } // If first character is a digit, prepend an underscore char firstCharacter = legalName.charAt(0); if (Character.isDigit(firstCharacter)) { legalName = UNDERSCORE + legalName; } } return legalName; } /* * Instance methods */ /** * Assigns database field names to all Attribute objects in the AttributeList. * The assigned field names comply with the following criteria: * (1) each is a legal database field name * (2) each is unique within this attribute list * * @param attributeList the AttributeList object containing the Attributes * that correspond to the fields in the database * table */ public void assignDbFieldNames(AttributeList attributeList) { Attribute[] list = attributeList.getAttributes(); TreeMap<String, String> usedNames = new TreeMap<String, String>(); int size = list.length; for (int i = 0; i < size; i++) { Attribute attribute = list[i]; String attributeName = attribute.getName(); String legalDbFieldName = getLegalDbFieldName(attributeName); String foundName = usedNames.get(legalDbFieldName); while (foundName != null) { String mangledName = mangleFieldName(legalDbFieldName); legalDbFieldName = mangledName; foundName = usedNames.get(legalDbFieldName); } usedNames.put(legalDbFieldName, legalDbFieldName); /* * Ensure that the field names are surrounded by quotes. * (See Bug #2737: * http://bugzilla.ecoinformatics.org/show_bug.cgi?id=2737 * ) */ String quotedName = DOUBLEQUOTE + legalDbFieldName + DOUBLEQUOTE; attribute.setDBFieldName(quotedName); } } /** * Creates a sql command to generate table. * * @param attributeList An AttributeList object holding the entity * attributes. * @param tableName The name of the table to be generated * @return the DDL string. In the parent DatabaseAdapter class, the string is * empty. */ public String generateDDL(AttributeList attributeList, String tableName) throws SQLException { String ddlString = ""; return ddlString; } /** * Creates a drop table sql command. * * @param tableName The name of the table to be dropped. * @return the SQL string. In the parent DatabaseAdapter class, the string is * empty. */ public String generateDropTableSQL(String tableName) { String sqlString = ""; return sqlString; } /** * Creates a SQL command to insert data. If some error happens, null will be * returned. * * @param attributeList AttributeList which will be inserted * @param tableName The name of the table which the data will be inserted into * @param oneRowData The data vector which contains data to be inserted * @return A SQL String that can be run to insert one row of data into table */ public String generateInsertSQL(AttributeList attributeList, String tableName, Vector oneRowData) throws DataNotMatchingMetadataException, SQLException { String sqlString = null; int NULLValueCounter = 0; int hasValueCounter = 0; if (attributeList == null) { throw new SQLException("The attribute list is null and couldn't generate insert sql statement"); } if (oneRowData == null || oneRowData.isEmpty()) { throw new SQLException("The the data is null and couldn't generte insert sql statement"); } StringBuffer sqlAttributePart = new StringBuffer(); StringBuffer sqlDataPart = new StringBuffer(); sqlAttributePart.append(INSERT); sqlAttributePart.append(SPACE); sqlAttributePart.append(tableName); sqlAttributePart.append(LEFTPARENTH); sqlDataPart.append(SPACE); sqlDataPart.append(VALUES); sqlDataPart.append(SPACE); sqlDataPart.append(LEFTPARENTH); Attribute[] list = attributeList.getAttributes(); if (list == null || list.length == 0) { throw new SQLException("The attributes is null and couldn't generate insert sql statement"); } int size = list.length; // column name part boolean firstAttribute = true; for (int i = 0; i < size; i++) { // if data vector Object obj = oneRowData.elementAt(i); String value = null; if (obj == null) { NULLValueCounter++; continue; } else { value = (String) obj; if (value.trim().equals("")) { continue; } } Attribute attribute = list[i]; if (attribute == null) { throw new SQLException("Attribute list contains a null attribute"); } String[] missingValues = attribute.getMissingValueCode(); boolean isMissingValue = isMissingValue(value, missingValues); if (isMissingValue) { continue; } String name = attribute.getDBFieldName(); String attributeType = getAttributeType(attribute); if (!firstAttribute) { sqlAttributePart.append(COMMA); sqlDataPart.append(COMMA); } sqlAttributePart.append(name); Domain domain = attribute.getDomain(); /* If attributeType is "datetime", convert to a timestamp * and wrap single quotes around the value. But only if we * have a format string! */ if (attributeType.equalsIgnoreCase("datetime")) { String formatString = ((DateTimeDomain) domain).getFormatString(); // Transform the datetime format string for database compatibility formatString = transformFormatString(formatString); // Transform the datetime value for database compatibility value = transformDatetime(value); value = escapeSpecialCharacterInData(value); sqlDataPart.append(TO_DATE_FUNCTION); sqlDataPart.append(LEFTPARENTH); sqlDataPart.append(SINGLEQUOTE); sqlDataPart.append(value); sqlDataPart.append(SINGLEQUOTE); sqlDataPart.append(COMMA); sqlDataPart.append(SINGLEQUOTE); sqlDataPart.append(formatString); sqlDataPart.append(SINGLEQUOTE); sqlDataPart.append(RIGHTPARENTH); hasValueCounter++; log.debug("datetime value expression= " + sqlDataPart.toString()); } /* If domain is null or it is not NumericDomain we assign it text type * and wrap single quotes around the value. */ else if (attributeType.equals("string")) { value = escapeSpecialCharacterInData(value); sqlDataPart.append(SINGLEQUOTE); sqlDataPart.append(value); sqlDataPart.append(SINGLEQUOTE); hasValueCounter++; } /* Else we have a NumericDomain. Determine whether it is a float or * integer. */ else { String dataType = mapDataType(attributeType); try { if (dataType.equals("FLOAT")) { Float floatObj = new Float(value); float floatNum = floatObj.floatValue(); sqlDataPart.append(floatNum); } else { Integer integerObj = new Integer(value); int integerNum = integerObj.intValue(); sqlDataPart.append(integerNum); } } catch (Exception e) { log.error("Error determining numeric value: " + e.getMessage()); throw new DataNotMatchingMetadataException( "Data value '" + value + "' is NOT the expected data type of '" + dataType + "'"); } hasValueCounter++; } firstAttribute = false; } // If all data is null, return null value for sql string. if (NULLValueCounter == list.length || hasValueCounter == 0) { return sqlString; } sqlAttributePart.append(RIGHTPARENTH); sqlDataPart.append(RIGHTPARENTH); sqlDataPart.append(SEMICOLON); // Combine the two parts sqlAttributePart.append(sqlDataPart.toString()); sqlString = sqlAttributePart.toString(); return sqlString; } /** * Gets attribute type for a given attribute. Attribute types include: * "datetime" * "string" * or, for numeric attributes, one of the allowed EML NumberType values: * "natural" * "whole" * "integer" * "real" * * @param attribute The Attribute object whose type is being determined. * @return a string value representing the attribute type */ protected abstract String getAttributeType(Attribute attribute); /** * If the metadata provides one or more storageType elements, use * them to determine the attribute type. Since storageType elements * are hints that the metadata provider uses to suggest the appropriate * data type for an attribute, they are more likely to be an accurate * reflection of the data type than relying just on the domain type. * * (See http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5308 for * additional info.) * * @param attribute * @param className * @return */ protected String getAttributeTypeFromStorageType(Attribute attribute, String className) { String attributeType = null; if (attribute != null) { ArrayList<StorageType> storageTypes = attribute.getStorageTypeArray(); for (StorageType storageType : storageTypes) { if (storageType != null) { String textValue = storageType.getTextValue(); if ((textValue != null) && (!textValue.equals(""))) { String typeSystem = storageType.getTypeSystem(); if ((typeSystem != null) && (!typeSystem.equals(""))) { if (typeSystem.equals(XML_SCHEMA_DATATYPES)) { // If one of the storageType element's uses XMLSchema-datatypes, // for its typeSystem, use this to determine the attributeType if (textValue.equalsIgnoreCase("string")) { attributeType = "string"; } else if (textValue.equalsIgnoreCase("int") || textValue.equalsIgnoreCase("long") || textValue.equalsIgnoreCase("short") || textValue.equalsIgnoreCase("integer")) { attributeType = "integer"; } else if (textValue.equalsIgnoreCase("float") || textValue.equalsIgnoreCase("double")) { attributeType = "real"; } else if (textValue.equalsIgnoreCase("date") || textValue.equalsIgnoreCase("datetime")) { attributeType = "datetime"; } else { attributeType = "string"; } return attributeType; } else if (textValue.equalsIgnoreCase("integer") || textValue.equalsIgnoreCase("datetime") || textValue.equalsIgnoreCase("natural") || textValue.equalsIgnoreCase("string") || textValue.equalsIgnoreCase("real") || textValue.equalsIgnoreCase("whole")) { attributeType = textValue.toLowerCase(); } } } } } } return attributeType; } /** * Gets the sql command to count the rows in a given table. * * @param tableName the given table name * @return the sql string which can count how many rows */ public abstract String getCountingRowNumberSQL(String tableName); /** * The map between metadat data type and database native data type. * * @return In the parent DatabaseAdapter class, returns null. */ public Map getDataTypeMap() { Map typeMap = null; return typeMap; } /** * Given an attribute name, return a legal database field name. This is the * generic implementation, but child classes may need to override this with * their own database-specific implementation. * * @param attributeName the attribute name * @return legalName, a String containing a legal field name for this * attribute name */ private String getLegalDbFieldName(String attributeName) { String legalName = attributeName; char[] badChars = { ' ', '-', '.', '/', ':', '@', '[', ']' }; char goodChar = '_'; for (int i = 0; i < badChars.length; i++) { legalName = legalName.replace(badChars[i], goodChar); } return legalName; } /* * Determins if the value is in the missValue list */ private boolean isMissingValue(String value, String[] missValues) { boolean isMissingValue = false; if (missValues != null && value != null) { int size = missValues.length; for (int i = 0; i < size; i++) { String missValue = missValues[i]; if (value.equals(missValue)) { isMissingValue = true; break; } } } return isMissingValue; } /** * Mangles a field name by appending a string to it. The purpose is to * handle the case where a field name has already been found in the table, * so a unique field name needs to be generated. * * @param originalName the original field name * @return the mangled field name */ private String mangleFieldName(String originalName) { StringBuffer stringBuffer = new StringBuffer(originalName); stringBuffer.append("_Prime"); return stringBuffer.toString(); } /** * Gets the database type based on attribute type. This data type * varies on different db system. * * @param attributeType a String holding the attribute type * @return a String holding the database type */ protected abstract String mapDataType(String attributeType); /* * Adds the attribute definitions to a create table command. * If one attribute is null or has some error an exception will be thrown. */ protected String parseAttributeList(AttributeList attributeList) throws SQLException { Attribute[] list = attributeList.getAttributes(); StringBuffer attributeSql = new StringBuffer(); if (list == null || list.length == 0) { // log.debug("There is no attribute definition in entity"); throw new SQLException("No attribute definition found in entity"); } /* * Determine a legal, unique field name to assign to each attribute in this * attribute list. */ assignDbFieldNames(attributeList); int size = list.length; // DBDataTypeResolver dataTypeResolver = new DBDataTypeResolver(); boolean firstAttribute = true; for (int i = 0; i < size; i++) { Attribute attribute = list[i]; if (attribute == null) { // log.debug("One attribute definition is null attribute list"); throw new SQLException("Attribute list contains a null attribute"); } // Get this attribute's database field name, which was assigned in the // call to assignDbFieldNames(attributeList) above. String attributeName = attribute.getName(); String fieldName = attribute.getDBFieldName(); String attributeType = getAttributeType(attribute); String dbDataType = mapDataType(attributeType); // String dataType = attribute.getDataType(); // String dbDataType = "VARCHAR(32)"; // String dbDataType = dataTypeResolver.resolveDBType(dataType); // String javaDataType = dataTypeResolver.resolveJavaType(dataType); // dbJavaDataTypeList.add(javaDataType); if (!firstAttribute) { attributeSql.append(COMMA); } attributeSql.append(fieldName); attributeSql.append(SPACE); attributeSql.append(dbDataType); firstAttribute = false; log.debug("Attribute Name: " + attributeName); log.debug("DB Field Name : " + fieldName); log.debug("dbDataType : " + dbDataType + "\n"); } return attributeSql.toString(); } /** * Transforms a datetime string value for compatibility * with a database timestamp field. The default behavior * implemented here is to return the original datetime string. * * @param datetimeString A datetime string as found in a data table * @return datetimeString The transformed datetime string */ protected String transformDatetime(String datetimeString) { return datetimeString; } /** * Transforms an EML datetime format string for compatibility * with a database timestamp field. The default behavior * implemented here is to return the original format string. * * @param emlFormatString EML format string for datetime * @return emlFormatString Database format string */ protected String transformFormatString(String emlFormatString) { return emlFormatString; } /** * Transforms ANSI selection SQL to a native db SQL command. * * @param ANSISQL The ANSI SQL string. * @return The native SQL string. In the parent DatabaseAdapter class, the * string is empty. */ public String transformSelectionSQL(String ANSISQL) { String sqlString = ""; return sqlString; } /* * This method will escape special character, e.g. single quote ('), in the string data * value. If the string has a single quote without escape, it will cause a problem. * The standard SQL syntax for escaping a single quote is to double it up. * For example: * INSERT INTO TABLE (comment) VALUES ('here's it'); * will cause a problem. While, * INSERT INTO TABLE (comment) VALUES ('here''s it'); * will be fine. */ protected String escapeSpecialCharacterInData(String data) { String[] specialArray = { "'" }; String escape = "'"; if (data == null) { return data; } int size = specialArray.length; for (int i = 0; i < size; i++) { String special = specialArray[i]; if (data.contains(special)) { data = data.replaceAll(special, escape + special); } } return data; } }