MultiDBObject


1   /* ====================================================================
2    * The Jcorporate Apache Style Software License, Version 1.2 05-07-2002
3    *
4    * Copyright (c) 1995-2002 Jcorporate Ltd. All rights reserved.
5    *
6    * Redistribution and use in source and binary forms, with or without
7    * modification, are permitted provided that the following conditions
8    * are met:
9    *
10   * 1. Redistributions of source code must retain the above copyright
11   *    notice, this list of conditions and the following disclaimer.
12   *
13   * 2. Redistributions in binary form must reproduce the above copyright
14   *    notice, this list of conditions and the following disclaimer in
15   *    the documentation and/or other materials provided with the
16   *    distribution.
17   *
18   * 3. The end-user documentation included with the redistribution,
19   *    if any, must include the following acknowledgment:
20   *       "This product includes software developed by Jcorporate Ltd.
21   *        (http://www.jcorporate.com/)."
22   *    Alternately, this acknowledgment may appear in the software itself,
23   *    if and wherever such third-party acknowledgments normally appear.
24   *
25   * 4. "Jcorporate" and product names such as "Expresso" must
26   *    not be used to endorse or promote products derived from this
27   *    software without prior written permission. For written permission,
28   *    please contact info@jcorporate.com.
29   *
30   * 5. Products derived from this software may not be called "Expresso",
31   *    or other Jcorporate product names; nor may "Expresso" or other
32   *    Jcorporate product names appear in their name, without prior
33   *    written permission of Jcorporate Ltd.
34   *
35   * 6. No product derived from this software may compete in the same
36   *    market space, i.e. framework, without prior written permission
37   *    of Jcorporate Ltd. For written permission, please contact
38   *    partners@jcorporate.com.
39   *
40   * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
41   * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
42   * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
43   * DISCLAIMED.  IN NO EVENT SHALL JCORPORATE LTD OR ITS CONTRIBUTORS
44   * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
45   * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
46   * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
47   * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
48   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
49   * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
50   * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
51   * SUCH DAMAGE.
52   * ====================================================================
53   *
54   * This software consists of voluntary contributions made by many
55   * individuals on behalf of the Jcorporate Ltd. Contributions back
56   * to the project(s) are encouraged when you make modifications.
57   * Please send them to support@jcorporate.com. For more information
58   * on Jcorporate Ltd. and its products, please see
59   * <http://www.jcorporate.com/>.
60   *
61   * Portions of this software are based upon other open source
62   * products and are subject to their respective licenses.
63   */
64  
65  package com.jcorporate.expresso.core.dbobj;
66  
67  import com.jcorporate.expresso.core.controller.ControllerRequest;
68  import com.jcorporate.expresso.core.dataobjects.DataException;
69  import com.jcorporate.expresso.core.dataobjects.DataFieldMetaData;
70  import com.jcorporate.expresso.core.dataobjects.jdbc.FieldRangeParser;
71  import com.jcorporate.expresso.core.db.DBConnection;
72  import com.jcorporate.expresso.core.db.DBConnectionPool;
73  import com.jcorporate.expresso.core.db.DBException;
74  import com.jcorporate.expresso.core.misc.ConfigJdbc;
75  import com.jcorporate.expresso.core.misc.ConfigManager;
76  import com.jcorporate.expresso.core.misc.ConfigurationException;
77  import com.jcorporate.expresso.core.misc.StringUtil;
78  import com.jcorporate.expresso.core.security.filters.Filter;
79  import com.jcorporate.expresso.kernel.util.ClassLocator;
80  import com.jcorporate.expresso.kernel.util.FastStringBuffer;
81  import org.apache.log4j.Logger;
82  
83  import java.math.BigDecimal  ;
84  import java.util.ArrayList  ;
85  import java.util.Date  ;
86  import java.util.Enumeration  ;
87  import java.util.HashMap  ;
88  import java.util.Hashtable  ;
89  import java.util.Iterator  ;
90  import java.util.List  ;
91  import java.util.StringTokenizer  ;
92  import java.util.Vector  ;
93  
94  
95  /**
96   * This class handles joins the 'old' way.  It is not deprectated, but
97   *
98   * @see com.jcorporate.expresso.core.dataobjects.jdbc.JoinedDataObject for a more modern, flexible approach to joins
99   *      <p/>
100  *      A MultiDBObject is a group of dbobjects that are "related" - e.g. defined
101  *      as being part of a foreign-key/primary-key relationship. This may be
102  *      master/detail or a more complex relationship, but it can be expressed as a
103  *      "join" operation between the tables.
104  *      <p/>
105  *      After establishing the relationships between the objects, the MultiDBObject
106  *      can have search criteria set for it & searchAndRetrieve operations done just
107  *      like a 'regular' DBObject, but these operations affect the entire related
108  *      group of objects. At the moment, MultiDBObjects are read-only, though that
109  *      may change in the future.
110  *      <p/>
111  *      Add 'model' objects, which may have criteria already set in their fields for a search,
112  *      via addDBObj(). After a query (via searchAndRetrieveList()),
113  *      each resulting MultiDBObject instance represents a
114  *      'join' row, and has its own encapsulated instances of whatever DBObject
115  *      models have been set with addDBObj()
116  *      before the query.  In order to access these instances, use getDBObject().
117  *      <p/>
118  *      Creation date: (9/18/00 11:32:03 AM)
119  *      author  Michael Nash
120  */
121 public class MultiDBObject {
122 
123     // ----------------------------------------------------
124     // 2003-11-05 /ebn-ma: added...
125     /**
126      * Holds the shortName of these DBObjects, which have to be ignored in the
127      * where-clause because they are still used in a on-clause of a join.
128      * Ex.: ignoreInWhereClause = ",bt,,an,,qu,";
129      * if (ignoreInWhereClause.indexOf(",bt,") >= 0) { continue; }
130      */
131     private String   ignoreInWhereClause = "";
132 
133     /**
134      * Hold the join-conditions in "original" form.
135      * For the getThisMultiDBObj method and for to create the from-clause
136      * at execution-time.
137      */
138     private Vector   originalJoins = new Vector  ();
139 
140     /**
141      * If we are using a custom from clause for this query, it's stored here.
142      * If null, then build the from clause with the join or the foreign-key
143      * definitions.
144      * Do not include the sql-keyword FROM.
145      * Note: If you define a customFromClause, you will usually have to define
146      * a custemWhereClause too.
147      */
148     private String   customFromClause = null;
149     // 2003-11-05 /ebn-ma: ...added
150     // ----------------------------------------------------
151 
152     /**
153      * Use a DISTINCT keyword right after SELECT keyword
154      */
155     private boolean selectDistinct = false;
156 
157     /**
158      * Attributes of this DB Object
159      */
160     private HashMap   attributes = null;
161 
162     /**
163      * The number of records we must skip over before we start reading
164      * the <code>ResultSet</code> proper in a searchAndRetrieve.
165      * 0 means no limit
166      * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
167      */
168     transient protected int offsetRecord = 0;
169 
170     /**
171      * If we are using a custom where clause for this query, this flag will
172      * signify if we need to append the custom where clause to that generated
173      * by the setForeignKey() calls
174      */
175     private boolean appendCustomWhereClause = false;
176 
177     /**
178      * Static variables for join type
179      */
180     protected static final int INNER_JOIN = 0;
181     protected static final int RIGHT_JOIN = 1;
182     protected static final int LEFT_JOIN = 2;
183 
184     /**
185      * Constant for shortName DBObject attribute
186      */
187     protected static final String   SHORT_NAME = "shortName";
188 
189     /**
190      * 'FROM' clause formed by calls to setJoin().
191      */
192     private String   fromClause = null;
193 
194     /**
195      * If set to true, the shortname for the DBObject will be used as an alias
196      * in the query; defaults to false for backward-compatiblity
197      */
198     private boolean shortNameAsAlias = false;
199 
200     /**
201      * Return an "attribute". Attributes are temporary (e.g. not stored in the DBMS)
202      * values associated with this particular DB object instance.
203      *
204      * @param attribName The attribute name to check
205      * @return the object associated with this attribute
206      */
207     public Object   getAttribute(String   attribName) {
208         if (attributes != null) {
209             return attributes.get(attribName);
210         } else {
211             return null;
212         }
213     } /* getAttribute(String) */
214 
215 
216     /**
217      * Set an attribute. Attributes are temporary (e.g. not stored in the DBMS) values
218      * associated with this particular DB object instance.
219      *
220      * @param attribName  The name of the attribute being defined
221      * @param attribValue The object to store under this attribute name
222      */
223     public synchronized void setAttribute(String   attribName,
224                                           Object   attribValue) {
225         if (attributes == null) {
226             attributes = new HashMap  ();
227         }
228 
229         attributes.put(attribName, attribValue);
230     } /* setAttribute(String, Object) */
231 
232 
233     /**
234      * A DB Object can be told to only retrieve a certain number of records. If a
235      * "max records" value has been specified, this method provides access to it.
236      *
237      * @return The maximum number of records that should be retrieved, or zero
238      *         if no max has been set
239      */
240     public int getMaxRecords() {
241         return maxRecords;
242     } /* getMaxRecords() */
243 
244     /**
245      * Specifies the number of records that should be skipped over
246      * before any data from the <code>ResultSet</code>
247      * is retrieved in any subsequent
248      * searchAndRetrieve() call. Records will be skipped over (in the specified
249      * sort order) until the record counts is equal to or greater
250      * than the offset record. Specifying zero indicates that no
251      * records should be skipped over and the
252      * <code>ResultSet</code> immediately from the start.
253      *
254      * @param newOffset The maximum number of records to retrieve.
255      * @throws DBException If the max number is less than 0
256      *                     <p/>
257      *                     author Peter Pilgrim <peterp  at  xenonsoft dot demon dot co dot  uk>
258      *                     date Tue Feb 05 23:06:38 GMT 2002
259      */
260     public synchronized void setOffsetRecord(int newOffset)
261             throws DBException {
262         if (newOffset < 0) {
263             throw new DBException("Offset records can't be less than 0");
264         }
265 
266         offsetRecord = newOffset;
267     } /* setOffsetRecord(int) */
268 
269     /**
270      * Gets the number of records that be skipped. The offset records.
271      * A DB Object can be told to skip a certain number of
272      * records, before reading records from the <code>ResultSet</code>.
273      * <p/>
274      * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
275      *
276      * @return The maximum number of records that should be
277      *         skipped over before reading the data records.
278      * @see #setOffsetRecord(int)
279      */
280     public int getOffsetRecord() {
281         return offsetRecord;
282     } /* getOffsetRecord() */
283 
284     // ----------------------------------------------------
285     // 2003-11-05 /ebn-ma: added...
286     /**
287      * Specify a custom "from" clause for the SQL used to retrieve
288      * records for this object.
289      *
290      * @param newCustomFrom java.lang.String
291      */
292     public synchronized void setCustomFromClause(String   newCustomFrom) {
293         customFromClause = newCustomFrom;
294     }
295 
296     /**
297      * Gets the customFromClause
298      *
299      * @return customFromClause
300      */
301     public String   getCustomFromClause() {
302         return customFromClause;
303     }
304 
305     /**
306      * Build a string consisting of an SQL 'from' clause using the
307      * customFromClause, the foreign-key or the join definitions.
308      * The result will be found in property fromClause.
309      *
310      * @return true = success.
311      *         <p/>
312      *         Modify by Yves Henri AMAIZO <amy_amaizo@compuserve.com>
313      * @since $DatabaseSchema  $Date: 2004/11/18 02:03:27 $
314      */
315     public boolean buildFromClause() throws DBException {
316         boolean success = false;
317         FastStringBuffer fsb = new FastStringBuffer(256);
318         int dboCount = myDBObjects.size();
319 
320         try {
321             // ----------------------------------------------------
322             if (customFromClause != null &&
323                     customFromClause.trim().length() > 0) {
324                 // As long as a customFromClause is defined , we will use it.
325                 fromClause = customFromClause;
326                 success = true;
327             }
328             // ----------------------------------------------------
329             else if (!originalJoins.isEmpty()) {
330                 if (!originalRelations.isEmpty()) {
331                     throw new DBException(thisClass + "count()" +
332                             "You cannot mix set???Join() with setForeignKey() definitions." +
333                             "Use setInnerJoin() instead of setForeignKey().");
334                 }
335 
336                 // Build the from clause with the join definitions...
337                 fromClause = null;
338                 String   oneJoin = null;
339 
340                 for (Enumeration   n = originalJoins.elements();
341                      n.hasMoreElements();) {
342                     oneJoin = (String  ) n.nextElement();
343 
344                     StringTokenizer   stk = new StringTokenizer  (oneJoin, "|");
345                     String   leftDbo = stk.nextToken();
346                     String   leftField = stk.nextToken();
347                     String   rightDbo = stk.nextToken();
348                     String   rightField = stk.nextToken();
349                     String   joinTyp = stk.nextToken().toLowerCase();
350 
351                     if ("left".equals(joinTyp)) {
352                         buildJoin(leftDbo, leftField, rightDbo, rightField, LEFT_JOIN);
353                     } else if ("inner".equals(joinTyp)) {
354                         buildJoin(leftDbo, leftField, rightDbo, rightField, INNER_JOIN);
355                     } else if ("right".equals(joinTyp)) {
356                         buildJoin(leftDbo, leftField, rightDbo, rightField, RIGHT_JOIN);
357                     } else {
358                         throw new DBException(thisClass + "count()" +
359                                 "Missing join-type in '" + oneJoin + "'");
360                     }
361                 }
362                 success = true;
363             }
364             // ----------------------------------------------------
365             else if (!originalRelations.isEmpty() || dboCount == 1) {
366                 // To build the relations with the foreign-key definitions,
367                 // we need to have all the DBObejcts in the from clause.
368                 // If this is not the special case, where our Object has only
369                 // one embedded DBObject: We have to link them later in the
370                 // where clause.
371                 boolean needComma = false;
372                 DBObject oneObj = null;
373 
374                 for (Enumeration   eachObj = myDBObjects.elements();
375                      eachObj.hasMoreElements();) {
376                     oneObj = (DBObject) eachObj.nextElement();
377 
378                     if (needComma) {
379                         fsb.append(",");
380                     }
381 
382                     String   targetTable = oneObj.getJDBCMetaData().getTargetSQLTable(oneObj.getDataContext());
383                     String   tableName = getTableName(oneObj);
384                     fsb.append(targetTable);
385                     if (!targetTable.equals(tableName)) {
386                         fsb.append(" AS " + tableName);
387                     }
388                     needComma = true;
389                 }
390 
391                 fromClause = fsb.toString();
392                 success = true;
393             }
394             // ----------------------------------------------------
395             else {
396                 // build clause
397                 boolean needComma = false;
398 
399                 for (Enumeration   eachObj = myDBObjects.elements();
400                      eachObj.hasMoreElements();) {
401                     DBObject oneObj = (DBObject) eachObj.nextElement();
402 
403                     if (needComma) {
404                         fsb.append(",");
405                     }
406 
407                     String   targetTable = oneObj.getJDBCMetaData().getTargetSQLTable(oneObj.getDataContext());
408                     String   tableName = getTableName(oneObj);
409                     fsb.append(targetTable);
410                     if (!targetTable.equals(tableName)) {
411                         fsb.append(" AS " + tableName);
412                     }
413                     needComma = true;
414                 }
415 
416                 fromClause = fsb.toString();
417                 success = true;
418             }
419         } catch (Throwable   t) {
420             throw new DBException(t);
421         } finally {
422             fsb.release();
423         }
424 
425         return (success);
426     }
427     // 2003-11-05 /ebn-ma: ...added
428     // ----------------------------------------------------
429 
430 
431     /**
432      * Just like find, but only retrieves the count, not the records themselves.
433      *
434      * @return integer Count of the records matching the criteria
435      * @throws DBException If the search could not be completed
436      */
437     public synchronized int count(String   expr)
438             throws DBException {
439 //        boolean needComma = false;
440 //        DBObject oneObj = null;
441 //        DBField oneField = null;
442 //
443 //        DBConnectionPool myPool = null;
444 //        DBConnection myConnection = null;
445 //
446 //        //
447 //        //If myPool isn't set yet, then we need to use getDBName to
448 //        //reset.  FIXME:  Have the system throw exceptions if DBName isn't
449 //        //set instead.
450 //        //
451 //        if (myPool == null) {
452 //            getDBName();
453 //        }
454 //        FastStringBuffer myStatement = new FastStringBuffer(48);
455 //        myStatement.append("SELECT COUNT(*) FROM ");
456 //        needComma = false;
457 //
458 //        for (Enumeration eachObj = myDBObjects.elements();
459 //             eachObj.hasMoreElements();) {
460 //            oneObj = (DBObject) eachObj.nextElement();
461 //
462 //            if (needComma) {
463 //                myStatement.append(",");
464 //            }
465 //
466 //            myStatement.append(oneObj.getJDBCMetaData().getTargetTable());
467 //            needComma = true;
468 //        }
469 //
470 //        if (customWhereClause != null) {
471 //            myStatement.append(customWhereClause);
472 //        } else {
473 //            FastStringBuffer fsb = FastStringBuffer.getInstance();
474 //            try {
475 //                myStatement.append(buildWhereClauseBuffer(true, fsb));
476 //            } finally {
477 //                fsb.release();
478 //            }
479 //        }
480 //
481 //        try {
482 //            if (localConnection != null) {
483 //                myConnection = localConnection;
484 //            } else {
485 //                myPool = DBConnectionPool.getInstance(getDBName());
486 //                myConnection = myPool.getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
487 //            }
488 //
489 
490 //            myConnection.execute(myStatement.toString());
491 
492 //            if (myConnection.next()) {
493 //                return myConnection.getInt(1);
494 //            }
495 //        } catch (DBException de) {
496 //            throw de;
497 //        } finally {
498 //            if (localConnection == null) {
499 //                myPool.release(myConnection);
500 //            }
501 //        }
502 
503 
504         DBConnectionPool myPool = null;
505         DBConnection myConnection = null;
506 
507         try {
508             if (localConnection != null) {
509                 myConnection = localConnection;
510             } else {
511                 myPool = DBConnectionPool.getInstance(getDBName());
512                 myConnection = myPool.getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
513             }
514 
515             if (recordSet == null) {
516                 String   myName = (thisClass + "count()");
517                 throw new DBException(myName +
518                         ":Database object not correctly initialized");
519             }
520 
521             recordSet.clear();
522 
523             FastStringBuffer myStatement = new FastStringBuffer(256);
524             myStatement.append("SELECT ");
525 
526             if (selectDistinct) {
527                 myStatement.append(" DISTINCT ");
528             }
529             if (StringUtil.isBlankOrNull(expr)) {
530                 expr = "*";
531             }
532             myStatement.append("COUNT(" + expr + ") ");
533 
534             // ----------------------------------------------------
535             // 2003-11-05 /ebn-ma: changed...
536             // We build the from clause every time we genereate a
537             // sql-statement. Because:
538             // If we use joins, this is the only chance to integrate
539             // filter-conditions that follow the actual values in
540             // the properties of the related DBObjects.
541 
542 //                myStatement.append(" FROM ");
543 //                if (fromClause == null) {
544 //                    needComma = false;
545 //
546 //                    for (Enumeration eachObj = myDBObjects.elements();
547 //                         eachObj.hasMoreElements();) {
548 //                        oneObj = (DBObject) eachObj.nextElement();
549 //
550 //                        if (needComma) {
551 //                            myStatement.append(",");
552 //                        }
553 //
554 //                        myStatement.append(oneObj.getJDBCMetaData().getTargetTable());
555 //                        needComma = true;
556 //                    }
557 //                } else {
558 //                    myStatement.append(fromClause);
559 //                    /**
560 //                     * FIXED: ma 16.10.2003
561 //                     * FIXED: Don't kill the fromClause,
562 //                     * FIXED: we need it later!
563 //                     *
564 //                     * fromClause = null;
565 //                     */
566 //                }
567 
568             myStatement.append(" FROM ");
569             if (buildFromClause() &&
570                     fromClause != null &&
571                     fromClause.trim().length() > 0) {
572                 myStatement.append(fromClause);
573             } else {
574                 throw new DBException(thisClass + "count()" +
575                         " :Building of FROM clause failed.");
576             }
577             // 2003-11-05 /ebn-ma: ...changed
578             // ----------------------------------------------------
579 
580             String   whereClause;
581             if (customWhereClause != null) {
582                 if (appendCustomWhereClause) {
583                     whereClause = buildWhereClause(true) + " AND " + customWhereClause;
584                 } else {
585                     whereClause = " WHERE " + customWhereClause;
586                 }
587             } else {
588                 whereClause = buildWhereClause(true);
589             }
590 
591             myStatement.append(whereClause);
592 
593             appendCustomWhereClause = false;
594 
595             if (log.isDebugEnabled()) {
596                 log.debug("Executing " + myStatement.toString());
597             }
598 
599             myConnection.execute(myStatement.toString());
600 
601             if (myConnection.next()) {
602                 return myConnection.getInt(1);
603             }
604         } finally {
605             if (localConnection == null) {
606                 myPool.release(myConnection);
607             }
608         }
609 
610 
611         return 0;
612     } /* count() */
613 
614     /**
615      * Just like find, but only retrieves the count, not the records themselves.
616      *
617      * @return integer Count of the records matching the criteria
618      * @throws DBException If the search could not be completed
619      */
620     public synchronized int count()
621             throws DBException {
622         return count("");
623     } /* count() */
624 
625 
626     /**
627      * Creates the limitation syntax optimisation stub
628      * to embed inside the SQL command that performs
629      * search and retrieve.
630      * <p/>
631      * <p>This method takes the limitation syntax string
632      * and performs a string replacement on the following
633      * tokens
634      * <p/>
635      * <ul>
636      * <p/>
637      * <li><b>%offset%</b><li><br>
638      * the number of rows in the <code>ResultSet</code> to skip
639      * before reading the data.
640      * <p/>
641      * <li><b>%maxrecord%</b><li><br>
642      * the maximum number of rows to read from  the <code>ResultSet</code>.
643      * Also known as the <i>rowlength</i>.
644      * <p/>
645      * <li><b>%endrecord%</b><li><br>
646      * the last record of in the <code>ResultSet</code> that the
647      * search and retrieved should retrieve. The end record number
648      * is equal to <code>( %offset% + %maxrecord% - 1 )</code>
649      * <p/>
650      * </ul>
651      * <p/>
652      * </p>
653      * <p/>
654      * <p/>
655      * author Peter Pilgrim, Thu Jun 21 10:30:59 BST 2001
656      *
657      * @param theConnection the db connection to make this stub from
658      * @return the limitation syntax stub string
659      * @see #searchAndRetrieve()
660      * @see #setOffsetRecord( int )
661      * @see #setMaxRecords( int )
662      */
663     protected String   makeLimitationStub(DBConnection theConnection) {
664         String   limit = theConnection.getLimitationSyntax();
665         int offset = this.getOffsetRecord();
666         int maxrec = this.getMaxRecords();
667         int endrec = offset + maxrec - 1;
668         limit = StringUtil.replace(limit, "%offset%", Integer.toString(offset));
669         limit = StringUtil.replace(limit, "%maxrecords%",
670                 Integer.toString(maxrec));
671 
672         // limit = StringUtil.replace( limit, "%length%",    Integer.toString( maxrec ) );
673         limit = StringUtil.replace(limit, "%endrecord%",
674                 Integer.toString(endrec));
675 
676         return limit;
677     } /* makeLimitationStub(DBConnection) */
678 
679 
680     private static final String   thisClass = MultiDBObject.class.getName() + ".";
681 
682     /**
683      * Hash that contains the DB objects that relate to make up this query,
684      * indexed by the "short" name provided by the user.
685      */
686     private Hashtable   myDBObjects = new Hashtable  ();
687 
688     /**
689      * Vector to hold the "relations" between the different db objects that
690      * make up this query
691      */
692     private Vector   relations = new Vector  ();
693     private String   dbName = null;
694 
695     /**
696      * This flag tells the buildWhereClause method(s) to either be case
697      * sensitive (normal queries) or to be case insensitive. The case
698      * insensitive query is useful when you want to do a search and retreive
699      * and want case insensitive matching.
700      */
701     private boolean caseSensitiveQuery = true;
702 
703     /**
704      * broken into a vector
705      */
706     private Vector   sortKeys = new Vector  ();
707 
708     /**
709      * The vector of MultiDB objects retrieved by the last searchAndRetrieve method
710      */
711     private List   recordSet = new ArrayList  ();
712 
713     /**
714      * If we are using a custom where clause for this query, it's stored here. If
715      * null, then build the where clause
716      */
717     private String   customWhereClause = null;
718 
719     /**
720      * Hold the relations in "original" form - for the getThisMultiDBObj method
721      */
722     private Vector   originalRelations = new Vector  ();
723 
724     /**
725      * Local connection that we use if it's initialized, but
726      * if it's null we generate our own connection
727      */
728     protected DBConnection localConnection = null;
729 
730     /* The max number of records we retrieve in a searchAndRetrieve.
731     * 0 means no limit
732     */
733     protected int maxRecords = 0;
734     private static Logger log = Logger.getLogger(MultiDBObject.class);
735 
736     /**
737      * MultiDBObject constructor. calls setupFields.
738      */
739     public MultiDBObject()
740             throws DBException {
741         setupFields();
742     } /* MultiDBObject() */
743 
744     /**
745      * MultiDBObject constructor which sets dbname from request
746      */
747     public MultiDBObject(ControllerRequest request)
748             throws DBException {
749         this();
750         setDBName(request.getDataContext());
751     } /* MultiDBObject() */
752 
753     /**
754      * Add a DB Object to the objects being used for this multidbobj query. The
755      * object is specified with a full classname, then a "short" name used to refer
756      * to it in this object. For example, the class name might be
757      * com.jcorporate.expresso.services.dbobj.SchemaList, the short name might be
758      * "schema".
759      *
760      * @param dbobjClassName java.lang.String
761      * @param shortName      java.lang.String
762      */
763     public void addDBObj(String   dbobjClassName, String   shortName)
764             throws DBException {
765         String   myName = thisClass + "addDBObj(String, String)";
766 
767         if (StringUtil.notNull(dbobjClassName).equals("")) {
768             throw new DBException(myName + ":Must specify a class name");
769         }
770         if (StringUtil.notNull(shortName).equals("")) {
771             throw new DBException(myName + ":Must specify a short name");
772         }
773 
774         DBObject oneDBObj = null;
775 
776         try {
777             Class   c = ClassLocator.loadClass(dbobjClassName);
778             oneDBObj = (DBObject) c.newInstance();
779         } catch (ClassNotFoundException   cn) {
780             throw new DBException(myName + ":DBObject '" + dbobjClassName +
781                     "' not found", cn);
782         } catch (InstantiationException   ie) {
783             throw new DBException(myName + ":DBObject '" + dbobjClassName +
784                     "' cannot be instantiated", ie);
785         } catch (IllegalAccessException   iae) {
786             throw new DBException(myName +
787                     ":Illegal access loading DBObject '" +
788                     dbobjClassName + "'", iae);
789         }
790 
791         addDBObj(oneDBObj, shortName);
792     }
793 
794     public void addDBObj(DBObject oneDBObj, String   shortName)
795             throws DBException {
796         StringUtil.assertNotBlank(shortName, "Short name cannot be blank here");
797         oneDBObj.setAttribute(SHORT_NAME, shortName);
798         myDBObjects.put(shortName, oneDBObj);
799 
800         if (getDBName() == null) {
801             setDBName(oneDBObj.getDataContext());
802         }
803 
804         /* Check to see if all of the db objects are in the same database */
805         oneDBObj.setDataContext(getDBName());
806 
807         if (!oneDBObj.getDataContext().equals(getDBName())) {
808             throw new DBException("DB Object '" +
809                     oneDBObj.getClass().getName() +
810                     "' was mapped to db/context '" +
811                     oneDBObj.getDataContext() +
812                     "', but this MultiDBObject is in context '" +
813                     getDBName() + "'");
814         }
815     } /* addDBOj(String, String) */
816 
817     /**
818      * Build and return a string consisting of an SQL 'where' clause
819      * using the current field values as criteria for the search. See
820      * setCustomWhereClause for information on specifying a more complex where clause.
821      *
822      * @param useAllFields True if all fields are to be used,
823      *                     false for only key fields
824      * @return The where clause to use in a query.
825      */
826     public String   buildWhereClause(boolean useAllFields)
827             throws DBException {
828         FastStringBuffer fsb = FastStringBuffer.getInstance();
829         try {
830             return buildWhereClauseBuffer(useAllFields, fsb).toString();
831         } finally {
832             fsb.release();
833         }
834     } /* buildWhereClause(boolean) */
835 
836     // ----------------------------------------------------
837     // 2003-11-05 /ebn-ma: added...
838     // Method is overwritten for to support a new parameter dboAlias.
839     /**
840      * Build and return a string consisting of an SQL 'where' clause
841      * using the current field values as criteria for the search. See
842      * setCustomWhereClause for information on specifying a more complex where clause.
843      *
844      * @param useAllFields True if all fields are to be used, false for only key fields
845      * @return java.lang.String.
846      */
847     protected String   buildWhereClauseBuffer(boolean useAllFields, FastStringBuffer myStatement)
848             throws DBException {
849         return buildWhereClauseBuffer(useAllFields, myStatement, " ").toString();
850     }
851     // 2003-11-05 /ebn-ma: ...added
852     // ----------------------------------------------------
853 
854     // ----------------------------------------------------
855     // 2003-11-05 /ebn-ma: changed...
856     // Method supports a new parameter dboAlias. With the help of this parameter
857     // we build selective clauses for a single DBObject.
858     // These clauses are used in method buildJoin.
859     /**
860      * Build and return a string consisting of an SQL 'where' clause
861      * using the current field values as criteria for the search. See
862      * setCustomWhereClause for information on specifying a more complex where clause.
863      *
864      * @param useAllFields True if all fields are to be used, false for only key fields
865      * @param myStatement  Buffer for to build the new where-clause.
866      * @param dboAlias     Build the where-clause for this DBObject. If empty,
867      *                     work with all DBObjects that are not marked to be ignored.
868      * @return java.lang.String.
869      */
870     protected String   buildWhereClauseBuffer(boolean useAllFields, FastStringBuffer myStatement, String   dboAlias)
871             throws DBException {
872 
873         /* list of field names (with table names prefixed) */
874         Vector   fields = new Vector  ();
875         DBObject oneObj = null;
876         String   fieldName = null;
877         Hashtable   byTableName = new Hashtable  ();
878 
879         if (useAllFields) {
880             for (Enumeration   eachObj = myDBObjects.elements();
881                  eachObj.hasMoreElements();) {
882                 oneObj = (DBObject) eachObj.nextElement();
883                 byTableName.put(getTableName(oneObj), oneObj);
884 
885                 // ----------------------------------------------------
886                 // 2003-11-05 /ebn-ma: added...
887                 // Two cases:
888                 // 1. If dboAlias tells us to compute the on-clause of a join,
889                 //    then we will ignore every DBObject that is not the one
890                 //    specified in dboAlias.
891                 // 2. If dboAlias is empty, we are computing the where-clause of
892                 //    our sql-statement.
893                 //    In this case we ignore the current DBObject, if it is a
894                 //    member of ignoreInWhereClause.
895                 String   thisDbo = (String  ) oneObj.getAttribute(SHORT_NAME);
896 
897                 if (dboAlias.trim().length() > 0) {
898                     if (!thisDbo.equals(dboAlias)) {
899                         continue;
900                     }
901                 } else if (ignoreInWhereClause.indexOf("," + thisDbo + ",") >= 0) {
902                     continue;
903                 }
904                 // 2003-11-05 /ebn-ma: ...added
905                 // ----------------------------------------------------
906 
907                 for (Iterator   i = oneObj.getMetaData().getFieldListArray().iterator(); i.hasNext();) {
908                     fieldName = (String  ) i.next();
909 
910                     if (!oneObj.getMetaData().getFieldMetadata(fieldName).isVirtual()) {
911                         fields.addElement(getTableName(oneObj) + "." +
912                                 fieldName);
913                     }
914                 } /* for each field */
915             }
916         } else { /* for each db object */
917             for (Enumeration   eachObj = myDBObjects.elements();
918                  eachObj.hasMoreElements();) {
919                 oneObj = (DBObject) eachObj.nextElement();
920 
921                 // ----------------------------------------------------
922                 // 2003-11-05 /ebn-ma: added...
923                 // Two cases:
924                 // 1. If dboAlias tells us to compute the on-clause of a join,
925                 //    then we will ignore every DBObject that is not the one
926                 //    specified in dboAlias.
927                 // 2. If dboAlias is empty, we are computing the where-clause of
928                 //    our sql-statement.
929                 //    In this case we ignore the current DBObject, if it is a
930                 //    member of ignoreInWhereClause.
931                 String   thisDbo = (String  ) oneObj.getAttribute(SHORT_NAME);
932 
933                 if (dboAlias.trim().length() > 0) {
934                     if (!thisDbo.equals(dboAlias)) {
935                         continue;
936                     }
937                 } else if (ignoreInWhereClause.indexOf("," + thisDbo + ",") >= 0) {
938                     continue;
939                 }
940                 // 2003-11-05 /ebn-ma: ...added
941                 // ----------------------------------------------------
942 
943                 for (Iterator   i = oneObj.getKeyFieldListIterator();
944                      i.hasNext();) {
945                     fieldName = (String  ) i.next();
946 
947                     if (!oneObj.getMetaData().getFieldMetadata(fieldName).isVirtual()) {
948                         fields.addElement(getTableName(oneObj) + "." +
949                                 fieldName);
950                     }
951                 } /* for each field */
952             } /* for each db object */
953         }
954 
955         /* Now go thru each field - if it is non-empty, add it's criteria */
956 
957         /* to the where clause. If it is empty, just skip to the next one */
958         boolean addWhere = true;
959         boolean addAnd = false;
960         String   oneFieldName = null;
961         String   oneTableName = null;
962         String   oneFullName = null;
963         String   oneFieldValue = null;
964         boolean skipText = false;
965 
966         try {
967             ConfigJdbc myConfig = ConfigManager.getJdbcRequired(getDBName());
968             skipText = myConfig.skipText();
969         } catch (ConfigurationException ce) {
970             throw new DBException(ce);
971         }
972 
973         boolean skipField = false;
974 
975         for (Enumeration   fieldsToUse = fields.elements();
976              fieldsToUse.hasMoreElements();) {
977             oneFullName = (String  ) fieldsToUse.nextElement();
978 
979             StringTokenizer   stk = new StringTokenizer  (oneFullName, ".");
980             int stkCount = stk.countTokens();
981             if (stkCount == 2) {
982                 oneTableName = stk.nextToken();
983                 oneFieldName = stk.nextToken();
984             } else if (stkCount == 3) {
985                 String   oneSchemaName = stk.nextToken();
986                 oneTableName = stk.nextToken();
987                 oneFieldName = stk.nextToken();
988                 oneTableName = oneSchemaName + "." + oneTableName;
989             }
990 
991             oneObj = (DBObject) byTableName.get(oneTableName);
992             skipField = false;
993             // ----------------------------------------------------
994             // 2003-11-18 /ebn-ma: changed...
995             // getField() makes a translation using the charset-filter.
996             // FieldRangeParser cannot succeed, if his control-characters
997             // are changed, e.g. '<' to '&lt'
998             // getFieldData() doesn't do this translation and so it works.
999             // Anyway: The corresponding code-block in JDBCUtil seems to be
1000
1001
1002            // a better solution.
1003            //  oneFieldValue =StringUtil.notNull(oneObj.getField(oneFieldName));
1004
1005
1006            oneFieldValue = StringUtil.notNull(oneObj.getFieldData(oneFieldName));
1007            // 2003-11-18 /ebn-ma: ...changed
1008            // ----------------------------------------------------
1009
1010            String   rangeString = oneObj.denotesRange(oneFieldValue);
1011
1012            if (!oneFieldValue.equals("")) {
1013                oneFieldValue = oneObj.quoteIfNeeded(oneFieldName, rangeString);
1014            }
1015            if (oneFieldValue.equals("")) {
1016                skipField = true;
1017            }
1018            if (oneFieldValue == null) {
1019                skipField = true;
1020            }
1021            if (oneFieldValue.equals("\'\'")) {
1022                skipField = true;
1023            }
1024            if ((oneObj.getMetaData().getType(oneFieldName).equals("text")) && (skipText)) {
1025                skipField = true;
1026            }
1027            if (!skipField) {
1028                // check to see if the field value is valid (protects agains sql injection)
1029                String   unalteredFieldValue = oneObj.getDataField(oneFieldName).asString();
1030                if (rangeString != null) {
1031                    FieldRangeParser rangeParser = new FieldRangeParser();
1032                    boolean valid = rangeParser.isValidRange(oneObj.getFieldMetaData(oneFieldName),
1033                            unalteredFieldValue);
1034                    if (!valid) {
1035                        throw new DBException("Invalid field range value: " + unalteredFieldValue);
1036                    }
1037                } else if (oneObj.containsWildCards(oneFieldValue)) {
1038                    Object   origValue = oneObj.getDataField(oneFieldName).getValue();
1039
1040                    String  [] wildcards = null;
1041                    wildcards = (String  []) oneObj.getConnectionPool().getWildCardsList().toArray(new String  [0]);
1042                    Filter filter = new Filter(wildcards, wildcards);
1043                    String   valueWithoutWildCards = filter.stripFilter(unalteredFieldValue);
1044                    // if the value without wildcards is empty, then we know the field is valid
1045                    if (!valueWithoutWildCards.equals("")) {
1046                        oneObj.getDataField(oneFieldName).setValue(valueWithoutWildCards);
1047                        oneObj.getDataField(oneFieldName).checkValue();
1048                        oneObj.getDataField(oneFieldName).setValue(origValue);
1049                    }
1050                } else {
1051                    oneObj.getDataField(oneFieldName).checkValue();
1052                }
1053
1054
1055                if (addWhere) {
1056                    myStatement.append(" WHERE ");
1057                    addWhere = false;
1058                }
1059                if (addAnd) {
1060                    myStatement.append(" AND ");
1061                }
1062                if (oneObj.containsWildCards(oneFieldValue)) {
1063                    if (caseSensitiveQuery) {
1064                        myStatement.append(getTableName(oneObj) + "." +
1065                                oneFieldName);
1066                        myStatement.append(" LIKE ");
1067                        myStatement.append(oneFieldValue);
1068                    } else {
1069                        myStatement.append("UPPER(");
1070                        myStatement.append(getTableName(oneObj) + "." +
1071                                oneFieldName);
1072                        myStatement.append(") LIKE ");
1073                        myStatement.append(oneFieldValue.toUpperCase());
1074                    }
1075                } else if (rangeString != null) {
1076                    myStatement.append(getTableName(oneObj) + "." +
1077                            oneFieldName);
1078                    myStatement.append(" " + rangeString + " ");
1079                    myStatement.append(oneFieldValue);
1080                } else {
1081                    if (caseSensitiveQuery) {
1082                        myStatement.append(getTableName(oneObj) + "." +
1083                                oneFieldName);
1084                        myStatement.append(" = ");
1085                        myStatement.append(oneFieldValue);
1086                    } else {
1087                        myStatement.append("UPPER(");
1088                        myStatement.append(getTableName(oneObj) + "." +
1089                                oneFieldName);
1090                        myStatement.append(") = ");
1091                        myStatement.append(oneFieldValue.toUpperCase());
1092                    }
1093                }
1094
1095                addAnd = true;
1096            }
1097
1098            /* if field is not skipped for some reason */
1099        }
1100
1101        /* for each field */
1102        boolean needAnd = false;
1103
1104        /**
1105         * FIXED: ma 17.10.2003
1106         * FIXED: Don't do that at here. We do not need
1107         * FIXED: WHERE, if we do not
1108         * FIXED: have any where-condition.
1109         * FIXED: So check that in the following for-loop.
1110         *        if (addWhere) {
1111         *            myStatement.append(" WHERE ");
1112         *        } else {
1113         *            needAnd = true;
1114         *        }
1115         */
1116
1117        // ----------------------------------------------------
1118        // 2003-11-05 /ebn-ma: changed...
1119        // If originalJoins is not empty, we are working with joins and
1120        // define our relations in the from clause.
1121        // In this case ignore all accidentially defined relations...
1122        if (originalJoins.isEmpty()) {
1123            String   oneRelation = null;
1124
1125            for (Enumeration   e = relations.elements(); e.hasMoreElements();) {
1126                /**
1127                 * FIXED: ma 17.10.2003  Here is a better
1128                 * FIXED: place for addWhere...
1129                 */
1130                if (addWhere) {
1131                    myStatement.append(" WHERE ");
1132                    addWhere = false;
1133                } else {
1134                    needAnd = true;
1135                }
1136
1137                oneRelation = (String  ) e.nextElement();
1138
1139                if (needAnd) {
1140                    myStatement.append(" AND ");
1141                }
1142
1143                myStatement.append(oneRelation);
1144                needAnd = true;
1145            }
1146        }
1147        // 2003-11-05 /ebn-ma: ...changed
1148        // ----------------------------------------------------
1149
1150        if (log.isDebugEnabled()) {
1151            log.debug(myStatement.toString());
1152        }
1153
1154        return myStatement.toString();
1155    } /* buildWhereClause(boolean) */
1156
1157
1158    /**
1159     * Insert the method's description here.
1160     * <p/>
1161     * Creation date: (10/3/00 10:48:12 AM)
1162     *
1163     * @throws com.jcorporate.expresso.core.db.DBException
1164     *          The exception description.
1165     */
1166    public void clear()
1167            throws com.jcorporate.expresso.core.db.DBException {
1168        DBObject oneObj = null;
1169
1170        for (Enumeration   eachObj = myDBObjects.elements();
1171             eachObj.hasMoreElements();) {
1172            oneObj = (DBObject) eachObj.nextElement();
1173            oneObj.clear();
1174        }
1175
1176        /**
1177         * FIXED: ma 17.10.2003  Kill the fromClause here!
1178         */
1179        fromClause = null;
1180
1181    } /* clear() */
1182
1183
1184    /**
1185     * Return the name of the context/database connection that this DB object is
1186     * using. If none is set, then we are using the "default" database/context.
1187     *
1188     * @return the name of the datacontext
1189     */
1190    public synchronized String   getDBName() {
1191        return dbName;
1192    } /* getDBName() */
1193
1194    /**
1195     * Return the name of the context/database connection that this DB object is
1196     * using. If none is set, then we are using the "default" database/context.
1197     *
1198     * @return the name of the datacontext
1199     */
1200    public synchronized String   getDataContext() {
1201        return dbName;
1202    }
1203
1204
1205    /**
1206     * This returns the encapsulated instance of the 'model' DBObject
1207     * that was provided in 'addDBObj()';
1208     * <p/>
1209     * After a searchAndRetrieveList(), each MultiDBObject in the list has,
1210     * encapsulated, an object of each type which was previously added as a model.
1211     * Fields of this encapsulated object have been filled from whatever was retrieved.
1212     * The original query object has just the original model instance.
1213     *
1214     * @param shortName the shortname of the model object
1215     * @return the instance encapsulated by this MultiDBObject
1216     *         author Abhi
1217     * @throws DBException upon error
1218     * @see #addDBObj(String, String)
1219     *      Creation date: (02/01/2002 9:33 AM)
1220     */
1221    public DBObject getDBObject(String   shortName)
1222            throws DBException {
1223
1224        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1225        if (oneObj == null) {
1226            String   myName = thisClass + "getDBObject(String)";
1227            throw new DBException(myName + ":No such object as '"
1228                    + shortName + "'");
1229        }
1230        return oneObj;
1231    } /* getDBObject(String) */
1232
1233
1234    /**
1235     * Get the actual DBField value specified by fieldname
1236     * <p/>
1237     * Creation date: (9/18/00 11:37:10 AM)
1238     *
1239     * @param shortName the shortname of the field
1240     * @param fieldName name of the field to retrieve
1241     * @return The value of the field/
1242     */
1243    public String   getField(String   shortName, String   fieldName)
1244            throws DBException {
1245        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1246
1247        if (oneObj == null) {
1248            String   myName = thisClass + "getField(String, String)";
1249            throw new DBException(myName + ":No such object as '" + shortName +
1250                    "'");
1251        }
1252
1253        return oneObj.getField(fieldName);
1254    } /* getFields(String, STring) */
1255
1256
1257    private DBObject getByShortName(String   shortName)
1258            throws DBException {
1259        StringUtil.assertNotBlank(shortName, "Short name may not be blank");
1260
1261        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1262
1263        if (oneObj == null) {
1264            throw new DBException("No such object as '" + shortName + "'");
1265        }
1266
1267        return oneObj;
1268    }
1269
1270    public String   getFieldDecimalFormatted(String   shortName, String   fieldName,
1271                                           String   formatPattern)
1272            throws DBException {
1273        return getByShortName(shortName).getFieldDecimalFormatted(fieldName,
1274                formatPattern);
1275    } /* getFieldDecimalFormatted(String, String) */
1276
1277
1278    public float getFieldFloat(String   shortName, String   fieldName)
1279            throws DBException {
1280        return getByShortName(shortName).getFieldFloat(fieldName);
1281    } /* getFieldFloat(String, String) */
1282
1283    /**
1284     * Returns a double object
1285     * author Peter Pilgrim <peterp@xenonsoft.demon.co.uk>
1286     * date Wed Jul 24 19:36:37 BST 2002
1287     */
1288    public double getFieldDouble(String   shortName, String   fieldName)
1289            throws DBException {
1290        return getByShortName(shortName).getFieldDouble(fieldName);
1291    } /* getFieldDouble(String, String) */
1292
1293    /**
1294     * Returns a BigDecimal object
1295     * author Peter Pilgrim <peterp@xenonsoft.demon.co.uk>
1296     * date Wed Jul 24 19:36:37 BST 2002
1297     */
1298    public BigDecimal   getFieldBigDecimal(String   shortName, String   fieldName)
1299            throws DBException {
1300        return getByShortName(shortName).getFieldBigDecimal(fieldName);
1301    } /* getFieldBigDecimal(String, String) */
1302
1303    public Date   getFieldDate(String   shortName, String   fieldName)
1304            throws DBException {
1305        return getByShortName(shortName).getFieldDate(fieldName);
1306    } /* getFieldDate(String, String) */
1307
1308
1309    public int getFieldInt(String   shortName, String   fieldName)
1310            throws DBException {
1311        return getByShortName(shortName).getFieldInt(fieldName);
1312    }
1313
1314    public long getFieldLong(String   shortName, String   fieldName)
1315            throws DBException {
1316        return getByShortName(shortName).getFieldLong(fieldName);
1317    }
1318
1319    /**
1320     * Construct a new MultiDBObject
1321     *
1322     * @return new MultiDBObject
1323     * @throws DBException upon error
1324     */
1325    protected MultiDBObject getThisMultiDBObj()
1326            throws DBException {
1327        MultiDBObject newObj = new MultiDBObject();
1328        newObj.setDBName(getDBName());
1329
1330        String   oneKey = null;
1331        DBObject oneDBObj = null;
1332
1333        for (Enumeration   ek = myDBObjects.keys(); ek.hasMoreElements();) {
1334            oneKey = (String  ) ek.nextElement();
1335            oneDBObj = (DBObject) myDBObjects.get(oneKey);
1336            newObj.addDBObj(oneDBObj.newInstance(), oneKey);
1337        }
1338
1339        String   oneRelation = null;
1340
1341        for (Enumeration   er = originalRelations.elements();
1342             er.hasMoreElements();) {
1343            oneRelation = (String  ) er.nextElement();
1344
1345            StringTokenizer   stk = new StringTokenizer  (oneRelation, "|");
1346            newObj.setForeignKey(stk.nextToken(), stk.nextToken(),
1347                    stk.nextToken(), stk.nextToken());
1348        }
1349
1350        // ----------------------------------------------------
1351        // 2003-11-05 /ebn-ma: added...
1352        // In the new Object we will also need this join-definitions.
1353        String   oneJoin = null;
1354
1355        for (Enumeration   n = originalJoins.elements();
1356             n.hasMoreElements();) {
1357            oneJoin = (String  ) n.nextElement();
1358
1359            StringTokenizer   stk = new StringTokenizer  (oneJoin, "|");
1360            String   leftDbo = stk.nextToken();
1361            String   leftField = stk.nextToken();
1362            String   rightDbo = stk.nextToken();
1363            String   rightField = stk.nextToken();
1364            String   joinTyp = stk.nextToken().toLowerCase();
1365
1366            if ("left".equals(joinTyp)) {
1367                newObj.setLeftJoin(leftDbo, leftField, rightDbo, rightField);
1368            } else if ("inner".equals(joinTyp)) {
1369                newObj.setInnerJoin(leftDbo, leftField, rightDbo, rightField);
1370            } else if ("right".equals(joinTyp)) {
1371                newObj.setRightJoin(leftDbo, leftField, rightDbo, rightField);
1372            }
1373        }
1374        // 2003-11-05 /ebn-ma: ...added
1375        // ----------------------------------------------------
1376
1377        return newObj;
1378    } /* getThisMultiDBObj() */
1379
1380    /**
1381     * Search and retrieve in a particular order
1382     *
1383     * @param sortKeyString A pipe-delimited list of key fields to sort
1384     *                      the returned set by
1385     * @return A list of new database objects retrieved by the search
1386     * @throws DBException If the search could not be completed
1387     */
1388    public synchronized List   searchAndRetrieveList(String   sortKeyString) throws DBException {
1389
1390        sortKeys.clear();
1391        if (sortKeyString != null) {
1392            StringTokenizer   stk = new StringTokenizer  (sortKeyString, "|");
1393            while (stk.hasMoreTokens()) {
1394                sortKeys.addElement(stk.nextToken());
1395            }
1396        }
1397
1398        return searchAndRetrieveList();
1399    }
1400
1401    /**
1402     * Search and retrieve in a particular order
1403     *
1404     * @return A list of new database objects retrieved by the search
1405     * @throws DBException If the search could not be completed
1406     */
1407    public synchronized List   searchAndRetrieveList() throws DBException {
1408        boolean needComma = false;
1409        DBObject oneObj = null;
1410
1411        HashMap   rtrvListByTable = new HashMap  ();
1412
1413        DBConnectionPool myPool = null;
1414        DBConnection myConnection = null;
1415
1416        try {
1417            if (localConnection != null) {
1418                myConnection = localConnection;
1419            } else {
1420                myPool = DBConnectionPool.getInstance(getDBName());
1421                myConnection = myPool.getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
1422            }
1423
1424            if (recordSet == null) {
1425                String   myName = (thisClass + "searchAndRetrieve()");
1426                throw new DBException(myName +
1427                        ":Database object not correctly initialized");
1428            }
1429
1430            recordSet.clear();
1431
1432            String   fieldName = null;
1433            FastStringBuffer myStatement = new FastStringBuffer(256);
1434            myStatement.append("SELECT ");
1435
1436            if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_SELECT &&
1437                    (offsetRecord > 0 || maxRecords > 0)) {
1438
1439                // Insert limitation stub after table nomination
1440                String   limitStub = makeLimitationStub(myConnection);
1441
1442                myStatement.append(" ");
1443                myStatement.append(limitStub);
1444                myStatement.append(" ");
1445            }
1446
1447            if (selectDistinct) {
1448                myStatement.append(" ");
1449                myStatement.append("DISTINCT");
1450                myStatement.append(" ");
1451            }
1452
1453            for (Enumeration   eachObj = myDBObjects.elements();
1454                 eachObj.hasMoreElements();) {
1455                oneObj = (DBObject) eachObj.nextElement();
1456
1457                ArrayList   retrievedFieldList = (ArrayList  ) rtrvListByTable.get(getTableName(oneObj));
1458                if (retrievedFieldList == null) {
1459                    retrievedFieldList = new ArrayList  ();
1460                    rtrvListByTable.put(getTableName(oneObj), retrievedFieldList);
1461                }
1462
1463                if (oneObj.anyFieldsDistinct) {
1464                    String   oneFieldName = null;
1465                    for (Iterator   i = oneObj.getDistinctFieldArrayList().iterator();
1466                         i.hasNext();
1467                            ) {
1468                        oneFieldName = (String  ) i.next();
1469                        if (needComma) {
1470                            myStatement.append(", ");
1471                        }
1472                        myStatement.append(" ");
1473                        myStatement.append(myPool.getDistinctRowsetKeyword());
1474                        myStatement.append(" ");
1475                        myStatement.append(selectFieldString(oneObj, oneFieldName));
1476                        retrievedFieldList.add(oneFieldName);
1477                        needComma = true;
1478                    }
1479                } else if (oneObj.anyFieldsToRetrieveMulti) {
1480                    String   oneFieldName = null;
1481                    for (Iterator   i = oneObj.getFieldsToRetrieveIterator();
1482                         i.hasNext();
1483                            ) {
1484                        oneFieldName = (String  ) i.next();
1485                        if (needComma) {
1486                            myStatement.append(", ");
1487                        }
1488                        myStatement.append(selectFieldString(oneObj, oneFieldName));
1489                        retrievedFieldList.add(oneFieldName);
1490                        needComma = true;
1491                    } /* for each field */
1492                } else {
1493                    for (Iterator   i = oneObj.getMetaData().getFieldListArray().iterator();
1494                         i.hasNext();
1495                            ) {
1496                        fieldName = (String  ) i.next();
1497                        DataFieldMetaData metaData =
1498                                oneObj.getFieldMetaData(fieldName);
1499
1500                        if (!metaData.isVirtual()
1501                                && !metaData.isBinaryObjectType()) {
1502                            if (needComma) {
1503                                myStatement.append(", ");
1504                            }
1505
1506                            myStatement.append(selectFieldString(oneObj, fieldName));
1507                            retrievedFieldList.add(fieldName);
1508                            needComma = true;
1509                        }
1510
1511                        /* if field is not virtual & not binary*/
1512                    }
1513
1514                    /* for each field */
1515                }
1516            }
1517
1518            myStatement.append(" FROM ");
1519            if (buildFromClause() &&
1520                    fromClause != null &&
1521                    fromClause.trim().length() > 0) {
1522                myStatement.append(fromClause);
1523            } else {
1524                throw new DBException(thisClass + "count()" +
1525                        " :Building of FROM clause failed.");
1526            }
1527            // 2003-11-05 /ebn-ma: ...changed
1528            // ----------------------------------------------------
1529
1530            if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_TABLE &&
1531                    (offsetRecord > 0 || maxRecords > 0)) {
1532
1533                // Insert limitation stub after table nomination
1534                String   limitStub = makeLimitationStub(myConnection);
1535                myStatement.append(" ");
1536                myStatement.append(limitStub);
1537                myStatement.append(" ");
1538            }
1539
1540            String   whereClause;
1541            if (customWhereClause != null) {
1542                if (appendCustomWhereClause) {
1543                    whereClause = buildWhereClause(true) + " AND " + customWhereClause;
1544                } else {
1545                    whereClause = " WHERE " + customWhereClause;
1546                }
1547            } else {
1548                whereClause = buildWhereClause(true);
1549            }
1550
1551            myStatement.append(whereClause);
1552
1553            appendCustomWhereClause = false;
1554
1555            if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_WHERE &&
1556                    (offsetRecord > 0 || maxRecords > 0)) {
1557
1558                // Insert limitation stub after table nomination
1559                String   limitStub = makeLimitationStub(myConnection);
1560
1561                if (whereClause.length() > 0) {
1562                    myStatement.append(" AND");
1563                }
1564
1565                myStatement.append(" ");
1566                myStatement.append(limitStub);
1567                myStatement.append(" ");
1568            }
1569
1570            /* Add the ORDER BY clause if any sortKeys are specified */
1571            if (sortKeys.size() > 0) {
1572                myStatement.append(" ORDER BY ");
1573
1574                boolean needComma2 = false;
1575
1576                for (Enumeration   e = sortKeys.elements(); e.hasMoreElements();) {
1577                    if (needComma2) {
1578                        myStatement.append(", ");
1579                    }
1580
1581                    myStatement.append((String  ) e.nextElement());
1582                    needComma2 = true;
1583                }
1584                if (myConnection.getLimitationPosition() == DBConnection.LIMITATION_AFTER_ORDER_BY &&
1585                        (offsetRecord > 0 || maxRecords > 0)) {
1586                    myStatement.append(" ");
1587                    myStatement.append(makeLimitationStub(myConnection));
1588                }
1589            }
1590
1591            if (log.isDebugEnabled()) {
1592                log.debug("Executing " + myStatement.toString());
1593            }
1594
1595            myConnection.execute(myStatement.toString());
1596
1597            int returnRecordCount = 0;
1598            int loopCount = 0;
1599            while (myConnection.next()) {
1600                loopCount++;
1601
1602                //If there's limitation syntax on, then the first record will be the
1603                //maximum record.
1604                if (loopCount <= offsetRecord && offsetRecord > 0 &&
1605                        myConnection.getLimitationPosition() == DBConnection.LIMITATION_DISABLED) {
1606                    continue;
1607                }
1608
1609                returnRecordCount++;
1610
1611                // maxRecords = 0 by default, so I guess this means 0 doesn't count as max number... should default to -1 ??  LAH 12/03
1612                if ((returnRecordCount > maxRecords) && (maxRecords > 0)) {
1613                    break;
1614                }
1615
1616
1617                if (log.isDebugEnabled()) {
1618                    log.debug("Returning row " + loopCount);
1619                }
1620
1621                MultiDBObject myObj = getThisMultiDBObj();
1622                String   oneFieldValue = null;
1623                int i = 1;
1624
1625                for (Enumeration   eachObj = myDBObjects.elements();
1626                     eachObj.hasMoreElements();) {
1627                    oneObj = (DBObject) eachObj.nextElement();
1628
1629                    ArrayList   retrievedFieldList = (ArrayList  ) rtrvListByTable.get(getTableName(oneObj));
1630                    // The following should never happen .... but CYA
1631                    if (retrievedFieldList == null) {
1632                        retrievedFieldList = new ArrayList  ();
1633                    }
1634                    for (Iterator   it = retrievedFieldList.listIterator();
1635                         it.hasNext();) {
1636                        fieldName = (String  ) it.next();
1637                        DataFieldMetaData metaData = oneObj.getFieldMetaData(fieldName);
1638
1639                        if (!metaData.isVirtual() && !metaData.isBinaryObjectType()) {
1640                            try {
1641                                oneFieldValue = myConnection.getString(i);
1642                            } catch (DBException de) {
1643                                String   myName = (thisClass + "searchAndRetrieve()");
1644                                throw new DBException(myName +
1645                                        ":Error retrieving field '" +
1646                                        getTableName(oneObj) +
1647                                        "." + fieldName + "'",
1648                                        de);
1649                            }
1650
1651                            i++;
1652
1653                            if (log.isDebugEnabled()) {
1654                                log.debug("Setting " +
1655                                        getTableName(oneObj) + "." +
1656                                        fieldName + " to " + oneFieldValue);
1657                            }
1658
1659                            myObj.setField((String  ) oneObj.getAttribute(SHORT_NAME),
1660                                    fieldName, oneFieldValue);
1661                        }
1662                    }
1663                } /* each db object */
1664
1665
1666                myObj.setDBName(getDBName());
1667                recordSet.add(myObj);
1668            }
1669
1670            /* each row retrieved from the db */
1671        } finally {
1672            if (localConnection == null) {
1673                myPool.release(myConnection);
1674            }
1675        }
1676
1677        return recordSet;
1678    }
1679
1680    /**
1681     * This tells the buildWhereClause to either respect case (true) or
1682     * ignore case (false). You can call this method before doing a search and
1683     * retreive if you want to match without worrying about case. For example
1684     * if you where to call this method with isCaseSensitiveQuery = FALSE
1685     * then this comparison would match in the search:
1686     * <p/>
1687     * vendor_name actual value = "My Name"
1688     * <p/>
1689     * query value = "my name"
1690     * <p/>
1691     * This would match in a search and retrieve.
1692     * <p/>
1693     * author Adam Rossi, PlatinumSolutions
1694     *
1695     * @param isCaseSensitiveQuery boolean
1696     */
1697    public void setCaseSensitiveQuery(boolean isCaseSensitiveQuery) {
1698        caseSensitiveQuery = isCaseSensitiveQuery;
1699    }
1700
1701    /**
1702     * Specify a custom "where" clause for the SQL used to retrieve records for
1703     * this object. The where clause 'reset' after each call to searchAndRetrieve()
1704     * or other retrieval methods, so it must be set just before the call to
1705     * retrieve the records is made. If no custom where clause is specified by this
1706     * method, the where clause is built from the field values in the object.
1707     * <p/>
1708     * DO NOT INCLUDE THE 'where' KEYWORD. Just what comes after the 'where'
1709     *
1710     * @param newCustomWhere java.lang.String
1711     */
1712    public synchronized void setCustomWhereClause(String   newCustomWhere) {
1713        setCustomWhereClause(newCustomWhere, false);
1714    } /* setCustomWhereClause(String) */
1715
1716    /**
1717     * Specify a custom "where" clause for the SQL used to retrieve records for
1718     * this object. The where clause 'reset' after each call to searchAndRetrieve()
1719     * or other retrieval methods, so it must be set just before the call to
1720     * retrieve the records is made. If no custom where clause is specified by this
1721     * method, the where clause is built from the field values in the object.
1722     *
1723     * @param newCustomWhere java.lang.String
1724     * @param append         true if the custom where clause is to be appended to the 'built'
1725     *                       where clause; THIS IS DIFFERENT THAN DBObject where 'append' means to append another condition onto the custom where clause
1726     */
1727    public synchronized void setCustomWhereClause(String   newCustomWhere, boolean append) {
1728        customWhereClause = newCustomWhere;
1729        appendCustomWhereClause = append;
1730    } /* setCustomWhereClause(String, boolean) */
1731
1732    // ----------------------------------------------------
1733    // 2003-11-05 /ebn-ma: added...
1734    /**
1735     * Gets the customWhereClause
1736     *
1737     * @return customFromClause
1738     */
1739    public String   getCustomWhereClause() {
1740        return customWhereClause;
1741    }
1742
1743    /**
1744     * Specify if the customWhereClause should be appended to a generated
1745     * whereClause.
1746     *
1747     * @param newValue true = append to whereClause, else overwrite it.
1748     */
1749    public synchronized void setAppendCustomWhereClause(boolean newValue) {
1750        appendCustomWhereClause = newValue;
1751    }
1752
1753    /**
1754     * Gets the settings of appendCustomWhereClause.
1755     *
1756     * @return appendCustomWhereClause
1757     */
1758    public boolean getAppendCustomWhereClause() {
1759        return appendCustomWhereClause;
1760    }
1761    // 2003-11-05 /ebn-ma: ...added
1762    // ----------------------------------------------------
1763
1764    /**
1765     * Set the database name/context for this multi db object. If setDBName is not called,
1766     * the "default" db name and context is used. See
1767     * com.jcorporate.expresso.core.misc.ConfigManager for information about multiple
1768     * contexts. Note that setting a db/context name only affects the object when it
1769     * allocates it's own db connections - if a specific connection is used (via the
1770     * setConnection(DBConnection) method) then that connection must be already
1771     * associated with the correct db/context.
1772     *
1773     * @param newOther The name of the context or database to use
1774     */
1775    public synchronized void setDBName(String   newOther)
1776            throws DBException {
1777        dbName = newOther;
1778    } /* setDBName(String) */
1779
1780
1781    /**
1782     * Specify that the DISTINCT keyword for unique rows must be specified right
1783     * after the SELECT keyword
1784     *
1785     * @param flag true if DISTINCT supported right after SELECT, false (default) otherwise.
1786     */
1787    public void setSelectDistinct(boolean flag) {
1788        selectDistinct = flag;
1789    }
1790
1791    /**
1792     * Specify a select list of fields to retrieve from a particular DBObject component
1793     *
1794     * @param shortName  The alias for the DBObject
1795     * @param fieldNames Pipe("|")-separated list of fieldnames
1796     */
1797    public void setFieldsToRetrieve(String   shortName, String   fieldNames) throws DBException {
1798        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1799
1800        if (oneObj == null) {
1801            String   myName = thisClass + "setFieldsToRetrieve(String, String)";
1802            throw new DBException(myName + ":No such object as '" + shortName +
1803                    "'");
1804        }
1805
1806        oneObj.setFieldsToRetrieve(fieldNames);
1807    }
1808
1809    /**
1810     * Specify to retrieve NO fields from a particular DBObject component
1811     * <p/>
1812     * author Zaz Harris, SRI International
1813     *
1814     * @param shortName The alias for the DBObject
1815     */
1816    public void setFieldsToRetrieveToNone(String   shortName) throws DBException {
1817        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1818
1819        if (oneObj == null) {
1820            String   myName = thisClass + "setFieldsToRetrieveToNone(String)";
1821            throw new DBException(myName + ":No such object as '" + shortName +
1822                    "'");
1823        }
1824
1825        oneObj.retrieveFields = null;
1826        oneObj.anyFieldsToRetrieveMulti = true;
1827    }
1828
1829    /**
1830     * Specify a field to be retieved uniquely froma component DBObject
1831     *
1832     * @param shortName The alias for the DBObject
1833     * @param fieldName The field to mark as unique
1834     * @param flag      true=distinct, flase=all matching rows
1835     */
1836    public void setFieldDistinct(String   shortName, String   fieldName, boolean flag) throws DBException {
1837        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1838
1839        if (oneObj == null) {
1840            String   myName = thisClass + "setFieldDistinct(String, String, boolean)";
1841            throw new DBException(myName + ":No such object as '" + shortName +
1842                    "'");
1843        }
1844
1845        oneObj.setFieldDistinct(fieldName, flag);
1846    }
1847
1848    /**
1849     * Insert the method's description here.
1850     * <p/>
1851     * Creation date: (9/18/00 11:37:10 AM)
1852     *
1853     * @param shortName  the short name to set
1854     * @param fieldName  the fieldname to set
1855     * @param fieldValue the value to set the field at.
1856     */
1857    public void setField(String   shortName, String   fieldName, String   fieldValue)
1858            throws DBException {
1859        DBObject oneObj = (DBObject) myDBObjects.get(shortName);
1860
1861        if (oneObj == null) {
1862            String   myName = thisClass + "setField(String, String, String)";
1863            throw new DBException(myName + ":No such object as '" + shortName +
1864                    "'");
1865        }
1866
1867        oneObj.setField(fieldName, fieldValue);
1868    } /* setField(String, String, String) */
1869
1870
1871    /**
1872     * Insert the method's description here.
1873     * <p/>
1874     * Creation date: (9/18/00 11:36:28 AM)
1875     *
1876     * @param shortName  java.lang.String
1877     * @param foreignKey java.lang.String
1878     * @param shortName2 java.lang.String
1879     * @param primaryKey java.lang.String
1880     */
1881    public void setForeignKey(String   shortName, String   foreignKey,
1882                              String   shortName2, String   primaryKey)
1883            throws DBException {
1884        String   myName = thisClass +
1885                "setForeignKey(String, String, String, String)";
1886        DBObject foreignDBObj = (DBObject) myDBObjects.get(shortName);
1887
1888        if (foreignDBObj == null) {
1889            throw new DBException(myName + ":DB Object with short name '" +
1890                    shortName + "' is not part of this query");
1891        }
1892
1893        DBObject primaryDBObj = (DBObject) myDBObjects.get(shortName2);
1894
1895        if (primaryDBObj == null) {
1896            throw new DBException(myName + ":DB Object with short name '" +
1897                    shortName2 + "' is not part of this query");
1898        }
1899
1900        relations.addElement(getTableName(foreignDBObj) + "." + foreignKey +
1901                " = " + getTableName(primaryDBObj) + "." +
1902                primaryKey);
1903        originalRelations.addElement(shortName + "|" + foreignKey + "|" +
1904                shortName2 + "|" + primaryKey);
1905    } /* setForeignKey(String, String, String, String) */
1906
1907    /**
1908     * Builds a 'FROM' clause using the 'INNER JOIN' syntax.
1909     *
1910     * @param leftShortName  short name for the left hand table.
1911     * @param leftColumn     name of the column from the left hand table for the JOIN condition
1912     * @param rightShortName short name for the right hand table.
1913     * @param rightColumn    name of the column from the right hand table for the JOIN condition
1914     */
1915    public void setInnerJoin(String   leftShortName, String   leftColumn,
1916                             String   rightShortName, String   rightColumn)
1917            throws DBException {
1918
1919        // ----------------------------------------------------
1920        // 2003-11-05 /ebn-ma: changed...
1921        // We will build the from-clause at execution time.
1922        // buildJoin(leftShortName, leftColumn, rightShortName, rightColumn, INNER_JOIN);
1923
1924        originalJoins.addElement(leftShortName + "|" + leftColumn + "|" +
1925                rightShortName + "|" + rightColumn + "|inner");
1926        // 2003-11-05 /ebn-ma: ...changed
1927        // ----------------------------------------------------
1928    } /* setInnerJoin(String, String, String, String) */
1929
1930    /**
1931     * Builds a 'FROM' clause using the 'LEFT JOIN' syntax.
1932     *
1933     * @param leftShortName  short name for the left hand table.
1934     * @param leftColumn     name of the column from the left hand table for the JOIN condition
1935     * @param rightShortName short name for the right hand table.
1936     * @param rightColumn    name of the column from the right hand table for the JOIN condition
1937     */
1938    public void setLeftJoin(String   leftShortName, String   leftColumn,
1939                            String   rightShortName, String   rightColumn)
1940            throws DBException {
1941
1942        // ----------------------------------------------------
1943        // 2003-11-05 /ebn-ma: changed...
1944        // We will build the from-clause at execution time.
1945        // buildJoin(leftShortName, leftColumn, rightShortName, rightColumn, LEFT_JOIN);
1946
1947        originalJoins.addElement(leftShortName + "|" + leftColumn + "|" +
1948                rightShortName + "|" + rightColumn + "|left");
1949        // 2003-11-05 /ebn-ma: ...changed
1950        // ----------------------------------------------------
1951    } /* setLeftJoin(String, String, String, String) */
1952
1953    /**
1954     * Builds a 'FROM' clause using the 'LEFT JOIN' syntax.
1955     *
1956     * @param leftShortName  short name for the left hand table.
1957     * @param leftColumn     name of the column from the left hand table for the JOIN condition
1958     * @param rightShortName short name for the right hand table.
1959     * @param rightColumn    name of the column from the right hand table for the JOIN condition
1960     */
1961    public void setRightJoin(String   leftShortName, String   leftColumn,
1962                             String   rightShortName, String   rightColumn)
1963            throws DBException {
1964
1965        // ----------------------------------------------------
1966        // 2003-11-05 /ebn-ma: changed...
1967        // We will build the from-clause at execution time.
1968        // buildJoin(leftShortName, leftColumn, rightShortName, rightColumn, RIGHT_JOIN);
1969
1970        originalJoins.addElement(leftShortName + "|" + leftColumn + "|" +
1971                rightShortName + "|" + rightColumn + "|right");
1972        // 2003-11-05 /ebn-ma: ...changed
1973        // ----------------------------------------------------
1974    } /* setRightJoin(String, String, String, String) */
1975
1976    /**
1977     * Builds a 'FROM' clause using ANSI 'JOIN' syntax.
1978     *
1979     * @param leftShortName  short name for the left hand table.
1980     * @param leftColumn     name of the column from the left hand table for the JOIN condition
1981     * @param rightShortName short name for the right hand table.
1982     * @param rightColumn    name of the column from the right hand table for the JOIN condition
1983     * @param joinType       the type of join to be performed, i.e. LEFT, RIGHT or INNER.
1984     */
1985    private void buildJoin(String   leftShortName, String   leftColumn,
1986                           String   rightShortName, String   rightColumn,
1987                           int joinType)
1988            throws DBException {
1989
1990        // ----------------------------------------------------
1991        // 2003-11-05 /ebn-ma: changed...
1992        // In the case of outer-joins we have to obtain in the on-clause of
1993        // the join these filter-conditions, that are defined for the related
1994        // tables.
1995        // Instead: If we would add the outer-join-filters to the where-clause,
1996        // we would never see the results we intended when we made the decision
1997        // to use an outer join.
1998        // Therefore: Mark these DBObjects to be ignored when the where-clause
1999        // will be computed.
2000
2001        String   onClause = "";
2002        FastStringBuffer s = new FastStringBuffer(2048);
2003        FastStringBuffer fsb = new FastStringBuffer(256);
2004
2005        try {
2006            DBObject leftDBObj = getByShortName(leftShortName);
2007            DBObject rightDBObj = getByShortName(rightShortName);
2008
2009            if (fromClause == null) {
2010                s.append(getTableName(leftDBObj));
2011            } else {
2012                s.append(fromClause);
2013            }
2014
2015            switch (joinType) {
2016                case INNER_JOIN:
2017                    s.append(" INNER JOIN ");
2018                    // Inner-join filters can still be placed in the where-clause.
2019                    onClause = "";
2020                    break;
2021                case RIGHT_JOIN:
2022                    s.append(" RIGHT JOIN ");
2023                    onClause = buildWhereClauseBuffer(true, fsb, leftShortName);
2024                    ignoreInWhereClause =
2025                            ignoreInWhereClause + "," + leftShortName + ",";
2026                    break;
2027                case LEFT_JOIN:
2028                    s.append(" LEFT JOIN ");
2029                    onClause = buildWhereClauseBuffer(true, fsb, rightShortName);
2030                    ignoreInWhereClause =
2031                            ignoreInWhereClause + "," + rightShortName + ",";
2032                    break;
2033            }
2034
2035            if (onClause.length() > 5) {
2036                onClause = " AND " + onClause.substring(6);
2037            }
2038
2039            s.append(getTableName(rightDBObj) +
2040                    " ON (" + getTableName(leftDBObj) + "." +
2041                    leftColumn + " = " + getTableName(rightDBObj) +
2042                    "." + rightColumn + onClause + ")");
2043
2044            fromClause = s.toString();
2045        } finally {
2046            s.release();
2047            fsb.release();
2048        }
2049        // 2003-11-05 /ebn-ma: ...changed
2050        // ----------------------------------------------------
2051    } /* buildJoin(String, String, String, String, int) */
2052
2053    /**
2054     * Specify a maximum number of records to be retrieved in any subsequent
2055     * searchAndRetrieve() call. Records will be retrieved (in the specified
2056     * sort order) until the specified maximum is reached, then the remainder
2057     * of the result set is discarded. Specifying zero indicates that all records are to be retrieved.
2058     *
2059     * @param newMax The maximum number of records to retrieve.
2060     * @throws DBException If the max number is less than 0
2061     */
2062    public synchronized void setMaxRecords(int newMax)
2063            throws DBException {
2064
2065        if (maxRecords < 0) {
2066            String   myName = (thisClass + "setMaxRecords(int)");
2067            throw new DBException(myName +
2068                    ":Max records can't be less than 0");
2069        }
2070
2071        maxRecords = newMax;
2072    } /* setMaxRecords(int) */
2073
2074
2075    /**
2076     * Method to set up the fields for this database object.  If you wish to set
2077     * up a MultiDBQuery ahead of time you can use this method in the inherited
2078     * class to specify the objects and relationships necessary. This become
2079     * something of the equivilant of a "view" in database terms.
2080     *
2081     * @throws DBException If there is an error setting up the fields
2082     *                     as requested.
2083     */
2084    protected void setupFields()
2085            throws DBException {
2086    } /* setupFields() */
2087
2088
2089    /**
2090     * Build an appropriate String for use in the select part of an SQL statement
2091     *
2092     * @param oneObj    Database object containing the field
2093     * @param fieldName The name of the field to be handled
2094     * @return The portion of the select clause with the appropriate function
2095     *         wrapped around it
2096     */
2097    public String   selectFieldString(DBObject oneObj, String   fieldName)
2098            throws DBException {
2099        return StringUtil.replaceStringOnce(oneObj.selectFieldString(fieldName),
2100                fieldName, getTableName(oneObj) + "." + fieldName);
2101
2102    } /* selectFieldString(String) */
2103
2104    /**
2105     * Execute custom SQL query
2106     *
2107     * @param sqlQuery   The SQL query
2108     * @param fieldCount The number of fields returned by the query
2109     * @return A list of recordlists
2110     * @throws DBException If the query could not be completed
2111     */
2112    public synchronized List   makeDirectQueryList(String   sqlQuery, int fieldCount) throws DBException {
2113
2114        DBConnectionPool myPool = null;
2115        DBConnection myConnection = null;
2116
2117        try {
2118            if (localConnection != null) {
2119                myConnection = localConnection;
2120            } else {
2121                myPool = DBConnectionPool.getInstance(getDBName());
2122                myConnection = myPool.getConnection("com.jcorporate.expresso.core.dbobj.DBObject");
2123            }
2124
2125            if (recordSet == null) {
2126                String   myName = (thisClass + "makeDirectQueryList()");
2127                throw new DBException(myName +
2128                        ":Database object not correctly initialized");
2129            }
2130
2131            recordSet.clear();
2132
2133            if (log.isDebugEnabled()) {
2134                log.debug("Executing " + sqlQuery);
2135            }
2136
2137            if (fieldCount > 0) {
2138                myConnection.execute(sqlQuery);
2139            } else {
2140                myConnection.executeUpdate(sqlQuery);
2141            }
2142
2143            int recordCount = 0;
2144            while (fieldCount > 0 && myConnection.next()) {
2145                recordCount++;
2146
2147                if ((recordCount > maxRecords) && (maxRecords > 0)) {
2148                    break;
2149                }
2150
2151//Retreive Row
2152                List   tFieldValues = new ArrayList  ();
2153                for (int i = 1; i <= fieldCount; i++) {
2154                    String   oneFieldValue = null;
2155
2156                    try {
2157                        oneFieldValue = myConnection.getString(i);
2158                    } catch (DBException de) {
2159                        throw new DBException(thisClass + "makeDirectQueryList()" + ":Error retrieving field " + i, de);
2160                    }
2161
2162                    tFieldValues.add(oneFieldValue);
2163                }
2164
2165//Add row to rowset
2166                recordSet.add(tFieldValues);
2167            }
2168
2169            /* each row retrieved from the db */
2170        } finally {
2171            if (localConnection == null) {
2172                myPool.release(myConnection);
2173            }
2174        }
2175
2176        return recordSet;
2177    }
2178
2179    /**
2180     * (assumes retrieval from DB has occurred)
2181     * assemble a given object, one retrieved by the join, getting
2182     * as many fields as found into the returned object. ignores all virtual fields.
2183     *
2184     * @param shortName name of join object
2185     * @return DBObject of type given by shortname
2186     * @deprecated v. 5.5+; 9/04; use getDBObject() instead
2187     */
2188    public DBObject assembleObject(String   shortName) throws DBException {
2189        DBObject result = null;
2190        DBObject model = getDBObject(shortName);
2191        result = model.newInstance();
2192        for (Iterator   iterator = model.getMetaData().getAllFieldsMap().values().iterator(); iterator.hasNext();) {
2193            DBField oneField = (DBField) iterator.next();
2194            try {
2195                if (oneField.isVirtual) {
2196                    continue;  // ignore virtual fields
2197                }
2198
2199                String   fieldName = oneField.getName();
2200                if (isFieldNull(shortName, fieldName)) {
2201                    result.setField(fieldName, (String  ) null);
2202                } else {
2203                    result.setField(fieldName, getField(shortName, fieldName));
2204                }
2205            } catch (DBException e) {
2206                // we don't care if getField throws; it will do so if there is nothing retrieved for this field
2207            }
2208        }
2209        result.setStatus(DBObject.STATUS_CURRENT);
2210        return result;
2211    }
2212
2213    /**
2214     * Specify whether the shortName specified when a DBObject is added is also
2215     * used as an alias for the table in the query.  Should be false by default.
2216     *
2217     * @param flag True if the shortName should be used as an alias
2218     */
2219    public void setShortNameAsAlias(boolean flag) {
2220        shortNameAsAlias = flag;
2221    }
2222
2223
2224    /**
2225     * Get whether the shortName specified when a DBObject is added is also
2226     * used as an alias for the table in the query.
2227     *
2228     * @return True if short name is used as an alias for the table
2229     */
2230    public boolean getShortNameAsAlias() {
2231        return shortNameAsAlias;
2232    }
2233
2234
2235    /**
2236     * Get the name a table should be referenced by.  Should be the name of the
2237     * table in the database unless using shortNames as alias.
2238     *
2239     * @param oneObj The DBObject to get the name of
2240     * @return The name of the table
2241     */
2242    private String   getTableName(DBObject oneObj)
2243            throws DataException {
2244        String   name = (String  ) oneObj.getAttribute(SHORT_NAME);
2245        if (name != null && shortNameAsAlias) {
2246            return name;
2247        }
2248        return oneObj.getJDBCMetaData().getTargetSQLTable(oneObj.getDataContext());
2249    }
2250
2251    /**
2252     * @return
2253     */
2254    public DBConnection getConnection() {
2255        return localConnection;
2256    }
2257
2258    /**
2259     * @param connection
2260     */
2261    public void setConnection(DBConnection connection) {
2262        localConnection = connection;
2263    }
2264
2265    /**
2266     * @return true if the given field is null
2267     */
2268    public boolean isFieldNull(String   shortName, String   fieldName)
2269            throws DBException {
2270        return getByShortName(shortName).isFieldNull(fieldName);
2271    }
2272
2273} /* MultiDBObject */
2274
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags