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.dbobj;
0066:
0067: import com.jcorporate.expresso.core.controller.ControllerRequest;
0068: import com.jcorporate.expresso.core.dataobjects.DataException;
0069: import com.jcorporate.expresso.core.dataobjects.DataFieldMetaData;
0070: import com.jcorporate.expresso.core.dataobjects.jdbc.FieldRangeParser;
0071: import com.jcorporate.expresso.core.db.DBConnection;
0072: import com.jcorporate.expresso.core.db.DBConnectionPool;
0073: import com.jcorporate.expresso.core.db.DBException;
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.ClassLocator;
0080: import com.jcorporate.expresso.kernel.util.FastStringBuffer;
0081: import org.apache.log4j.Logger;
0082:
0083: import java.math.BigDecimal;
0084: import java.util.ArrayList;
0085: import java.util.Date;
0086: import java.util.Enumeration;
0087: import java.util.HashMap;
0088: import java.util.Hashtable;
0089: import java.util.Iterator;
0090: import java.util.List;
0091: import java.util.StringTokenizer;
0092: import java.util.Vector;
0093:
0094: /**
0095: * This class handles joins the 'old' way. It is not deprectated, but
0096: *
0097: * @see com.jcorporate.expresso.core.dataobjects.jdbc.JoinedDataObject for a more modern, flexible approach to joins
0098: * <p/>
0099: * A MultiDBObject is a group of dbobjects that are "related" - e.g. defined
0100: * as being part of a foreign-key/primary-key relationship. This may be
0101: * master/detail or a more complex relationship, but it can be expressed as a
0102: * "join" operation between the tables.
0103: * <p/>
0104: * After establishing the relationships between the objects, the MultiDBObject
0105: * can have search criteria set for it & searchAndRetrieve operations done just
0106: * like a 'regular' DBObject, but these operations affect the entire related
0107: * group of objects. At the moment, MultiDBObjects are read-only, though that
0108: * may change in the future.
0109: * <p/>
0110: * Add 'model' objects, which may have criteria already set in their fields for a search,
0111: * via addDBObj(). After a query (via searchAndRetrieveList()),
0112: * each resulting MultiDBObject instance represents a
0113: * 'join' row, and has its own encapsulated instances of whatever DBObject
0114: * models have been set with addDBObj()
0115: * before the query. In order to access these instances, use getDBObject().
0116: * <p/>
0117: * Creation date: (9/18/00 11:32:03 AM)
0118: * author Michael Nash
0119: */
0120: public class MultiDBObject {
0121:
0122: // ----------------------------------------------------
0123: // 2003-11-05 /ebn-ma: added...
0124: /**
0125: * Holds the shortName of these DBObjects, which have to be ignored in the
0126: * where-clause because they are still used in a on-clause of a join.
0127: * Ex.: ignoreInWhereClause = ",bt,,an,,qu,";
0128: * if (ignoreInWhereClause.indexOf(",bt,") >= 0) { continue; }
0129: */
0130: private String ignoreInWhereClause = "";
0131:
0132: /**
0133: * Hold the join-conditions in "original" form.
0134: * For the getThisMultiDBObj method and for to create the from-clause
0135: * at execution-time.
0136: */
0137: private Vector originalJoins = new Vector();
0138:
0139: /**
0140: * If we are using a custom from clause for this query, it's stored here.
0141: * If null, then build the from clause with the join or the foreign-key
0142: * definitions.
0143: * Do not include the sql-keyword FROM.
0144: * Note: If you define a customFromClause, you will usually have to define
0145: * a custemWhereClause too.
0146: */
0147: private String customFromClause = null;
0148: // 2003-11-05 /ebn-ma: ...added
0149: // ----------------------------------------------------
0150:
0151: /**
0152: * Use a DISTINCT keyword right after SELECT keyword
0153: */
0154: private boolean selectDistinct = false;
0155:
0156: /**
0157: * Attributes of this DB Object
0158: */
0159: private HashMap attributes = null;
0160:
0161: /**
0162: * The number of records we must skip over before we start reading
0163: * the <code>ResultSet</code> proper in a searchAndRetrieve.
0164: * 0 means no limit
0165: * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
0166: */
0167: transient protected int offsetRecord = 0;
0168:
0169: /**
0170: * If we are using a custom where clause for this query, this flag will
0171: * signify if we need to append the custom where clause to that generated
0172: * by the setForeignKey() calls
0173: */
0174: private boolean appendCustomWhereClause = false;
0175:
0176: /**
0177: * Static variables for join type
0178: */
0179: protected static final int INNER_JOIN = 0;
0180: protected static final int RIGHT_JOIN = 1;
0181: protected static final int LEFT_JOIN = 2;
0182:
0183: /**
0184: * Constant for shortName DBObject attribute
0185: */
0186: protected static final String SHORT_NAME = "shortName";
0187:
0188: /**
0189: * 'FROM' clause formed by calls to setJoin().
0190: */
0191: private String fromClause = null;
0192:
0193: /**
0194: * If set to true, the shortname for the DBObject will be used as an alias
0195: * in the query; defaults to false for backward-compatiblity
0196: */
0197: private boolean shortNameAsAlias = false;
0198:
0199: /**
0200: * Return an "attribute". Attributes are temporary (e.g. not stored in the DBMS)
0201: * values associated with this particular DB object instance.
0202: *
0203: * @param attribName The attribute name to check
0204: * @return the object associated with this attribute
0205: */
0206: public Object getAttribute(String attribName) {
0207: if (attributes != null) {
0208: return attributes.get(attribName);
0209: } else {
0210: return null;
0211: }
0212: } /* getAttribute(String) */
0213:
0214: /**
0215: * Set an attribute. Attributes are temporary (e.g. not stored in the DBMS) values
0216: * associated with this particular DB object instance.
0217: *
0218: * @param attribName The name of the attribute being defined
0219: * @param attribValue The object to store under this attribute name
0220: */
0221: public synchronized void setAttribute(String attribName,
0222: Object attribValue) {
0223: if (attributes == null) {
0224: attributes = new HashMap();
0225: }
0226:
0227: attributes.put(attribName, attribValue);
0228: } /* setAttribute(String, Object) */
0229:
0230: /**
0231: * A DB Object can be told to only retrieve a certain number of records. If a
0232: * "max records" value has been specified, this method provides access to it.
0233: *
0234: * @return The maximum number of records that should be retrieved, or zero
0235: * if no max has been set
0236: */
0237: public int getMaxRecords() {
0238: return maxRecords;
0239: } /* getMaxRecords() */
0240:
0241: /**
0242: * Specifies the number of records that should be skipped over
0243: * before any data from the <code>ResultSet</code>
0244: * is retrieved in any subsequent
0245: * searchAndRetrieve() call. Records will be skipped over (in the specified
0246: * sort order) until the record counts is equal to or greater
0247: * than the offset record. Specifying zero indicates that no
0248: * records should be skipped over and the
0249: * <code>ResultSet</code> immediately from the start.
0250: *
0251: * @param newOffset The maximum number of records to retrieve.
0252: * @throws DBException If the max number is less than 0
0253: * <p/>
0254: * author Peter Pilgrim <peterp at xenonsoft dot demon dot co dot uk>
0255: * date Tue Feb 05 23:06:38 GMT 2002
0256: */
0257: public synchronized void setOffsetRecord(int newOffset)
0258: throws DBException {
0259: if (newOffset < 0) {
0260: throw new DBException("Offset records can't be less than 0");
0261: }
0262:
0263: offsetRecord = newOffset;
0264: } /* setOffsetRecord(int) */
0265:
0266: /**
0267: * Gets the number of records that be skipped. The offset records.
0268: * A DB Object can be told to skip a certain number of
0269: * records, before reading records from the <code>ResultSet</code>.
0270: * <p/>
0271: * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
0272: *
0273: * @return The maximum number of records that should be
0274: * skipped over before reading the data records.
0275: * @see #setOffsetRecord(int)
0276: */
0277: public int getOffsetRecord() {
0278: return offsetRecord;
0279: } /* getOffsetRecord() */
0280:
0281: // ----------------------------------------------------
0282: // 2003-11-05 /ebn-ma: added...
0283: /**
0284: * Specify a custom "from" clause for the SQL used to retrieve
0285: * records for this object.
0286: *
0287: * @param newCustomFrom java.lang.String
0288: */
0289: public synchronized void setCustomFromClause(String newCustomFrom) {
0290: customFromClause = newCustomFrom;
0291: }
0292:
0293: /**
0294: * Gets the customFromClause
0295: *
0296: * @return customFromClause
0297: */
0298: public String getCustomFromClause() {
0299: return customFromClause;
0300: }
0301:
0302: /**
0303: * Build a string consisting of an SQL 'from' clause using the
0304: * customFromClause, the foreign-key or the join definitions.
0305: * The result will be found in property fromClause.
0306: *
0307: * @return true = success.
0308: * <p/>
0309: * Modify by Yves Henri AMAIZO <amy_amaizo@compuserve.com>
0310: * @since $DatabaseSchema $Date: 2004/11/18 02:03:27 $
0311: */
0312: public boolean buildFromClause() throws DBException {
0313: boolean success = false;
0314: FastStringBuffer fsb = new FastStringBuffer(256);
0315: int dboCount = myDBObjects.size();
0316:
0317: try {
0318: // ----------------------------------------------------
0319: if (customFromClause != null
0320: && customFromClause.trim().length() > 0) {
0321: // As long as a customFromClause is defined , we will use it.
0322: fromClause = customFromClause;
0323: success = true;
0324: }
0325: // ----------------------------------------------------
0326: else if (!originalJoins.isEmpty()) {
0327: if (!originalRelations.isEmpty()) {
0328: throw new DBException(
0329: this Class
0330: + "count()"
0331: + "You cannot mix set???Join() with setForeignKey() definitions."
0332: + "Use setInnerJoin() instead of setForeignKey().");
0333: }
0334:
0335: // Build the from clause with the join definitions...
0336: fromClause = null;
0337: String oneJoin = null;
0338:
0339: for (Enumeration n = originalJoins.elements(); n
0340: .hasMoreElements();) {
0341: oneJoin = (String) n.nextElement();
0342:
0343: StringTokenizer stk = new StringTokenizer(oneJoin,
0344: "|");
0345: String leftDbo = stk.nextToken();
0346: String leftField = stk.nextToken();
0347: String rightDbo = stk.nextToken();
0348: String rightField = stk.nextToken();
0349: String joinTyp = stk.nextToken().toLowerCase();
0350:
0351: if ("left".equals(joinTyp)) {
0352: buildJoin(leftDbo, leftField, rightDbo,
0353: rightField, LEFT_JOIN);
0354: } else if ("inner".equals(joinTyp)) {
0355: buildJoin(leftDbo, leftField, rightDbo,
0356: rightField, INNER_JOIN);
0357: } else if ("right".equals(joinTyp)) {
0358: buildJoin(leftDbo, leftField, rightDbo,
0359: rightField, RIGHT_JOIN);
0360: } else {
0361: throw new DBException(this Class + "count()"
0362: + "Missing join-type in '" + oneJoin
0363: + "'");
0364: }
0365: }
0366: success = true;
0367: }
0368: // ----------------------------------------------------
0369: else if (!originalRelations.isEmpty() || dboCount == 1) {
0370: // To build the relations with the foreign-key definitions,
0371: // we need to have all the DBObejcts in the from clause.
0372: // If this is not the special case, where our Object has only
0373: // one embedded DBObject: We have to link them later in the
0374: // where clause.
0375: boolean needComma = false;
0376: DBObject oneObj = null;
0377:
0378: for (Enumeration eachObj = myDBObjects.elements(); eachObj
0379: .hasMoreElements();) {
0380: oneObj = (DBObject) eachObj.nextElement();
0381:
0382: if (needComma) {
0383: fsb.append(",");
0384: }
0385:
0386: String targetTable = oneObj.getJDBCMetaData()
0387: .getTargetSQLTable(oneObj.getDataContext());
0388: String tableName = getTableName(oneObj);
0389: fsb.append(targetTable);
0390: if (!targetTable.equals(tableName)) {
0391: fsb.append(" AS " + tableName);
0392: }
0393: needComma = true;
0394: }
0395:
0396: fromClause = fsb.toString();
0397: success = true;
0398: }
0399: // ----------------------------------------------------
0400: else {
0401: // build clause
0402: boolean needComma = false;
0403:
0404: for (Enumeration eachObj = myDBObjects.elements(); eachObj
0405: .hasMoreElements();) {
0406: DBObject oneObj = (DBObject) eachObj.nextElement();
0407:
0408: if (needComma) {
0409: fsb.append(",");
0410: }
0411:
0412: String targetTable = oneObj.getJDBCMetaData()
0413: .getTargetSQLTable(oneObj.getDataContext());
0414: String tableName = getTableName(oneObj);
0415: fsb.append(targetTable);
0416: if (!targetTable.equals(tableName)) {
0417: fsb.append(" AS " + tableName);
0418: }
0419: needComma = true;
0420: }
0421:
0422: fromClause = fsb.toString();
0423: success = true;
0424: }
0425: } catch (Throwable t) {
0426: throw new DBException(t);
0427: } finally {
0428: fsb.release();
0429: }
0430:
0431: return (success);
0432: }
0433:
0434: // 2003-11-05 /ebn-ma: ...added
0435: // ----------------------------------------------------
0436:
0437: /**
0438: * Just like find, but only retrieves the count, not the records themselves.
0439: *
0440: * @return integer Count of the records matching the criteria
0441: * @throws DBException If the search could not be completed
0442: */
0443: public synchronized int count(String expr) throws DBException {
0444: // boolean needComma = false;
0445: // DBObject oneObj = null;
0446: // DBField oneField = null;
0447: //
0448: // DBConnectionPool myPool = null;
0449: // DBConnection myConnection = null;
0450: //
0451: // //
0452: // //If myPool isn't set yet, then we need to use getDBName to
0453: // //reset. FIXME: Have the system throw exceptions if DBName isn't
0454: // //set instead.
0455: // //
0456: // if (myPool == null) {
0457: // getDBName();
0458: // }
0459: // FastStringBuffer myStatement = new FastStringBuffer(48);
0460: // myStatement.append("SELECT COUNT(*) FROM ");
0461: // needComma = false;
0462: //
0463: // for (Enumeration eachObj = myDBObjects.elements();
0464: // eachObj.hasMoreElements();) {
0465: // oneObj = (DBObject) eachObj.nextElement();
0466: //
0467: // if (needComma) {
0468: // myStatement.append(",");
0469: // }
0470: //
0471: // myStatement.append(oneObj.getJDBCMetaData().getTargetTable());
0472: // needComma = true;
0473: // }
0474: //
0475: // if (customWhereClause != null) {
0476: // myStatement.append(customWhereClause);
0477: // } else {
0478: // FastStringBuffer fsb = FastStringBuffer.getInstance();
0479: // try {
0480: // myStatement.append(buildWhereClauseBuffer(true, fsb));
0481: // } finally {
0482: // fsb.release();
0483: // }
0484: // }
0485: //
0486: // try {
0487: // if (localConnection != null) {
0488: // myConnection = localConnection;
0489: // } else {
0490: // myPool = DBConnectionPool.getInstance(getDBName());
0491: // myConnection = myPool.getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
0492: // }
0493: //
0494:
0495: // myConnection.execute(myStatement.toString());
0496:
0497: // if (myConnection.next()) {
0498: // return myConnection.getInt(1);
0499: // }
0500: // } catch (DBException de) {
0501: // throw de;
0502: // } finally {
0503: // if (localConnection == null) {
0504: // myPool.release(myConnection);
0505: // }
0506: // }
0507:
0508: DBConnectionPool myPool = null;
0509: DBConnection myConnection = null;
0510:
0511: try {
0512: if (localConnection != null) {
0513: myConnection = localConnection;
0514: } else {
0515: myPool = DBConnectionPool.getInstance(getDBName());
0516: myConnection = myPool
0517: .getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
0518: }
0519:
0520: if (recordSet == null) {
0521: String myName = (this Class + "count()");
0522: throw new DBException(myName
0523: + ":Database object not correctly initialized");
0524: }
0525:
0526: recordSet.clear();
0527:
0528: FastStringBuffer myStatement = new FastStringBuffer(256);
0529: myStatement.append("SELECT ");
0530:
0531: if (selectDistinct) {
0532: myStatement.append(" DISTINCT ");
0533: }
0534: if (StringUtil.isBlankOrNull(expr)) {
0535: expr = "*";
0536: }
0537: myStatement.append("COUNT(" + expr + ") ");
0538:
0539: // ----------------------------------------------------
0540: // 2003-11-05 /ebn-ma: changed...
0541: // We build the from clause every time we genereate a
0542: // sql-statement. Because:
0543: // If we use joins, this is the only chance to integrate
0544: // filter-conditions that follow the actual values in
0545: // the properties of the related DBObjects.
0546:
0547: // myStatement.append(" FROM ");
0548: // if (fromClause == null) {
0549: // needComma = false;
0550: //
0551: // for (Enumeration eachObj = myDBObjects.elements();
0552: // eachObj.hasMoreElements();) {
0553: // oneObj = (DBObject) eachObj.nextElement();
0554: //
0555: // if (needComma) {
0556: // myStatement.append(",");
0557: // }
0558: //
0559: // myStatement.append(oneObj.getJDBCMetaData().getTargetTable());
0560: // needComma = true;
0561: // }
0562: // } else {
0563: // myStatement.append(fromClause);
0564: // /**
0565: // * FIXED: ma 16.10.2003
0566: // * FIXED: Don't kill the fromClause,
0567: // * FIXED: we need it later!
0568: // *
0569: // * fromClause = null;
0570: // */
0571: // }
0572:
0573: myStatement.append(" FROM ");
0574: if (buildFromClause() && fromClause != null
0575: && fromClause.trim().length() > 0) {
0576: myStatement.append(fromClause);
0577: } else {
0578: throw new DBException(this Class + "count()"
0579: + " :Building of FROM clause failed.");
0580: }
0581: // 2003-11-05 /ebn-ma: ...changed
0582: // ----------------------------------------------------
0583:
0584: String whereClause;
0585: if (customWhereClause != null) {
0586: if (appendCustomWhereClause) {
0587: whereClause = buildWhereClause(true) + " AND "
0588: + customWhereClause;
0589: } else {
0590: whereClause = " WHERE " + customWhereClause;
0591: }
0592: } else {
0593: whereClause = buildWhereClause(true);
0594: }
0595:
0596: myStatement.append(whereClause);
0597:
0598: appendCustomWhereClause = false;
0599:
0600: if (log.isDebugEnabled()) {
0601: log.debug("Executing " + myStatement.toString());
0602: }
0603:
0604: myConnection.execute(myStatement.toString());
0605:
0606: if (myConnection.next()) {
0607: return myConnection.getInt(1);
0608: }
0609: } finally {
0610: if (localConnection == null) {
0611: myPool.release(myConnection);
0612: }
0613: }
0614:
0615: return 0;
0616: } /* count() */
0617:
0618: /**
0619: * Just like find, but only retrieves the count, not the records themselves.
0620: *
0621: * @return integer Count of the records matching the criteria
0622: * @throws DBException If the search could not be completed
0623: */
0624: public synchronized int count() throws DBException {
0625: return count("");
0626: } /* count() */
0627:
0628: /**
0629: * Creates the limitation syntax optimisation stub
0630: * to embed inside the SQL command that performs
0631: * search and retrieve.
0632: * <p/>
0633: * <p>This method takes the limitation syntax string
0634: * and performs a string replacement on the following
0635: * tokens
0636: * <p/>
0637: * <ul>
0638: * <p/>
0639: * <li><b>%offset%</b><li><br>
0640: * the number of rows in the <code>ResultSet</code> to skip
0641: * before reading the data.
0642: * <p/>
0643: * <li><b>%maxrecord%</b><li><br>
0644: * the maximum number of rows to read from the <code>ResultSet</code>.
0645: * Also known as the <i>rowlength</i>.
0646: * <p/>
0647: * <li><b>%endrecord%</b><li><br>
0648: * the last record of in the <code>ResultSet</code> that the
0649: * search and retrieved should retrieve. The end record number
0650: * is equal to <code>( %offset% + %maxrecord% - 1 )</code>
0651: * <p/>
0652: * </ul>
0653: * <p/>
0654: * </p>
0655: * <p/>
0656: * <p/>
0657: * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
0658: *
0659: * @param theConnection the db connection to make this stub from
0660: * @return the limitation syntax stub string
0661: * @see #searchAndRetrieve()
0662: * @see #setOffsetRecord( int )
0663: * @see #setMaxRecords( int )
0664: */
0665: protected String makeLimitationStub(DBConnection theConnection) {
0666: String limit = theConnection.getLimitationSyntax();
0667: int offset = this .getOffsetRecord();
0668: int maxrec = this .getMaxRecords();
0669: int endrec = offset + maxrec - 1;
0670: limit = StringUtil.replace(limit, "%offset%", Integer
0671: .toString(offset));
0672: limit = StringUtil.replace(limit, "%maxrecords%", Integer
0673: .toString(maxrec));
0674:
0675: // limit = StringUtil.replace( limit, "%length%", Integer.toString( maxrec ) );
0676: limit = StringUtil.replace(limit, "%endrecord%", Integer
0677: .toString(endrec));
0678:
0679: return limit;
0680: } /* makeLimitationStub(DBConnection) */
0681:
0682: private static final String this Class = MultiDBObject.class
0683: .getName()
0684: + ".";
0685:
0686: /**
0687: * Hash that contains the DB objects that relate to make up this query,
0688: * indexed by the "short" name provided by the user.
0689: */
0690: private Hashtable myDBObjects = new Hashtable();
0691:
0692: /**
0693: * Vector to hold the "relations" between the different db objects that
0694: * make up this query
0695: */
0696: private Vector relations = new Vector();
0697: private String dbName = null;
0698:
0699: /**
0700: * This flag tells the buildWhereClause method(s) to either be case
0701: * sensitive (normal queries) or to be case insensitive. The case
0702: * insensitive query is useful when you want to do a search and retreive
0703: * and want case insensitive matching.
0704: */
0705: private boolean caseSensitiveQuery = true;
0706:
0707: /**
0708: * broken into a vector
0709: */
0710: private Vector sortKeys = new Vector();
0711:
0712: /**
0713: * The vector of MultiDB objects retrieved by the last searchAndRetrieve method
0714: */
0715: private List recordSet = new ArrayList();
0716:
0717: /**
0718: * If we are using a custom where clause for this query, it's stored here. If
0719: * null, then build the where clause
0720: */
0721: private String customWhereClause = null;
0722:
0723: /**
0724: * Hold the relations in "original" form - for the getThisMultiDBObj method
0725: */
0726: private Vector originalRelations = new Vector();
0727:
0728: /**
0729: * Local connection that we use if it's initialized, but
0730: * if it's null we generate our own connection
0731: */
0732: protected DBConnection localConnection = null;
0733:
0734: /* The max number of records we retrieve in a searchAndRetrieve.
0735: * 0 means no limit
0736: */
0737: protected int maxRecords = 0;
0738: private static Logger log = Logger.getLogger(MultiDBObject.class);
0739:
0740: /**
0741: * MultiDBObject constructor. calls setupFields.
0742: */
0743: public MultiDBObject() throws DBException {
0744: setupFields();
0745: } /* MultiDBObject() */
0746:
0747: /**
0748: * MultiDBObject constructor which sets dbname from request
0749: */
0750: public MultiDBObject(ControllerRequest request) throws DBException {
0751: this ();
0752: setDBName(request.getDataContext());
0753: } /* MultiDBObject() */
0754:
0755: /**
0756: * Add a DB Object to the objects being used for this multidbobj query. The
0757: * object is specified with a full classname, then a "short" name used to refer
0758: * to it in this object. For example, the class name might be
0759: * com.jcorporate.expresso.services.dbobj.SchemaList, the short name might be
0760: * "schema".
0761: *
0762: * @param dbobjClassName java.lang.String
0763: * @param shortName java.lang.String
0764: */
0765: public void addDBObj(String dbobjClassName, String shortName)
0766: throws DBException {
0767: String myName = this Class + "addDBObj(String, String)";
0768:
0769: if (StringUtil.notNull(dbobjClassName).equals("")) {
0770: throw new DBException(myName + ":Must specify a class name");
0771: }
0772: if (StringUtil.notNull(shortName).equals("")) {
0773: throw new DBException(myName + ":Must specify a short name");
0774: }
0775:
0776: DBObject oneDBObj = null;
0777:
0778: try {
0779: Class c = ClassLocator.loadClass(dbobjClassName);
0780: oneDBObj = (DBObject) c.newInstance();
0781: } catch (ClassNotFoundException cn) {
0782: throw new DBException(myName + ":DBObject '"
0783: + dbobjClassName + "' not found", cn);
0784: } catch (InstantiationException ie) {
0785: throw new DBException(myName + ":DBObject '"
0786: + dbobjClassName + "' cannot be instantiated", ie);
0787: } catch (IllegalAccessException iae) {
0788: throw new DBException(myName
0789: + ":Illegal access loading DBObject '"
0790: + dbobjClassName + "'", iae);
0791: }
0792:
0793: addDBObj(oneDBObj, shortName);
0794: }
0795:
0796: public void addDBObj(DBObject oneDBObj, String shortName)
0797: throws DBException {
0798: StringUtil.assertNotBlank(shortName,
0799: "Short name cannot be blank here");
0800: oneDBObj.setAttribute(SHORT_NAME, shortName);
0801: myDBObjects.put(shortName, oneDBObj);
0802:
0803: if (getDBName() == null) {
0804: setDBName(oneDBObj.getDataContext());
0805: }
0806:
0807: /* Check to see if all of the db objects are in the same database */
0808: oneDBObj.setDataContext(getDBName());
0809:
0810: if (!oneDBObj.getDataContext().equals(getDBName())) {
0811: throw new DBException("DB Object '"
0812: + oneDBObj.getClass().getName()
0813: + "' was mapped to db/context '"
0814: + oneDBObj.getDataContext()
0815: + "', but this MultiDBObject is in context '"
0816: + getDBName() + "'");
0817: }
0818: } /* addDBOj(String, String) */
0819:
0820: /**
0821: * Build and return a string consisting of an SQL 'where' clause
0822: * using the current field values as criteria for the search. See
0823: * setCustomWhereClause for information on specifying a more complex where clause.
0824: *
0825: * @param useAllFields True if all fields are to be used,
0826: * false for only key fields
0827: * @return The where clause to use in a query.
0828: */
0829: public String buildWhereClause(boolean useAllFields)
0830: throws DBException {
0831: FastStringBuffer fsb = FastStringBuffer.getInstance();
0832: try {
0833: return buildWhereClauseBuffer(useAllFields, fsb).toString();
0834: } finally {
0835: fsb.release();
0836: }
0837: } /* buildWhereClause(boolean) */
0838:
0839: // ----------------------------------------------------
0840: // 2003-11-05 /ebn-ma: added...
0841: // Method is overwritten for to support a new parameter dboAlias.
0842: /**
0843: * Build and return a string consisting of an SQL 'where' clause
0844: * using the current field values as criteria for the search. See
0845: * setCustomWhereClause for information on specifying a more complex where clause.
0846: *
0847: * @param useAllFields True if all fields are to be used, false for only key fields
0848: * @return java.lang.String.
0849: */
0850: protected String buildWhereClauseBuffer(boolean useAllFields,
0851: FastStringBuffer myStatement) throws DBException {
0852: return buildWhereClauseBuffer(useAllFields, myStatement, " ")
0853: .toString();
0854: }
0855:
0856: // 2003-11-05 /ebn-ma: ...added
0857: // ----------------------------------------------------
0858:
0859: // ----------------------------------------------------
0860: // 2003-11-05 /ebn-ma: changed...
0861: // Method supports a new parameter dboAlias. With the help of this parameter
0862: // we build selective clauses for a single DBObject.
0863: // These clauses are used in method buildJoin.
0864: /**
0865: * Build and return a string consisting of an SQL 'where' clause
0866: * using the current field values as criteria for the search. See
0867: * setCustomWhereClause for information on specifying a more complex where clause.
0868: *
0869: * @param useAllFields True if all fields are to be used, false for only key fields
0870: * @param myStatement Buffer for to build the new where-clause.
0871: * @param dboAlias Build the where-clause for this DBObject. If empty,
0872: * work with all DBObjects that are not marked to be ignored.
0873: * @return java.lang.String.
0874: */
0875: protected String buildWhereClauseBuffer(boolean useAllFields,
0876: FastStringBuffer myStatement, String dboAlias)
0877: throws DBException {
0878:
0879: /* list of field names (with table names prefixed) */
0880: Vector fields = new Vector();
0881: DBObject oneObj = null;
0882: String fieldName = null;
0883: Hashtable byTableName = new Hashtable();
0884:
0885: if (useAllFields) {
0886: for (Enumeration eachObj = myDBObjects.elements(); eachObj
0887: .hasMoreElements();) {
0888: oneObj = (DBObject) eachObj.nextElement();
0889: byTableName.put(getTableName(oneObj), oneObj);
0890:
0891: // ----------------------------------------------------
0892: // 2003-11-05 /ebn-ma: added...
0893: // Two cases:
0894: // 1. If dboAlias tells us to compute the on-clause of a join,
0895: // then we will ignore every DBObject that is not the one
0896: // specified in dboAlias.
0897: // 2. If dboAlias is empty, we are computing the where-clause of
0898: // our sql-statement.
0899: // In this case we ignore the current DBObject, if it is a
0900: // member of ignoreInWhereClause.
0901: String this Dbo = (String) oneObj
0902: .getAttribute(SHORT_NAME);
0903:
0904: if (dboAlias.trim().length() > 0) {
0905: if (!this Dbo.equals(dboAlias)) {
0906: continue;
0907: }
0908: } else if (ignoreInWhereClause.indexOf("," + this Dbo
0909: + ",") >= 0) {
0910: continue;
0911: }
0912: // 2003-11-05 /ebn-ma: ...added
0913: // ----------------------------------------------------
0914:
0915: for (Iterator i = oneObj.getMetaData()
0916: .getFieldListArray().iterator(); i.hasNext();) {
0917: fieldName = (String) i.next();
0918:
0919: if (!oneObj.getMetaData().getFieldMetadata(
0920: fieldName).isVirtual()) {
0921: fields.addElement(getTableName(oneObj) + "."
0922: + fieldName);
0923: }
0924: } /* for each field */
0925: }
0926: } else { /* for each db object */
0927: for (Enumeration eachObj = myDBObjects.elements(); eachObj
0928: .hasMoreElements();) {
0929: oneObj = (DBObject) eachObj.nextElement();
0930:
0931: // ----------------------------------------------------
0932: // 2003-11-05 /ebn-ma: added...
0933: // Two cases:
0934: // 1. If dboAlias tells us to compute the on-clause of a join,
0935: // then we will ignore every DBObject that is not the one
0936: // specified in dboAlias.
0937: // 2. If dboAlias is empty, we are computing the where-clause of
0938: // our sql-statement.
0939: // In this case we ignore the current DBObject, if it is a
0940: // member of ignoreInWhereClause.
0941: String this Dbo = (String) oneObj
0942: .getAttribute(SHORT_NAME);
0943:
0944: if (dboAlias.trim().length() > 0) {
0945: if (!this Dbo.equals(dboAlias)) {
0946: continue;
0947: }
0948: } else if (ignoreInWhereClause.indexOf("," + this Dbo
0949: + ",") >= 0) {
0950: continue;
0951: }
0952: // 2003-11-05 /ebn-ma: ...added
0953: // ----------------------------------------------------
0954:
0955: for (Iterator i = oneObj.getKeyFieldListIterator(); i
0956: .hasNext();) {
0957: fieldName = (String) i.next();
0958:
0959: if (!oneObj.getMetaData().getFieldMetadata(
0960: fieldName).isVirtual()) {
0961: fields.addElement(getTableName(oneObj) + "."
0962: + fieldName);
0963: }
0964: } /* for each field */
0965: } /* for each db object */
0966: }
0967:
0968: /* Now go thru each field - if it is non-empty, add it's criteria */
0969:
0970: /* to the where clause. If it is empty, just skip to the next one */
0971: boolean addWhere = true;
0972: boolean addAnd = false;
0973: String oneFieldName = null;
0974: String oneTableName = null;
0975: String oneFullName = null;
0976: String oneFieldValue = null;
0977: boolean skipText = false;
0978:
0979: try {
0980: ConfigJdbc myConfig = ConfigManager
0981: .getJdbcRequired(getDBName());
0982: skipText = myConfig.skipText();
0983: } catch (ConfigurationException ce) {
0984: throw new DBException(ce);
0985: }
0986:
0987: boolean skipField = false;
0988:
0989: for (Enumeration fieldsToUse = fields.elements(); fieldsToUse
0990: .hasMoreElements();) {
0991: oneFullName = (String) fieldsToUse.nextElement();
0992:
0993: StringTokenizer stk = new StringTokenizer(oneFullName, ".");
0994: int stkCount = stk.countTokens();
0995: if (stkCount == 2) {
0996: oneTableName = stk.nextToken();
0997: oneFieldName = stk.nextToken();
0998: } else if (stkCount == 3) {
0999: String oneSchemaName = stk.nextToken();
1000: oneTableName = stk.nextToken();
1001: oneFieldName = stk.nextToken();
1002: oneTableName = oneSchemaName + "." + oneTableName;
1003: }
1004:
1005: oneObj = (DBObject) byTableName.get(oneTableName);
1006: skipField = false;
1007: // ----------------------------------------------------
1008: // 2003-11-18 /ebn-ma: changed...
1009: // getField() makes a translation using the charset-filter.
1010: // FieldRangeParser cannot succeed, if his control-characters
1011: // are changed, e.g. '<' to '<'
1012: // getFieldData() doesn't do this translation and so it works.
1013: // Anyway: The corresponding code-block in JDBCUtil seems to be
1014:
1015: // a better solution.
1016: // oneFieldValue =StringUtil.notNull(oneObj.getField(oneFieldName));
1017:
1018: oneFieldValue = StringUtil.notNull(oneObj
1019: .getFieldData(oneFieldName));
1020: // 2003-11-18 /ebn-ma: ...changed
1021: // ----------------------------------------------------
1022:
1023: String rangeString = oneObj.denotesRange(oneFieldValue);
1024:
1025: if (!oneFieldValue.equals("")) {
1026: oneFieldValue = oneObj.quoteIfNeeded(oneFieldName,
1027: rangeString);
1028: }
1029: if (oneFieldValue.equals("")) {
1030: skipField = true;
1031: }
1032: if (oneFieldValue == null) {
1033: skipField = true;
1034: }
1035: if (oneFieldValue.equals("\'\'")) {
1036: skipField = true;
1037: }
1038: if ((oneObj.getMetaData().getType(oneFieldName)
1039: .equals("text"))
1040: && (skipText)) {
1041: skipField = true;
1042: }
1043: if (!skipField) {
1044: // check to see if the field value is valid (protects agains sql injection)
1045: String unalteredFieldValue = oneObj.getDataField(
1046: oneFieldName).asString();
1047: if (rangeString != null) {
1048: FieldRangeParser rangeParser = new FieldRangeParser();
1049: boolean valid = rangeParser.isValidRange(oneObj
1050: .getFieldMetaData(oneFieldName),
1051: unalteredFieldValue);
1052: if (!valid) {
1053: throw new DBException(
1054: "Invalid field range value: "
1055: + unalteredFieldValue);
1056: }
1057: } else if (oneObj.containsWildCards(oneFieldValue)) {
1058: Object origValue = oneObj
1059: .getDataField(oneFieldName).getValue();
1060:
1061: String[] wildcards = null;
1062: wildcards = (String[]) oneObj.getConnectionPool()
1063: .getWildCardsList().toArray(new String[0]);
1064: Filter filter = new Filter(wildcards, wildcards);
1065: String valueWithoutWildCards = filter
1066: .stripFilter(unalteredFieldValue);
1067: // if the value without wildcards is empty, then we know the field is valid
1068: if (!valueWithoutWildCards.equals("")) {
1069: oneObj.getDataField(oneFieldName).setValue(
1070: valueWithoutWildCards);
1071: oneObj.getDataField(oneFieldName).checkValue();
1072: oneObj.getDataField(oneFieldName).setValue(
1073: origValue);
1074: }
1075: } else {
1076: oneObj.getDataField(oneFieldName).checkValue();
1077: }
1078:
1079: if (addWhere) {
1080: myStatement.append(" WHERE ");
1081: addWhere = false;
1082: }
1083: if (addAnd) {
1084: myStatement.append(" AND ");
1085: }
1086: if (oneObj.containsWildCards(oneFieldValue)) {
1087: if (caseSensitiveQuery) {
1088: myStatement.append(getTableName(oneObj) + "."
1089: + oneFieldName);
1090: myStatement.append(" LIKE ");
1091: myStatement.append(oneFieldValue);
1092: } else {
1093: myStatement.append("UPPER(");
1094: myStatement.append(getTableName(oneObj) + "."
1095: + oneFieldName);
1096: myStatement.append(") LIKE ");
1097: myStatement.append(oneFieldValue.toUpperCase());
1098: }
1099: } else if (rangeString != null) {
1100: myStatement.append(getTableName(oneObj) + "."
1101: + oneFieldName);
1102: myStatement.append(" " + rangeString + " ");
1103: myStatement.append(oneFieldValue);
1104: } else {
1105: if (caseSensitiveQuery) {
1106: myStatement.append(getTableName(oneObj) + "."
1107: + oneFieldName);
1108: myStatement.append(" = ");
1109: myStatement.append(oneFieldValue);
1110: } else {
1111: myStatement.append("UPPER(");
1112: myStatement.append(getTableName(oneObj) + "."
1113: + oneFieldName);
1114: myStatement.append(") = ");
1115: myStatement.append(oneFieldValue.toUpperCase());
1116: }
1117: }
1118:
1119: addAnd = true;
1120: }
1121:
1122: /* if field is not skipped for some reason */
1123: }
1124:
1125: /* for each field */
1126: boolean needAnd = false;
1127:
1128: /**
1129: * FIXED: ma 17.10.2003
1130: * FIXED: Don't do that at here. We do not need
1131: * FIXED: WHERE, if we do not
1132: * FIXED: have any where-condition.
1133: * FIXED: So check that in the following for-loop.
1134: * if (addWhere) {
1135: * myStatement.append(" WHERE ");
1136: * } else {
1137: * needAnd = true;
1138: * }
1139: */
1140:
1141: // ----------------------------------------------------
1142: // 2003-11-05 /ebn-ma: changed...
1143: // If originalJoins is not empty, we are working with joins and
1144: // define our relations in the from clause.
1145: // In this case ignore all accidentially defined relations...
1146: if (originalJoins.isEmpty()) {
1147: String oneRelation = null;
1148:
1149: for (Enumeration e = relations.elements(); e
1150: .hasMoreElements();) {
1151: /**
1152: * FIXED: ma 17.10.2003 Here is a better
1153: * FIXED: place for addWhere...
1154: */
1155: if (addWhere) {
1156: myStatement.append(" WHERE ");
1157: addWhere = false;
1158: } else {
1159: needAnd = true;
1160: }
1161:
1162: oneRelation = (String) e.nextElement();
1163:
1164: if (needAnd) {
1165: myStatement.append(" AND ");
1166: }
1167:
1168: myStatement.append(oneRelation);
1169: needAnd = true;
1170: }
1171: }
1172: // 2003-11-05 /ebn-ma: ...changed
1173: // ----------------------------------------------------
1174:
1175: if (log.isDebugEnabled()) {
1176: log.debug(myStatement.toString());
1177: }
1178:
1179: return myStatement.toString();
1180: } /* buildWhereClause(boolean) */
1181:
1182: /**
1183: * Insert the method's description here.
1184: * <p/>
1185: * Creation date: (10/3/00 10:48:12 AM)
1186: *
1187: * @throws com.jcorporate.expresso.core.db.DBException
1188: * The exception description.
1189: */
1190: public void clear()
1191: throws com.jcorporate.expresso.core.db.DBException {
1192: DBObject oneObj = null;
1193:
1194: for (Enumeration eachObj = myDBObjects.elements(); eachObj
1195: .hasMoreElements();) {
1196: oneObj = (DBObject) eachObj.nextElement();
1197: oneObj.clear();
1198: }
1199:
1200: /**
1201: * FIXED: ma 17.10.2003 Kill the fromClause here!
1202: */
1203: fromClause = null;
1204:
1205: } /* clear() */
1206:
1207: /**
1208: * Return the name of the context/database connection that this DB object is
1209: * using. If none is set, then we are using the "default" database/context.
1210: *
1211: * @return the name of the datacontext
1212: */
1213: public synchronized String getDBName() {
1214: return dbName;
1215: } /* getDBName() */
1216:
1217: /**
1218: * Return the name of the context/database connection that this DB object is
1219: * using. If none is set, then we are using the "default" database/context.
1220: *
1221: * @return the name of the datacontext
1222: */
1223: public synchronized String getDataContext() {
1224: return dbName;
1225: }
1226:
1227: /**
1228: * This returns the encapsulated instance of the 'model' DBObject
1229: * that was provided in 'addDBObj()';
1230: * <p/>
1231: * After a searchAndRetrieveList(), each MultiDBObject in the list has,
1232: * encapsulated, an object of each type which was previously added as a model.
1233: * Fields of this encapsulated object have been filled from whatever was retrieved.
1234: * The original query object has just the original model instance.
1235: *
1236: * @param shortName the shortname of the model object
1237: * @return the instance encapsulated by this MultiDBObject
1238: * author Abhi
1239: * @throws DBException upon error
1240: * @see #addDBObj(String, String)
1241: * Creation date: (02/01/2002 9:33 AM)
1242: */
1243: public DBObject getDBObject(String shortName) throws DBException {
1244:
1245: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1246: if (oneObj == null) {
1247: String myName = this Class + "getDBObject(String)";
1248: throw new DBException(myName + ":No such object as '"
1249: + shortName + "'");
1250: }
1251: return oneObj;
1252: } /* getDBObject(String) */
1253:
1254: /**
1255: * Get the actual DBField value specified by fieldname
1256: * <p/>
1257: * Creation date: (9/18/00 11:37:10 AM)
1258: *
1259: * @param shortName the shortname of the field
1260: * @param fieldName name of the field to retrieve
1261: * @return The value of the field/
1262: */
1263: public String getField(String shortName, String fieldName)
1264: throws DBException {
1265: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1266:
1267: if (oneObj == null) {
1268: String myName = this Class + "getField(String, String)";
1269: throw new DBException(myName + ":No such object as '"
1270: + shortName + "'");
1271: }
1272:
1273: return oneObj.getField(fieldName);
1274: } /* getFields(String, STring) */
1275:
1276: private DBObject getByShortName(String shortName)
1277: throws DBException {
1278: StringUtil.assertNotBlank(shortName,
1279: "Short name may not be blank");
1280:
1281: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1282:
1283: if (oneObj == null) {
1284: throw new DBException("No such object as '" + shortName
1285: + "'");
1286: }
1287:
1288: return oneObj;
1289: }
1290:
1291: public String getFieldDecimalFormatted(String shortName,
1292: String fieldName, String formatPattern) throws DBException {
1293: return getByShortName(shortName).getFieldDecimalFormatted(
1294: fieldName, formatPattern);
1295: } /* getFieldDecimalFormatted(String, String) */
1296:
1297: public float getFieldFloat(String shortName, String fieldName)
1298: throws DBException {
1299: return getByShortName(shortName).getFieldFloat(fieldName);
1300: } /* getFieldFloat(String, String) */
1301:
1302: /**
1303: * Returns a double object
1304: * author Peter Pilgrim <peterp@xenonsoft.demon.co.uk>
1305: * date Wed Jul 24 19:36:37 BST 2002
1306: */
1307: public double getFieldDouble(String shortName, String fieldName)
1308: throws DBException {
1309: return getByShortName(shortName).getFieldDouble(fieldName);
1310: } /* getFieldDouble(String, String) */
1311:
1312: /**
1313: * Returns a BigDecimal object
1314: * author Peter Pilgrim <peterp@xenonsoft.demon.co.uk>
1315: * date Wed Jul 24 19:36:37 BST 2002
1316: */
1317: public BigDecimal getFieldBigDecimal(String shortName,
1318: String fieldName) throws DBException {
1319: return getByShortName(shortName).getFieldBigDecimal(fieldName);
1320: } /* getFieldBigDecimal(String, String) */
1321:
1322: public Date getFieldDate(String shortName, String fieldName)
1323: throws DBException {
1324: return getByShortName(shortName).getFieldDate(fieldName);
1325: } /* getFieldDate(String, String) */
1326:
1327: public int getFieldInt(String shortName, String fieldName)
1328: throws DBException {
1329: return getByShortName(shortName).getFieldInt(fieldName);
1330: }
1331:
1332: public long getFieldLong(String shortName, String fieldName)
1333: throws DBException {
1334: return getByShortName(shortName).getFieldLong(fieldName);
1335: }
1336:
1337: /**
1338: * Construct a new MultiDBObject
1339: *
1340: * @return new MultiDBObject
1341: * @throws DBException upon error
1342: */
1343: protected MultiDBObject getThisMultiDBObj() throws DBException {
1344: MultiDBObject newObj = new MultiDBObject();
1345: newObj.setDBName(getDBName());
1346:
1347: String oneKey = null;
1348: DBObject oneDBObj = null;
1349:
1350: for (Enumeration ek = myDBObjects.keys(); ek.hasMoreElements();) {
1351: oneKey = (String) ek.nextElement();
1352: oneDBObj = (DBObject) myDBObjects.get(oneKey);
1353: newObj.addDBObj(oneDBObj.newInstance(), oneKey);
1354: }
1355:
1356: String oneRelation = null;
1357:
1358: for (Enumeration er = originalRelations.elements(); er
1359: .hasMoreElements();) {
1360: oneRelation = (String) er.nextElement();
1361:
1362: StringTokenizer stk = new StringTokenizer(oneRelation, "|");
1363: newObj.setForeignKey(stk.nextToken(), stk.nextToken(), stk
1364: .nextToken(), stk.nextToken());
1365: }
1366:
1367: // ----------------------------------------------------
1368: // 2003-11-05 /ebn-ma: added...
1369: // In the new Object we will also need this join-definitions.
1370: String oneJoin = null;
1371:
1372: for (Enumeration n = originalJoins.elements(); n
1373: .hasMoreElements();) {
1374: oneJoin = (String) n.nextElement();
1375:
1376: StringTokenizer stk = new StringTokenizer(oneJoin, "|");
1377: String leftDbo = stk.nextToken();
1378: String leftField = stk.nextToken();
1379: String rightDbo = stk.nextToken();
1380: String rightField = stk.nextToken();
1381: String joinTyp = stk.nextToken().toLowerCase();
1382:
1383: if ("left".equals(joinTyp)) {
1384: newObj.setLeftJoin(leftDbo, leftField, rightDbo,
1385: rightField);
1386: } else if ("inner".equals(joinTyp)) {
1387: newObj.setInnerJoin(leftDbo, leftField, rightDbo,
1388: rightField);
1389: } else if ("right".equals(joinTyp)) {
1390: newObj.setRightJoin(leftDbo, leftField, rightDbo,
1391: rightField);
1392: }
1393: }
1394: // 2003-11-05 /ebn-ma: ...added
1395: // ----------------------------------------------------
1396:
1397: return newObj;
1398: } /* getThisMultiDBObj() */
1399:
1400: /**
1401: * Search and retrieve in a particular order
1402: *
1403: * @param sortKeyString A pipe-delimited list of key fields to sort
1404: * the returned set by
1405: * @return A list of new database objects retrieved by the search
1406: * @throws DBException If the search could not be completed
1407: */
1408: public synchronized List searchAndRetrieveList(String sortKeyString)
1409: throws DBException {
1410:
1411: sortKeys.clear();
1412: if (sortKeyString != null) {
1413: StringTokenizer stk = new StringTokenizer(sortKeyString,
1414: "|");
1415: while (stk.hasMoreTokens()) {
1416: sortKeys.addElement(stk.nextToken());
1417: }
1418: }
1419:
1420: return searchAndRetrieveList();
1421: }
1422:
1423: /**
1424: * Search and retrieve in a particular order
1425: *
1426: * @return A list of new database objects retrieved by the search
1427: * @throws DBException If the search could not be completed
1428: */
1429: public synchronized List searchAndRetrieveList() throws DBException {
1430: boolean needComma = false;
1431: DBObject oneObj = null;
1432:
1433: HashMap rtrvListByTable = new HashMap();
1434:
1435: DBConnectionPool myPool = null;
1436: DBConnection myConnection = null;
1437:
1438: try {
1439: if (localConnection != null) {
1440: myConnection = localConnection;
1441: } else {
1442: myPool = DBConnectionPool.getInstance(getDBName());
1443: myConnection = myPool
1444: .getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
1445: }
1446:
1447: if (recordSet == null) {
1448: String myName = (this Class + "searchAndRetrieve()");
1449: throw new DBException(myName
1450: + ":Database object not correctly initialized");
1451: }
1452:
1453: recordSet.clear();
1454:
1455: String fieldName = null;
1456: FastStringBuffer myStatement = new FastStringBuffer(256);
1457: myStatement.append("SELECT ");
1458:
1459: if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_SELECT
1460: && (offsetRecord > 0 || maxRecords > 0)) {
1461:
1462: // Insert limitation stub after table nomination
1463: String limitStub = makeLimitationStub(myConnection);
1464:
1465: myStatement.append(" ");
1466: myStatement.append(limitStub);
1467: myStatement.append(" ");
1468: }
1469:
1470: if (selectDistinct) {
1471: myStatement.append(" ");
1472: myStatement.append("DISTINCT");
1473: myStatement.append(" ");
1474: }
1475:
1476: for (Enumeration eachObj = myDBObjects.elements(); eachObj
1477: .hasMoreElements();) {
1478: oneObj = (DBObject) eachObj.nextElement();
1479:
1480: ArrayList retrievedFieldList = (ArrayList) rtrvListByTable
1481: .get(getTableName(oneObj));
1482: if (retrievedFieldList == null) {
1483: retrievedFieldList = new ArrayList();
1484: rtrvListByTable.put(getTableName(oneObj),
1485: retrievedFieldList);
1486: }
1487:
1488: if (oneObj.anyFieldsDistinct) {
1489: String oneFieldName = null;
1490: for (Iterator i = oneObj
1491: .getDistinctFieldArrayList().iterator(); i
1492: .hasNext();) {
1493: oneFieldName = (String) i.next();
1494: if (needComma) {
1495: myStatement.append(", ");
1496: }
1497: myStatement.append(" ");
1498: myStatement.append(myPool
1499: .getDistinctRowsetKeyword());
1500: myStatement.append(" ");
1501: myStatement.append(selectFieldString(oneObj,
1502: oneFieldName));
1503: retrievedFieldList.add(oneFieldName);
1504: needComma = true;
1505: }
1506: } else if (oneObj.anyFieldsToRetrieveMulti) {
1507: String oneFieldName = null;
1508: for (Iterator i = oneObj
1509: .getFieldsToRetrieveIterator(); i.hasNext();) {
1510: oneFieldName = (String) i.next();
1511: if (needComma) {
1512: myStatement.append(", ");
1513: }
1514: myStatement.append(selectFieldString(oneObj,
1515: oneFieldName));
1516: retrievedFieldList.add(oneFieldName);
1517: needComma = true;
1518: } /* for each field */
1519: } else {
1520: for (Iterator i = oneObj.getMetaData()
1521: .getFieldListArray().iterator(); i
1522: .hasNext();) {
1523: fieldName = (String) i.next();
1524: DataFieldMetaData metaData = oneObj
1525: .getFieldMetaData(fieldName);
1526:
1527: if (!metaData.isVirtual()
1528: && !metaData.isBinaryObjectType()) {
1529: if (needComma) {
1530: myStatement.append(", ");
1531: }
1532:
1533: myStatement.append(selectFieldString(
1534: oneObj, fieldName));
1535: retrievedFieldList.add(fieldName);
1536: needComma = true;
1537: }
1538:
1539: /* if field is not virtual & not binary*/
1540: }
1541:
1542: /* for each field */
1543: }
1544: }
1545:
1546: myStatement.append(" FROM ");
1547: if (buildFromClause() && fromClause != null
1548: && fromClause.trim().length() > 0) {
1549: myStatement.append(fromClause);
1550: } else {
1551: throw new DBException(this Class + "count()"
1552: + " :Building of FROM clause failed.");
1553: }
1554: // 2003-11-05 /ebn-ma: ...changed
1555: // ----------------------------------------------------
1556:
1557: if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_TABLE
1558: && (offsetRecord > 0 || maxRecords > 0)) {
1559:
1560: // Insert limitation stub after table nomination
1561: String limitStub = makeLimitationStub(myConnection);
1562: myStatement.append(" ");
1563: myStatement.append(limitStub);
1564: myStatement.append(" ");
1565: }
1566:
1567: String whereClause;
1568: if (customWhereClause != null) {
1569: if (appendCustomWhereClause) {
1570: whereClause = buildWhereClause(true) + " AND "
1571: + customWhereClause;
1572: } else {
1573: whereClause = " WHERE " + customWhereClause;
1574: }
1575: } else {
1576: whereClause = buildWhereClause(true);
1577: }
1578:
1579: myStatement.append(whereClause);
1580:
1581: appendCustomWhereClause = false;
1582:
1583: if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_WHERE
1584: && (offsetRecord > 0 || maxRecords > 0)) {
1585:
1586: // Insert limitation stub after table nomination
1587: String limitStub = makeLimitationStub(myConnection);
1588:
1589: if (whereClause.length() > 0) {
1590: myStatement.append(" AND");
1591: }
1592:
1593: myStatement.append(" ");
1594: myStatement.append(limitStub);
1595: myStatement.append(" ");
1596: }
1597:
1598: /* Add the ORDER BY clause if any sortKeys are specified */
1599: if (sortKeys.size() > 0) {
1600: myStatement.append(" ORDER BY ");
1601:
1602: boolean needComma2 = false;
1603:
1604: for (Enumeration e = sortKeys.elements(); e
1605: .hasMoreElements();) {
1606: if (needComma2) {
1607: myStatement.append(", ");
1608: }
1609:
1610: myStatement.append((String) e.nextElement());
1611: needComma2 = true;
1612: }
1613: if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_ORDER_BY
1614: && (offsetRecord > 0 || maxRecords > 0)) {
1615: myStatement.append(" ");
1616: myStatement
1617: .append(makeLimitationStub(myConnection));
1618: }
1619: }
1620:
1621: if (log.isDebugEnabled()) {
1622: log.debug("Executing " + myStatement.toString());
1623: }
1624:
1625: myConnection.execute(myStatement.toString());
1626:
1627: int returnRecordCount = 0;
1628: int loopCount = 0;
1629: while (myConnection.next()) {
1630: loopCount++;
1631:
1632: //If there's limitation syntax on, then the first record will be the
1633: //maximum record.
1634: if (loopCount <= offsetRecord
1635: && offsetRecord > 0
1636: && myConnection.getLimitationPosition() == DBConnection.LIMITATION_DISABLED) {
1637: continue;
1638: }
1639:
1640: returnRecordCount++;
1641:
1642: // maxRecords = 0 by default, so I guess this means 0 doesn't count as max number... should default to -1 ?? LAH 12/03
1643: if ((returnRecordCount > maxRecords)
1644: && (maxRecords > 0)) {
1645: break;
1646: }
1647:
1648: if (log.isDebugEnabled()) {
1649: log.debug("Returning row " + loopCount);
1650: }
1651:
1652: MultiDBObject myObj = getThisMultiDBObj();
1653: String oneFieldValue = null;
1654: int i = 1;
1655:
1656: for (Enumeration eachObj = myDBObjects.elements(); eachObj
1657: .hasMoreElements();) {
1658: oneObj = (DBObject) eachObj.nextElement();
1659:
1660: ArrayList retrievedFieldList = (ArrayList) rtrvListByTable
1661: .get(getTableName(oneObj));
1662: // The following should never happen .... but CYA
1663: if (retrievedFieldList == null) {
1664: retrievedFieldList = new ArrayList();
1665: }
1666: for (Iterator it = retrievedFieldList
1667: .listIterator(); it.hasNext();) {
1668: fieldName = (String) it.next();
1669: DataFieldMetaData metaData = oneObj
1670: .getFieldMetaData(fieldName);
1671:
1672: if (!metaData.isVirtual()
1673: && !metaData.isBinaryObjectType()) {
1674: try {
1675: oneFieldValue = myConnection
1676: .getString(i);
1677: } catch (DBException de) {
1678: String myName = (this Class + "searchAndRetrieve()");
1679: throw new DBException(myName
1680: + ":Error retrieving field '"
1681: + getTableName(oneObj) + "."
1682: + fieldName + "'", de);
1683: }
1684:
1685: i++;
1686:
1687: if (log.isDebugEnabled()) {
1688: log.debug("Setting "
1689: + getTableName(oneObj) + "."
1690: + fieldName + " to "
1691: + oneFieldValue);
1692: }
1693:
1694: myObj.setField((String) oneObj
1695: .getAttribute(SHORT_NAME),
1696: fieldName, oneFieldValue);
1697: }
1698: }
1699: } /* each db object */
1700:
1701: myObj.setDBName(getDBName());
1702: recordSet.add(myObj);
1703: }
1704:
1705: /* each row retrieved from the db */
1706: } finally {
1707: if (localConnection == null) {
1708: myPool.release(myConnection);
1709: }
1710: }
1711:
1712: return recordSet;
1713: }
1714:
1715: /**
1716: * This tells the buildWhereClause to either respect case (true) or
1717: * ignore case (false). You can call this method before doing a search and
1718: * retreive if you want to match without worrying about case. For example
1719: * if you where to call this method with isCaseSensitiveQuery = FALSE
1720: * then this comparison would match in the search:
1721: * <p/>
1722: * vendor_name actual value = "My Name"
1723: * <p/>
1724: * query value = "my name"
1725: * <p/>
1726: * This would match in a search and retrieve.
1727: * <p/>
1728: * author Adam Rossi, PlatinumSolutions
1729: *
1730: * @param isCaseSensitiveQuery boolean
1731: */
1732: public void setCaseSensitiveQuery(boolean isCaseSensitiveQuery) {
1733: caseSensitiveQuery = isCaseSensitiveQuery;
1734: }
1735:
1736: /**
1737: * Specify a custom "where" clause for the SQL used to retrieve records for
1738: * this object. The where clause 'reset' after each call to searchAndRetrieve()
1739: * or other retrieval methods, so it must be set just before the call to
1740: * retrieve the records is made. If no custom where clause is specified by this
1741: * method, the where clause is built from the field values in the object.
1742: * <p/>
1743: * DO NOT INCLUDE THE 'where' KEYWORD. Just what comes after the 'where'
1744: *
1745: * @param newCustomWhere java.lang.String
1746: */
1747: public synchronized void setCustomWhereClause(String newCustomWhere) {
1748: setCustomWhereClause(newCustomWhere, false);
1749: } /* setCustomWhereClause(String) */
1750:
1751: /**
1752: * Specify a custom "where" clause for the SQL used to retrieve records for
1753: * this object. The where clause 'reset' after each call to searchAndRetrieve()
1754: * or other retrieval methods, so it must be set just before the call to
1755: * retrieve the records is made. If no custom where clause is specified by this
1756: * method, the where clause is built from the field values in the object.
1757: *
1758: * @param newCustomWhere java.lang.String
1759: * @param append true if the custom where clause is to be appended to the 'built'
1760: * where clause; THIS IS DIFFERENT THAN DBObject where 'append' means to append another condition onto the custom where clause
1761: */
1762: public synchronized void setCustomWhereClause(
1763: String newCustomWhere, boolean append) {
1764: customWhereClause = newCustomWhere;
1765: appendCustomWhereClause = append;
1766: } /* setCustomWhereClause(String, boolean) */
1767:
1768: // ----------------------------------------------------
1769: // 2003-11-05 /ebn-ma: added...
1770: /**
1771: * Gets the customWhereClause
1772: *
1773: * @return customFromClause
1774: */
1775: public String getCustomWhereClause() {
1776: return customWhereClause;
1777: }
1778:
1779: /**
1780: * Specify if the customWhereClause should be appended to a generated
1781: * whereClause.
1782: *
1783: * @param newValue true = append to whereClause, else overwrite it.
1784: */
1785: public synchronized void setAppendCustomWhereClause(boolean newValue) {
1786: appendCustomWhereClause = newValue;
1787: }
1788:
1789: /**
1790: * Gets the settings of appendCustomWhereClause.
1791: *
1792: * @return appendCustomWhereClause
1793: */
1794: public boolean getAppendCustomWhereClause() {
1795: return appendCustomWhereClause;
1796: }
1797:
1798: // 2003-11-05 /ebn-ma: ...added
1799: // ----------------------------------------------------
1800:
1801: /**
1802: * Set the database name/context for this multi db object. If setDBName is not called,
1803: * the "default" db name and context is used. See
1804: * com.jcorporate.expresso.core.misc.ConfigManager for information about multiple
1805: * contexts. Note that setting a db/context name only affects the object when it
1806: * allocates it's own db connections - if a specific connection is used (via the
1807: * setConnection(DBConnection) method) then that connection must be already
1808: * associated with the correct db/context.
1809: *
1810: * @param newOther The name of the context or database to use
1811: */
1812: public synchronized void setDBName(String newOther)
1813: throws DBException {
1814: dbName = newOther;
1815: } /* setDBName(String) */
1816:
1817: /**
1818: * Specify that the DISTINCT keyword for unique rows must be specified right
1819: * after the SELECT keyword
1820: *
1821: * @param flag true if DISTINCT supported right after SELECT, false (default) otherwise.
1822: */
1823: public void setSelectDistinct(boolean flag) {
1824: selectDistinct = flag;
1825: }
1826:
1827: /**
1828: * Specify a select list of fields to retrieve from a particular DBObject component
1829: *
1830: * @param shortName The alias for the DBObject
1831: * @param fieldNames Pipe("|")-separated list of fieldnames
1832: */
1833: public void setFieldsToRetrieve(String shortName, String fieldNames)
1834: throws DBException {
1835: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1836:
1837: if (oneObj == null) {
1838: String myName = this Class
1839: + "setFieldsToRetrieve(String, String)";
1840: throw new DBException(myName + ":No such object as '"
1841: + shortName + "'");
1842: }
1843:
1844: oneObj.setFieldsToRetrieve(fieldNames);
1845: }
1846:
1847: /**
1848: * Specify to retrieve NO fields from a particular DBObject component
1849: * <p/>
1850: * author Zaz Harris, SRI International
1851: *
1852: * @param shortName The alias for the DBObject
1853: */
1854: public void setFieldsToRetrieveToNone(String shortName)
1855: throws DBException {
1856: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1857:
1858: if (oneObj == null) {
1859: String myName = this Class
1860: + "setFieldsToRetrieveToNone(String)";
1861: throw new DBException(myName + ":No such object as '"
1862: + shortName + "'");
1863: }
1864:
1865: oneObj.retrieveFields = null;
1866: oneObj.anyFieldsToRetrieveMulti = true;
1867: }
1868:
1869: /**
1870: * Specify a field to be retieved uniquely froma component DBObject
1871: *
1872: * @param shortName The alias for the DBObject
1873: * @param fieldName The field to mark as unique
1874: * @param flag true=distinct, flase=all matching rows
1875: */
1876: public void setFieldDistinct(String shortName, String fieldName,
1877: boolean flag) throws DBException {
1878: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1879:
1880: if (oneObj == null) {
1881: String myName = this Class
1882: + "setFieldDistinct(String, String, boolean)";
1883: throw new DBException(myName + ":No such object as '"
1884: + shortName + "'");
1885: }
1886:
1887: oneObj.setFieldDistinct(fieldName, flag);
1888: }
1889:
1890: /**
1891: * Insert the method's description here.
1892: * <p/>
1893: * Creation date: (9/18/00 11:37:10 AM)
1894: *
1895: * @param shortName the short name to set
1896: * @param fieldName the fieldname to set
1897: * @param fieldValue the value to set the field at.
1898: */
1899: public void setField(String shortName, String fieldName,
1900: String fieldValue) throws DBException {
1901: DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1902:
1903: if (oneObj == null) {
1904: String myName = this Class
1905: + "setField(String, String, String)";
1906: throw new DBException(myName + ":No such object as '"
1907: + shortName + "'");
1908: }
1909:
1910: oneObj.setField(fieldName, fieldValue);
1911: } /* setField(String, String, String) */
1912:
1913: /**
1914: * Insert the method's description here.
1915: * <p/>
1916: * Creation date: (9/18/00 11:36:28 AM)
1917: *
1918: * @param shortName java.lang.String
1919: * @param foreignKey java.lang.String
1920: * @param shortName2 java.lang.String
1921: * @param primaryKey java.lang.String
1922: */
1923: public void setForeignKey(String shortName, String foreignKey,
1924: String shortName2, String primaryKey) throws DBException {
1925: String myName = this Class
1926: + "setForeignKey(String, String, String, String)";
1927: DBObject foreignDBObj = (DBObject) myDBObjects.get(shortName);
1928:
1929: if (foreignDBObj == null) {
1930: throw new DBException(myName
1931: + ":DB Object with short name '" + shortName
1932: + "' is not part of this query");
1933: }
1934:
1935: DBObject primaryDBObj = (DBObject) myDBObjects.get(shortName2);
1936:
1937: if (primaryDBObj == null) {
1938: throw new DBException(myName
1939: + ":DB Object with short name '" + shortName2
1940: + "' is not part of this query");
1941: }
1942:
1943: relations.addElement(getTableName(foreignDBObj) + "."
1944: + foreignKey + " = " + getTableName(primaryDBObj) + "."
1945: + primaryKey);
1946: originalRelations.addElement(shortName + "|" + foreignKey + "|"
1947: + shortName2 + "|" + primaryKey);
1948: } /* setForeignKey(String, String, String, String) */
1949:
1950: /**
1951: * Builds a 'FROM' clause using the 'INNER JOIN' syntax.
1952: *
1953: * @param leftShortName short name for the left hand table.
1954: * @param leftColumn name of the column from the left hand table for the JOIN condition
1955: * @param rightShortName short name for the right hand table.
1956: * @param rightColumn name of the column from the right hand table for the JOIN condition
1957: */
1958: public void setInnerJoin(String leftShortName, String leftColumn,
1959: String rightShortName, String rightColumn)
1960: throws DBException {
1961:
1962: // ----------------------------------------------------
1963: // 2003-11-05 /ebn-ma: changed...
1964: // We will build the from-clause at execution time.
1965: // buildJoin(leftShortName, leftColumn, rightShortName, rightColumn, INNER_JOIN);
1966:
1967: originalJoins.addElement(leftShortName + "|" + leftColumn + "|"
1968: + rightShortName + "|" + rightColumn + "|inner");
1969: // 2003-11-05 /ebn-ma: ...changed
1970: // ----------------------------------------------------
1971: } /* setInnerJoin(String, String, String, String) */
1972:
1973: /**
1974: * Builds a 'FROM' clause using the 'LEFT JOIN' syntax.
1975: *
1976: * @param leftShortName short name for the left hand table.
1977: * @param leftColumn name of the column from the left hand table for the JOIN condition
1978: * @param rightShortName short name for the right hand table.
1979: * @param rightColumn name of the column from the right hand table for the JOIN condition
1980: */
1981: public void setLeftJoin(String leftShortName, String leftColumn,
1982: String rightShortName, String rightColumn)
1983: throws DBException {
1984:
1985: // ----------------------------------------------------
1986: // 2003-11-05 /ebn-ma: changed...
1987: // We will build the from-clause at execution time.
1988: // buildJoin(leftShortName, leftColumn, rightShortName, rightColumn, LEFT_JOIN);
1989:
1990: originalJoins.addElement(leftShortName + "|" + leftColumn + "|"
1991: + rightShortName + "|" + rightColumn + "|left");
1992: // 2003-11-05 /ebn-ma: ...changed
1993: // ----------------------------------------------------
1994: } /* setLeftJoin(String, String, String, String) */
1995:
1996: /**
1997: * Builds a 'FROM' clause using the 'LEFT JOIN' syntax.
1998: *
1999: * @param leftShortName short name for the left hand table.
2000: * @param leftColumn name of the column from the left hand table for the JOIN condition
2001: * @param rightShortName short name for the right hand table.
2002: * @param rightColumn name of the column from the right hand table for the JOIN condition
2003: */
2004: public void setRightJoin(String leftShortName, String leftColumn,
2005: String rightShortName, String rightColumn)
2006: throws DBException {
2007:
2008: // ----------------------------------------------------
2009: // 2003-11-05 /ebn-ma: changed...
2010: // We will build the from-clause at execution time.
2011: // buildJoin(leftShortName, leftColumn, rightShortName, rightColumn, RIGHT_JOIN);
2012:
2013: originalJoins.addElement(leftShortName + "|" + leftColumn + "|"
2014: + rightShortName + "|" + rightColumn + "|right");
2015: // 2003-11-05 /ebn-ma: ...changed
2016: // ----------------------------------------------------
2017: } /* setRightJoin(String, String, String, String) */
2018:
2019: /**
2020: * Builds a 'FROM' clause using ANSI 'JOIN' syntax.
2021: *
2022: * @param leftShortName short name for the left hand table.
2023: * @param leftColumn name of the column from the left hand table for the JOIN condition
2024: * @param rightShortName short name for the right hand table.
2025: * @param rightColumn name of the column from the right hand table for the JOIN condition
2026: * @param joinType the type of join to be performed, i.e. LEFT, RIGHT or INNER.
2027: */
2028: private void buildJoin(String leftShortName, String leftColumn,
2029: String rightShortName, String rightColumn, int joinType)
2030: throws DBException {
2031:
2032: // ----------------------------------------------------
2033: // 2003-11-05 /ebn-ma: changed...
2034: // In the case of outer-joins we have to obtain in the on-clause of
2035: // the join these filter-conditions, that are defined for the related
2036: // tables.
2037: // Instead: If we would add the outer-join-filters to the where-clause,
2038: // we would never see the results we intended when we made the decision
2039: // to use an outer join.
2040: // Therefore: Mark these DBObjects to be ignored when the where-clause
2041: // will be computed.
2042:
2043: String onClause = "";
2044: FastStringBuffer s = new FastStringBuffer(2048);
2045: FastStringBuffer fsb = new FastStringBuffer(256);
2046:
2047: try {
2048: DBObject leftDBObj = getByShortName(leftShortName);
2049: DBObject rightDBObj = getByShortName(rightShortName);
2050:
2051: if (fromClause == null) {
2052: s.append(getTableName(leftDBObj));
2053: } else {
2054: s.append(fromClause);
2055: }
2056:
2057: switch (joinType) {
2058: case INNER_JOIN:
2059: s.append(" INNER JOIN ");
2060: // Inner-join filters can still be placed in the where-clause.
2061: onClause = "";
2062: break;
2063: case RIGHT_JOIN:
2064: s.append(" RIGHT JOIN ");
2065: onClause = buildWhereClauseBuffer(true, fsb,
2066: leftShortName);
2067: ignoreInWhereClause = ignoreInWhereClause + ","
2068: + leftShortName + ",";
2069: break;
2070: case LEFT_JOIN:
2071: s.append(" LEFT JOIN ");
2072: onClause = buildWhereClauseBuffer(true, fsb,
2073: rightShortName);
2074: ignoreInWhereClause = ignoreInWhereClause + ","
2075: + rightShortName + ",";
2076: break;
2077: }
2078:
2079: if (onClause.length() > 5) {
2080: onClause = " AND " + onClause.substring(6);
2081: }
2082:
2083: s.append(getTableName(rightDBObj) + " ON ("
2084: + getTableName(leftDBObj) + "." + leftColumn
2085: + " = " + getTableName(rightDBObj) + "."
2086: + rightColumn + onClause + ")");
2087:
2088: fromClause = s.toString();
2089: } finally {
2090: s.release();
2091: fsb.release();
2092: }
2093: // 2003-11-05 /ebn-ma: ...changed
2094: // ----------------------------------------------------
2095: } /* buildJoin(String, String, String, String, int) */
2096:
2097: /**
2098: * Specify a maximum number of records to be retrieved in any subsequent
2099: * searchAndRetrieve() call. Records will be retrieved (in the specified
2100: * sort order) until the specified maximum is reached, then the remainder
2101: * of the result set is discarded. Specifying zero indicates that all records are to be retrieved.
2102: *
2103: * @param newMax The maximum number of records to retrieve.
2104: * @throws DBException If the max number is less than 0
2105: */
2106: public synchronized void setMaxRecords(int newMax)
2107: throws DBException {
2108:
2109: if (maxRecords < 0) {
2110: String myName = (this Class + "setMaxRecords(int)");
2111: throw new DBException(myName
2112: + ":Max records can't be less than 0");
2113: }
2114:
2115: maxRecords = newMax;
2116: } /* setMaxRecords(int) */
2117:
2118: /**
2119: * Method to set up the fields for this database object. If you wish to set
2120: * up a MultiDBQuery ahead of time you can use this method in the inherited
2121: * class to specify the objects and relationships necessary. This become
2122: * something of the equivilant of a "view" in database terms.
2123: *
2124: * @throws DBException If there is an error setting up the fields
2125: * as requested.
2126: */
2127: protected void setupFields() throws DBException {
2128: } /* setupFields() */
2129:
2130: /**
2131: * Build an appropriate String for use in the select part of an SQL statement
2132: *
2133: * @param oneObj Database object containing the field
2134: * @param fieldName The name of the field to be handled
2135: * @return The portion of the select clause with the appropriate function
2136: * wrapped around it
2137: */
2138: public String selectFieldString(DBObject oneObj, String fieldName)
2139: throws DBException {
2140: return StringUtil.replaceStringOnce(oneObj
2141: .selectFieldString(fieldName), fieldName,
2142: getTableName(oneObj) + "." + fieldName);
2143:
2144: } /* selectFieldString(String) */
2145:
2146: /**
2147: * Execute custom SQL query
2148: *
2149: * @param sqlQuery The SQL query
2150: * @param fieldCount The number of fields returned by the query
2151: * @return A list of recordlists
2152: * @throws DBException If the query could not be completed
2153: */
2154: public synchronized List makeDirectQueryList(String sqlQuery,
2155: int fieldCount) throws DBException {
2156:
2157: DBConnectionPool myPool = null;
2158: DBConnection myConnection = null;
2159:
2160: try {
2161: if (localConnection != null) {
2162: myConnection = localConnection;
2163: } else {
2164: myPool = DBConnectionPool.getInstance(getDBName());
2165: myConnection = myPool
2166: .getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
2167: }
2168:
2169: if (recordSet == null) {
2170: String myName = (this Class + "makeDirectQueryList()");
2171: throw new DBException(myName
2172: + ":Database object not correctly initialized");
2173: }
2174:
2175: recordSet.clear();
2176:
2177: if (log.isDebugEnabled()) {
2178: log.debug("Executing " + sqlQuery);
2179: }
2180:
2181: if (fieldCount > 0) {
2182: myConnection.execute(sqlQuery);
2183: } else {
2184: myConnection.executeUpdate(sqlQuery);
2185: }
2186:
2187: int recordCount = 0;
2188: while (fieldCount > 0 && myConnection.next()) {
2189: recordCount++;
2190:
2191: if ((recordCount > maxRecords) && (maxRecords > 0)) {
2192: break;
2193: }
2194:
2195: //Retreive Row
2196: List tFieldValues = new ArrayList();
2197: for (int i = 1; i <= fieldCount; i++) {
2198: String oneFieldValue = null;
2199:
2200: try {
2201: oneFieldValue = myConnection.getString(i);
2202: } catch (DBException de) {
2203: throw new DBException(this Class
2204: + "makeDirectQueryList()"
2205: + ":Error retrieving field " + i, de);
2206: }
2207:
2208: tFieldValues.add(oneFieldValue);
2209: }
2210:
2211: //Add row to rowset
2212: recordSet.add(tFieldValues);
2213: }
2214:
2215: /* each row retrieved from the db */
2216: } finally {
2217: if (localConnection == null) {
2218: myPool.release(myConnection);
2219: }
2220: }
2221:
2222: return recordSet;
2223: }
2224:
2225: /**
2226: * (assumes retrieval from DB has occurred)
2227: * assemble a given object, one retrieved by the join, getting
2228: * as many fields as found into the returned object. ignores all virtual fields.
2229: *
2230: * @param shortName name of join object
2231: * @return DBObject of type given by shortname
2232: * @deprecated v. 5.5+; 9/04; use getDBObject() instead
2233: */
2234: public DBObject assembleObject(String shortName) throws DBException {
2235: DBObject result = null;
2236: DBObject model = getDBObject(shortName);
2237: result = model.newInstance();
2238: for (Iterator iterator = model.getMetaData().getAllFieldsMap()
2239: .values().iterator(); iterator.hasNext();) {
2240: DBField oneField = (DBField) iterator.next();
2241: try {
2242: if (oneField.isVirtual) {
2243: continue; // ignore virtual fields
2244: }
2245:
2246: String fieldName = oneField.getName();
2247: if (isFieldNull(shortName, fieldName)) {
2248: result.setField(fieldName, (String) null);
2249: } else {
2250: result.setField(fieldName, getField(shortName,
2251: fieldName));
2252: }
2253: } catch (DBException e) {
2254: // we don't care if getField throws; it will do so if there is nothing retrieved for this field
2255: }
2256: }
2257: result.setStatus(DBObject.STATUS_CURRENT);
2258: return result;
2259: }
2260:
2261: /**
2262: * Specify whether the shortName specified when a DBObject is added is also
2263: * used as an alias for the table in the query. Should be false by default.
2264: *
2265: * @param flag True if the shortName should be used as an alias
2266: */
2267: public void setShortNameAsAlias(boolean flag) {
2268: shortNameAsAlias = flag;
2269: }
2270:
2271: /**
2272: * Get whether the shortName specified when a DBObject is added is also
2273: * used as an alias for the table in the query.
2274: *
2275: * @return True if short name is used as an alias for the table
2276: */
2277: public boolean getShortNameAsAlias() {
2278: return shortNameAsAlias;
2279: }
2280:
2281: /**
2282: * Get the name a table should be referenced by. Should be the name of the
2283: * table in the database unless using shortNames as alias.
2284: *
2285: * @param oneObj The DBObject to get the name of
2286: * @return The name of the table
2287: */
2288: private String getTableName(DBObject oneObj) throws DataException {
2289: String name = (String) oneObj.getAttribute(SHORT_NAME);
2290: if (name != null && shortNameAsAlias) {
2291: return name;
2292: }
2293: return oneObj.getJDBCMetaData().getTargetSQLTable(
2294: oneObj.getDataContext());
2295: }
2296:
2297: /**
2298: * @return
2299: */
2300: public DBConnection getConnection() {
2301: return localConnection;
2302: }
2303:
2304: /**
2305: * @param connection
2306: */
2307: public void setConnection(DBConnection connection) {
2308: localConnection = connection;
2309: }
2310:
2311: /**
2312: * @return true if the given field is null
2313: */
2314: public boolean isFieldNull(String shortName, String fieldName)
2315: throws DBException {
2316: return getByShortName(shortName).isFieldNull(fieldName);
2317: }
2318:
2319: } /* MultiDBObject */
|