0001: /* ====================================================================
0002: * The Jcorporate Apache Style Software License, Version 1.2 05-07-2002
0003: *
0004: * Copyright (c) 1995-2002 Jcorporate Ltd. All rights reserved.
0005: *
0006: * Redistribution and use in source and binary forms, with or without
0007: * modification, are permitted provided that the following conditions
0008: * are met:
0009: *
0010: * 1. Redistributions of source code must retain the above copyright
0011: * notice, this list of conditions and the following disclaimer.
0012: *
0013: * 2. Redistributions in binary form must reproduce the above copyright
0014: * notice, this list of conditions and the following disclaimer in
0015: * the documentation and/or other materials provided with the
0016: * distribution.
0017: *
0018: * 3. The end-user documentation included with the redistribution,
0019: * if any, must include the following acknowledgment:
0020: * "This product includes software developed by Jcorporate Ltd.
0021: * (http://www.jcorporate.com/)."
0022: * Alternately, this acknowledgment may appear in the software itself,
0023: * if and wherever such third-party acknowledgments normally appear.
0024: *
0025: * 4. "Jcorporate" and product names such as "Expresso" must
0026: * not be used to endorse or promote products derived from this
0027: * software without prior written permission. For written permission,
0028: * please contact info@jcorporate.com.
0029: *
0030: * 5. Products derived from this software may not be called "Expresso",
0031: * or other Jcorporate product names; nor may "Expresso" or other
0032: * Jcorporate product names appear in their name, without prior
0033: * written permission of Jcorporate Ltd.
0034: *
0035: * 6. No product derived from this software may compete in the same
0036: * market space, i.e. framework, without prior written permission
0037: * of Jcorporate Ltd. For written permission, please contact
0038: * partners@jcorporate.com.
0039: *
0040: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
0041: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
0042: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
0043: * DISCLAIMED. IN NO EVENT SHALL JCORPORATE LTD OR ITS CONTRIBUTORS
0044: * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
0045: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
0046: * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
0047: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
0048: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
0049: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
0050: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
0051: * SUCH DAMAGE.
0052: * ====================================================================
0053: *
0054: * This software consists of voluntary contributions made by many
0055: * individuals on behalf of the Jcorporate Ltd. Contributions back
0056: * to the project(s) are encouraged when you make modifications.
0057: * Please send them to support@jcorporate.com. For more information
0058: * on Jcorporate Ltd. and its products, please see
0059: * <http://www.jcorporate.com/>.
0060: *
0061: * Portions of this software are based upon other open source
0062: * products and are subject to their respective licenses.
0063: */
0064:
0065: package com.jcorporate.expresso.core.dataobjects.jdbc;
0066:
0067: import com.jcorporate.expresso.core.dataobjects.DataException;
0068: import com.jcorporate.expresso.core.dataobjects.DataFieldMetaData;
0069: import com.jcorporate.expresso.core.dataobjects.DataObject;
0070: import com.jcorporate.expresso.core.db.DBConnection;
0071: import com.jcorporate.expresso.core.db.DBException;
0072: import com.jcorporate.expresso.core.db.TypeMapper;
0073: import com.jcorporate.expresso.core.dbobj.DBField;
0074: import com.jcorporate.expresso.core.misc.ConfigJdbc;
0075: import com.jcorporate.expresso.core.misc.ConfigManager;
0076: import com.jcorporate.expresso.core.misc.ConfigurationException;
0077: import com.jcorporate.expresso.core.misc.StringUtil;
0078: import com.jcorporate.expresso.core.security.filters.Filter;
0079: import com.jcorporate.expresso.kernel.util.FastStringBuffer;
0080: import org.apache.commons.collections.LRUMap;
0081: import org.apache.log4j.Logger;
0082:
0083: import java.sql.CallableStatement;
0084: import java.sql.SQLException;
0085: import java.text.ParseException;
0086: import java.text.SimpleDateFormat;
0087: import java.util.Collections;
0088: import java.util.Iterator;
0089: import java.util.Map;
0090:
0091: /**
0092: * This class provides utility functions that are common across the <code>jdbc.*</code>
0093: * package classes. This class is normally not accessed except through other
0094: * classes in the jdbc package.
0095: *
0096: * @author Michael Rimov
0097: * @since Expresso 5.0
0098: */
0099:
0100: public class JDBCUtil extends DataException {
0101: static private JDBCUtil theInstance = new JDBCUtil();
0102: static private Logger log = Logger
0103: .getLogger("expresso.core.dataobjects.jdbc.JDBCUtil");
0104:
0105: private FieldRangeParser rangeParser = new FieldRangeParser();
0106: public static final String DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
0107: public static final String DATE_FORMAT = "yyyy-MM-dd";
0108: public static final String TIME_FORMAT = "HH:mm:ss";
0109:
0110: private class DateReturnFormat {
0111: String returnValue;
0112: boolean foundConvertFunction;
0113: }
0114:
0115: /**
0116: * It takes quite a bit of horsepower to create a SimpleDateFormat object.
0117: * By caching the most recent 30 patterns, we can save a lot of CPU time.
0118: */
0119: static private Map dateConvertFormatMap = Collections
0120: .synchronizedMap(new LRUMap(30));
0121:
0122: protected JDBCUtil() {
0123: }
0124:
0125: static public JDBCUtil getInstance() {
0126: return theInstance;
0127: }
0128:
0129: /**
0130: * Given the value of a date/time or date/time field, return the value formatted
0131: * as appropriate for the current DBMS. Can be configured using property
0132: * file values.
0133: *
0134: * @param fieldName java.lang.String The value for the date/time field.
0135: * @param theObject the <code>DataObject</code> that the date field is coming
0136: * from.
0137: * @return java.lang.String The formatted date time, ready for use in the DBMS
0138: */
0139: public String formatDateTime(DataObject theObject, String fieldName)
0140: throws DataException {
0141: return formatDateTime(theObject, fieldName, true);
0142: }
0143:
0144: /**
0145: * Given the value of a date/time or datetime field, return the value formatted
0146: * as appropriate for the current DBMS. Can be configured using expresso-config
0147: * file values.
0148: *
0149: * @param fieldName java.lang.String The value for the date/time field.
0150: * @param theObject the <code>DataObject</code> that the date field is coming
0151: * from.
0152: * @param surroundWithQuotes set to <b>true</b> if you want the returned value
0153: * to be surrounded with single quotes, such as for a standard JDBC SELECT statement.
0154: * Use false if you want to use it, for example, in a PreparedStatement
0155: * @return java.lang.String The formatted date time, ready for use in the DBMS
0156: */
0157: public String formatDateTime(DataObject theObject,
0158: String fieldName, boolean surroundWithQuotes)
0159: throws DataException {
0160: DateReturnFormat df = formatDateTimeInternal(theObject,
0161: fieldName);
0162: String oneValue = df.returnValue;
0163:
0164: if (oneValue == null) {
0165: return null;
0166: }
0167:
0168: if (surroundWithQuotes && !df.foundConvertFunction) {
0169: FastStringBuffer fsb = FastStringBuffer.getInstance();
0170: String returnValue = null;
0171: try {
0172: fsb.append("'");
0173: fsb.append(oneValue);
0174: fsb.append("'");
0175: returnValue = fsb.toString();
0176: } finally {
0177: fsb.release();
0178: fsb = null;
0179: }
0180: return returnValue;
0181: } else {
0182: return oneValue;
0183: }
0184: }
0185:
0186: /**
0187: * Get a SimpleDateFormat object that is cached. Make sure you have
0188: * dateConvertFormatMap already locked before calling this function or you'll
0189: * have a race condition.
0190: *
0191: * @param pattern the format pattern to look up
0192: * @return an instantiated SimpleDateFormat object. SimpleDateFormat is NOT
0193: * threadsafe, so make sure you do your parsing while still in the synchronized
0194: * block. Perhaps in the future a keyed Object pool will be better.
0195: */
0196: protected SimpleDateFormat getSimpleDateFormat(String pattern) {
0197: SimpleDateFormat aFormat = null;
0198: aFormat = (SimpleDateFormat) dateConvertFormatMap.get(pattern);
0199:
0200: if (aFormat == null) {
0201: aFormat = new SimpleDateFormat(pattern);
0202: dateConvertFormatMap.put(pattern, aFormat);
0203: }
0204:
0205: return aFormat;
0206: }
0207:
0208: /**
0209: * Helper function that helps convert any string values from the database's
0210: * format
0211: *
0212: * @param theObject the source data object
0213: * @param fieldName the name of the field
0214: * @return java.util.Date
0215: * @throws DataException upon error (possibly conversion error)
0216: */
0217: public java.util.Date getDateField(DataObject theObject,
0218: String fieldName) throws DataException {
0219: DataFieldMetaData oneField = theObject
0220: .getFieldMetaData(fieldName);
0221:
0222: Object o = theObject.get(oneField.getName());
0223:
0224: java.util.Date returnDate = null;
0225: String convertFormat = null;
0226: ConfigJdbc myConfig = null;
0227: String strVal = null;
0228:
0229: if (o == null) {
0230: return null;
0231: } else if (o instanceof String) {
0232: strVal = (String) o;
0233: } else if (o instanceof java.util.Date) {
0234: returnDate = (java.util.Date) o;
0235: } else {
0236: strVal = o.toString();
0237: }
0238:
0239: if (strVal != null && strVal.length() == 0) {
0240: return null;
0241: }
0242:
0243: if (returnDate == null) {
0244:
0245: try {
0246: myConfig = ConfigManager.getJdbcRequired(theObject
0247: .getMappedDataContext());
0248: } catch (ConfigurationException ce) {
0249: throw new DataException(ce);
0250: }
0251: if (oneField.isDateOnlyType()) {
0252: if (!StringUtil.notNull(myConfig.getDateSelectFormat())
0253: .equals("")) {
0254: convertFormat = myConfig.getDateSelectFormat();
0255: } else {
0256: convertFormat = DATE_FORMAT;
0257: }
0258: } else if (oneField.isDateTimeType()) {
0259: if (!StringUtil.notNull(
0260: myConfig.getDateTimeSelectFormat()).equals("")) {
0261: convertFormat = myConfig.getDateTimeSelectFormat();
0262: } else {
0263: convertFormat = DATETIME_FORMAT;
0264: }
0265: } else if (oneField.isTimeType()) {
0266: if (!StringUtil.notNull(myConfig.getTimeSelectFormat())
0267: .equals("")) {
0268: convertFormat = myConfig.getTimeSelectFormat();
0269: } else {
0270: convertFormat = TIME_FORMAT;
0271: }
0272: } else {
0273: throw new DataException(
0274: "Field '"
0275: + fieldName
0276: + "' is not a date, datetime or time - it is a "
0277: + oneField.getTypeString()
0278: + ", which cannot be retrieved as a Date type");
0279: }
0280: try {
0281: //We have to lock the formatter because we don't want somebody
0282: //else to call parse while we're working on the value.
0283: SimpleDateFormat formatter = getSimpleDateFormat(convertFormat);
0284: synchronized (formatter) {
0285: returnDate = formatter.parse(strVal);
0286: }
0287: } catch (ParseException pe) {
0288: String message = "(" + getClass().getName()
0289: + ") Unable to parse a date value from field '"
0290: + fieldName + "' which contained '" + strVal
0291: + "' using pattern '" + convertFormat + "'";
0292: log.error(message, pe);
0293: throw new DataException(message, pe);
0294: }
0295: if (returnDate == null) {
0296: throw new DataException("(" + getClass().getName()
0297: + ") Unable to get date value from field "
0298: + fieldName + ", value was " + strVal);
0299: }
0300:
0301: }
0302:
0303: return returnDate;
0304: }
0305:
0306: /**
0307: * This class combines the old getFieldDate() with quoteIfNeeded() to format
0308: * the Date-Time fields appropriately for storage. This probably could use
0309: * some serious optimization since it looks like at first glance it does
0310: * the same operation twice. Any volunteers? :)
0311: *
0312: * @param theObject The object that contains the data to froatm
0313: * @param fieldName the name of the field to format
0314: * @return a DateReturnFormat object.
0315: * @throws DataException upon error
0316: */
0317: protected DateReturnFormat formatDateTimeInternal(
0318: DataObject theObject, String fieldName)
0319: throws DataException {
0320:
0321: DateReturnFormat returnFormat = new DateReturnFormat();
0322: DataFieldMetaData oneField = theObject
0323: .getFieldMetaData(fieldName);
0324: java.util.Date returnDate = this .getDateField(theObject,
0325: fieldName);
0326: if (returnDate == null) {
0327: returnFormat.returnValue = "";
0328: return returnFormat;
0329: }
0330: java.util.Date originalDate = (java.util.Date) returnDate
0331: .clone();
0332: String convertFormat = null;
0333: ConfigJdbc myConfig = null;
0334: String strVal = null;
0335: convertFormat = "";
0336: String convertFunction = "";
0337: try {
0338: myConfig = ConfigManager.getJdbcRequired(theObject
0339: .getMappedDataContext());
0340: } catch (ConfigurationException ce) {
0341: throw new DataException(ce);
0342: }
0343:
0344: try {
0345: if (myConfig == null) {
0346: myConfig = ConfigManager.getJdbcRequired(theObject
0347: .getMappedDataContext());
0348: }
0349:
0350: if (oneField.getTypeString().equalsIgnoreCase("date")) {
0351: convertFormat = myConfig.getDateUpdateFormat();
0352: convertFunction = myConfig.getDateUpdateFunction();
0353:
0354: if (StringUtil.notNull(convertFormat).length() == 0
0355: && StringUtil.notNull(convertFunction).length() == 0) {
0356: convertFormat = "yyyy-MM-dd";
0357: }
0358: } else if (oneField.getTypeString().equalsIgnoreCase(
0359: "datetime")
0360: || oneField.getTypeString().equalsIgnoreCase(
0361: DBField.TIMESTAMP_TYPE)) {
0362: convertFormat = myConfig.getDateTimeUpdateFormat();
0363: convertFunction = myConfig.getDateTimeUpdateFunction();
0364: if (StringUtil.notNull(convertFormat).length() == 0
0365: && StringUtil.notNull(convertFunction).length() == 0) {
0366: convertFormat = "yyyy-MM-dd HH:mm:ss";
0367: }
0368: } else if (oneField.getTypeString()
0369: .equalsIgnoreCase("time")) {
0370: convertFormat = myConfig.getTimeUpdateFormat();
0371: convertFunction = myConfig.getTimeUpdateFunction();
0372: if (StringUtil.notNull(convertFormat).length() == 0
0373: && StringUtil.notNull(convertFunction).length() == 0) {
0374: convertFormat = "HH:mm:ss";
0375: }
0376: } else {
0377: throw new DataException(
0378: "Field '"
0379: + fieldName
0380: + "' is not a date, datetime or time - it is a "
0381: + oneField.getTypeString()
0382: + ", which cannot be formatted "
0383: + "as a Date/Time type");
0384: }
0385: } catch (ConfigurationException ce) {
0386: throw new DataException(ce);
0387: }
0388:
0389: convertFormat = StringUtil.notNull(convertFormat);
0390: convertFunction = StringUtil.notNull(convertFunction);
0391:
0392: String returnValue = null;
0393:
0394: /* If no format was specified, don't change the existing field */
0395: if (convertFormat == null || convertFormat.length() == 0) {
0396: if (strVal == null) {
0397: strVal = returnDate.toString();
0398: }
0399: if (convertFunction.length() > 0) {
0400: returnFormat.foundConvertFunction = true;
0401: returnFormat.returnValue = StringUtil.replace(
0402: convertFunction, "%s", strVal);
0403: return returnFormat;
0404: } else {
0405: returnFormat.returnValue = strVal;
0406: return returnFormat;
0407: }
0408: }
0409:
0410: SimpleDateFormat formatter = getSimpleDateFormat(convertFormat);
0411: synchronized (formatter) {
0412: returnValue = formatter.format(originalDate);
0413: }
0414:
0415: if (convertFunction == null || convertFunction.length() == 0) {
0416: // no conversion
0417: } else {
0418: returnFormat.foundConvertFunction = true;
0419: if (returnValue != null) {
0420: returnValue = StringUtil.replace(convertFunction, "%s",
0421: returnValue);
0422: }
0423: }
0424:
0425: if (returnValue == null) {
0426: throw new DataException("(" + getClass().getName()
0427: + ") Unable to format date value from field "
0428: + fieldName + ", value was " + strVal);
0429: }
0430:
0431: returnFormat.returnValue = returnValue;
0432: return returnFormat;
0433: }
0434:
0435: /**
0436: * Build and return a string consisting of an SQL 'where' clause
0437: * using the current field values as criteria for the search. See
0438: * setCustomWhereClause for information on specifying a more complex where clause.
0439: *
0440: * @param criteria the JDBCDataObject to build from
0441: * @param useAllFields True if all fields are to be used,
0442: * false for only key fields
0443: * @return The where clause to use in a query.
0444: */
0445: public String buildWhereClause(JDBCDataObject criteria,
0446: boolean useAllFields) throws DataException {
0447: FastStringBuffer fsb = FastStringBuffer.getInstance();
0448: try {
0449: return buildWhereClauseBuffer(criteria, useAllFields, fsb)
0450: .toString();
0451: } finally {
0452: fsb.release();
0453: }
0454: } /* buildWhereClause(boolean) */
0455:
0456: /**
0457: * Build and return a FastStringBuffer ring consisting of an SQL 'where' clause
0458: * using the current field values as criteria for the search. See
0459: * setCustomWhereClause for information on specifying a more complex where clause.
0460: *
0461: * @param criteria the JDBCDataObject to build from
0462: * @param useAllFields True if all fields are to be used,
0463: * false for only key fields
0464: * @param allocatedBuffer - An already allocated FastStringBuffer to fill out.
0465: * This allows for compatability with, for example, object pools.
0466: * @return A FastStringBuffer containing the "where" clause for the SQL statement
0467: * @throws DataException upon error
0468: */
0469: public FastStringBuffer buildWhereClauseBuffer(
0470: JDBCDataObject criteria, boolean useAllFields,
0471: FastStringBuffer allocatedBuffer) throws DataException {
0472: Iterator fieldsToUse = null;
0473: FastStringBuffer myStatement = allocatedBuffer;
0474:
0475: if (useAllFields) {
0476: fieldsToUse = criteria.getMetaData().getFieldListArray()
0477: .iterator();
0478: } else {
0479: fieldsToUse = criteria.getMetaData().getKeyFieldListArray()
0480: .iterator();
0481: }
0482:
0483: /* Now go thru each field - if it is non-empty, add it's criteria */
0484:
0485: /* to the where clause. If it is empty, just skip to the next one */
0486: boolean addWhere = true;
0487: boolean addAnd = false;
0488: DataFieldMetaData oneField = null;
0489: String oneFieldName = null;
0490: String oneFieldValue = null;
0491: boolean skipText = false;
0492: boolean postgresql = false;
0493:
0494: try {
0495: ConfigJdbc myConfig = ConfigManager
0496: .getJdbcRequired(criteria.getMappedDataContext());
0497: skipText = myConfig.skipText();
0498: //We have to do this because postgres won't be smart enough to
0499: //cast floating point literals to truly a floating point value. :(
0500: if ("org.postgresql.Driver".equals(myConfig.getDriver())) {
0501: postgresql = true;
0502: }
0503: } catch (ConfigurationException ce) {
0504: throw new DataException(ce);
0505: }
0506:
0507: boolean skipField = false;
0508:
0509: while (fieldsToUse.hasNext()) {
0510: oneFieldName = (String) fieldsToUse.next();
0511: oneField = criteria.getFieldMetaData(oneFieldName);
0512: skipField = false;
0513:
0514: if (oneField.isVirtual()) {
0515: skipField = true;
0516: }
0517:
0518: try {
0519: oneFieldValue = StringUtil.notNull(criteria
0520: .getDataField(oneField.getName()).asString());
0521: } catch (DBException ex) {
0522: if (ex instanceof DataException) {
0523: throw ((DataException) ex);
0524: } else {
0525: throw new DataException(
0526: "Error getting field value", ex);
0527: }
0528: }
0529:
0530: String rangeString = rangeParser
0531: .denotesRange(oneFieldValue);
0532:
0533: if (!oneFieldValue.equals("")) {
0534: if (oneFieldValue.trim().equalsIgnoreCase("is null")
0535: || oneFieldValue.trim().equalsIgnoreCase(
0536: "is not null")) {
0537: ;
0538: } else {
0539: oneFieldValue = quoteIfNeeded(criteria,
0540: oneFieldName, rangeString);
0541: }
0542: }
0543: if (oneFieldValue == null) {
0544: skipField = true;
0545: }
0546: if (oneFieldValue.trim().equals("\'\'")) {
0547: skipField = true;
0548: }
0549:
0550: //
0551: //There was a TODO item asking about why \r and \n's aren't allowed
0552: //in text fiels here. and the reason is that the JDBC parsers expect
0553: //all quoted fields to be without crlf in them. in all honesty,
0554: //you shouldn't be searching for a text field anyway since it's arbitrary
0555: //length and you SERIOUSLY dog performance by doing it that way.
0556: //If you must search for a text field, you can deal with cr/lf's by
0557: //using a prepared statement such as the LOBSupport class.
0558: //
0559: if (oneField.getTypeString().equalsIgnoreCase("text")) {
0560: if (skipText) {
0561: skipField = true;
0562:
0563: if (log.isDebugEnabled()) {
0564: log.debug("Skipping criteria in text field '"
0565: + oneFieldName + "'");
0566: }
0567: } else {
0568: if (oneFieldValue.indexOf("\n") > 0) {
0569: oneFieldValue = StringUtil.replace(
0570: oneFieldValue, "\n", "");
0571: }
0572: if (oneFieldValue.indexOf("\r") > 0) {
0573: oneFieldValue = StringUtil.replace(
0574: oneFieldValue, "\r", "");
0575: }
0576: if (oneFieldValue.equals("\'\'")) {
0577: skipField = true;
0578: }
0579: }
0580: } /* if text field */
0581:
0582: if (oneFieldValue.trim().equals("")) {
0583: skipField = true;
0584: }
0585: if (!skipField) {
0586: // check to see if the field value is valid (protects agains sql injection)
0587: try {
0588: String unalteredFieldValue = criteria.getDataField(
0589: oneField.getName()).asString();
0590: if (rangeString != null) {
0591: boolean valid = rangeParser.isValidRange(
0592: criteria.getFieldMetaData(oneField
0593: .getName()),
0594: unalteredFieldValue);
0595: if (!valid) {
0596: throw new DataException(
0597: "Invalid field range value: "
0598: + unalteredFieldValue);
0599: }
0600: } else if (containsWildCards(criteria,
0601: oneFieldValue)) {
0602: Object origValue = criteria.getDataField(
0603: oneFieldName).getValue();
0604:
0605: String[] wildcards = null;
0606: wildcards = (String[]) criteria
0607: .getConnectionPool().getWildCardsList()
0608: .toArray(new String[0]);
0609: Filter filter = new Filter(wildcards, wildcards);
0610: String valueWithoutWildCards = filter
0611: .stripFilter(unalteredFieldValue);
0612: // if the value without wildcards is empty, then we know the field is valid
0613: if (!valueWithoutWildCards.equals("")) {
0614: criteria.getDataField(oneFieldName)
0615: .setValue(valueWithoutWildCards);
0616: criteria.getDataField(oneFieldName)
0617: .checkValue();
0618: criteria.getDataField(oneFieldName)
0619: .setValue(origValue);
0620: }
0621: } else {
0622: criteria.getDataField(oneFieldName)
0623: .checkValue();
0624: }
0625: } catch (DBException ex) {
0626: if (ex instanceof DataException) {
0627: throw ((DataException) ex);
0628: } else {
0629: throw new DataException(
0630: "Error getting field value", ex);
0631: }
0632: }
0633:
0634: if (addWhere) {
0635: myStatement.append(" WHERE ");
0636: addWhere = false;
0637: }
0638: if (addAnd) {
0639: myStatement.append(" AND ");
0640: }
0641: if (containsWildCards(criteria, oneFieldValue)) {
0642: if (criteria.caseSensitiveQuery) {
0643: myStatement.append(oneFieldName);
0644: myStatement.append(" LIKE ");
0645: myStatement.append(oneFieldValue);
0646: } else {
0647: myStatement.append("UPPER(");
0648: myStatement.append(oneFieldName);
0649: myStatement.append(") LIKE ");
0650: myStatement.append(oneFieldValue.toUpperCase());
0651: }
0652: } else if (rangeString != null) {
0653: myStatement.append(oneFieldName);
0654: String theValue = rangeString + " " + oneFieldValue;
0655: boolean valid = rangeParser.isValidRange(criteria
0656: .getFieldMetaData(oneField.getName()),
0657: theValue);
0658: if (!valid) {
0659: throw new DataException(
0660: "Invalid field range value: "
0661: + theValue);
0662: }
0663:
0664: myStatement.append(" ");
0665: myStatement.append(theValue);
0666: } else if ((oneFieldValue.trim()
0667: .equalsIgnoreCase("is null"))
0668: || (oneFieldValue.trim()
0669: .equalsIgnoreCase("is not null"))) {
0670: myStatement.append(oneFieldName);
0671: myStatement.append(" ");
0672: myStatement.append(oneFieldValue.trim());
0673: } else if (oneField.isDateType()) {
0674: myStatement.append(oneFieldName);
0675: myStatement.append(" = ");
0676: Object tmpData = null;
0677: try {
0678: tmpData = criteria.getDataField(oneFieldName)
0679: .getValue();
0680: } catch (DBException ex) {
0681: if (ex instanceof DataException) {
0682: throw ((DataException) ex);
0683: } else {
0684: throw new DataException(
0685: "Error getting field value", ex);
0686: }
0687: }
0688: String data;
0689: //
0690: //FIXME allow for appropriate support of other data types.
0691: //
0692: if (tmpData == null) {
0693: data = null;
0694: } else if (tmpData instanceof String) {
0695: data = (String) tmpData;
0696: } else {
0697: data = tmpData.toString();
0698: }
0699:
0700: if (data == null || (data.length() == 0)) {
0701: myStatement.append("null");
0702: } else {
0703: myStatement.append(JDBCUtil.getInstance()
0704: .formatDateTime(criteria,
0705: oneField.getName()));
0706: }
0707: } else if (oneField.isFloatingPointType()) {
0708: //Floating point types have to be searched within a certain
0709: //precision. Thus we compare the ABS to the less than
0710: //the field precision.
0711: myStatement.append("ABS(");
0712: myStatement.append(oneFieldName);
0713: myStatement.append(" - ");
0714: if (postgresql) {
0715: myStatement.append(" CAST (");
0716: }
0717: myStatement.append(oneFieldValue);
0718: if (postgresql) {
0719: myStatement.append(" as FLOAT)");
0720: }
0721: myStatement.append(") <");
0722: if (postgresql) {
0723: myStatement.append(" CAST (");
0724: }
0725: myStatement.append(".");
0726: int precision = oneField.getPrecision();
0727: if (precision == 0) {
0728: precision = 1;
0729: }
0730: for (int i = 0; i < oneField.getPrecision() - 1; i++) {
0731: myStatement.append("0");
0732: }
0733: myStatement.append("1");
0734: if (postgresql) {
0735: myStatement.append(" as FLOAT)");
0736: }
0737:
0738: } else {
0739: if (oneField.isQuotedTextType()
0740: && !criteria.caseSensitiveQuery) {
0741: myStatement.append("UPPER(");
0742: myStatement.append(oneFieldName);
0743: myStatement.append(") = ");
0744: myStatement.append(oneFieldValue.toUpperCase());
0745: } else {
0746: myStatement.append(oneFieldName);
0747: myStatement.append(" = ");
0748: myStatement.append(oneFieldValue);
0749: }
0750: }
0751:
0752: addAnd = true;
0753: }
0754:
0755: /* if field is not skipped for some reason */
0756: }
0757: /* for each field */
0758: if (log.isDebugEnabled()) {
0759: log.debug("Built where clause '" + myStatement.toString()
0760: + "'");
0761: }
0762:
0763: return myStatement;
0764: }
0765:
0766: /**
0767: * Creates the limitation syntax optimisation stub
0768: * to embed inside the SQL command that performs
0769: * search and retrieve.
0770: * <p/>
0771: * <p>This method takes the limitation syntax string
0772: * and performs a string replacement on the following
0773: * tokens
0774: * <p/>
0775: * <ul>
0776: * <p/>
0777: * <li><b>%offset%</b><li><br>
0778: * the number of rows in the <code>ResultSet</code> to skip
0779: * before reading the data.
0780: * <p/>
0781: * <li><b>%maxrecord%</b><li><br>
0782: * the maximum number of rows to read from the <code>ResultSet</code>.
0783: * Also known as the <i>rowlength</i>.
0784: * <p/>
0785: * <li><b>%endrecord%</b><li><br>
0786: * the last record of in the <code>ResultSet</code> that the
0787: * search and retrieved should retrieve. The end record number
0788: * is equal to <code>( %offset% + %maxrecord% - 1 )</code>
0789: * <p/>
0790: * </ul>
0791: * <p/>
0792: * </p>
0793: * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
0794: *
0795: * @param theConnection the db connection to make this stub from
0796: * @param theObj the DataObject to query for the limitation stub
0797: * @return the limitation syntax stub string
0798: */
0799: public String makeLimitationStub(DBConnection theConnection,
0800: DataObject theObj) {
0801: String limit = theConnection.getLimitationSyntax();
0802: int offset = theObj.getOffsetRecord();
0803: int maxrec = theObj.getMaxRecords();
0804: int endrec = offset + maxrec - 1;
0805: limit = StringUtil.replace(limit, "%offset%", Integer
0806: .toString(offset));
0807: limit = StringUtil.replace(limit, "%maxrecords%", Integer
0808: .toString(maxrec));
0809:
0810: // limit = StringUtil.replace( limit, "%length%", Integer.toString( maxrec ) );
0811: limit = StringUtil.replace(limit, "%endrecord%", Integer
0812: .toString(endrec));
0813:
0814: return limit;
0815: } /* makeLimitationStub(DBConnection) */
0816:
0817: /**
0818: * Return the value of this field, placing double quotes around it if the
0819: * field's datatype requires it.
0820: *
0821: * @param fieldName The name of the field to be used
0822: * @param rangeString the appropriately formatted string
0823: * @param targetObject the JDBCDataObject to query for metadata
0824: * @return A string, quoted if necessary, to be used in building an SQL statement
0825: * @throws DataException If there is no such field or it's value cannot be
0826: * determined
0827: */
0828: public String quoteIfNeeded(JDBCDataObject targetObject,
0829: String fieldName, String rangeString) throws DataException {
0830: DataFieldMetaData oneField = targetObject
0831: .getFieldMetaData(fieldName);
0832: if (oneField == null) {
0833: throw new DataException("("
0834: + targetObject.getClass().getName()
0835: + ") No such field as " + fieldName);
0836: }
0837: boolean noTrim = false;
0838: if (!oneField.isMasked() && !targetObject.isGlobalMasked()) {
0839: try {
0840: noTrim = ConfigManager.getJdbcRequired(
0841: targetObject.getMappedDataContext())
0842: .isStringNotTrim();
0843: } catch (ConfigurationException ce) {
0844: throw new DataException(ce);
0845: }
0846: }
0847:
0848: String fieldValue = targetObject.getSerialForm(oneField);
0849:
0850: if (rangeString != null) {
0851: fieldValue = fieldValue.substring(rangeString.length());
0852: }
0853:
0854: /* if the field is null, we don't need to worry about quotes */
0855: if (fieldValue == null) {
0856: return null;
0857: }
0858:
0859: if (oneField.isNumericType()) {
0860: if (fieldValue.length() == 0) {
0861: return "0";
0862: }
0863:
0864: return fieldValue.trim();
0865: } /* if a numeric type */
0866:
0867: if (oneField.isQuotedTextType()) {
0868: if (rangeString != null) {
0869: return fieldValue;
0870: }
0871: FastStringBuffer returnValue = FastStringBuffer
0872: .getInstance();
0873: String returnString = null;
0874: try {
0875: String value = "";
0876: if (noTrim) {
0877: value = fieldValue;
0878: } else {
0879: value = fieldValue.trim();
0880: }
0881: returnValue.append("\'");
0882: // returnValue.append(targetObject.getConnectionPool().getEscapeHandler().escapeString(fieldValue.trim()));
0883: returnValue.append(targetObject.getConnectionPool()
0884: .getEscapeHandler().escapeString(value));
0885: returnValue.append("\'");
0886: returnString = returnValue.toString();
0887: } catch (DBException e) {
0888: throw new DataException(e);
0889: } finally {
0890: returnValue.release();
0891: returnValue = null;
0892: }
0893: return returnString;
0894: } /* if a quoted type */
0895:
0896: if (oneField.isDateType()) {
0897: if (rangeString != null) {
0898: return fieldValue;
0899: }
0900: FastStringBuffer returnValue = FastStringBuffer
0901: .getInstance();
0902: String returnString = null;
0903: try {
0904: returnValue.append("\'");
0905: returnValue.append(fieldValue);
0906: returnValue.append("\'");
0907: returnString = returnValue.toString();
0908: } finally {
0909: returnValue.release();
0910: returnValue = null;
0911: }
0912: return returnString;
0913: }
0914:
0915: //
0916: //We don't care about rangestrings in boolean types.... they don't
0917: //exactly make sense.
0918: //
0919: if (oneField.isBooleanType()) {
0920: try {
0921: boolean nativeBoolean = ConfigManager.getContext(
0922: targetObject.getMappedDataContext()).getJdbc()
0923: .isNativeBool();
0924:
0925: if (!nativeBoolean) {
0926: FastStringBuffer returnValue = FastStringBuffer
0927: .getInstance();
0928: String returnString = null;
0929: try {
0930: returnValue.append("\'");
0931: returnValue.append(fieldValue.trim());
0932: returnValue.append("\'");
0933: returnString = returnValue.toString();
0934: } finally {
0935: returnValue.release();
0936: returnValue = null;
0937: }
0938: return returnString;
0939: }
0940: } catch (ConfigurationException ce) {
0941: throw new DataException(ce);
0942: }
0943: }
0944:
0945: // if the field contains wildcards, it needs to be quoted regardless of it's type
0946: if (containsWildCards(targetObject, fieldValue)) {
0947: if (rangeString != null) {
0948: return fieldValue;
0949: }
0950: FastStringBuffer returnValue = FastStringBuffer
0951: .getInstance();
0952: String returnString = null;
0953: try {
0954: returnValue.append("\'");
0955: returnValue.append(fieldValue);
0956: returnValue.append("\'");
0957: returnString = returnValue.toString();
0958: } finally {
0959: returnValue.release();
0960: returnValue = null;
0961: }
0962: return returnString;
0963: }
0964:
0965: if (oneField.isNumericType()) {
0966: if (fieldValue.length() == 0) {
0967: return "0";
0968: }
0969: }
0970:
0971: if (noTrim) {
0972: return fieldValue;
0973: } else {
0974:
0975: return fieldValue.trim();
0976: }
0977: } /* quoteIfNeeded(String) */
0978:
0979: /**
0980: * See if this field value contains wild cards (e.g. pattern matching
0981: * criteria for the database). The wild cards can be configured via the
0982: * properties file.
0983: *
0984: * @param fieldValue The field value to check for wild cards
0985: * @param ownerObject the obejct to query for metadata
0986: * @return True if the string does contain wild cards, False if it does not
0987: */
0988: public boolean containsWildCards(JDBCDataObject ownerObject,
0989: String fieldValue) throws DataException {
0990: if (fieldValue == null) {
0991: fieldValue = ("");
0992: }
0993:
0994: for (Iterator it = ownerObject.getConnectionPool()
0995: .getWildCardsList().iterator(); it.hasNext();) {
0996: if (fieldValue.indexOf((String) it.next()) >= 0) {
0997: return true;
0998: }
0999: }
1000:
1001: return false;
1002: } /* containsWildCards(String) */
1003:
1004: /**
1005: * Build and return a FastStringBuffer ring consisting of an SQL 'where' clause
1006: * using the current field values as criteria for the search. See
1007: * setCustomWhereClause for information on specifying a more complex where clause.
1008: *
1009: * @param criteria the JDBCDataObject to build from
1010: * @param useAllFields True if all fields are to be used,
1011: * false for only key fields
1012: * @param allocatedBuffer - An already allocated FastStringBuffer to fill out.
1013: * This allows for compatability with, for example, object pools.
1014: * @return A FastStringBuffer containing the "where" clause for the SQL statement
1015: * @throws DataException upon error
1016: */
1017: public void buildStoreProcedureCallableStatement(
1018: JDBCDataObject criteria,
1019: CallableStatement myCallableStatement) throws DataException {
1020: Iterator fieldsToUse = null;
1021: FastStringBuffer myStatement = FastStringBuffer.getInstance();
1022:
1023: fieldsToUse = criteria.getMetaData().getFieldListArray()
1024: .iterator();
1025:
1026: /* Now go thru each field - if it is non-empty, add it's criteria */
1027:
1028: boolean inField = false;
1029: boolean outField = false;
1030: DataFieldMetaData oneField = null;
1031: String oneFieldName = null;
1032: String oneFieldValue = null;
1033: boolean skipText = false;
1034: boolean postgresql = false;
1035: TypeMapper typeMapper = null;
1036:
1037: try {
1038: ConfigJdbc myConfig = ConfigManager
1039: .getJdbcRequired(criteria.getMappedDataContext());
1040: skipText = myConfig.skipText();
1041: //We have to do this because postgres won't be smart enough to
1042: //cast floating point literals to truly a floating point value. :(
1043: if ("org.postgresql.Driver".equals(myConfig.getDriver())) {
1044: postgresql = true;
1045: }
1046: typeMapper = TypeMapper.getInstance(criteria
1047: .getDataContext());
1048: } catch (ConfigurationException ce) {
1049: throw new DataException(ce);
1050: } catch (DBException de) {
1051: throw new DataException(de);
1052: }
1053:
1054: boolean skipField = false;
1055: try {
1056:
1057: while (fieldsToUse.hasNext()) {
1058: oneFieldName = (String) fieldsToUse.next();
1059: oneField = criteria.getFieldMetaData(oneFieldName);
1060: skipField = false;
1061:
1062: if (oneField.isVirtual()) {
1063: skipField = true;
1064: }
1065:
1066: if (criteria.getDef().isInField(oneField.getName())) {
1067: inField = true;
1068: }
1069:
1070: if (criteria.getDef().isOutField(oneField.getName())) {
1071: outField = true;
1072: }
1073:
1074: try {
1075: oneFieldValue = StringUtil.notNull(criteria
1076: .getDataField(oneField.getName())
1077: .asString());
1078: } catch (DBException ex) {
1079: if (ex instanceof DataException) {
1080: throw ((DataException) ex);
1081: } else {
1082: throw new DataException(
1083: "Error getting field value", ex);
1084: }
1085: }
1086:
1087: String rangeString = rangeParser
1088: .denotesRange(oneFieldValue);
1089: if (!oneFieldValue.equals("")) {
1090: if (oneFieldValue.trim()
1091: .equalsIgnoreCase("is null")
1092: || oneFieldValue.trim().equalsIgnoreCase(
1093: "is not null")) {
1094: ;
1095: } else {
1096: oneFieldValue = quoteIfNeeded(criteria,
1097: oneFieldName, rangeString);
1098: }
1099: }
1100: if (oneFieldValue == null) {
1101: skipField = true;
1102: }
1103: if (oneFieldValue.trim().equals("\'\'")) {
1104: skipField = true;
1105: }
1106:
1107: //
1108: //There was a TODO item asking about why \r and \n's aren't allowed
1109: //in text fiels here. and the reason is that the JDBC parsers expect
1110: //all quoted fields to be without crlf in them. in all honesty,
1111: //you shouldn't be searching for a text field anyway since it's arbitrary
1112: //length and you SERIOUSLY dog performance by doing it that way.
1113: //If you must search for a text field, you can deal with cr/lf's by
1114: //using a prepared statement such as the LOBSupport class.
1115: //
1116: if (oneField.getTypeString().equalsIgnoreCase("text")) {
1117: if (skipText) {
1118: skipField = true;
1119:
1120: if (log.isDebugEnabled()) {
1121: log
1122: .debug("Skipping criteria in text field '"
1123: + oneFieldName + "'");
1124: }
1125: } else {
1126: if (oneFieldValue.indexOf("\n") > 0) {
1127: oneFieldValue = StringUtil.replace(
1128: oneFieldValue, "\n", "");
1129: }
1130: if (oneFieldValue.indexOf("\r") > 0) {
1131: oneFieldValue = StringUtil.replace(
1132: oneFieldValue, "\r", "");
1133: }
1134: if (oneFieldValue.equals("\'\'")) {
1135: skipField = true;
1136: }
1137: }
1138: } /* if text field */
1139:
1140: if (oneFieldValue.trim().equals("")) {
1141: skipField = true;
1142: }
1143: if (!skipField) {
1144: if (rangeString != null) {
1145: String theValue = rangeString + " "
1146: + oneFieldValue;
1147: boolean valid = rangeParser.isValidRange(
1148: criteria.getFieldMetaData(oneField
1149: .getName()), theValue);
1150: if (!valid) {
1151: throw new DataException(
1152: "Invalid field range value: "
1153: + theValue);
1154: }
1155:
1156: if (inField) {
1157: myCallableStatement.setString(Integer
1158: .parseInt(oneFieldName), theValue);
1159: }
1160: if (outField) {
1161: myCallableStatement.registerOutParameter(
1162: Integer.parseInt(oneFieldName),
1163: typeMapper.getJavaSQLType(oneField
1164: .getTypeString()));
1165: }
1166: } else if ((oneFieldValue.trim()
1167: .equalsIgnoreCase("is null"))
1168: || (oneFieldValue.trim()
1169: .equalsIgnoreCase("is not null"))) {
1170: if (inField) {
1171: myCallableStatement.setString(Integer
1172: .parseInt(oneFieldName),
1173: oneFieldValue.trim());
1174: }
1175: if (outField) {
1176: myCallableStatement.registerOutParameter(
1177: Integer.parseInt(oneFieldName),
1178: typeMapper.getJavaSQLType(oneField
1179: .getTypeString()));
1180: }
1181: } else if (oneField.isDateType()) {
1182: Object tmpData = null;
1183: try {
1184: tmpData = criteria.getDataField(
1185: oneFieldName).getValue();
1186: } catch (DBException ex) {
1187: if (ex instanceof DataException) {
1188: throw ((DataException) ex);
1189: } else {
1190: throw new DataException(
1191: "Error getting field value", ex);
1192: }
1193: }
1194: String data;
1195: //
1196: //FIXME allow for appropriate support of other data types.
1197: //
1198: if (tmpData == null) {
1199: data = null;
1200: } else if (tmpData instanceof String) {
1201: data = (String) tmpData;
1202: } else {
1203: data = tmpData.toString();
1204: }
1205:
1206: if (data == null || (data.length() == 0)) {
1207: if (inField) {
1208: myCallableStatement
1209: .setString(
1210: Integer
1211: .parseInt(oneFieldName),
1212: "null");
1213: }
1214: } else {
1215: myCallableStatement.setString(Integer
1216: .parseInt(oneFieldName), JDBCUtil
1217: .getInstance().formatDateTime(
1218: criteria,
1219: oneField.getName()));
1220: }
1221: if (outField) {
1222: myCallableStatement.registerOutParameter(
1223: Integer.parseInt(oneFieldName),
1224: typeMapper.getJavaSQLType(oneField
1225: .getTypeString()));
1226: }
1227: } else if (oneField.isFloatingPointType()) {
1228: //Floating point types have to be searched within a certain
1229: //precision. Thus we compare the ABS to the less than
1230: //the field precision.
1231: myStatement.append("ABS(");
1232: myStatement.append(oneFieldName);
1233: myStatement.append(" - ");
1234: if (postgresql) {
1235: myStatement.append(" CAST (");
1236: }
1237:
1238: myStatement.append(oneFieldValue);
1239: if (postgresql) {
1240: myStatement.append(" as FLOAT)");
1241: }
1242: myStatement.append(") <");
1243: if (postgresql) {
1244: myStatement.append(" CAST (");
1245: }
1246: myStatement.append(".");
1247: int precision = oneField.getPrecision();
1248: if (precision == 0) {
1249: precision = 1;
1250: }
1251: for (int i = 0; i < oneField.getPrecision() - 1; i++) {
1252: myStatement.append("0");
1253: }
1254: myStatement.append("1");
1255: if (postgresql) {
1256: myStatement.append(" as FLOAT)");
1257: }
1258: if (inField) {
1259: myCallableStatement.setString(Integer
1260: .parseInt(oneFieldName),
1261: myStatement.toString());
1262: }
1263: myStatement.clear();
1264: if (outField) {
1265: myCallableStatement.registerOutParameter(
1266: Integer.parseInt(oneFieldName),
1267: typeMapper.getJavaSQLType(oneField
1268: .getTypeString()), oneField
1269: .getPrecision());
1270: }
1271: } else {
1272: if (inField) {
1273: myCallableStatement.setString(Integer
1274: .parseInt(oneFieldName),
1275: oneFieldValue);
1276: }
1277: if (outField) {
1278: myCallableStatement.registerOutParameter(
1279: Integer.parseInt(oneFieldName),
1280: typeMapper.getJavaSQLType(oneField
1281: .getTypeString()));
1282: }
1283: }/* if (oneField.isFloatingPointType()) */
1284:
1285: /* if field is not skipped for some reason */
1286: myStatement.release();
1287: myStatement = null;
1288: } /* if (!skipField) */
1289:
1290: } /* While (for each field) */
1291: } catch (SQLException ce) {
1292: throw new DataException(ce);
1293: } catch (DBException de) {
1294: throw new DataException(de);
1295: }
1296: if (log.isDebugEnabled()) {
1297: log.debug("Built callable statement for store procedure ");
1298: }
1299: } /* buildStoreProcedureCallableStatement(JDBCDataObject, boolean, CallableStatement) */
1300:
1301: }
|