0001: /*
0002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
0003: *
0004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
0005: *
0006: * The contents of this file are subject to the terms of either the GNU
0007: * General Public License Version 2 only ("GPL") or the Common
0008: * Development and Distribution License("CDDL") (collectively, the
0009: * "License"). You may not use this file except in compliance with the
0010: * License. You can obtain a copy of the License at
0011: * http://www.netbeans.org/cddl-gplv2.html
0012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
0013: * specific language governing permissions and limitations under the
0014: * License. When distributing the software, include this License Header
0015: * Notice in each file and include the License file at
0016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
0017: * particular file as subject to the "Classpath" exception as provided
0018: * by Sun in the GPL Version 2 section of the License file that
0019: * accompanied this code. If applicable, add the following below the
0020: * License Header, with the fields enclosed by brackets [] replaced by
0021: * your own identifying information:
0022: * "Portions Copyrighted [year] [name of copyright owner]"
0023: *
0024: * Contributor(s):
0025: *
0026: * The Original Software is NetBeans. The Initial Developer of the Original
0027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
0028: * Microsystems, Inc. All Rights Reserved.
0029: *
0030: * If you wish your version of this file to be governed by only the CDDL
0031: * or only the GPL Version 2, indicate your decision by adding
0032: * "[Contributor] elects to include this software in this distribution
0033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
0034: * single choice of license, a recipient has the option to distribute
0035: * your version of this file under either the CDDL, the GPL Version 2 or
0036: * to extend the choice of license to its licensees as provided above.
0037: * However, if you add GPL Version 2 code and therefore, elected the GPL
0038: * Version 2 license, then the option applies only if the new code is
0039: * made subject to such option by the copyright holder.
0040: */
0041: package org.netbeans.modules.sql.framework.common.jdbc;
0042:
0043: import java.sql.PreparedStatement;
0044: import java.sql.Types;
0045: import java.util.ArrayList;
0046: import java.util.Calendar;
0047: import java.util.Collections;
0048: import java.util.GregorianCalendar;
0049: import java.util.HashMap;
0050: import java.util.Iterator;
0051: import java.util.List;
0052: import java.util.ListIterator;
0053: import java.util.Map;
0054: import java.util.NoSuchElementException;
0055: import java.util.Set;
0056: import java.util.StringTokenizer;
0057: import java.util.TimeZone;
0058: import java.util.TreeMap;
0059:
0060: import com.sun.sql.framework.exception.BaseException;
0061: import com.sun.sql.framework.jdbc.DBConstants;
0062: import net.java.hulp.i18n.Logger;
0063: import com.sun.sql.framework.utils.RuntimeAttribute;
0064: import com.sun.sql.framework.utils.StringUtil;
0065: import org.netbeans.modules.etl.logger.Localizer;
0066: import org.netbeans.modules.etl.logger.LogUtil;
0067:
0068: /**
0069: * Utility class supplying lookup and conversion methods for SQL-related tasks.
0070: *
0071: * @author Sudhendra Seshachala
0072: * @author Girish Patil
0073: *
0074: * @version $Revision$
0075: */
0076: public class SQLUtils {
0077:
0078: private static transient final Logger mLogger = LogUtil
0079: .getLogger(SQLUtils.class.getName());
0080: private static transient final Localizer mLoc = Localizer.get();
0081: /* Log4J cateogry */
0082: static final String LOG_CATEGORY = SQLUtils.class.getName();
0083: /* System constant : basically an unqoted varchar */
0084: public static final int VARCHAR_UNQUOTED = 3345336;
0085: private static final int ANYTYPE_CONSTANT = VARCHAR_UNQUOTED - 1;
0086: public static final String VARCHAR_UNQUOTED_STR = "varchar:unquoted";
0087: /** Undefined jdbc type */
0088: public static final int JDBCSQL_TYPE_UNDEFINED = -65535;
0089: private static HashMap<Integer, Integer> dataTypePrecedenceMap = new HashMap<Integer, Integer>();
0090: private static Map<Integer, String> dbIdNameMap = new TreeMap<Integer, String>();
0091: private static Map<String, Integer> dbNameIdMap = new TreeMap<String, Integer>();
0092: private static Map<String, String> JDBC_SQL_MAP = new HashMap<String, String>();
0093: private static Map<String, String> SQL_JDBC_MAP = new HashMap<String, String>();
0094: private static final List<String> SUPPORTED_DATE_FORMATS = new ArrayList<String>();
0095: private static final List<String> SUPPORTED_DATE_PARTS = new ArrayList<String>();
0096: private static final List<String> SUPPORTED_INTERVAL_TYPES = new ArrayList<String>();
0097: private static final List<String> SUPPORTED_LITERAL_JDBC_TYPES = new ArrayList<String>();
0098: private static final List<String> SUPPORTED_CAST_JDBC_TYPES = new ArrayList<String>();
0099:
0100: static {
0101: dbNameIdMap.put(DBConstants.ANSI92_STR, new Integer(
0102: DBConstants.ANSI92));
0103: dbNameIdMap.put(DBConstants.ORACLE8_STR, new Integer(
0104: DBConstants.ORACLE8));
0105: dbNameIdMap.put(DBConstants.ORACLE9_STR, new Integer(
0106: DBConstants.ORACLE9));
0107: dbNameIdMap.put(DBConstants.AXION_STR, new Integer(
0108: DBConstants.AXION));
0109: dbNameIdMap.put(DBConstants.MSSQLSERVER_STR, new Integer(
0110: DBConstants.MSSQLSERVER));
0111: dbNameIdMap.put(DBConstants.DB2V7_STR, new Integer(
0112: DBConstants.DB2V7));
0113: dbNameIdMap.put(DBConstants.DB2V8_STR, new Integer(
0114: DBConstants.DB2V8));
0115: dbNameIdMap.put(DBConstants.DB2V5_STR, new Integer(
0116: DBConstants.DB2V5));
0117: dbNameIdMap.put(DBConstants.SYBASE_STR, new Integer(
0118: DBConstants.SYBASE));
0119: dbNameIdMap.put(DBConstants.MYSQL_STR, new Integer(
0120: DBConstants.MYSQL));
0121: dbNameIdMap.put(DBConstants.DERBY_STR, new Integer(
0122: DBConstants.DERBY));
0123: dbNameIdMap.put(DBConstants.POSTGRES_STR, new Integer(
0124: DBConstants.POSTGRESQL));
0125: }
0126:
0127: static {
0128: dbIdNameMap.put(new Integer(DBConstants.ANSI92),
0129: DBConstants.ANSI92_STR);
0130: dbIdNameMap.put(new Integer(DBConstants.ORACLE8),
0131: DBConstants.ORACLE8_STR);
0132: dbIdNameMap.put(new Integer(DBConstants.ORACLE9),
0133: DBConstants.ORACLE9_STR);
0134: dbIdNameMap.put(new Integer(DBConstants.AXION),
0135: DBConstants.AXION_STR);
0136: dbIdNameMap.put(new Integer(DBConstants.MSSQLSERVER),
0137: DBConstants.MSSQLSERVER_STR);
0138: dbIdNameMap.put(new Integer(DBConstants.DB2V7),
0139: DBConstants.DB2V7_STR);
0140: dbIdNameMap.put(new Integer(DBConstants.DB2V8),
0141: DBConstants.DB2V8_STR);
0142: dbIdNameMap.put(new Integer(DBConstants.DB2V5),
0143: DBConstants.DB2V5_STR);
0144: dbIdNameMap.put(new Integer(DBConstants.SYBASE),
0145: DBConstants.SYBASE_STR);
0146: dbIdNameMap.put(new Integer(DBConstants.DERBY),
0147: DBConstants.DERBY_STR);
0148: dbIdNameMap.put(new Integer(DBConstants.MYSQL),
0149: DBConstants.MYSQL_STR);
0150: dbIdNameMap.put(new Integer(DBConstants.POSTGRESQL),
0151: DBConstants.POSTGRES_STR);
0152:
0153: }
0154:
0155: static {
0156: SUPPORTED_LITERAL_JDBC_TYPES.add("char");
0157: SUPPORTED_LITERAL_JDBC_TYPES.add("integer");
0158: SUPPORTED_LITERAL_JDBC_TYPES.add("numeric");
0159: SUPPORTED_LITERAL_JDBC_TYPES.add("timestamp");
0160: SUPPORTED_LITERAL_JDBC_TYPES.add("varchar");
0161:
0162: Collections.sort(SUPPORTED_LITERAL_JDBC_TYPES);
0163: }
0164:
0165: static {
0166: SUPPORTED_CAST_JDBC_TYPES.add("bigint");
0167: SUPPORTED_CAST_JDBC_TYPES.add("bit");
0168: SUPPORTED_CAST_JDBC_TYPES.add("char");
0169: SUPPORTED_CAST_JDBC_TYPES.add("date");
0170: SUPPORTED_CAST_JDBC_TYPES.add("double");
0171: SUPPORTED_CAST_JDBC_TYPES.add("decimal");
0172: SUPPORTED_CAST_JDBC_TYPES.add("float");
0173: SUPPORTED_CAST_JDBC_TYPES.add("integer");
0174: SUPPORTED_CAST_JDBC_TYPES.add("numeric");
0175: SUPPORTED_CAST_JDBC_TYPES.add("real");
0176: SUPPORTED_CAST_JDBC_TYPES.add("smallint");
0177: SUPPORTED_CAST_JDBC_TYPES.add("time");
0178: SUPPORTED_CAST_JDBC_TYPES.add("timestamp");
0179: SUPPORTED_CAST_JDBC_TYPES.add("tinyint");
0180: SUPPORTED_CAST_JDBC_TYPES.add("longvarchar");
0181: SUPPORTED_CAST_JDBC_TYPES.add("varchar");
0182: SUPPORTED_CAST_JDBC_TYPES.add("binary");
0183: SUPPORTED_CAST_JDBC_TYPES.add("varbinary");
0184: SUPPORTED_CAST_JDBC_TYPES.add("longvarbinary");
0185:
0186: Collections.sort(SUPPORTED_CAST_JDBC_TYPES);
0187: }
0188:
0189: static {
0190: SUPPORTED_INTERVAL_TYPES.add("second");
0191: SUPPORTED_INTERVAL_TYPES.add("minute");
0192: SUPPORTED_INTERVAL_TYPES.add("hour");
0193: SUPPORTED_INTERVAL_TYPES.add("day");
0194: SUPPORTED_INTERVAL_TYPES.add("week");
0195: SUPPORTED_INTERVAL_TYPES.add("month");
0196: SUPPORTED_INTERVAL_TYPES.add("quarter");
0197: SUPPORTED_INTERVAL_TYPES.add("year");
0198:
0199: Collections.sort(SUPPORTED_INTERVAL_TYPES);
0200: }
0201:
0202: static {
0203: SUPPORTED_DATE_FORMATS.add("MON DD YYYY HH:MIAM");
0204: SUPPORTED_DATE_FORMATS.add("MM/DD/YY");
0205: SUPPORTED_DATE_FORMATS.add("MM/DD/YYYY");
0206: SUPPORTED_DATE_FORMATS.add("YY.MM.DD");
0207: SUPPORTED_DATE_FORMATS.add("YYYY.MM.DD");
0208: SUPPORTED_DATE_FORMATS.add("DD/MM/YY");
0209: SUPPORTED_DATE_FORMATS.add("DD/MM/YYYY");
0210: SUPPORTED_DATE_FORMATS.add("DD.MM.YY");
0211: SUPPORTED_DATE_FORMATS.add("DD.MM.YYYY");
0212: SUPPORTED_DATE_FORMATS.add("DD-MM-YY");
0213: SUPPORTED_DATE_FORMATS.add("DD-MM-YYYY");
0214: SUPPORTED_DATE_FORMATS.add("DD MON YY");
0215: SUPPORTED_DATE_FORMATS.add("DD MON YYYY");
0216: SUPPORTED_DATE_FORMATS.add("MON DD, YY");
0217: SUPPORTED_DATE_FORMATS.add("MON DD, YYYY");
0218: SUPPORTED_DATE_FORMATS.add("HH:MI:SS");
0219: SUPPORTED_DATE_FORMATS.add("MM-DD-YY");
0220: SUPPORTED_DATE_FORMATS.add("MM-DD-YYYY");
0221: SUPPORTED_DATE_FORMATS.add("YY/MM/DD");
0222: SUPPORTED_DATE_FORMATS.add("YYYY/MM/DD");
0223: SUPPORTED_DATE_FORMATS.add("YYMMDD");
0224: SUPPORTED_DATE_FORMATS.add("YYYYMMDD");
0225: SUPPORTED_DATE_FORMATS.add("DD MON YYYY HH24:MI:SS.FF");
0226: SUPPORTED_DATE_FORMATS.add("HH24:MI:SS:FF");
0227: SUPPORTED_DATE_FORMATS.add("DD MON YYYY HH24:MI:SS");
0228: SUPPORTED_DATE_FORMATS.add("HH24:MI:SS");
0229: SUPPORTED_DATE_FORMATS.add("YYYY-MM-DD HH24:MI:SS.FF");
0230: SUPPORTED_DATE_FORMATS.add("YYYY-MM-DDTHH24:MI:SS");
0231: SUPPORTED_DATE_FORMATS.add("YYYYMMDDTHH24MISS");
0232: SUPPORTED_DATE_FORMATS.add("DD MON YYYY HH:MI:SS.FFFAM");
0233: SUPPORTED_DATE_FORMATS.add("DD/MM/YYYY HH:MI:SS.FFFAM");
0234: Collections.sort(SUPPORTED_DATE_FORMATS);
0235: }
0236:
0237: static {
0238: SUPPORTED_DATE_PARTS.add("WEEKDAY");
0239: SUPPORTED_DATE_PARTS.add("WEEKDAY3");
0240: SUPPORTED_DATE_PARTS.add("WEEKDAYFULL");
0241: SUPPORTED_DATE_PARTS.add("DAY");
0242: SUPPORTED_DATE_PARTS.add("MONTH");
0243: SUPPORTED_DATE_PARTS.add("MONTH3");
0244: SUPPORTED_DATE_PARTS.add("MONTHFULL");
0245: SUPPORTED_DATE_PARTS.add("YEAR");
0246: SUPPORTED_DATE_PARTS.add("HOUR");
0247: SUPPORTED_DATE_PARTS.add("HOUR12");
0248: SUPPORTED_DATE_PARTS.add("HOUR24");
0249: SUPPORTED_DATE_PARTS.add("MINUTE");
0250: SUPPORTED_DATE_PARTS.add("SECOND");
0251: SUPPORTED_DATE_PARTS.add("WEEK");
0252: SUPPORTED_DATE_PARTS.add("QUARTER");
0253: SUPPORTED_DATE_PARTS.add("MILLISECOND");
0254: SUPPORTED_DATE_PARTS.add("AMPM");
0255: }
0256:
0257: static {
0258: SQL_JDBC_MAP.put("array", String.valueOf(Types.ARRAY));
0259: SQL_JDBC_MAP.put("bigint", String.valueOf(Types.BIGINT));
0260: SQL_JDBC_MAP.put("binary", String.valueOf(Types.BINARY));
0261: SQL_JDBC_MAP.put("boolean", String.valueOf(Types.BOOLEAN));
0262: SQL_JDBC_MAP.put("bit", String.valueOf(Types.BIT));
0263: SQL_JDBC_MAP.put("blob", String.valueOf(Types.BLOB));
0264: SQL_JDBC_MAP.put("char", String.valueOf(Types.CHAR));
0265: SQL_JDBC_MAP.put("clob", String.valueOf(Types.CLOB));
0266: SQL_JDBC_MAP.put("date", String.valueOf(Types.DATE));
0267: SQL_JDBC_MAP.put("decimal", String.valueOf(Types.DECIMAL));
0268: SQL_JDBC_MAP.put("distinct", String.valueOf(Types.DISTINCT));
0269: SQL_JDBC_MAP.put("double", String.valueOf(Types.DOUBLE));
0270: SQL_JDBC_MAP.put("float", String.valueOf(Types.FLOAT));
0271: SQL_JDBC_MAP.put("integer", String.valueOf(Types.INTEGER));
0272: SQL_JDBC_MAP.put("longvarbinary", String
0273: .valueOf(Types.LONGVARBINARY));
0274: SQL_JDBC_MAP.put("longvarchar", String
0275: .valueOf(Types.LONGVARCHAR));
0276: SQL_JDBC_MAP.put("numeric", String.valueOf(Types.NUMERIC));
0277: SQL_JDBC_MAP.put("real", String.valueOf(Types.REAL));
0278: SQL_JDBC_MAP.put("smallint", String.valueOf(Types.SMALLINT));
0279: SQL_JDBC_MAP.put("time", String.valueOf(Types.TIME));
0280: SQL_JDBC_MAP.put("timestamp", String.valueOf(Types.TIMESTAMP));
0281: SQL_JDBC_MAP.put("tinyint", String.valueOf(Types.TINYINT));
0282: SQL_JDBC_MAP.put("varbinary", String.valueOf(Types.VARBINARY));
0283: SQL_JDBC_MAP.put("varchar", String.valueOf(Types.VARCHAR));
0284: SQL_JDBC_MAP.put("null", String.valueOf(Types.NULL));
0285: SQL_JDBC_MAP.put(VARCHAR_UNQUOTED_STR, String
0286: .valueOf(VARCHAR_UNQUOTED));
0287: SQL_JDBC_MAP.put("anytype", String.valueOf(ANYTYPE_CONSTANT));
0288:
0289: JDBC_SQL_MAP.put(String.valueOf(Types.ARRAY), "array");
0290: JDBC_SQL_MAP.put(String.valueOf(Types.BIGINT), "bigint");
0291: JDBC_SQL_MAP.put(String.valueOf(Types.BINARY), "binary");
0292: JDBC_SQL_MAP.put(String.valueOf(Types.BIT), "bit");
0293: JDBC_SQL_MAP.put(String.valueOf(Types.BLOB), "blob");
0294: JDBC_SQL_MAP.put(String.valueOf(Types.BOOLEAN), "boolean");
0295: JDBC_SQL_MAP.put(String.valueOf(Types.CHAR), "char");
0296: JDBC_SQL_MAP.put(String.valueOf(Types.CLOB), "clob");
0297: JDBC_SQL_MAP.put(String.valueOf(Types.DATE), "date");
0298: JDBC_SQL_MAP.put(String.valueOf(Types.DECIMAL), "decimal");
0299: JDBC_SQL_MAP.put(String.valueOf(Types.DISTINCT), "distinct");
0300: JDBC_SQL_MAP.put(String.valueOf(Types.DOUBLE), "double");
0301: JDBC_SQL_MAP.put(String.valueOf(Types.FLOAT), "float");
0302: JDBC_SQL_MAP.put(String.valueOf(Types.INTEGER), "integer");
0303: JDBC_SQL_MAP.put(String.valueOf(Types.LONGVARBINARY),
0304: "longvarbinary");
0305: JDBC_SQL_MAP.put(String.valueOf(Types.LONGVARCHAR),
0306: "longvarchar");
0307: JDBC_SQL_MAP.put(String.valueOf(Types.NUMERIC), "numeric");
0308: JDBC_SQL_MAP.put(String.valueOf(Types.REAL), "real");
0309: JDBC_SQL_MAP.put(String.valueOf(Types.SMALLINT), "smallint");
0310: JDBC_SQL_MAP.put(String.valueOf(Types.TIME), "time");
0311: JDBC_SQL_MAP.put(String.valueOf(Types.TIMESTAMP), "timestamp");
0312: JDBC_SQL_MAP.put(String.valueOf(Types.TINYINT), "tinyint");
0313: JDBC_SQL_MAP.put(String.valueOf(Types.VARBINARY), "varbinary");
0314: JDBC_SQL_MAP.put(String.valueOf(Types.VARCHAR), "varchar");
0315: JDBC_SQL_MAP.put(String.valueOf(Types.NULL), "null");
0316: JDBC_SQL_MAP.put(String.valueOf(VARCHAR_UNQUOTED),
0317: VARCHAR_UNQUOTED_STR);
0318: JDBC_SQL_MAP.put(String.valueOf(ANYTYPE_CONSTANT), "anytype");
0319: }
0320: /**
0321: * Data types in decreasing order of precedence 1 is hightest
0322: */
0323:
0324: static {
0325: dataTypePrecedenceMap.put(new Integer(Types.DOUBLE),
0326: new Integer(1));
0327: dataTypePrecedenceMap.put(new Integer(Types.FLOAT),
0328: new Integer(2));
0329: dataTypePrecedenceMap.put(new Integer(Types.REAL), new Integer(
0330: 3));
0331: dataTypePrecedenceMap.put(new Integer(Types.NUMERIC),
0332: new Integer(4));
0333: dataTypePrecedenceMap.put(new Integer(Types.DECIMAL),
0334: new Integer(5));
0335: dataTypePrecedenceMap.put(new Integer(Types.BIGINT),
0336: new Integer(6));
0337: dataTypePrecedenceMap.put(new Integer(Types.INTEGER),
0338: new Integer(7));
0339: dataTypePrecedenceMap.put(new Integer(Types.SMALLINT),
0340: new Integer(8));
0341: dataTypePrecedenceMap.put(new Integer(Types.TINYINT),
0342: new Integer(9));
0343: dataTypePrecedenceMap.put(new Integer(Types.BIT), new Integer(
0344: 10));
0345: dataTypePrecedenceMap.put(new Integer(Types.TIMESTAMP),
0346: new Integer(11));
0347: dataTypePrecedenceMap.put(new Integer(Types.CLOB), new Integer(
0348: 12));
0349: dataTypePrecedenceMap.put(new Integer(Types.VARCHAR),
0350: new Integer(13));
0351: dataTypePrecedenceMap.put(new Integer(Types.CHAR), new Integer(
0352: 14));
0353: dataTypePrecedenceMap.put(new Integer(Types.VARBINARY),
0354: new Integer(15));
0355: dataTypePrecedenceMap.put(new Integer(Types.BINARY),
0356: new Integer(16));
0357: }
0358:
0359: /**
0360: * convertFromIso8601
0361: *
0362: * @param isoDateTime - ISO datetime
0363: * @return a long value
0364: */
0365: public static long convertFromIso8601(String isoDateTime) {
0366: return getCalendar(isoDateTime).getTimeInMillis();
0367: }
0368:
0369: /**
0370: * Create a SQL String to be used with java.sql.PreparedStatement by substituting
0371: * symbols beginning "$" with "?". Binding variable order is preserved.
0372: *
0373: * @pre rawSql does not contain any "?"
0374: * @post processedSql has all the "$attributeName" in the rawSql is replaced with "?"
0375: * where "attributeName" is name/key in attrMap
0376: * @param rawSql
0377: * @param attrMap map of RuntimeAttribute attribute name and RuntimeAttribute.
0378: * @param paramList
0379: * @return preparedStatement string
0380: */
0381: public static String createPreparedStatement(String rawSql,
0382: Map attrMap, List<String> paramList) {
0383: Iterator iter = attrMap.values().iterator();
0384: if (!iter.hasNext()) {
0385: return rawSql;
0386: }
0387:
0388: if (paramList != null) {
0389: List<String> orderedSymbolList = SQLUtils
0390: .getOrderedSymbolList(rawSql, attrMap);
0391: paramList.clear();
0392: paramList.addAll(orderedSymbolList);
0393: }
0394: String processedSql = rawSql;
0395:
0396: do {
0397: RuntimeAttribute attr = (RuntimeAttribute) iter.next();
0398: boolean flag = false;
0399: do {
0400: processedSql = StringUtil.replaceFirst(processedSql,
0401: "?", "\\$" + attr.getAttributeName());
0402: if (!rawSql.equals(processedSql)) {
0403: flag = true;
0404: } else {
0405: flag = false;
0406: }
0407:
0408: rawSql = processedSql;
0409: } while (flag);
0410: } while (iter.hasNext());
0411: mLogger.infoNoloc(mLoc.t(
0412: "PRSR091: >>> Generated PreparedStatement: {0}",
0413: processedSql));
0414: return processedSql;
0415: }
0416:
0417: /**
0418: * Create a SQL String to be used with java.sql.PreparedStatement by substituting
0419: * symbols beginning "$" with "?". Binding variable order is preserved.
0420: *
0421: * @pre rawSql may contain any "?"
0422: * @post processedSql has all the "$attributeName" in the rawSql is replaced with "?"
0423: * where "attributeName" is name/key in attrMap
0424: * @param rawSql
0425: * @param symbols list of Source column names and runtime input (prefixed $).
0426: * @param paramList ordered list of above appearing in the statement
0427: * @return preparedStatement string with binding parameter (?) for each occurence of the symbols.
0428: */
0429: public static String createPreparedStatement(String rawSql,
0430: final List symbols, List<String> orderedSymbols) {
0431: String symbol = null;
0432: boolean noMore = false;
0433: Iterator iter = symbols.iterator();
0434: if (!iter.hasNext()) {
0435: return rawSql;
0436: }
0437:
0438: if (orderedSymbols != null) {
0439: List<String> orderedSymbolList = SQLUtils
0440: .getOrderedSymbolList(rawSql, symbols);
0441: orderedSymbols.clear();
0442: orderedSymbols.addAll(orderedSymbolList);
0443: }
0444: String processedSql = rawSql;
0445:
0446: do {
0447: symbol = (String) iter.next();
0448: if ((symbol != null) && (symbol.startsWith("$"))) {
0449: symbol = "\\" + symbol;
0450: }
0451:
0452: noMore = false;
0453: do {
0454: processedSql = StringUtil.replaceFirst(processedSql,
0455: "?", symbol);
0456: if (!rawSql.equals(processedSql)) {
0457: noMore = true;
0458: } else {
0459: noMore = false;
0460: }
0461:
0462: rawSql = processedSql;
0463: } while (noMore);
0464: } while (iter.hasNext());
0465:
0466: mLogger.infoNoloc(mLoc.t(
0467: "PRSR092: >>> Generated PreparedStatement: \n {0}",
0468: processedSql));
0469: return processedSql;
0470: }
0471:
0472: /**
0473: * returns a Gregorian Calendar of given iso date
0474: *
0475: * @param isodate date in YYYY-MM-DDThh:mm:ss.sTZD format
0476: * @return GregorianCalendar
0477: */
0478: public static GregorianCalendar getCalendar(String isodate) {
0479: // YYYY-MM-DDThh:mm:ss.sTZD
0480: StringTokenizer st = new StringTokenizer(isodate, "-T:.+Z",
0481: true);
0482:
0483: GregorianCalendar calendar = new GregorianCalendar(TimeZone
0484: .getTimeZone("UTC"));
0485: calendar.clear();
0486:
0487: try {
0488: // Year
0489: if (st.hasMoreTokens()) {
0490: int year = Integer.parseInt(st.nextToken());
0491: calendar.set(Calendar.YEAR, year);
0492: } else {
0493: return calendar;
0494: }
0495:
0496: // Month
0497: if (check(st, "-") && (st.hasMoreTokens())) {
0498: int month = Integer.parseInt(st.nextToken()) - 1;
0499: calendar.set(Calendar.MONTH, month);
0500: } else {
0501: return calendar;
0502: }
0503:
0504: // Day
0505: if (check(st, "-") && (st.hasMoreTokens())) {
0506: int day = Integer.parseInt(st.nextToken());
0507: calendar.set(Calendar.DAY_OF_MONTH, day);
0508: } else {
0509: return calendar;
0510: }
0511:
0512: // Hour
0513: if (check(st, "T") && (st.hasMoreTokens())) {
0514: int hour = Integer.parseInt(st.nextToken());
0515: calendar.set(Calendar.HOUR_OF_DAY, hour);
0516: } else {
0517: calendar.set(Calendar.HOUR_OF_DAY, 0);
0518: calendar.set(Calendar.MINUTE, 0);
0519: calendar.set(Calendar.SECOND, 0);
0520: calendar.set(Calendar.MILLISECOND, 0);
0521:
0522: return calendar;
0523: }
0524:
0525: // Minutes
0526: if (check(st, ":") && (st.hasMoreTokens())) {
0527: int minutes = Integer.parseInt(st.nextToken());
0528: calendar.set(Calendar.MINUTE, minutes);
0529: } else {
0530: calendar.set(Calendar.MINUTE, 0);
0531: calendar.set(Calendar.SECOND, 0);
0532: calendar.set(Calendar.MILLISECOND, 0);
0533:
0534: return calendar;
0535: }
0536:
0537: //
0538: // Not mandatory now
0539: //
0540: // Secondes
0541: if (!st.hasMoreTokens()) {
0542: return calendar;
0543: }
0544:
0545: String tok = st.nextToken();
0546:
0547: if (tok.equals(":")) { // seconds
0548:
0549: if (st.hasMoreTokens()) {
0550: int secondes = Integer.parseInt(st.nextToken());
0551: calendar.set(Calendar.SECOND, secondes);
0552:
0553: if (!st.hasMoreTokens()) {
0554: return calendar;
0555: }
0556:
0557: // frac sec
0558: tok = st.nextToken();
0559:
0560: if (tok.equals(".")) {
0561: // bug fixed, thx to Martin Bottcher
0562: String nt = st.nextToken();
0563:
0564: while (nt.length() < 3) {
0565: nt += "0";
0566: }
0567:
0568: nt = nt.substring(0, 3); // Cut trailing chars..
0569:
0570: int millisec = Integer.parseInt(nt);
0571:
0572: // int millisec = Integer.parseInt(st.nextToken()) * 10;
0573: calendar.set(Calendar.MILLISECOND, millisec);
0574:
0575: if (!st.hasMoreTokens()) {
0576: return calendar;
0577: }
0578:
0579: tok = st.nextToken();
0580: } else {
0581: calendar.set(Calendar.MILLISECOND, 0);
0582: }
0583: } else {
0584: throw new RuntimeException("No secondes specified");
0585: }
0586: } else {
0587: calendar.set(Calendar.SECOND, 0);
0588: calendar.set(Calendar.MILLISECOND, 0);
0589: }
0590:
0591: // Timezone
0592: if (!tok.equals("Z")) { // UTC
0593:
0594: if (!(tok.equals("+") || tok.equals("-"))) {
0595: throw new RuntimeException("only Z, + or - allowed");
0596: }
0597:
0598: boolean plus = tok.equals("+");
0599:
0600: if (!st.hasMoreTokens()) {
0601: throw new RuntimeException("Missing hour field");
0602: }
0603:
0604: int tzhour = Integer.parseInt(st.nextToken());
0605: int tzmin;
0606:
0607: if (check(st, ":") && (st.hasMoreTokens())) {
0608: tzmin = Integer.parseInt(st.nextToken());
0609: } else {
0610: throw new RuntimeException("Missing minute field");
0611: }
0612:
0613: // Since the time is represented at UTC (tz 0) format
0614: // we need to convert the local time to UTC timezone
0615: // for example if PST (-8) is 1.00 PM then UTC is 9.00 PM
0616: if (!plus) {
0617: calendar.add(Calendar.HOUR, tzhour);
0618: calendar.add(Calendar.MINUTE, tzmin);
0619: } else {
0620: calendar.add(Calendar.HOUR, -tzhour);
0621: calendar.add(Calendar.MINUTE, -tzmin);
0622: }
0623: }
0624: } catch (NumberFormatException ex) {
0625: throw new RuntimeException("[" + ex.getMessage()
0626: + "] is not an integer");
0627: }
0628:
0629: return calendar;
0630: }
0631:
0632: /**
0633: * Gets datatype resulting from the combination of the given datatypes. When two
0634: * expressions that have different data types are combined by an operator: The data
0635: * type of the resulting value is determined by applying the rules of data type
0636: * precedence to the data types of the input expressions.
0637: *
0638: * @param dataType1 first datatype to evaluate
0639: * @param dataType2 second datatype to evaluate
0640: * @return resulting datatype
0641: */
0642: public static int getResultantDataType(int dataType1, int dataType2) {
0643:
0644: Integer dPrecedence1 = dataTypePrecedenceMap.get(new Integer(
0645: dataType1));
0646: Integer dPrecedence2 = dataTypePrecedenceMap.get(new Integer(
0647: dataType1));
0648:
0649: int retDataType;
0650:
0651: if (dPrecedence1 != null && dPrecedence2 != null) {
0652: retDataType = dPrecedence1.intValue() > dPrecedence2
0653: .intValue() ? dataType1 : dataType2;
0654: } else if (dPrecedence1 != null) {
0655: retDataType = dataType1;
0656: } else if (dPrecedence2 != null) {
0657: retDataType = dataType2;
0658: } else {
0659: retDataType = dataType1;
0660: }
0661:
0662: return retDataType;
0663: }
0664:
0665: /**
0666: * Gets JDBC int type, if any, corresponding to the given SQL datatype string.
0667: *
0668: * @param dataType SQL datatype whose equivalent JDBC int type is sought
0669: * @return java.sql.Types value equivalent to dataType
0670: * @exception IllegalArgumentException if dataType is empty, null, or does not
0671: * correspond to a valid value of java.sql.Types
0672: */
0673: public static int getStdJdbcType(String dataType)
0674: throws IllegalArgumentException {
0675: if (StringUtil.isNullString(dataType)) {
0676: throw new IllegalArgumentException(
0677: "Must supply non-empty String value for dataType.");
0678: }
0679:
0680: Object intStr = SQL_JDBC_MAP.get(dataType.toLowerCase().trim());
0681: if (intStr instanceof String) {
0682: return Integer.parseInt((String) intStr);
0683: }
0684: return JDBCSQL_TYPE_UNDEFINED;
0685: }
0686:
0687: /**
0688: * Gets SQL datatype string, if any, corresponding to the given JDBC int value.
0689: *
0690: * @param dataType SQL datatype whose corresopnding JDBC int type is sought
0691: * @return SQL datatype string corresponding to dataType
0692: * @exception IllegalArgumentException if dataType does not correspond to a known SQL
0693: * datatype string
0694: */
0695: public static String getStdSqlType(int dataType)
0696: throws IllegalArgumentException {
0697: Object o = JDBC_SQL_MAP.get(String.valueOf(dataType));
0698: if (o instanceof String) {
0699: return (String) o;
0700: }
0701: return null;
0702: }
0703:
0704: /**
0705: * Gets List of Strings representing standard SQL datatypes.
0706: *
0707: * @return List of standard SQL datatypes.
0708: */
0709: public static List<String> getStdSqlTypes() {
0710: return new ArrayList<String>(JDBC_SQL_MAP.keySet());
0711: }
0712:
0713: public static List getSupportedDateParts() {
0714: return SUPPORTED_DATE_PARTS;
0715: }
0716:
0717: /**
0718: * getSupportedDBTypes
0719: *
0720: * @param dbName - dbName
0721: * @return int
0722: */
0723: public static int getSupportedDBType(final String dbName) {
0724: String normalizedName = dbName.toUpperCase().trim();
0725: // WT #65169: DB2 eWay returns "Db29" as data type; map over to "DB2V7"
0726: if (normalizedName.startsWith(DBConstants.DB2_STR)) {
0727: normalizedName = DBConstants.DB2V7_STR;
0728: }
0729:
0730: Integer dbType = dbNameIdMap.get(normalizedName);
0731: if (dbType != null) {
0732: return dbType.intValue();
0733: }
0734:
0735: return DBConstants.JDBC;
0736: }
0737:
0738: public static String getSupportedDBType(int dbType) {
0739: String dbName = dbIdNameMap.get(new Integer(dbType));
0740: if (dbName != null) {
0741: return dbName;
0742: }
0743: return DBConstants.JDBC_STR;
0744: }
0745:
0746: /**
0747: * Get List of supported database types.
0748: *
0749: * @return List of Strings representing supported database types
0750: */
0751: public static Set getSupportedDBTypes() {
0752: return dbNameIdMap.keySet();
0753: }
0754:
0755: public static List getSupportedFormatTypes() {
0756: return SUPPORTED_DATE_FORMATS;
0757: }
0758:
0759: public static List getSupportedIntervalTypes() {
0760: return SUPPORTED_INTERVAL_TYPES;
0761: }
0762:
0763: /**
0764: * getSupportedLiteralTypes
0765: *
0766: * @return List
0767: */
0768: public static List getSupportedLiteralTypes() {
0769: return SUPPORTED_LITERAL_JDBC_TYPES;
0770: }
0771:
0772: public static List getSupportedCastTypes() {
0773: return SUPPORTED_CAST_JDBC_TYPES;
0774: }
0775:
0776: /**
0777: * Gets the stdJdbcType attribute of the Database class
0778: *
0779: * @param jdbcType instance of Types
0780: * @return The stdJdbcType value
0781: */
0782: public static synchronized boolean isStdJdbcType(int jdbcType) {
0783: return SQL_JDBC_MAP.containsValue(String.valueOf(jdbcType));
0784: }
0785:
0786: /**
0787: * Make a data string oracle "safe" by escaping single quote marks which are used to
0788: * start and end strings.
0789: *
0790: * @param value to be made oracle String safe.
0791: * @return String which is Oracle safe
0792: */
0793: public static String makeStringOracleSafe(String value) {
0794: if (value.indexOf("'") == -1) {
0795: return value;
0796: // nothing to escape
0797: }
0798:
0799: // the string contains a "'"
0800: StringBuilder newValue = new StringBuilder();
0801: for (int i = 0; i < value.length(); i++) {
0802: char currChar = value.charAt(i);
0803: if (currChar == '\'') {
0804: // Append it twice to escape it
0805: newValue.append(currChar);
0806: }
0807: newValue.append(currChar);
0808: }
0809: return newValue.toString();
0810: }
0811:
0812: public static void populatePreparedStatement(PreparedStatement ps,
0813: Map attrMap, List paramList) throws BaseException {
0814: ListIterator iter = paramList.listIterator();
0815: try {
0816: while (iter.hasNext()) {
0817: String attrName = (String) iter.next();
0818: RuntimeAttribute attr = (RuntimeAttribute) attrMap
0819: .get(attrName);
0820: int index = iter.nextIndex();
0821: int jdbcType = attr.getJdbcType();
0822: Object valueObj = attr.getAttributeObject();
0823: Number numberObj = null;
0824:
0825: switch (jdbcType) {
0826:
0827: case Types.DOUBLE:
0828: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0829: : Double.valueOf(valueObj.toString());
0830: ps.setDouble(index, numberObj.doubleValue());
0831: break;
0832:
0833: case Types.FLOAT:
0834: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0835: : Float.valueOf(valueObj.toString());
0836: ps.setFloat(index, numberObj.floatValue());
0837: break;
0838:
0839: case Types.INTEGER:
0840: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0841: : Integer.valueOf(valueObj.toString());
0842: ps.setInt(index, numberObj.intValue());
0843: break;
0844:
0845: case Types.TIMESTAMP:
0846: long ts = com.sun.sql.framework.jdbc.SQLUtils
0847: .convertFromIso8601(valueObj.toString());
0848: mLogger.infoNoloc(mLoc.t(
0849: "PRSR093: **** timestamp **** {0}", ts));
0850: try {
0851: ps.setTimestamp(index, new java.sql.Timestamp(
0852: ts));
0853: } catch (java.sql.SQLException e) {
0854: ps.setDate(index, new java.sql.Date(ts));
0855:
0856: }
0857: break;
0858:
0859: case Types.CHAR:
0860: case Types.VARCHAR:
0861: default:
0862: ps.setString(index, valueObj.toString());
0863: break;
0864: }
0865: }
0866: } catch (Exception e) {
0867: String details = e.getMessage();
0868: if (StringUtil.isNullString(details)) {
0869: details = e.toString();
0870: }
0871: mLogger.errorNoloc(mLoc.t("PRSR094: details{0}",
0872: LOG_CATEGORY), e);
0873: throw new BaseException(details, e);
0874: }
0875: }
0876:
0877: public static Map getRuntimeInputNameValueMap(Map attribMap) {
0878: Map<String, Object> values = new HashMap<String, Object>();
0879: RuntimeAttribute ra = null;
0880: int jdbcType = 0;
0881: Object valueObj = null;
0882: Number numberObj = null;
0883:
0884: if (attribMap != null) {
0885: Set keys = attribMap.keySet();
0886: Iterator itr = keys.iterator();
0887: String name = null;
0888:
0889: while (itr.hasNext()) {
0890: name = (String) itr.next();
0891: ra = (RuntimeAttribute) attribMap.get(name);
0892: jdbcType = ra.getJdbcType();
0893: valueObj = ra.getAttributeObject();
0894: numberObj = null;
0895:
0896: switch (jdbcType) {
0897:
0898: case Types.DOUBLE:
0899: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0900: : Double.valueOf(valueObj.toString());
0901: values.put(name, numberObj);
0902: break;
0903:
0904: case Types.FLOAT:
0905: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0906: : Float.valueOf(valueObj.toString());
0907: values.put(name, numberObj);
0908: break;
0909:
0910: case Types.INTEGER:
0911: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0912: : Integer.valueOf(valueObj.toString());
0913: values.put(name, numberObj);
0914: break;
0915:
0916: case Types.TIMESTAMP:
0917: long ts = com.sun.sql.framework.jdbc.SQLUtils
0918: .convertFromIso8601(valueObj.toString());
0919: mLogger.infoNoloc(mLoc.t(
0920: "PRSR095: **** timestamp **** {0}", ts));
0921: try {
0922: values.put(name, new java.sql.Timestamp(ts));
0923: } catch (Exception e) {
0924: values.put(name, new java.sql.Date(ts));
0925: }
0926: break;
0927:
0928: case Types.CHAR:
0929: case Types.VARCHAR:
0930: default:
0931: values.put(name, valueObj.toString());
0932: break;
0933: }
0934: }
0935: }
0936:
0937: return values;
0938: }
0939:
0940: public static void setAttributeValue(PreparedStatement ps,
0941: int index, int jdbcType, Object valueObj)
0942: throws BaseException {
0943: Number numberObj = null;
0944:
0945: try {
0946: switch (jdbcType) {
0947:
0948: case Types.DOUBLE:
0949: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0950: : Double.valueOf(valueObj.toString());
0951: ps.setDouble(index, numberObj.doubleValue());
0952: break;
0953:
0954: case Types.FLOAT:
0955: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0956: : Float.valueOf(valueObj.toString());
0957: ps.setFloat(index, numberObj.floatValue());
0958: break;
0959:
0960: case Types.INTEGER:
0961: numberObj = (valueObj instanceof Number) ? (Number) valueObj
0962: : Integer.valueOf(valueObj.toString());
0963: ps.setInt(index, numberObj.intValue());
0964: break;
0965:
0966: case Types.TIMESTAMP:
0967: long ts = com.sun.sql.framework.jdbc.SQLUtils
0968: .convertFromIso8601(valueObj.toString());
0969: mLogger.infoNoloc(mLoc.t(
0970: "PRSR096: **** timestamp **** {0}", ts));
0971: try {
0972: ps.setTimestamp(index, new java.sql.Timestamp(ts));
0973: } catch (java.sql.SQLException e) {
0974: ps.setDate(index, new java.sql.Date(ts));
0975: }
0976: break;
0977:
0978: case Types.CHAR:
0979: case Types.VARCHAR:
0980: default:
0981: ps.setString(index, valueObj.toString());
0982: break;
0983: }
0984: } catch (Exception e) {
0985: String details = e.getMessage();
0986: if (StringUtil.isNullString(details)) {
0987: details = e.toString();
0988: }
0989: mLogger.errorNoloc(mLoc.t("PRSR097: details{0}",
0990: LOG_CATEGORY), e);
0991: throw new BaseException(details, e);
0992: }
0993:
0994: }
0995:
0996: public static boolean isPrecisionRequired(int jdbcType) {
0997: switch (jdbcType) {
0998: case Types.BIT:
0999: case Types.BIGINT:
1000: case Types.BOOLEAN:
1001: case Types.INTEGER:
1002: case Types.SMALLINT:
1003: case Types.TINYINT:
1004: case Types.FLOAT:
1005: case Types.REAL:
1006: case Types.DOUBLE:
1007: case Types.DATE:
1008: case Types.TIME:
1009: case Types.TIMESTAMP:
1010: case Types.JAVA_OBJECT:
1011: case Types.LONGVARCHAR:
1012: case Types.LONGVARBINARY:
1013: case Types.BLOB:
1014: case Types.CLOB:
1015: case Types.ARRAY:
1016: case Types.STRUCT:
1017: case Types.DISTINCT:
1018: case Types.REF:
1019: case Types.DATALINK:
1020: return false;
1021:
1022: default:
1023: return true;
1024: }
1025: }
1026:
1027: public static boolean isNumeric(int jdbcType) {
1028: switch (jdbcType) {
1029: case Types.BIT:
1030: case Types.BIGINT:
1031: case Types.BOOLEAN:
1032: case Types.INTEGER:
1033: case Types.SMALLINT:
1034: case Types.TINYINT:
1035: case Types.FLOAT:
1036: case Types.REAL:
1037: case Types.DOUBLE:
1038: return true;
1039:
1040: default:
1041: return false;
1042: }
1043: }
1044:
1045: public static boolean isScaleRequired(int type) {
1046: switch (type) {
1047: case java.sql.Types.DECIMAL:
1048: case java.sql.Types.NUMERIC:
1049: return true;
1050: default:
1051: return false;
1052: }
1053: }
1054:
1055: public static boolean isBinary(int jdbcType) {
1056: switch (jdbcType) {
1057: case Types.BINARY:
1058: case Types.VARBINARY:
1059: case Types.LONGVARBINARY:
1060: return true;
1061: default:
1062: return false;
1063: }
1064: }
1065:
1066: private static boolean check(StringTokenizer st, String token)
1067: throws RuntimeException {
1068: try {
1069: if (st.nextToken().equals(token)) {
1070: return true;
1071: }
1072: throw new RuntimeException("Missing [" + token + "]");
1073: } catch (NoSuchElementException ex) {
1074: return false;
1075: }
1076: }
1077:
1078: /**
1079: * Returns the list of the Sumbol Names in "attrMap" available in "rawSql" in the
1080: * order of appearance.
1081: *
1082: * @param rawSql
1083: * @param attrMap
1084: * @return
1085: */
1086: private static List<String> getOrderedSymbolList(String rawSql,
1087: final List symbolList) {
1088: Map<Integer, String> map = new TreeMap<Integer, String>();
1089: String symbolName = null;
1090:
1091: if ((rawSql != null) && (symbolList != null)) {
1092: Iterator iter = symbolList.iterator();
1093:
1094: int pos = -1;
1095: int indexFrom = 0;
1096:
1097: while (iter.hasNext()) {
1098: symbolName = (String) iter.next();
1099: indexFrom = 0;
1100: boolean morePresent = true;
1101: while (morePresent) {
1102: pos = rawSql.indexOf(symbolName, indexFrom);
1103: if (pos >= 0) {
1104: map.put(new Integer(pos), symbolName);
1105: indexFrom = pos + symbolName.length();
1106: } else {
1107: morePresent = false;
1108: }
1109: }
1110: }
1111: }
1112:
1113: return new ArrayList<String>(map.values());
1114: }
1115:
1116: /**
1117: * Returns the list of the Sumbol Names in "attrMap" available in "rawSql" in the
1118: * order of appearance.
1119: *
1120: * @param rawSql
1121: * @param attrMap
1122: * @return
1123: */
1124: private static List<String> getOrderedSymbolList(String rawSql,
1125: Map attrMap) {
1126: Map<Integer, String> map = new TreeMap<Integer, String>();
1127:
1128: if ((rawSql != null) && (attrMap != null)) {
1129: Iterator iter = attrMap.values().iterator();
1130:
1131: RuntimeAttribute attr = null;
1132:
1133: int pos = -1;
1134: int indexFrom = 0;
1135:
1136: while (iter.hasNext()) {
1137: attr = (RuntimeAttribute) iter.next();
1138: indexFrom = 0;
1139: boolean morePresent = true;
1140: while (morePresent) {
1141: pos = rawSql.indexOf("$" + attr.getAttributeName(),
1142: indexFrom);
1143: if (pos >= 0) {
1144: map.put(new Integer(pos), attr
1145: .getAttributeName());
1146: indexFrom = pos
1147: + attr.getAttributeName().length();
1148: } else {
1149: morePresent = false;
1150: }
1151: }
1152: }
1153: }
1154:
1155: return new ArrayList<String>(map.values());
1156: }
1157:
1158: /* Private no-arg constructor; this class should not be instantiable. */
1159: private SQLUtils() {
1160: }
1161: }
|