QueryBuilder


1   /*
2    * Enhydra Java Application Server Project
3    *
4    * The contents of this file are subject to the Enhydra Public License
5    * Version 1.1 (the "License"); you may not use this file except in
6    * compliance with the License. You may obtain a copy of the License on
7    * the Enhydra web site ( http://www.enhydra.org/ ).
8    *
9    * Software distributed under the License is distributed on an "AS IS"
10   * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See
11   * the License for the specific terms governing rights and limitations
12   * under the License.
13   *
14   * The Initial Developer of the Enhydra Application Server is Lutris
15   * Technologies, Inc. The Enhydra Application Server and portions created
16   * by Lutris Technologies, Inc. are Copyright Lutris Technologies, Inc.
17   * All Rights Reserved.
18   *
19   * Contributor(s):
20   *
21   * $Id: QueryBuilder.java,v 1.10 2005/05/26 08:08:10 predrag Exp $
22   *
23   * contains WebDocWf extension for extended wildcard support
24   *
25   * conatins WebDocWf extension for row counters
26   *
27   */
28  package com.lutris.dods.builder.generator.query;
29  
30  import java.math.BigDecimal  ;
31  import java.sql.PreparedStatement  ;
32  import java.sql.ResultSet  ;
33  import java.sql.SQLException  ;
34  import java.util.BitSet  ;
35  import java.util.Date  ;
36  import java.util.Enumeration  ;
37  import java.util.Hashtable  ;
38  import java.util.Vector  ;
39  
40  import org.enhydra.dods.Common;
41  import org.enhydra.dods.CommonConstants;
42  import org.enhydra.dods.DODS;
43  import org.enhydra.dods.cache.CacheConstants;
44  
45  import com.lutris.appserver.server.sql.CoreDataStruct;
46  import com.lutris.appserver.server.sql.DBConnection;
47  import com.lutris.appserver.server.sql.DBQuery;
48  import com.lutris.appserver.server.sql.DatabaseManagerException;
49  import com.lutris.appserver.server.sql.ExtendedDBConnection;
50  import com.lutris.appserver.server.sql.ExtendedQuery;
51  import com.lutris.appserver.server.sql.ObjectId;
52  import com.lutris.appserver.server.sql.ObjectIdException;
53  import com.lutris.appserver.server.sql.StandardDatabaseManager;
54  import com.lutris.appserver.server.sql.standard.DriverSpecificConstants;
55  import com.lutris.appserver.server.sql.standard.StandardLogicalDatabase;
56  import com.lutris.dods.builder.generator.dataobject.GenericDO;
57  import com.lutris.logging.Logger;
58  
59  /**
60   * @author Jay Gunter
61   * <PRE>
62   *      A QueryBuilder is a helper-object used by the xxxQuery classes.
63   *
64   *      Stand-alone usage:
65   *
66   *              QueryBuilder qb;
67   *              qb = new QueryBuilder( "cats" );
68   *                      or
69   *              qb = new QueryBuilder( "cats", "name, age" );
70   *                      ...
71   *              qb.addWhereClause( "owner.name", "Smith",
72   *                                      QueryBuilder.NOT_NULL,
73   *                                      QueryBuilder.EXACT_MATCH );
74   *              qb.addWhereClause( "paws", 3, QueryBuilder.GREATER_THAN );
75   *              qb.addTwoColumnWhereClause(
76   *                      "cat.ownerId", "owner.Id", QueryBuilder.EQUAL );
77   *              ResultSet rs = qb.executeQuery( conn );
78   *                      ...
79   *              qb.reset();
80   *
81   * </PRE>
82   *
83   *
84   * @version $Revision: 1.10 $
85   */
86  public class QueryBuilder implements ExtendedQuery {
87      // Special values for whereClauses:
88      private static final String   OR = " OR ";
89      private static final String   OPEN_PAREN = " ( ";
90      private static final String   CLOSE_PAREN = " ) ";
91      // Comparison operator passed as 3rd arg to addWhereClause( int|float|double )
92      public static final String    EQUAL = "=";
93      public static       String    NOT_EQUAL = "!=";
94      public static final String    LESS_THAN = "<";
95      public static final String    LESS_THAN_OR_EQUAL = "<=";
96      public static final String    GREATER_THAN = ">";
97      public static final String    GREATER_THAN_OR_EQUAL = ">=";
98      public static final String    IS_NULL = " IS NULL ";
99      public static final String    IS_NOT_NULL = " IS NOT NULL ";
100     public static final String    OPEN_IN = " IN ( ";
101     public static final String    OPEN_NOT_IN = " NOT IN ( ";
102     public static final String    OPEN_EXISTS = " EXISTS ( ";
103     public static final String    OPEN_NOT_EXISTS = " NOT EXISTS ( ";
104     public static final String    CLOSE_IN = " ) ";
105     public static final String    CASE_SENSITIVE_CONTAINS = "%X%";
106     public static final String    CASE_INSENSITIVE_CONTAINS = "%x%";
107     public static final String    CASE_SENSITIVE_STARTS_WITH = "X%";
108     public static final String    CASE_INSENSITIVE_STARTS_WITH = "x%";
109     public static final String    CASE_SENSITIVE_ENDS_WITH = "%X";
110     public static final String    CASE_INSENSITIVE_ENDS_WITH = "%x";
111     public static final String    CASE_INSENSITIVE_EQUAL = "%xxxx";
112     // WebDocWf extension for extended wildcard support
113     // The following lines have been added:
114     public static final String    CASE_SENSITIVE_MATCH = "X";
115     public static final String    CASE_INSENSITIVE_MATCH = "x";
116     public static final String    USER_CASE_SENSITIVE_MATCH = "U";
117     public static final String    USER_CASE_INSENSITIVE_MATCH = "u";
118     // end ofWebDocWf extension for extended wildcard support
119     /**
120      * Null-acceptability indicator passed as 3rd arg to addWhereClause( String )
121      * @deprecated Use addWhere() methods instead of addWhereClause() methods.
122      */
123     public static final boolean NULL_OK = true;
124 
125     /**
126      * Null-acceptability indicator passed as 3rd arg to addWhereClause( String )
127      * @deprecated Use addWhere() methods instead of addWhereClause() methods.
128      */
129     public static final boolean NOT_NULL = false;
130 
131     /**
132      * Wild-card          indicator passed as 4th arg to addWhereClause( String )
133      * @deprecated Use addWhere() methods instead of addWhereClause() methods.
134      */
135     public static final boolean EXACT_MATCH = true;
136 
137     /**
138      * Wild-card          indicator passed as 4th arg to addWhereClause( String )
139      * @deprecated Use addWhere() methods instead of addWhereClause() methods.
140      */
141     public static final boolean NOT_EXACT = false;
142     // Default inexact string match keyword (e.g. can be MATCHES or LIKE)
143     public static final String   DEFAULT_MATCHES_KEYWORD = "MATCHES";
144     private String   likeKeyword = DEFAULT_MATCHES_KEYWORD;
145     // Default inexact string wildcard (e.g. can be * or %)
146     public static final String   DEFAULT_WILDCARD = "*";
147     private String   wildcard = DEFAULT_WILDCARD;
148     // WebDocWf extension for extended wildcard support
149     // The following lines have been added:
150     // Do NOT use "\" or "\\" or "\\\\" ... as wildcard/escape characters
151     // this would lead to runtime errrors because of multiple string parsing in the DODS code
152     public static final String   DEFAULT_SINGLE_WILDCARD = "_";
153     public static final String   DEFAULT_SINGLE_WILDCARD_ESCAPE = "?";
154     public static final String   DEFAULT_WILDCARD_ESCAPE = "?";
155     public static final String   DEFAULT_WILDCARD_ESCAPE_CLAUSE = "ESCAPE '?'";
156     public static final String   DEFAULT_USER_WILDCARD = "*";
157     public static final String   DEFAULT_USER_SINGLE_WILDCARD = "?";
158     public static final String   DEFAULT_USER_SINGLE_WILDCARD_ESCAPE = "?";
159     public static final String   DEFAULT_USER_WILDCARD_ESCAPE = "?";
160     public static final boolean DEFAULT_USER_APPEND_WILDCARD = false;
161     public static final boolean DEFAULT_USER_TRIM_STRING = false;
162     public String   userConfigWildcard = DEFAULT_USER_WILDCARD;
163     public String   userConfigSingleWildcard = DEFAULT_USER_SINGLE_WILDCARD;
164     public String   userConfigSingleWildcardEscape = DEFAULT_USER_SINGLE_WILDCARD_ESCAPE;
165     public String   userConfigWildcardEscape = DEFAULT_USER_WILDCARD_ESCAPE;
166     // end of first part WebDocWf extension for extended wildcard support
167     // See debug() method.
168     static private boolean debugAllSQL = false;
169     private boolean debugSQL = false;
170     // true if it is used at least one method that provides possibility of join between tables
171     private boolean multiTableJoin = false;
172     private boolean unionTableJoin = false;
173     // true if select <table>.primaryKey can be performed
174     private boolean preventPrimaryKeySelect = false;
175     // WebDocWf extension cursor-handling
176     // Value -1 means not defined
177     private int iCurrentFetchSize = -1;
178     private static int iDefaultFetchSize = -1;
179     private int iCurrentQueryTimeout = CacheConstants.DEFAULT_QUERY_TIMEOUT;
180     private static int iDefaultQueryTimeout = CacheConstants.DEFAULT_QUERY_TIMEOUT;
181     // WebDocWf extension cursor-handling end
182     private String   databaseName = null;
183 
184     private int DEFAULT_RS_TYPE = -100;
185     private int DEFAULT_RS_CONCURRENCY = -100;
186     private int iResultSetConcurrency = DEFAULT_RS_CONCURRENCY;
187     private int iResultSetType = DEFAULT_RS_TYPE;
188     
189     private String   customNotEqualSqlOperator = null;
190     
191     private boolean caseInsensitiveDatabase = CommonConstants.DEFAULT_CASE_INSENSITIVE_DATABASE;
192     
193     /*
194      The DODS (Data Object Design Studio) development tool
195      generates data-layer source code which uses QueryBuilder objects
196      to perform database queries.
197      This is the original (1st, old) purpose of QueryBuilder.
198      QueryBuilder can also be used by application developers
199      to perform straight SQL queries.  Certain static final data members
200      of the DO classes generated by DODS are used to specify the tables and columns
201      for the query.  This allows developers to write straight SQL queries
202      but still enjoy the compile-time checking.
203      This is the 2nd (new) purpose of QueryBuilder.
204      ================= Usage 1: DODS generated code =================================
205      NOTE!  Application developers, please see "Usage 2" section below.
206      1st, old, String-based style (used by DODS-generated Query classes):
207      Query creation:
208      The ctor takes a String tablename
209      (and optional String list of comma-separated column names).
210      E.g.:
211      // In PersonQuery class:
212      QueryBuilder qb = new QueryBuilder( "person", "person.LastName" );
213      addWhereClause() methods:
214      A where-clause is added by calling one of the addWhereClause() methods
215      which takes a String column name a String value for comparison.
216      Because table and column names are Strings, the application code
217      can become out-of-sync with a changing database schema,
218      and the developer gets no warning from the compiler.
219      This scheme is ok for use by DODS since the application code
220      (DO, Query classes) are (re)generated every time the
221      schema (the DODS project file) is changed.
222      However, this scheme is non-ideal for manual coding.
223      E.g.:
224      // In PersonQuery class:
225      qb.addWhereClause( "FirstName", "Bob", QueryBuilder.EQUAL );
226      Obtaining results:
227      The results of the query are returned as a JDBC ResultSet.
228      Column values are retrieved by their postion within each returned row,
229      or by the String name of the column in the returned row.
230      Again, no hope for compile-time checking.
231      E.g.:
232      // PersonQuery class creates a DBQuery object to run the query:
233      DBQuery dbQuery = DODS.getDatabaseManager().createQuery();
234      dbQuery.query( this );  // invokes PersonQuery.executeQuery();
235      // PersonQuery class extends Query and implements executeQuery():
236      ResultSet rs = qb.executeQuery( connection );
237      while ( rs.next() ) {
238      String lastname = rs.getString( 1 );
239      }
240      ================= Usage 2: Application Developers =============================
241      2nd, new, Object Oriented style (used by application developers):
242      Query creation:
243      The ctor takes a Vector of RDBColumn objects.
244      Each RDBColumn object knows which RDBTable it belongs to.
245      In each DO class generated by DODS, there is a static RDBColumn object
246      for each column in database table for the DO.
247      E.g.:
248      Vector fields = new Vector();
249      fields.addElement( PersonDO.LastName );
250      QueryBuilder qb = new QueryBuilder( fields );
251      Or:
252      QueryBuilder qb = new QueryBuilder();
253      qb.select( PersonDO.LastName );
254      addWhere() methods:
255      A where-clause is added by passing an RDBColumn object and a value.
256      Because objects (not Strings) are used to specify columns in the query,
257      errors in hand-written application code can be caught by the compiler.
258      E.g.:
259      qb.addWhere( PersonDO.FirstName, "Bob" );
260      Obtaining results:
261      The results of the query are returned as a series of RDBRow objects.
262      Each RDBRow contains an RDBColumnValue object for each returned column.
263      An RDBColumnValue object is retrieved from the RDBRow using
264      a one of the RDBColumn objects passed to the QueryBuilder ctor.
265      The value is extracted from an RDBColumnValue object via its
266      getString or getInteger (...) method.
267      These methods throw an exception if the application attempts to
268      extract an incorrect type for the RDBColumnValue.
269      E.g.:
270      RDBRow row;
271      while ( null != ( row = qb.getNextRow() ) ) {
272      String lastname = row.get( PersonDO.LastName ).getString();
273      }
274      qb.close();  // always release the resources
275      Note:
276      The templates used by the DODS sourceGenerator_Query class
277      could be converted to use this 2nd style.
278      */
279 
280     /**
281      * Construct a QueryBuilder object that will return specified fields of a table.
282      * Style 2 (see above.)
283      *
284      * @param fields Vector of RDBColumn objects,
285      * one for each column to be returned.  Fields can refer to different tables.
286      */
287     public QueryBuilder(Vector   fields) {
288         init();
289         reset();
290         if (null != fields) {
291             selectedFields = (Vector  ) fields.clone();
292         }
293     }
294 
295     private void init(){
296         try {
297             String   value;
298 
299             value=((StandardDatabaseManager)DODS.getDatabaseManager())
300                 .getDatabaseManagerConfiguration().getUserConfigWildcard();
301             if (value!= null) {
302                 userConfigWildcard = value;
303             }
304 
305             value=((StandardDatabaseManager)DODS.getDatabaseManager())
306                 .getDatabaseManagerConfiguration().getUserConfigSingleWildcard();
307             if (value!= null) {
308                 userConfigSingleWildcard = value;
309             }
310 
311             value=((StandardDatabaseManager)DODS.getDatabaseManager())
312                 .getDatabaseManagerConfiguration().getUserConfigSingleWildcardEscape();
313             if (value!= null) {
314                 userConfigSingleWildcardEscape = value;
315             }
316         } catch (Exception   except) {}
317         initDefaultWildCards();
318         
319         try {
320             caseInsensitiveDatabase=((StandardLogicalDatabase)DODS.getDatabaseManager()
321                      .findLogicalDatabase(getDatabaseName())).getDatabaseConfiguration().isCaseInsensitiveDatabase();
322         } catch (Exception   e) {
323             caseInsensitiveDatabase = CommonConstants.DEFAULT_CASE_INSENSITIVE_DATABASE;
324         }
325         
326         customNotEqualSqlOperator = null;
327         try {
328             String   customNotEqualSqlOperatorStr = ((StandardLogicalDatabase)DODS.getDatabaseManager()
329                      .findLogicalDatabase(getDatabaseName())).getDriverProperty(DriverSpecificConstants.PARAMNAME_CUSTOM_NOT_EQUAL_SQL_OPERATOR);
330             if(customNotEqualSqlOperatorStr!=null) {
331                     customNotEqualSqlOperator=customNotEqualSqlOperatorStr;
332             }else {
333                 customNotEqualSqlOperator = DriverSpecificConstants.DEFAULT_CUSTOM_NOT_EQUAL_SQL_OPERATOR;
334             }
335         }catch(Exception   e) {}
336         if(customNotEqualSqlOperator!=null) NOT_EQUAL=customNotEqualSqlOperator;
337     }
338 
339     /**
340      * Construct a QueryBuilder object that will return specified fields of a table.
341      * Style 2 (see above.)
342      *
343      * @param fields array of RDBColumn objects,
344      * one for each column to be returned.  Fields can refer to different tables.
345      */
346     public QueryBuilder(RDBColumn[] fields) {
347         init();
348         reset();
349         for (int i = 0; i < fields.length; i++) {
350             select(fields[i]);
351         }
352     }
353 
354     /**
355      * Construct a QueryBuilder object.
356      * When no fields are specified in the constructor,
357      * you must use calls to select() to specify the RDBColumns
358      * that will be returned.
359      * This can be more convenient than constructing the Vector before
360      * calling the QueryBuilder(Vector) constructor.
361      * Style 2 (see above.)
362      */
363     public QueryBuilder() {
364         init();
365         reset();
366     }
367 
368     /**
369      * Add a field to be returned by this QueryBuilder.
370      * Style 2 (see above.)
371      *
372      * @param field RDBColumn object to be returned.
373      * Fields can refer to different tables.
374      */
375     public void select(RDBColumn field) {
376         if (null == field) {
377             return;
378         }
379         for (int i = 0; i < selectedFields.size(); i++) {
380             if (field.equals((RDBColumn) selectedFields.elementAt(i))) {
381                 return;
382             }
383         }
384         selectedFields.addElement(field);
385     }
386 
387     /**
388      * Returns an array of RDBRow objects.
389      *
390      * @return array of RDBRow objects.
391      * @exception ATException If the data is not accessible
392      * @exception BusinessRuleException If x is invalid.
393      * @see #getNextRow
394      * author Jay Gunter
395      */
396     public RDBRow[] getRows()
397         throws QueryException {
398         Vector   v = new Vector  ();
399         RDBRow row;
400 
401         while (null != (row = getNextRow())) {
402             v.addElement(row);
403         }
404         RDBRow[] ret = new RDBRow[ v.size() ];
405 
406         v.copyInto(ret);
407         return ret;
408     }
409 
410     /**
411      * Returns an RDBRow object containing the fields specified in the ctor.
412      * On first call, executes the query.
413      * NOTE:  to use this method, the QueryBuilder object must be created
414      * using the
415      *  QueryBuilder( RDBColumn[] )
416      * constructor.
417      *
418      * @return row the RDBRow representing 1 row from the ResultSet.
419      * @exception ATException If the data is not accessible
420      * @exception BusinessRuleException If x is invalid.
421      * author Jay Gunter
422      */
423     private DBQuery dbQuery = null;
424     private ResultSet   rs = null;
425     private Boolean     rsClosed = null;
426     private boolean done = false;
427     public RDBRow getNextRow() throws QueryException {
428         if (done) {
429             return null;
430         }
431         if (null == rs) {
432             try {
433                 if (null == dbQuery) {
434                     // May not be null if I later implement
435                     // a ctor that takes a DBQuery.
436                     dbQuery = DODS.getDatabaseManager().createQuery();
437                 }
438             } catch (Exception   e) {
439                 throw new QueryException("SQL=[" + sql
440                                              + "]: Unable to create query",
441                                          e);
442             }
443             try {
444                 // invokes executeQuery which sets ResultSet rs
445                 dbQuery.query(this);
446             } catch (Exception   e) {
447                 throw new QueryException("SQL=[" + sql
448                                              + "]: Unable to run query",
449                                          e);
450             }
451             if (null == rs) {
452                 throw new QueryException("No ResultSet for Query.");
453             }
454         }
455         try {
456             if (!rs.next()) {
457                 // ResultSet.next() only returns false once.
458                 // After that, drivers typically throw "result set empty" exception.
459                 // That isn't very friendly or useful to an application programmer.
460                 // Setting done=true allows us to just keep returning nulls.
461                 done = true;
462                 rsClosed=new Boolean  (true);
463                 rs.close();
464                 dbQuery.release();
465                 return null;
466             }
467         } catch (Exception   e) {
468             throw new QueryException("SQL=[" + sql
469                                          + "]: Unable to get query results",
470                                      e);
471         }
472         RDBColumnValue[] vals = new RDBColumnValue[ selectedFields.size() ];
473         RDBColumn field = null;
474 
475         for (int i = 0; i < selectedFields.size(); i++) {
476             try {
477                 field = (RDBColumn) selectedFields.elementAt(i);
478                 vals[i] = new RDBColumnValue(field, rs.getObject(i + 1));
479             } catch (Exception   e) {
480                 throw new QueryException("SQL=[" + sql
481                                              + "]: Unable to get query result for column " + field,
482                                          e);
483             }
484         }
485         return new RDBRow(vals);
486     }
487 
488     /**
489      * WARNING:
490      * This method is disabled and should never be called.
491      * It's implementation is forced by the Query interface.
492      * Use getNextRow instead().
493      */
494     public Object   next(ResultSet   rs)
495         throws SQLException  , ObjectIdException {
496         return null;
497     }
498 
499     // /////////////////////////////////////////////////////////////////////////////
500     // / Constructors used by DODS-generated Query classes. ////////////////////////
501     // /////////////////////////////////////////////////////////////////////////////
502     /**
503      * Construct a QueryBuilder object that will return all fields of a table.
504      * Style 1 (see above.)
505      * WARNING: QueryBuilder objects created with this constructor
506      * cannot be used with the getNextRow() method.
507      * @see #getNextRow
508      *
509      * @param tableName Name of the table on which to perform the SQL 'select'.
510      */
511     public QueryBuilder(String   tableName) {
512         this(tableName, tableName + ".*");
513     }
514 
515     /**
516      * Construct a QueryBuilder object that will return specified fields of a table.
517      * Style 1 (see above.)
518      *
519      * @param tableName     Name of the table on which to perform the SQL 'select'.
520      * @param fieldList Comma-separated string of field names to retrieve.
521      */
522     public QueryBuilder(String   tableName, String   fieldList) {
523         reset();
524         init();
525         /* mainTableName = tableName.toLowerCase(); */
526         mainTableName = tableName;
527         storeTableName(mainTableName);
528         selectClause = fieldList;
529     }
530 
531     /**
532      * Determine table name of column.
533      * For example, we return 'tableX' from the following column specification:
534      *     UPPER(tableX.columnY)
535      * WARNING: trickier column specifications will cause this method to fail.
536      *
537      * @param column     A column (field) name, possibly with a leading table name.
538      */
539     private String   getTableName(String   column) {
540         int offset = 0;
541         int end = 0;
542         String   str = column;
543         int index = str.lastIndexOf("(");
544 
545         if (-1 != index) {
546             str = str.substring(index + 1);
547         }
548         index = str.indexOf(")");
549         if (-1 != index) {
550             str = str.substring(0, index);
551         }
552         index = str.indexOf(".");
553         if (-1 == index) {
554             return null;
555         }
556         return str.substring(0, index);
557     }
558 
559     /**
560      * Returnes multiTableJoin attribute.
561      *
562      * @return multiTableJoin attribute.
563      */
564     public boolean isMultiTableJoin() {
565         return multiTableJoin;
566     }
567     
568     /**
569      * Returnes unionTableJoin attribute.
570      *
571      * @return unionTableJoin attribute.
572      */
573     public boolean isUnionTableJoin() {
574         return unionTableJoin;
575     }
576     /**
577      * Returnes preventPrimaryKeySelect attribute.
578      *
579      * @return preventPrimaryKeySelect attribute.
580      */
581     public boolean getPreventPrimaryKeySelect() {
582         return preventPrimaryKeySelect;
583     }
584 
585     /**
586      * Resets selected fields.
587      */
588     public void resetSelectedFields() {
589         selectedFields = new Vector  ();
590         selectClause = "";
591     }
592 
593     /**
594      * Store all table names that occur as prefix of column specifications.
595      * All table names must appear in the "from" clause.
596      *
597      * @param column     A column (field) name, possibly with a leading table name.
598      */
599     private void storeTableNameForColumn(String   column) {
600         String   table = getTableName(column);
601 
602         storeTableName(table);
603     }
604 
605     /**
606      * Store all table names that occur as prefix of column specifications.
607      * All table names must appear in the "from" clause.
608      *
609      * @param column     RDBColumn containing table name.
610      */
611     private void storeTableNameForColumn(RDBColumn column) {
612         storeTableName(column.getTableName());
613     }
614 
615     /**
616      * Store a table name.
617      * All table names must appear in the "from" clause.
618      *
619      * @param table name.
620      */
621     private void storeTableName(String   table) {
622         if (null != table) {
623             tableNames.put(table, "");
624         }
625     }
626 
627     /**
628      * <PRE>
629      * Specify that the next conditional expression (where-clause)
630      * should be preceded by OR instead of AND.  (AND is the default).
631      * For example:
632      *          addWhereClause( "person.fname", "Bob", EQUAL );
633      *          addWhereClause( "person.lname", "Smith", EQUAL );
634      *                  produces
635      *          person.fname = 'Bob' AND person.lname = 'Smith'
636      * But:
637      *          addWhereClause( "person.fname", "Bob", EQUAL );
638      *          addWhereOr();
639      *          addWhereClause( "person.lname", "Smith", EQUAL );
640      *                  produces
641      *          person.fname = 'Bob' OR person.lname = 'Smith'
642      * Disallows 'OR OR'.
643      * Trailing ORs are removed by prepareStatement().
644      *
645      * </PRE>
646      *
647      * author Jay Gunter
648      */
649     public void addWhereOr() {
650         int n = whereClauses.size();
651 
652         if (0 == n) {
653             return;
654         }
655         String   prev = (String  ) whereClauses.lastElement();
656 
657         if (!OR.equals(prev)) {
658             whereClauses.addElement(OR);
659         }
660     }
661 
662     /**
663      * Specify that the next conditional expression (where-clause)
664      * should be preceded by '('.
665      * Trailing OPEN_PARENs are removed by prepareStatement().
666      *
667      * author Jay Gunter
668      */
669     public void addWhereOpenParen() {
670         whereClauses.addElement(OPEN_PAREN);
671 
672         /*
673          for ( int i = 0; i < whereClauses.size(); i++ ) {
674          System.err.println("whereClauses.elementAt("+i+")="+whereClauses.elementAt(i));
675          }
676          */
677     }
678 
679     /**
680      * Close a previous addWhereOpenParen.
681      * Automatically disallows bad sequences like 'OR )' and '( )'.
682      *
683      * author Jay Gunter
684      */
685     public void addWhereCloseParen() {
686         int n = whereClauses.size();
687 
688         if (0 == n) {
689             return;
690         }
691         String   prev = (String  ) whereClauses.lastElement();
692 
693         if (!(OR.equals(prev) || OPEN_PAREN.equals(prev))) {
694             whereClauses.addElement(CLOSE_PAREN);
695         }
696     }
697 
698     /**
699      * Add a where-clause that compares a column against a String.
700      * @param column  A column (field) name to use as a search constraint.
701      * @param value   int value to compare against.
702      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
703      *
704      * author Jay Gunter
705      */
706     public void addWhere(RDBColumn column, GenericDO value, String   cmp_op) {
707         BigDecimal   id = null;
708 
709         if (null != value) {
710             id = value.get_OId().toBigDecimal();
711         }
712         _addWhereClause(column, id, cmp_op);
713     }
714 
715     /**
716      * Add a where-clause that compares a column against a byte array.
717      * @param column  A column (field) name to use as a search constraint.
718      * @param value   byte array to compare against.
719      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
720      *
721      * author Jay Gunter
722      */
723     public void addWhere(RDBColumn column, byte[] value, String   cmp_op) {
724         String   v = null;
725 
726         if (null != value) {
727             v = new String  (value);
728         }
729         _addWhereClause(column, v, cmp_op);
730     }
731 
732     /**
733      * Add a where-clause that compares a column against a String.
734      * @param column  A column (field) name to use as a search constraint.
735      * @param value   int value to compare against.
736      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
737      *
738      * author Jay Gunter
739      */
740     public void addWhere(RDBColumn column, java.sql.Time   value, String   cmp_op) {
741         _addWhereClause(column, value, cmp_op);
742     }
743 
744     /**
745      * Add a where-clause that compares a column against a String.
746      * @param column  A column (field) name to use as a search constraint.
747      * @param value   int value to compare against.
748      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
749      *
750      * author Jay Gunter
751      */
752     public void addWhere(RDBColumn column, java.sql.Timestamp   value, String   cmp_op) {
753         _addWhereClause(column, value, cmp_op);
754     }
755 
756     /**
757      * Add a where-clause that compares a column against a String.
758      * @param column  A column (field) name to use as a search constraint.
759      * @param value   long value to compare against.
760      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
761      *
762      * author Jay Gunter
763      */
764     public void addWhere(RDBColumn column, long value, String   cmp_op) {
765         _addWhereClause(column, new Long  (value), cmp_op);
766     }
767 
768     /**
769      * Add a where-clause that compares a column against a String.
770      * @param column  A column (field) name to use as a search constraint.
771      * @param value   double value to compare against.
772      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
773      *
774      * author Jay Gunter
775      */
776     public void addWhere(RDBColumn column, double value, String   cmp_op) {
777         _addWhereClause(column, new Double  (value), cmp_op);
778     }
779 
780     /**
781      * Add a where-clause that compares a column against a String.
782      * @param column  A column (field) name to use as a search constraint.
783      * @param value   float value to compare against.
784      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
785      *
786      * author Jay Gunter
787      */
788     public void addWhere(RDBColumn column, float value, String   cmp_op) {
789         _addWhereClause(column, new Float  (value), cmp_op);
790     }
791 
792     /**
793      * Add a where-clause that compares a column against a String.
794      * @param column  A column (field) name to use as a search constraint.
795      * @param value   BigDecimal value to compare against.
796      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
797      *
798      * author Jay Gunter
799      */
800     public void addWhere(RDBColumn column, BigDecimal   value, String   cmp_op) {
801         _addWhereClause(column, value, cmp_op);
802     }
803 
804     /**
805      * Add a where-clause that compares a column against a String.
806      * @param column  A column (field) name to use as a search constraint.
807      * @param value   int value to compare against.
808      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
809      *
810      * author Jay Gunter
811      */
812     public void addWhere(RDBColumn column, int value, String   cmp_op) {
813         _addWhereClause(column, new Integer  (value), cmp_op);
814     }
815 
816     /**
817      * Add a where-clause that compares a column against a String.
818      * @param column  A column (field) name to use as a search constraint.
819      * @param value   boolean value to compare against.
820      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
821      *
822      * author Jay Gunter
823      */
824     public void addWhere(RDBColumn column, boolean value, String   cmp_op) {
825         _addWhereClause(column, new Boolean  (value), cmp_op);
826     }
827 
828     /**
829      * Add a where-clause that tests if a column is equal to a given String.
830      * @param column  A column (field) name to use as a search constraint.
831      * @param value   int value to compare against.
832      *
833      * author Jay Gunter
834      */
835     public void addWhere(RDBColumn column, int value) {
836         addWhere(column, value, EQUAL);
837     }
838 
839     /**
840      * Add a where-clause that compares a column against a String.
841      * @param column  A column (field) name to use as a search constraint.
842      * @param value   String value to compare against.
843      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
844      *
845      * author Jay Gunter
846      */
847     public void addWhereLower(RDBColumn column, String   value, String   cmp_op) {
848         if (caseInsensitiveDatabase) {
849             _addWhereClause(column.getFullColumnName(), value, cmp_op);
850         }else {
851             _addWhereClause("LOWER(" + column.getFullColumnName() + ")", value, cmp_op);
852         }
853     }
854 
855     /**
856      * Add a where-clause that compares a column against a String.
857      * @param column  A column (field) name to use as a search constraint.
858      * @param value   String value to compare against.
859      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
860      *
861      * author Jay Gunter
862      */
863     public void addWhere(RDBColumn column, String   value, String   cmp_op) {
864         _addWhereClause(column, value, cmp_op);
865     }
866 
867     /**
868      * Add a where-clause that tests if a column is equal to a given String.
869      * @param column  A column (field) name to use as a search constraint.
870      * @param value   String value to compare against.
871      *
872      * author Jay Gunter
873      */
874     public void addWhere(RDBColumn column, String   value) {
875         addWhere(column, value, EQUAL);
876     }
877 
878     /**
879      * Add a where-clause that compares a column against a Date.
880      * @param column  A column (field) name to use as a search constraint.
881      * @param value   Date value to compare against.
882      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
883      *
884      * author Jay Gunter
885      */
886     public void addWhere(RDBColumn column, java.sql.Date   value, String   cmp_op) {
887         _addWhereClause(column, value, cmp_op);
888     }
889 
890     /**
891      * Add a where-clause that compares a column against another column.
892      * @param column1  A column (field) name to use as a search constraint.
893      * @param column2  A column (field) name to use as a search constraint.
894      * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
895      *
896      * author Jay Gunter
897      */
898     public void addWhere(RDBColumn column1, RDBColumn column2, String   cmp_op) {
899 
900         /*
901          storeTableNameForColumn( column1 );
902          storeTableNameForColumn( column2 );
903          addTwoColumnWhereClause(       column1.getFullColumnName(),
904          column2.getFullColumnName(),
905          cmp_op );
906          */
907         String   cn1 = column1.getFullColumnName();
908         String   cn2 = column2.getFullColumnName();
909 
910         storeTableNameForColumn(cn1);
911         storeTableNameForColumn(cn2);
912         whereClauses.addElement(cn1 + " " + cmp_op + " " + cn2);
913         if(!column1.getTableName().equals(column2.getTableName()))
914             multiTableJoin = true;
915     }
916 
917     /**
918      * Add a where-clause that tests if a column is equal to another column.
919      * @param column1  A column (field) name to use as a search constraint.
920      * @param column2  A column (field) name to use as a search constraint.
921      *
922      * author Jay Gunter
923      */
924     public void addWhere(RDBColumn column1, RDBColumn column2) {
925         addWhere(column1, column2, EQUAL);
926     }
927 
928     /**
929      * Add a where-clause that tests if a column is null.
930      * @param column  A column (field) name to use as a search constraint.
931      *
932      * author kapusta@grecodata.sk
933      */
934     public void addWhereIsNull(RDBColumn column) {
935         _addWhereClause(column, null, IS_NULL);
936     }
937 
938     /**
939      * Add a where-clause that tests if a column is null.
940      * @param column  A column (field) name to use as a search constraint.
941      *
942      * author kapusta@grecodata.sk
943      */
944     public void addWhereIsNotNull(RDBColumn column) {
945         _addWhereClause(column, null, IS_NOT_NULL);
946     }
947 
948     /**
949      * Add a where-clause that tests if a column is null.
950      * @param column  A column (field) name to use as a search constraint.
951      * @deprecated  Use addWhereIsNull(RDBColumn column ) instead.
952      */
953     public void addWhereIsNull(String   column) {
954         _addWhereClause(column, null, IS_NULL);
955     }
956 
957     /**
958      * Add a where-clause that tests if a column is null.
959      * @param column  A column (field) name to use as a search constraint.
960      * @deprecated  Use addWhereIsNotNull(RDBColumn column ) instead.
961      */
962     public void addWhereIsNotNull(String   column) {
963         _addWhereClause(column, null, IS_NOT_NULL);
964     }
965 
966     /**
967      * Add a where-clause that imposes a RIGHT OUTER JOIN to
968      * test if a column is equal to another column.
969      * @param column1  A column (field) name to use as a search constraint.
970      * @param column2  A column (field) name to use as a search constraint.
971      *
972      * author Jay Gunter
973      */
974     public void addWhereRightOuter(RDBColumn column1, RDBColumn column2) {
975         String   cn1 = column1.getFullColumnName() + "(+)";
976         String   cn2 = column2.getFullColumnName();
977 
978         storeTableNameForColumn(column1);
979         storeTableNameForColumn(column2);
980         whereClauses.addElement(cn1 + " = " + cn2);
981 
982         /*
983          addTwoColumnWhereClause(
984          column1.getFullColumnName() + "(+)",
985          column2.getFullColumnName(), QueryBuilder.EQUAL );
986          */
987     }
988 
989     /**
990      * Add a where-clause that imposes a LEFT OUTER JOIN to
991      * test if a column is equal to another column.
992      * @param column1  A column (field) name to use as a search constraint.
993      * @param column2  A column (field) name to use as a search constraint.
994      *
995      * author Jay Gunter
996      */
997     public void addWhereLeftOuter(RDBColumn column1, RDBColumn column2) {
998         String   cn1 = column1.getFullColumnName();
999         String   cn2 = column2.getFullColumnName() + "(+)";
1000
1001        storeTableNameForColumn(column1);
1002        storeTableNameForColumn(column2);
1003        whereClauses.addElement(cn1 + " = " + cn2);
1004    }
1005
1006    /**
1007     * <PRE>
1008     * Add a where-clause to the SQL command.
1009     * The where-clause compare the values of two columns
1010     * using the specified comparison operator.
1011     * E.g., we want to find cats with too many paws:
1012     *      column1 = "cat.paws"
1013     *      column2 = "animalNorms.catPaws"
1014     *      cmp_op  = QueryBuilder.GREATER_THAN
1015     * generates the where-clause:
1016     *      cat.paws > animalNorms.catPaws
1017     * and remembers the table names (cat, animalNorms)
1018     * for inclusion in the from-clause.
1019     * </PRE>
1020     *
1021     * @param column1  A column (field) name to use as a search constraint.
1022     * @param column2  Another column to compare against.
1023     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1024     * @deprecated  Use addWhere(RDBColumn c1, RDBColumn c1, String cmp_op) instead.
1025     */
1026    public void addTwoColumnWhereClause(
1027        String   column1, String   column2, String   cmp_op) {
1028        storeTableNameForColumn(column1);
1029        storeTableNameForColumn(column2);
1030        whereClauses.addElement(column1 + " " + cmp_op + " " + column2);
1031    }
1032
1033    /**
1034     * <PRE>
1035     * Add a where-clause to the SQL command.
1036     * The where-clause compare the values of two columns
1037     * using the EQUAL operator.
1038     * E.g., we want to find cats with the normal number of paws:
1039     *      column1 = "cat.paws"
1040     *      column2 = "animalNorms.catPaws"
1041     * generates the where-clause:
1042     *      cat.paws = animalNorms.catPaws
1043     * and remembers the table names (cat, animalNorms)
1044     * for inclusion in the from-clause.
1045     *
1046     * </PRE>
1047     *
1048     * @param column1  A column (field) name to use as a search constraint.
1049     * @param column2  Another column to compare against.
1050     * @deprecated  Use addWhere(RDBColumn c1, RDBColumn c1) instead.
1051     */
1052    public void addTwoColumnWhereClause(String   column1, String   column2) {
1053        addTwoColumnWhereClause(column1, column2, EQUAL);
1054    }
1055
1056    /**
1057     * Replace a substring with another string.
1058     *
1059     * @return the resulting string after replacement.
1060     * Returns null if any params are null.
1061     * Returns original string if 'find' not found.
1062     * @param s the original string.
1063     * @param find the substring to replace.
1064     * @param replace the replacement string.
1065     * author Jay Gunter
1066     */
1067    public static String   stringReplace(String   s, String   find, String   replace) {
1068        if (null == s || null == find || null == replace) {
1069            return s;
1070        }
1071        int x;
1072
1073        if (-1 == (x = s.indexOf(find))) {
1074            return s;
1075        }
1076        String   ret = s.substring(0, x);
1077
1078        ret += replace;
1079        ret += s.substring(x + find.length());
1080        return ret;
1081    }
1082
1083    private void _addWhereClause(RDBColumn column, Object   value, String   cmp_op) {
1084        storeTableNameForColumn(column);
1085        buildWhereClause(column.getFullColumnName(), value, cmp_op);
1086    }
1087
1088    private void _addWhereClause(String   column, Object   value, String   cmp_op) {
1089        storeTableNameForColumn(column);
1090        buildWhereClause(column, value, cmp_op);
1091    }
1092
1093    private void buildWhereClause(String   column, Object   value, String   cmp_op) {
1094        // WebDocWf extension for extended wildcard support
1095        // The following line has been changed:
1096        String   escapeString = "";
1097
1098        // end of WebDocWf extension for extended wildcard support
1099        if (null == value) {
1100            if (EQUAL.equals(cmp_op)) {
1101                cmp_op = IS_NULL;
1102            } else if (NOT_EQUAL.equals(cmp_op)) {
1103                cmp_op = IS_NOT_NULL;
1104            }
1105            whereClauses.addElement(column + cmp_op);
1106        } else if (IS_NULL.equals(cmp_op) || IS_NOT_NULL.equals(cmp_op)) {
1107            whereClauses.addElement(column + cmp_op);
1108        } else {
1109            // WebDocWf extension for extended wildcard support
1110            // The following line has been changed:
1111            if ((-1 != cmp_op.indexOf('%'))
1112                || (cmp_op.equals(CASE_INSENSITIVE_MATCH))
1113                || (cmp_op.equals(CASE_SENSITIVE_MATCH))
1114                || (cmp_op.equals(USER_CASE_INSENSITIVE_MATCH))
1115                || (cmp_op.equals(USER_CASE_SENSITIVE_MATCH))) {  // CASE_...
1116                // before:      if ( -1 != cmp_op.indexOf( '%' ) ) {  // CASE_...
1117                // end of WebDocWf extension for extended wildcard support
1118                // WebDocWf extension for extended wildcard support
1119                // The following line has been changed:
1120                // There are 11 wildcarded string comparison operators:
1121                // before:          // There are 7 wildcarded string comparison operators:
1122                // end of WebDocWf extension for extended wildcard support
1123                // CASE_INSENSITIVE_EQUAL               = "%xxxx";
1124                // CASE_SENSITIVE_CONTAINS              = "%X%";
1125                // CASE_INSENSITIVE_CONTAINS    = "%x%";
1126                // CASE_SENSITIVE_STARTS_WITH   = "X%";
1127                // CASE_INSENSITIVE_STARTS_WITH = "x%";
1128                // CASE_SENSITIVE_ENDS_WITH     = "%X";
1129                // CASE_INSENSITIVE_ENDS_WITH   = "%x";
1130                // WebDocWf extension for extended wildcard support
1131                // The following lines have been added:
1132                // CASE_INSENSITIVE_MATCH       = "x";
1133                // CASE_SENSITIVE_MATCH = "X";
1134                // USER_CASE_INSENSITIVE_MATCH  = "u";
1135                // USER_CASE_SENSITIVE_MATCH    = "U";
1136                // end of WebDocWf extension for extended wildcard support
1137                // A lower-case 'x' means a case-insensitive comparison is desired.
1138                String   s = (String  ) value;
1139
1140                // WebDocWf extension for extended wildcard support
1141                // The following lines have been added:
1142                if (-1 != cmp_op.indexOf('u')) {
1143                    s = convertUserSearchValue(s);
1144                    if (userTrimString) {
1145                        s = s.trim();
1146                    }
1147                    if (userAppendWildcard) {
1148                        s = s + wildcard;
1149                    }
1150                    cmp_op = stringReplace(cmp_op, "u", "x");
1151                }
1152                if (-1 != cmp_op.indexOf('U')) {
1153                    s = convertUserSearchValue(s);
1154                    if (userTrimString) {
1155                        s = s.trim();
1156                    }
1157                    if (userAppendWildcard) {
1158                        s = s + wildcard;
1159                    }
1160                    cmp_op = stringReplace(cmp_op, "U", "X");
1161                }
1162                // end of WebDocWf extension for extended wildcard support
1163                if (-1 != cmp_op.indexOf('x')) {
1164                    // for case-insensitive comparisons,
1165                    // push both column and value to lower case.
1166                    s = s.toLowerCase();
1167                    // Or, could do
1168                    // s = "LOWER(" + s + ")";
1169                    // David Corbin thought that might be better somehow.
1170                    // I'd rather not count on the database optimizer
1171                    // any more than necessary.
1172                    if (!caseInsensitiveDatabase) {
1173                         column = "LOWER( " + column + ")";
1174                    }
1175                }
1176                // now we transform the CASE_ operator into the value
1177                // and set the comparison operator
1178                if (cmp_op.equals(CASE_INSENSITIVE_EQUAL)) {
1179                    cmp_op = EQUAL;
1180                    value = s;
1181                } else {
1182                    value = stringReplace(cmp_op.toLowerCase(), "x", s);
1183                    // WebDocWf extension for extended wildcard support
1184                    // The following lines has been added:
1185                    if (containsWildcards((String  ) value)) {
1186                        if (((String  ) value).equals(wildcard)) {
1187                            return;
1188                        }
1189                        escapeString = " " + wildcardEscapeClause;
1190                        // end of WebDocWf extension for extended wildcard support
1191                        cmp_op = likeKeyword;
1192                        // WebDocWf extension for extended wildcard support
1193                        // The following lines has been added:
1194                    } else {
1195                        cmp_op = EQUAL;
1196                    }
1197                    // end of WebDocWf extension for extended wildcard support
1198                }
1199            }
1200            // WebDocWf extension for extended wildcard support
1201            // The following line has been changed:
1202            whereClauses.addElement(column + " " + cmp_op + " ?" + escapeString);
1203            // before : whereClauses.addElement( column + " " + cmp_op + " ?" );
1204            // end of WebDocWf extension for extended wildcard support
1205            parms.addElement(value);
1206        }
1207    }
1208
1209    /**
1210     * Add a where-clause that compares a column against a string value
1211     * to the SQL command.
1212     *
1213     * @param column   The column (field) name to use as a search constraint.
1214     * @param value    The value to search for.
1215     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1216     * @deprecated  Use addWhere(RDBColumn column, String value, String cmp_op ) instead.
1217     */
1218    public void addWhereClause(String   column, String   value, String   cmp_op) {
1219        _addWhereClause(column, value, cmp_op);
1220    }
1221
1222    // WebDocWf extension for extended wildcard support
1223    // All following lines have been added:
1224    /**
1225     * Add a DB specific match/like clause to the query
1226     *
1227     * WebDocWf extension for extended wildcard support
1228     *
1229     * @param column   The column (field) name to use as a search constraint.
1230     * @param value    The value containing DB (vendor) specific wildcards to search for.
1231     * @return Whether a where-clause was generated.
1232     * @deprecated Use comparison operators instead
1233     */
1234    public boolean addMatchClause(String   column, String   value) {
1235        String   append = "";
1236        String   pattern = value;
1237        boolean added = false;
1238
1239        if (pattern != null) {
1240            if (userTrimString) {
1241                pattern = pattern.trim();
1242            }
1243            if (userAppendWildcard) {
1244                pattern = pattern + wildcard;
1245            }
1246            if (!pattern.equals(wildcard)) {
1247                append = " " + column + " " + likeKeyword + " ? "
1248                    + wildcardEscapeClause;
1249                added = true;
1250                parms.addElement(pattern);
1251            }
1252        }
1253        if (added) {
1254            storeTableNameForColumn(column);
1255            whereClauses.addElement(append);
1256        }
1257        return added;
1258    }
1259
1260    /**
1261     * Add a User specific match/like clause to the query
1262     *
1263     * WebDocWf extension for extended wildcard support
1264     *
1265     * @param column   The column (field) name to use as a search constraint.
1266     * @param value    The value containing User specific wildcards to search for.
1267     * @return Whether a where-clause was generated.
1268     * @deprecated Use comparison operators instead
1269     */
1270    public boolean addUserMatchClause(String   column, String   value) {
1271        boolean ret = false;
1272        String   pattern = convertUserSearchValue(value);
1273
1274        if (containsWildcards(pattern) || userAppendWildcard) {
1275            ret = addMatchClause(column, pattern);
1276        } else {
1277            addWhereClause(column, value, QueryBuilder.EQUAL);
1278            ret = true;
1279        }
1280        return ret;
1281    }
1282
1283    /**
1284     * Add a DB specific match/like clause to the query
1285     *
1286     * WebDocWf extension for extended wildcard support
1287     *
1288     * @param column   The column (field) to use as a search constraint.
1289     * @param value    The value containing DB (vendor) specific wildcards to search for.
1290     * @return Whether a where-clause was generated.
1291     * @deprecated Use comparison operators instead
1292     */
1293    public boolean addMatchClause(RDBColumn column, String   value) {
1294        return addMatchClause(column.getFullColumnName(), value);
1295    }
1296
1297    /**
1298     * Add a User specific match/like clause to the query
1299     *
1300     * WebDocWf extension for extended wildcard support
1301     *
1302     * @param column   The column (field) to use as a search constraint.
1303     * @param value    The value containing User specific wildcards to search for.
1304     * @return Whether a where-clause was generated.
1305     * @deprecated Use comparison operators instead
1306     */
1307    public boolean addUserMatchClause(RDBColumn column, String   value) {
1308        return addUserMatchClause(column.getFullColumnName(), value);
1309    }
1310
1311    /**
1312     * Evaluate if string contains Wildcards
1313     *
1314     * WebDocWf extension for extended wildcard support
1315     *
1316     * @param value    The value containing DB (vendor) specific wildcards to search for.
1317     * @return Whether the value contains wildcards
1318     */
1319    public boolean containsWildcards(String   value) {
1320        boolean ret = false;
1321
1322        if (-1 != value.indexOf(wildcard)) {
1323            ret = true;
1324        }
1325        if (-1 != value.indexOf(singleWildcard)) {
1326            ret = true;
1327        }
1328        return ret;
1329    }
1330
1331    /**
1332     * Replace User wildcards with DB vendor specific wildcards according to dods.conf
1333     *
1334     * WebDocWf extension for extended wildcard support
1335     *
1336     * @param value    The string with User wildcards to be changed
1337     * @return The string with DB vendor specific wildcards
1338     */
1339    public String   convertUserSearchValue(String   value) {
1340        String   ret;
1341
1342        if (value == null) {
1343            ret = null;
1344        } else {
1345            ret = "";
1346            for (int x = 0; x < value.length(); x++) {
1347                if (value.startsWith(userSingleWildcard, x)) {
1348                    ret = ret + singleWildcard;
1349                } else {
1350                    if (value.startsWith(userWildcard, x)) {
1351                        ret = ret + wildcard;
1352                    } else {
1353                        if (value.startsWith(wildcard, x)) {
1354                            ret = ret + wildcardEscape + wildcard;
1355                        } else {
1356                            if (value.startsWith(singleWildcard, x)) {
1357                                ret = ret + singleWildcardEscape
1358                                    + singleWildcard;
1359                            } else {
1360                                if (x + 1 < value.length()) {
1361                                    if (value.startsWith(userSingleWildcardEscape
1362                                                             + userSingleWildcard,
1363                                                         x)) {
1364                                        ret = ret + userSingleWildcard;
1365                                        x++;
1366                                    } else {
1367                                        if (value.startsWith(userWildcardEscape
1368                                                                 + userWildcard,
1369                                                             x)) {
1370                                            ret = ret + userWildcard;
1371                                            x++;
1372                                        } else {
1373                                            if (value.startsWith(userWildcardEscape
1374                                                                     + userWildcardEscape,
1375                                                                 x)) {
1376                                                ret = ret + userWildcardEscape;
1377                                                if (userWildcardEscape
1378                                                    == wildcardEscape) {
1379                                                    ret = ret + wildcardEscape;
1380                                                }
1381                                                x++;
1382                                            } else {
1383                                                if (value.startsWith(userSingleWildcardEscape
1384                                                                         + userSingleWildcardEscape,
1385                                                                     x)) {
1386                                                    ret = ret
1387                                                        + userSingleWildcardEscape;
1388                                                    if (userSingleWildcardEscape
1389                                                        == singleWildcardEscape) {
1390                                                        ret = ret
1391                                                            + singleWildcardEscape;
1392                                                    }
1393                                                    x++;
1394                                                } else {
1395                                                    ret = ret
1396                                                        + value.substring(x,
1397                                                                          x + 1);
1398                                                    if (value.startsWith(singleWildcardEscape,
1399                                                                         x)) {
1400                                                        ret = ret
1401                                                            + singleWildcardEscape;
1402                                                    } else {
1403                                                        if (value.startsWith(wildcardEscape,
1404                                                                             x)) {
1405                                                            ret = ret
1406                                                                + wildcardEscape;
1407                                                        }
1408                                                    }
1409                                                }
1410                                            }
1411                                        }
1412                                    }
1413                                } else {
1414                                    ret = ret + value.substring(x, x + 1);
1415                                    if (value.startsWith(singleWildcardEscape, x)) {
1416                                        ret = ret + singleWildcardEscape;
1417                                    } else {
1418                                        if (value.startsWith(wildcardEscape, x)) {
1419                                            ret = ret + wildcardEscape;
1420                                        }
1421                                    }
1422                                }
1423                            }
1424                        }
1425                    }
1426                }
1427            }
1428        }
1429        return ret;
1430    }
1431
1432    // end of second part WebDocWf extension for extended wildcard support
1433    /**
1434     * Add a where-clause to the SQL command.
1435     *
1436     * @param column   The column (field) name to use as a search constraint.
1437     * @param value    The value to search for.
1438     * @param nullOk   Whether a value of NULL is acceptable.
1439     * @return Whether a where-clause was generated.
1440     * false if value==null and nullOk==false.
1441     * @deprecated  Use addWhere(RDBColumn c1, BigDecimal value, String cmp_op) instead.
1442     */
1443    public boolean addWhereClause(String   column, BigDecimal   value,
1444                                  boolean nullOk) {
1445        storeTableNameForColumn(column);
1446        String   append = "";
1447        boolean added = false;
1448
1449        if (value == null) {
1450            if (nullOk) {
1451                append = " " + column + " is null";
1452                added = true;
1453            }
1454        } else {
1455            append = " " + column + " = ?";
1456            added = true;
1457            parms.addElement(value);
1458        }
1459        if (added) {
1460            whereClauses.addElement(append);
1461        }
1462        return added;
1463    }
1464
1465    /**
1466     * Add a where-clause to the SQL command.
1467     *
1468     * @param column   The column (field) name to use as a search constraint.
1469     * @param value    The int value to search for.
1470     * @deprecated  Use addWhere(RDBColumn column, int value) instead.
1471     */
1472    public void addWhereClause(String   column, int value) {
1473        _addWhereClause(column, new Integer  (value), EQUAL);
1474    }
1475
1476    /**
1477     * Add a where-clause to the SQL command.
1478     *
1479     * @param column   The column (field) name to use as a search constraint.
1480     * @param value    The int value to search for.
1481     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1482     * @deprecated  Use addWhere(RDBColumn column, int value, String cmp_op ) instead.
1483     */
1484    public void addWhereClause(String   column, int value, String   cmp_op) {
1485        _addWhereClause(column, new Integer  (value), cmp_op);
1486    }
1487
1488    /**
1489     * Add a where-clause to the SQL command.
1490     *
1491     * @param column   The column (field) name to use as a search constraint.
1492     * @param value    The long value to search for.
1493     * @deprecated  Use addWhere(RDBColumn column, long value) instead.
1494     */
1495    public void addWhereClause(String   column, long value) {
1496        _addWhereClause(column, new Long  (value), EQUAL);
1497    }
1498
1499    /**
1500     * Add a where-clause to the SQL command.
1501     *
1502     * @param column   The column (field) name to use as a search constraint.
1503     * @param value    The long value to search for.
1504     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1505     * @deprecated  Use addWhere(RDBColumn column, long value, String cmp_op ) instead.
1506     */
1507    public void addWhereClause(String   column, long value, String   cmp_op) {
1508        _addWhereClause(column, new Long  (value), cmp_op);
1509    }
1510
1511    /**
1512     * Add a where-clause to the SQL command.
1513     *
1514     * @param column   The column (field) name to use as a search constraint.
1515     * @param value    The value to search for.
1516     * @deprecated  Use addWhere(RDBColumn column, float value) instead.
1517     */
1518    public void addWhereClause(String   column, float value) {
1519        _addWhereClause(column, new Float  (value), EQUAL);
1520    }
1521
1522    /**
1523     * Add a where-clause to the SQL command.
1524     *
1525     * @param column   The column (field) name to use as a search constraint.
1526     * @param value    The value to search for.
1527     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1528     * @deprecated  Use addWhere(RDBColumn column, float value, String cmp_op ) instead.
1529     */
1530    public void addWhereClause(String   column, float value, String   cmp_op) {
1531        _addWhereClause(column, new Float  (value), cmp_op);
1532    }
1533
1534    /**
1535     * Add a where-clause to the SQL command.
1536     *
1537     * @param column   The column (field) name to use as a search constraint.
1538     * @param value    The value to search for.
1539     * @deprecated  Use addWhere(RDBColumn column, double value) instead.
1540     */
1541    public void addWhereClause(String   column, double value) {
1542        _addWhereClause(column, new Double  (value), EQUAL);
1543    }
1544
1545    /**
1546     * Add a where-clause to the SQL command.
1547     *
1548     * @param column   The column (field) name to use as a search constraint.
1549     * @param value    The value to search for.
1550     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1551     * @deprecated  Use addWhere(RDBColumn column, double value, String cmp_op ) instead.
1552     */
1553    public void addWhereClause(String   column, double value, String   cmp_op) {
1554        _addWhereClause(column, new Double  (value), cmp_op);
1555    }
1556
1557    /**
1558     * Add a where-clause to the SQL command.
1559     *
1560     * @param column   The column (field) name to use as a search constraint.
1561     * @param value    The value to search for.
1562     * @deprecated  Use addWhere(RDBColumn column, boolean value) instead.
1563     */
1564    public void addWhereClause(String   column, boolean value) {
1565        _addWhereClause(column, new Integer  (value ? 1 : 0), EQUAL);
1566    }
1567
1568    /**
1569     * Add a where-clause to the SQL command.
1570     *
1571     * @param column   The column (field) name to use as a search constraint.
1572     * @param value    The value to search for.
1573     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
1574     * @deprecated  Use addWhere(RDBColumn column, boolean value, String cmp_op ) instead.
1575     */
1576    public void addWhereClause(String   column, boolean value, String   cmp_op) {
1577        _addWhereClause(column, new Integer  (value ? 1 : 0), cmp_op);
1578    }
1579
1580    /**
1581     * Add a trailing clause (order by, group by, etc.) to the SQL command.
1582     *
1583     * @param clause     The clause to append to the SQL command.
1584     */
1585    public void addEndClause(String   clause) {
1586        endClauses.put(clause, "");
1587        preventPrimaryKeySelect = true;
1588    }
1589
1590    /**
1591     * Append to the 'select' portion of the SQL command.
1592     *
1593     * @param str     The string to append to the SQL command.
1594     * @deprecated  Use addEndClause() instead.
1595     */
1596    public void add(String   str) {
1597        addEndClause(str);
1598    }
1599
1600    /**
1601     * Sets debug flag to print the SQL statement just before its execution.
1602     * Affects just this instance of QueryBuilder.
1603     */
1604    public void debug() {
1605        debugSQL = true;
1606    }
1607
1608    /**
1609     * Sets debug flag to print the SQL statement just before its execution.
1610     * Affects all instances of QueryBuilder.
1611     */
1612    static public void debugAll() {
1613        debugAllSQL = true;
1614    }
1615
1616    /**
1617     * Add DISTINCT keyword to query.
1618     */
1619    private String   distinct = "";
1620    public void distinct() {
1621        distinct = "distinct ";
1622    }
1623
1624    public void addWhere(String   fullClause) {
1625        whereClauses.addElement(fullClause);
1626        multiTableJoin = true;
1627        preventPrimaryKeySelect = true;
1628    }
1629
1630    // sinisa 14.06.2004. new addWhereIn methods, to be added in complex query cache
1631
1632
1633    /**
1634     * QueryBuilder.addWhereIn( RDBColumn, String[] )
1635     * allows queries like
1636     *   select Person.Age from Person
1637     *   where Person.Name in ( "Bob", "Joe" )
1638     * with code like
1639     *   QueryBuilder qb = new QueryBuilder();
1640     *   qb.select( PersonDO.Age );
1641     *   String[] values = { "Bob", "Joe" };
1642     *   qb.addWhereIn( PersonDO.Name, values );
1643     *
1644     * @param val : PersonDO.Name in the example above
1645     * @param values : array of Strings containing values for IN clause
1646     *
1647     * @exception QueryException if values is null or empty
1648     * author Sinisa Milosevic
1649     */
1650    public void addWhereIn(RDBColumn val, String  [] values)
1651        throws QueryException {
1652        if (null == values || 0 == values.length) {
1653            throw new QueryException("IN clause must have at least one value.");
1654        }
1655        if (null == val) {
1656            throw new QueryException("IN clause left value cannot be null");
1657        }
1658        String   comma = "";
1659        String   qmarks = "";
1660
1661        for (int i = 0; i < values.length; i++) {
1662            String   v = values[i];
1663
1664            parms.addElement(values[i]);
1665            qmarks += comma + "?";
1666            comma = ", ";
1667        }
1668
1669        storeTableName(val.getTableName());
1670        addWhere(val.getFullColumnName() + OPEN_IN + qmarks + CLOSE_IN);
1671    }
1672
1673
1674    /**
1675     * QueryBuilder.addWhereIn( RDBColumn, BigDecimal[] )
1676     * allows queries like
1677     *   select Person.Age from Person
1678     *   where Person.oid in ( 100, 101 )
1679     * with code like
1680     *   QueryBuilder qb = new QueryBuilder();
1681     *   qb.select( PersonDO.Age );
1682     *   BigDecimal[] values = { new BigDecimal("100") , new BigDecimal("101") };
1683     *   qb.addWhereIn( PersonDO.Name, values );
1684     *
1685     * @param val : PersonDO.Name in the example above
1686     * @param values : array of Strings containing values for IN clause
1687     *
1688     * @exception QueryException if values is null or empty
1689     * author Sinisa Milosevic
1690     */
1691    public void addWhereIn(RDBColumn val, BigDecimal  [] values)
1692        throws QueryException {
1693        if (null == values || 0 == values.length) {
1694            throw new QueryException("IN clause must have at least one value.");
1695        }
1696        if (null == val) {
1697            throw new QueryException("IN clause left value cannot be null");
1698        }
1699        String   comma = "";
1700        String   qmarks = "";
1701
1702        for (int i = 0; i < values.length; i++) {
1703            BigDecimal   v = values[i];
1704            if(v!=null) {
1705                //               parms.addElement(values[i]);
1706                qmarks += comma + v.toString();
1707                comma = ", ";
1708            }
1709        }
1710
1711        storeTableName(val.getTableName());
1712        addWhere(val.getFullColumnName() + OPEN_IN + qmarks + CLOSE_IN);
1713    }
1714
1715
1716
1717    /**
1718     * QueryBuilder.addWhereIn( RDBColumn, ObjectId[] )
1719     * allows queries like
1720     *   select Person.Age from Person
1721     *   where Person.oid in ( 100, 101 )
1722     * with code like
1723     *   QueryBuilder qb = new QueryBuilder();
1724     *   qb.select( PersonDO.Age );
1725     *   ObjectId[] values = { new ObjectId("100") , new ObjectId("101") };
1726     *   qb.addWhereIn( PersonDO.Name, values );
1727     *
1728     * @param val : PersonDO.Name in the example above
1729     * @param values : array of Strings containing values for IN clause
1730     *
1731     * @exception QueryException if values is null or empty
1732     * author Sinisa Milosevic
1733     */
1734    public void addWhereIn(RDBColumn val, ObjectId[] values)
1735        throws QueryException {
1736        if (null == values || 0 == values.length) {
1737            throw new QueryException("IN clause must have at least one value.");
1738        }
1739        if (null == val) {
1740            throw new QueryException("IN clause left value cannot be null");
1741        }
1742        String   comma = "";
1743        String   qmarks = "";
1744
1745        for (int i = 0; i < values.length; i++) {
1746            ObjectId v = values[i];
1747            if(v!=null) {
1748                qmarks += comma + v.toString();
1749                comma = ", ";
1750            }
1751        }
1752
1753        storeTableName(val.getTableName());
1754        addWhere(val.getFullColumnName() + OPEN_IN + qmarks + CLOSE_IN);
1755    }
1756
1757
1758    /**
1759     * QueryBuilder.addWhereIn( RDBColumn, GenericDO[] )
1760     * allows queries like
1761     *   select Person.Age from Person
1762     *   where Person.oid in ( 100, 101 )
1763     * with code like
1764     *   QueryBuilder qb = new QueryBuilder();
1765     *   qb.select( PersonDO.Age );
1766     *   GenericDO[] values = { new GenericDO("100") , new GenericDO("101") };
1767     *   qb.addWhereIn( PersonDO.Name, values );
1768     *
1769     * @param val : PersonDO.oid in the example above
1770     * @param values : array of Strings containing values for IN clause
1771     *
1772     * @exception QueryException if values is null or empty
1773     * author Sinisa Milosevic
1774     */
1775    public void addWhereIn(RDBColumn val, GenericDO[] values)
1776        throws QueryException {
1777        if (null == values || 0 == values.length) {
1778            throw new QueryException("IN clause must have at least one value.");
1779        }
1780        if (null == val) {
1781            throw new QueryException("IN clause left value cannot be null");
1782        }
1783        String   comma = "";
1784        String   qmarks = "";
1785
1786        for (int i = 0; i < values.length; i++) {
1787            GenericDO v = values[i];
1788            if(v!=null) {
1789                try {
1790                    qmarks += comma + v.getHandle();
1791                    comma = ", ";
1792                } catch (DatabaseManagerException e) {
1793                    throw new QueryException("Error during preparing where clause in SQL statement");
1794                }
1795
1796            }
1797        }
1798
1799        storeTableName(val.getTableName());
1800        addWhere(val.getFullColumnName() + OPEN_IN + qmarks + CLOSE_IN);
1801    }
1802
1803    //end sinisa 14.06.2004
1804
1805    /**
1806     * QueryBuilder.addWhereIn( RDBColumn, QueryBuilder )
1807     * allows queries like
1808     *   select Person.Name from Person where Person.City
1809     *   in ( select City.oid from City where City.Size > 1000 )
1810     * with code like
1811     *   QueryBuilder mainQuery = new QueryBuilder();
1812     *   mainQuery.select( PersonDO.Name );
1813     *   QueryBuilder subQuery = new QueryBuilder();
1814     *   subQuery.select( CityDO.PrimaryKey );
1815     *   subQuery.addWhere( CityDO.Size, 1000, QueryBuilder.GREATER_THAN );
1816     *   mainQuery.addWhereIn( PersonDO.City, subQuery );
1817     *
1818     * @param field T1DO.T1Y in the example above
1819     * @param qb subQuery in the example above
1820     * author Jay Gunter
1821     */
1822    // following line is removed - no longer needed: rka
1823    // prev: private Vector subQueries = new Vector();
1824    public void addWhereIn(RDBColumn field, QueryBuilder qb)
1825        throws QueryException {
1826        if (this == qb) {
1827            throw new QueryException("Recursion detection: subquery cannot be "
1828                                         + "the same object as the main query.");
1829        }
1830        storeTableName(field.getTableName());
1831        addWhere(field.getFullColumnName() + OPEN_IN + qb.getSQL() + CLOSE_IN);
1832        // the following line is changed: rka
1833        parms.addElement(qb);
1834        // prev: subQueries.addElement( qb );
1835        multiTableJoin = true;
1836    }
1837  
1838    /**
1839     * Reset (remove) "UNION [ALL]" part of query.
1840     */
1841    public void resetUnion(){
1842        doUnionAll = new Vector  ();  // 
1843        unionQuerys= new Vector  ();  //
1844        unionTableJoin = false;
1845    }
1846    /**
1847     * QueryBuilder.addUnion(QueryBuilder qb, boolean all)
1848     *
1849     * @param qb  Additional query that will be added in "UNION" part of this query.
1850     * @param all If this parameter is set to "true" then union clausule will be set to "UNION ALL" instead of "UNION"
1851     */
1852    
1853    public void addUnion(QueryBuilder qb, boolean all)
1854    throws QueryException {
1855        if (this == qb) {
1856            throw new QueryException("Recursion detection: union query cannot be the same object as the main query.");
1857        }
1858        doUnionAll.add(new Boolean  (all));
1859        unionQuerys.add(qb);
1860        unionTableJoin = true;
1861    }
1862
1863
1864    /**
1865     * QueryBuilder.addWhereExists( QueryBuilder )
1866     *
1867     * @param qb subQuery in the example above
1868     * author rkapusta@together.sk
1869     */
1870    public void addWhereExists(QueryBuilder qb)
1871        throws QueryException {
1872        if (this == qb) {
1873            throw new QueryException("Recursion detection: subquery cannot be "
1874                                         + "the same object as the main query.");
1875        }
1876        addWhere(OPEN_EXISTS + qb.getSQL() + CLOSE_IN);
1877        parms.addElement(qb);
1878        multiTableJoin = true;
1879    }
1880
1881    /**
1882     * QueryBuilder.addWhereNotExists( QueryBuilder )
1883     *
1884     * @param qb subQuery in the example above
1885     * author rkapusta@together.sk
1886     */
1887    public void addWhereNotExists(QueryBuilder qb)
1888        throws QueryException {
1889        if (this == qb) {
1890            throw new QueryException("Recursion detection: subquery cannot be "
1891                                         + "the same object as the main query.");
1892        }
1893        addWhere(OPEN_NOT_EXISTS + qb.getSQL() + CLOSE_IN);
1894        parms.addElement(qb);
1895        multiTableJoin = true;
1896    }
1897
1898    /**
1899     * QueryBuilder.addWhereNotIn( RDBColumn, QueryBuilder )
1900     * allows queries like
1901     *   select Person.Name from Person where Person.City
1902     *   not in ( select City.oid from City where City.Size > 1000 )
1903     * with code like
1904     *   QueryBuilder mainQuery = new QueryBuilder();
1905     *   mainQuery.select( PersonDO.Name );
1906     *   QueryBuilder subQuery = new QueryBuilder();
1907     *   subQuery.select( CityDO.PrimaryKey );
1908     *   subQuery.addWhere( CityDO.Size, 1000, QueryBuilder.GREATER_THAN );
1909     *   mainQuery.addWhereNotIn( PersonDO.City, subQuery );
1910     *
1911     * @param field T1DO.T1Y in the example above
1912     * @param qb subQuery in the example above
1913     * author Jay Gunter
1914     */
1915    public void addWhereNotIn(RDBColumn field, QueryBuilder qb)
1916        throws QueryException {
1917        if (this == qb) {
1918            throw new QueryException("Recursion detection: subquery cannot be "
1919                                         + "the same object as the main query.");
1920        }
1921        storeTableName(field.getTableName());
1922        addWhere(field.getFullColumnName() + OPEN_NOT_IN + qb.getSQL()
1923                     + CLOSE_IN);
1924        // the following line is changed
1925        parms.addElement(qb);
1926        // prev: subQueries.addElement( qb );
1927        multiTableJoin = true;
1928    }
1929
1930    /**
1931     * QueryBuilder.addWhereIn( Object, Object[] )
1932     * allows queries like
1933     *   select Person.Age from Person
1934     *   where Person.Name in ( "Bob", "Joe" )
1935     * with code like
1936     *   QueryBuilder qb = new QueryBuilder();
1937     *   qb.select( PersonDO.Age );
1938     *   String[] values = { "Bob", "Joe" };
1939     *   qb.addWhereIn( PersonDO.Name, values );
1940     *
1941     * or query
1942     *   select Person.Age from Person
1943     *   where "Tom" in ( Person.Surname, Person.Nick )
1944     * with code
1945     *   QueryBuilder qb = new QueryBuilder();
1946     *   qb.select( PersonDO.Age );
1947     *   RDBColumn[] values = { PersonDO.Surname, PersonDO.Nick };
1948     *   qb.addWhereIn( "Tom", values );
1949     *
1950     * @param val : PersonDO.Name & "Tom" in the example above
1951     * @param values : array of Objects containing values for IN clause
1952     *              any of previous Object types can be RDBColumn, String,
1953     *              java.sql.Date, java.sql.Timestamp, BigDecimal, Integer,
1954     *              Long, Float, Double, Boolean
1955     * @exception QueryException if values is null or empty
1956     * author Jay Gunter
1957     */
1958    public void addWhereIn(Object   val, Object  [] values)
1959        throws QueryException {
1960        if (null == values || 0 == values.length) {
1961            throw new QueryException("IN clause must have at least one value.");
1962        }
1963        if (null == val) {
1964            throw new QueryException("IN clause left value cannot be null");
1965        }
1966        String   comma = "";
1967        String   qmarks = "";
1968
1969        for (int i = 0; i < values.length; i++) {
1970            Object   v = values[i];
1971
1972            if (v instanceof RDBColumn) {
1973                RDBColumn f = (RDBColumn) v;
1974
1975                storeTableName(f.getTableName());
1976                qmarks += comma + f.getFullColumnName();
1977            } else {
1978                parms.addElement(values[i]);
1979                qmarks += comma + "?";
1980            }
1981            comma = ", ";
1982        }
1983        if (val instanceof RDBColumn) {
1984            RDBColumn f = (RDBColumn) val;
1985
1986            storeTableName(f.getTableName());
1987            addWhere(f.getFullColumnName() + OPEN_IN + qmarks + CLOSE_IN);
1988        } else {
1989            parms.addElement(val);
1990            addWhere("?" + OPEN_IN + qmarks + CLOSE_IN);
1991        }
1992    }
1993
1994    /**
1995     * QueryBuilder.addWhereNotIn( Object, Object[] )
1996     * see addWhereIn( Object val, Object[] values ) for example
1997     *
1998     * @param val
1999     * @param values
2000     * @exception QueryException if values is null or empty
2001     * author Jay Gunter
2002     */
2003    public void addWhereNotIn(Object   val, Object  [] values)
2004        throws QueryException {
2005        if (null == values || 0 == values.length) {
2006            throw new QueryException("NOT IN clause must have at least one value.");
2007        }
2008        if (null == val) {
2009            throw new QueryException("NOT IN clause left value cannot be null");
2010        }
2011        String   comma = "";
2012        String   qmarks = "";
2013
2014        for (int i = 0; i < values.length; i++) {
2015            Object   v = values[i];
2016
2017            if (v instanceof RDBColumn) {
2018                RDBColumn f = (RDBColumn) v;
2019
2020                storeTableName(f.getTableName());
2021                qmarks += comma + f.getFullColumnName();
2022            } else {
2023                parms.addElement(values[i]);
2024                qmarks += comma + "?";
2025            }
2026            comma = ", ";
2027        }
2028        if (val instanceof RDBColumn) {
2029            RDBColumn f = (RDBColumn) val;
2030
2031            storeTableName(f.getTableName());
2032            addWhere(f.getFullColumnName() + OPEN_NOT_IN + qmarks + CLOSE_IN);
2033        } else {
2034            parms.addElement(val);
2035            addWhere("?" + OPEN_NOT_IN + qmarks + CLOSE_IN);
2036        }
2037    }
2038
2039    // WebDocWf extension for getting SQL command with parms
2040    public String   getSQLwithParms() {
2041        return recurseSQLwithParms(getSQL());
2042    }
2043
2044    private String   recurseSQLwithParms(String   sSQL) {
2045        try {
2046            for (int iParm = 0; iParm < parms.size(); iParm++) {
2047                Object   o = parms.get(iParm);           
2048                int iIndex = sSQL.indexOf("?");
2049                
2050                if (o instanceof QueryBuilder) {
2051                    sSQL = ((QueryBuilder) o).recurseSQLwithParms(sSQL);
2052                } else {
2053                    StringBuffer   sbSQL = new StringBuffer  (sSQL);
2054                    String   s = o.toString();
2055                    
2056                    sbSQL.replace(iIndex, iIndex + 1, s);
2057                    sSQL = sbSQL.toString();
2058                }                               
2059            }
2060        } catch (Exception   ex) {
2061            sSQL = sSQL + " : error occured during getting SQL command : " + ex;
2062        }
2063        return sSQL;
2064    }
2065
2066    // end webdocwf 310.2.26 changes
2067    public String   getSQL() {
2068        // Query begins with SELECT <fields> FROM <tablelist>
2069        if (iQueryType == UPDATE_QUERY) {
2070            sql = "update ";
2071        } else if (iQueryType == DELETE_QUERY) {
2072            sql = "delete from ";
2073        } else {  
2074            
2075            if(selectClause!=null){
2076                boolean withPrefix = selectClause.startsWith("@T@_");
2077                boolean withNoPrefix = selectClause.startsWith("@F@_");
2078                String   fakeTableName;
2079                String   tableName="";
2080                if (withPrefix||withNoPrefix) {
2081                    tableName =selectClause.substring(selectClause.indexOf("_")+1,selectClause.indexOf("_@@."));
2082                    if(withPrefix) {
2083                        fakeTableName = "@T@_"+tableName+"_@@.";
2084                    } else {
2085                        fakeTableName = "@F@_"+tableName+"_@@.";
2086                    }
2087                    tableName=tableName+".";
2088                    if(withPrefix || isMultiTableJoin()) {
2089                        selectClause = Common.replaceAll(selectClause, fakeTableName, tableName);
2090                    }else{
2091                        selectClause = Common.replaceAll(selectClause, fakeTableName, "");
2092                    }
2093                }
2094            }
2095   
2096            String   glue = 0 == selectClause.length() ? "" : ", ";
2097
2098            for (int i = 0; i < selectedFields.size(); i++) {
2099                RDBColumn field = (RDBColumn) selectedFields.elementAt(i);
2100
2101                selectClause += glue + field.getFullColumnName();
2102                storeTableName(mainTableName = field.getTableName());
2103                glue = ", ";
2104            }
2105            sql = "select " + distinct + selectClause + " from ";
2106        }
2107        Enumeration   tables = tableNames.keys();
2108
2109        for (int i = 0; tables.hasMoreElements(); i++) {
2110            if (i > 0) {
2111                sql += ", ";
2112            }
2113            sql += tables.nextElement();
2114        }
2115        if (iQueryType == UPDATE_QUERY) {
2116            sql += " set ";
2117            int nr_updateFields = updateFields.size();
2118
2119            for (int i = 0; i < nr_updateFields; i++) {
2120                if (i > 0) {
2121                    sql += ", ";
2122                }
2123                sql += updateFields.elementAt(i);
2124            }
2125        }
2126        // Cleanup WHERE clauses.
2127        // Discard trailing ORs and OPEN_PARENs.
2128        String   chunk = null;
2129        int numWhereClauses = whereClauses.size();
2130
2131        for (; numWhereClauses > 0; numWhereClauses--) {
2132            chunk = (String  ) whereClauses.elementAt(numWhereClauses - 1);
2133            if (!(chunk.equals(OR) || chunk.equals(OPEN_PAREN))) {
2134                break;
2135            }
2136        }
2137        // Add WHERE clauses to query.
2138        int parenDepth = 0;
2139        boolean prevWas_OpenParen_or_OR = true;
2140
2141        for (int i = 0; i < numWhereClauses; i++) {
2142            chunk = (String  ) whereClauses.elementAt(i);
2143
2144            /* System.err.println("chunk ="+chunk ); */
2145            if (0 == i) {
2146                sql += " WHERE ";
2147            }
2148            if (chunk.equals(OR)) {
2149                prevWas_OpenParen_or_OR = true;
2150            } else if (chunk.equals(OPEN_PAREN)) {
2151                if (!prevWas_OpenParen_or_OR) {
2152                    sql += " AND ";
2153                }
2154                prevWas_OpenParen_or_OR = true;
2155                parenDepth++;
2156            } else if (chunk.equals(CLOSE_PAREN)) {
2157                if (parenDepth > 0) {
2158                    prevWas_OpenParen_or_OR = false;
2159                    parenDepth--;
2160                } else {
2161                    chunk = "";
2162                }
2163            } else { // chunk is actual expression for where-clause
2164                if (!prevWas_OpenParen_or_OR) {
2165                    sql += " AND ";
2166                }
2167                prevWas_OpenParen_or_OR = false;
2168            }
2169            sql += chunk;
2170        }
2171        // Add any needed trailing CLOSE_PARENs.
2172        while (parenDepth-- > 0) {
2173            sql += CLOSE_PAREN;
2174        }
2175        if (iQueryType == SELECT_QUERY) {
2176
2177            /*
2178             * ORDER BY code added by Chris Ryan (cryan@plugged.net.au)
2179             * The following code adds the ORDER BY 'xxx[, xxx]' to the
2180             * statement for querying.
2181             */
2182            int nr_OrderFields = orderFields.size();
2183
2184            // ORDER BY required?
2185            if (nr_OrderFields > 0) {
2186                // yes, fill out the SQL...
2187                sql += " ORDER BY ";
2188                for (int i = 0; i < nr_OrderFields; i++) {
2189                    if (i > 0) {
2190                        sql += ", ";
2191                    }
2192                    sql += orderFields.elementAt(i) + " "
2193                        + orderDirections.elementAt(i);
2194                    // sql += orderFields.elementAt(i);
2195                }
2196            }
2197        }
2198        Enumeration   endings = endClauses.keys();
2199
2200        while (endings.hasMoreElements()) {
2201            sql += endings.nextElement();
2202        }
2203        
2204        if (isUnionTableJoin()) {
2205            for (int i=0 ; i<unionQuerys.size() ; i++){
2206                QueryBuilder tmpQB=((QueryBuilder)unionQuerys.elementAt(i));
2207                if(((Boolean  )doUnionAll.elementAt(i)).booleanValue()){
2208                    sql +="  UNION  ALL  "+tmpQB.getSQL();
2209                }else{
2210                    sql +="  UNION  "+tmpQB.getSQL();
2211                }
2212                parms.addElement(tmpQB);
2213            }
2214        }      
2215        
2216        return sql;
2217    }
2218
2219    // WebDocWf fix, the following line has been changed
2220    public int applyParameters(PreparedStatement   ps, int paramCount)
2221        throws SQLException   {
2222        // before: public void applyParameters( PreparedStatement ps, int paramCount )
2223        for (int i = 0; i < parms.size(); i++) {
2224            int n = ++paramCount;
2225            Object      o = parms.elementAt(i);
2226
2227            if (debugSQL || debugAllSQL) {
2228                System.out.print("Param: " + i + ", paramCount=" + paramCount);
2229                System.out.println(", Value: " + o);
2230            }
2231            if (o instanceof String  ) {
2232
2233                /*
2234                 if ( fuzzies.get( i ) ) {
2235                 if (likeKeyword.equals(DEFAULT_MATCHES_KEYWORD)) {
2236                 ps.setString(     n, getRegExpMatch( (String) o ) );
2237                 } else {
2238                 ps.setString(     n, (String)                 o );
2239                 }
2240                 } else {
2241                 ps.setString(     n, (String)                 o );
2242                 }
2243                 */
2244                ps.setString(n, (String  ) o);
2245            } else if (o instanceof java.sql.Date  ) { /* VB 2/2/2000 */
2246                ps.setDate(n, ((java.sql.Date  ) o));
2247            } else if (o instanceof java.sql.Timestamp  ) { /* VB 2/9/2000 */
2248                ps.setTimestamp(n, ((java.sql.Timestamp  ) o));
2249            } else if (o instanceof BigDecimal  ) {
2250                ps.setBigDecimal(n, (BigDecimal  ) o);
2251            } else if (o instanceof Integer  ) {
2252                ps.setInt(n, ((Integer  ) o).intValue());
2253            } else if (o instanceof Long  ) {
2254                ps.setLong(n, ((Long  ) o).longValue());
2255            } else if (o instanceof Float  ) {
2256                ps.setFloat(n, ((Float  ) o).floatValue());
2257            } else if (o instanceof Double  ) {
2258                ps.setDouble(n, ((Double  ) o).doubleValue());
2259            } else if (o instanceof Boolean  ) {
2260                ps.setBoolean(n, ((Boolean  ) o).booleanValue());
2261            } else if (o instanceof GenericDO) {
2262                ps.setBigDecimal(n, ((GenericDO) o).get_OId().toBigDecimal());
2263            } // the following lines have been inserted: rka
2264            else if (o instanceof QueryBuilder) {
2265                QueryBuilder subQuery = (QueryBuilder) o;
2266
2267                paramCount = subQuery.applyParameters(ps, --paramCount);
2268            }
2269            // end of insertion
2270        }
2271        // |the following lines have been removed due to change of subquery
2272        // |manipulation: rka
2273        /*
2274         for ( int i = 0; i < subQueries.size(); i++ ) {
2275         QueryBuilder subQuery = (QueryBuilder) subQueries.elementAt(i);
2276         // WebDocWf fix, the following line has been changed
2277         paramCount = subQuery.applyParameters( ps, paramCount );
2278         // before:     subQuery.applyParameters( ps, paramCount );
2279         }*/
2280        // |end of deletion
2281        // the following line has been inserted
2282        return paramCount;
2283    }
2284
2285    /**
2286     * Generate a JDBC PreparedStatement using the values passed
2287     * for the where-clauses.
2288     *
2289     * @param conn   The DBConnection object to use for the search.
2290     */
2291    private String   sql = null;
2292    private void prepareStatement(DBConnection conn)
2293        throws SQLException   {
2294        getSQL();
2295        if (debugSQL || debugAllSQL) {
2296            System.out.println("\nQueryBuilder.prepareStatement sql=\n"
2297                                   + prettySQL(sql) + "\n");
2298        }
2299        try {
2300            if((iResultSetConcurrency == DEFAULT_RS_CONCURRENCY) && (iResultSetType == DEFAULT_RS_TYPE)) {
2301                stmt = conn.prepareStatement(sql);
2302            } else {
2303                stmt = ((ExtendedDBConnection)conn).prepareStatement(sql,iResultSetType,iResultSetConcurrency);
2304            }
2305
2306            int qto = (0 < iCurrentQueryTimeout)
2307                ? iCurrentQueryTimeout
2308                : iDefaultQueryTimeout;
2309
2310            if (qto > 0) {
2311                stmt.setQueryTimeout(qto);
2312            }
2313            // if ( maxRows > 0 )
2314            // WebDocWf extension cursor-handling
2315            // fetchsize is not defined for this query; try applicationfetchsize
2316            int ifs = (iCurrentFetchSize < 0)
2317                ? iDefaultFetchSize
2318                : iCurrentFetchSize;
2319
2320            // check for queryfetchsize
2321            if (ifs > 0) {
2322                if (doCursorName(conn)){
2323                    stmt.setCursorName(getCountX());
2324                }
2325                if (doSetFetchSize(conn)){
2326                    stmt.setFetchSize(ifs);
2327                }
2328            }
2329            // WebDocWf extension cursor-handling end
2330            stmt.setMaxRows(maxRows);
2331        } catch (SQLException   se) {
2332            System.err.println("\n\nSQLException: " + se.getMessage() + ","
2333                                   + se.getErrorCode() + "," + se.getSQLState());
2334            throw se;
2335        }
2336        applyParameters(stmt, 0);
2337    }
2338    private int maxRows = -1;
2339    public void setMaxRows(int x) {
2340        if (0 > x) {
2341            x = 0;
2342        }
2343        maxRows = x;
2344    }
2345    private String  []    breakWords = {
2346        "(", ")", "FROM", "from", "WHERE", "where", "AND", "and", "OR", "or" };
2347    private String   indent(int level) {
2348        String   ret = "";
2349
2350        for (int i = 0; i < level; i++) {
2351            ret += "    ";
2352        }
2353        return ret;
2354    }
2355
2356    private String   prettySQL(String   sql) {
2357        int next = 0;
2358        int sp = 0;
2359        int level = 0;
2360        int word = 0;
2361        String   out = "";
2362        int pastend = 9999999;
2363
2364        sql = sql + " ";
2365        for (int offset = 0; offset < sql.length();) {
2366            int nearest = pastend;
2367            int i = 0;
2368
2369            for (; i < breakWords.length; i++) {
2370                if (-1 != (next = sql.indexOf(breakWords[i], offset))) {
2371                    if (next < nearest) {
2372                        nearest = next;
2373                        word = i;
2374                    }
2375                }
2376            }
2377            next = nearest;
2378            i = word;
2379            if (i < breakWords.length && pastend != nearest) {
2380                int wordlen = breakWords[i].length();
2381
2382                if (0 == i) {
2383                    level++;
2384                }
2385                out += sql.substring(offset, next);
2386                out += "\n";
2387                out += indent(level);
2388                out += sql.substring(next, next + wordlen) + " ";
2389                next += wordlen;
2390                if (1 == i) {
2391                    level--;
2392                }
2393                offset = next;
2394            } else {
2395                out += indent(level) + sql.substring(offset);
2396                break;
2397            }
2398        }
2399        return out;
2400    }
2401
2402    /**
2403     * Return the JDBC PreparedStatement generated by prepareStatement().
2404     * @return The JDBC PreparedStatement.
2405     */
2406    public PreparedStatement   getStatement() {
2407        // Would anyone ever want this?
2408        return stmt;
2409    }
2410
2411    /**
2412     * Return the JDBC PreparedStatement generated by prepareStatement().
2413     * @return The JDBC PreparedStatement.
2414     */
2415    public PreparedStatement   getStatement(DBConnection conn) throws SQLException   {
2416        prepareStatement(conn);
2417        return stmt;
2418    }
2419
2420    /**
2421     * Execute the PreparedStatement.
2422     * @return The ResultSet object generated by JDBC for the search.
2423     */
2424
2425    /*
2426     public ResultSet executeQuery() {
2427     conn = new DBConnection();
2428     return executeQuery( conn );
2429     }
2430     */
2431
2432    /**
2433     * Execute the PreparedStatement and return a JDBC ResultSet.
2434     * @param conn   The DBConnection object to use for the search.
2435     * @return The ResultSet object generated by JDBC for the search.
2436     */
2437    public ResultSet   executeQuery(DBConnection conn)
2438        throws SQLException   {
2439        prepareStatement(conn);
2440        int maxExecuteTime = ((StandardDatabaseManager)DODS.getDatabaseManager()).getDatabaseManagerConfiguration().getMaxExecuteTime();
2441    
2442        boolean throwException = false;
2443        String    errMsg = "<no-defined>";
2444        
2445        Date   startQueryT = new Date  ();
2446        try {
2447            rs = stmt.executeQuery();
2448        }catch(SQLException   esql){
2449            throwException=true;
2450            errMsg="<Replicated>:"+esql.getMessage();
2451            DODS.getLogChannel().write(Logger.ERROR," <SqlQueryExecute><SqlQueryException>("+ errMsg+")");
2452        }
2453        Date   stopQueryT = new Date  ();
2454        
2455        int qTime = ( new Long  (stopQueryT.getTime() - startQueryT.getTime())).intValue();
2456        int qto = (0 < iCurrentQueryTimeout)
2457        ? iCurrentQueryTimeout
2458        : iDefaultQueryTimeout;
2459        
2460        int queryTimeOutValue = qto*1000; //X(s)*1000 = (X*1000)(ms) 
2461        boolean overrun = (queryTimeOutValue>0)&&(qTime>=queryTimeOutValue);
2462        
2463        if (qTime>=maxExecuteTime)  {
2464            DODS.getLogChannel().write(Logger.DEBUG,
2465                            " <SqlQueryTime>" +
2466                            " :ExecuteTime="+qTime+" [MaxExecuteTime(ms)="+maxExecuteTime+"]"+
2467                            " :sql="+getSQLwithParms()+
2468                            " :execute time ="+qTime+
2469                            " :max execute time = "+maxExecuteTime);
2470        }
2471        if (overrun) {
2472            DODS.getLogChannel().write(Logger.DEBUG,
2473                            " <SqlQueryTimeOut>" +
2474                            " :ExecuteTime="+qTime+" [QueryTimeOut(ms)="+queryTimeOutValue+"]"+
2475                            " :sql="+getSQLwithParms()+
2476                            " :execute time ="+qTime+
2477                            " :QueryTimeOut ="+queryTimeOutValue);
2478        }
2479        
2480        if(throwException)throw new SQLException  ("<SqlQueryExecute><SqlQueryException>("+errMsg+")");
2481        
2482        if(overrun) {
2483            try {
2484                if(rs!=null) {
2485                    rs.close();
2486                    rs=null;
2487                }
2488            }catch(SQLException   esql) {
2489                DODS.getLogChannel().write(Logger.DEBUG," <SqlQueryTimeOut> Forced ResultSet close error.");
2490            }
2491            throw new SQLException  (" <SqlQueryTimeOut> Query timeout, forced close of ResultSet");
2492        }
2493        rsClosed = new Boolean  (false);
2494        return rs;
2495    }
2496
2497    /**
2498     * Reset the where-clause information.
2499     * The same fields specified in the QueryBuilder constructor,
2500     * can be retrieved for a different set of where-clauses.
2501     */
2502    public void reset() {
2503        setMaxRows(0);
2504        whereClauses = new Vector  (); // keep the select, forget the where-clauses
2505        parms = new Vector  ();   // discard parms for previous where-clause
2506        fuzzies = new BitSet  (); // forget special string handling
2507        multiTableJoin = false;
2508        // Clear the ORDER BY Vectors
2509        orderFields = new Vector  ();
2510        orderDirections = new Vector  ();
2511        try {
2512            if (null != rs && null != rsClosed && !rsClosed.booleanValue()) {
2513                rsClosed = new Boolean  (true);
2514                rs.close();
2515            }
2516            rs = null;
2517            rsClosed = new Boolean  (true);
2518        } catch (SQLException   e) {
2519            DODS.getLogChannel().write(Logger.DEBUG,"QueryBuilder unable to close ResultSet: SQLException="
2520                                   + e.getMessage());
2521        }
2522        if (null != conn) {     // if this class created the connection, close it.
2523            conn.close();
2524        }
2525        conn = null;
2526    }
2527
2528    public void close() {
2529        try {
2530            if (null != dbQuery) {
2531                dbQuery.release();
2532            }
2533            if (null != rs && null != rsClosed && !rsClosed.booleanValue()) {
2534                rsClosed = new Boolean  (true);
2535                rs.close();
2536            }
2537            rsClosed = new Boolean  (true);
2538            rs = null;
2539        } catch (SQLException   e) {
2540            DODS.getLogChannel().write(Logger.DEBUG,"QueryBuilder unable to close ResultSet: SQLException="
2541                                   + e.getMessage());
2542        }
2543    }
2544
2545    /**
2546     * Returns a regular expression, as supported by "MATCHES"
2547     * that will will perform a case insensitive match of a substring.
2548     * @param base   The base String to convert to a regular expression.
2549     * @return       The generated regular expression.
2550     */
2551    private String   getRegExpMatch(String   base) {
2552        int len = base.length();
2553        char[] baseChars = new char[len];
2554
2555        base.getChars(0, len, baseChars, 0);
2556        // The result will never exceed 5 * the length of
2557        // the original string.
2558        char[] result = new char[len * 5];
2559        int resultIdx = 0;
2560
2561        for (int idx = 0; idx < baseChars.length; idx++) {
2562            if (Character.isSpaceChar(baseChars[idx])) {
2563                // Convert spaces to ' *'
2564                if ((resultIdx > 0) && (result[resultIdx - 1] != '*')) {
2565                    result[resultIdx++] = ' ';
2566                    // result[resultIdx++] = '*';
2567                    result[resultIdx++] = '%';
2568                }
2569            } else if (Character.isLetter(baseChars[idx])) {
2570                // Convert letters to [Aa]
2571                result[resultIdx++] = '[';
2572                result[resultIdx++] = Character.toUpperCase(baseChars[idx]);
2573                result[resultIdx++] = Character.toLowerCase(baseChars[idx]);
2574                result[resultIdx++] = ']';
2575            } else {
2576                // Leave everything else as is.
2577                result[resultIdx++] = baseChars[idx];
2578            }
2579        }
2580        // Match anything at end of line
2581        result[resultIdx++] = '*';
2582        return new String  (result);
2583    }
2584    private String   mainTableName;                       // prepended to column names
2585    private Hashtable   tableNames = new Hashtable  ();     // table names in FROM clause
2586    private Hashtable   endClauses = new Hashtable  ();     // clauses appended to SQL
2587    private Vector   selectedFields = new Vector  ();       // RDBColumns to return
2588    private String   selectClause = "";           // comma separated fields
2589    private Vector   whereClauses = new Vector  (); // WHERE clauses
2590    private Vector   orderFields = new Vector  ();  // fields for ORDER BY clauses
2591    private Vector   orderDirections = new Vector  ();      // ascending/descending
2592    private PreparedStatement   stmt;                     // what we execute
2593    private Vector   parms;                               // parameters for stmt
2594    private BitSet   fuzzies;                             // which parms use regex match
2595    private int param = 0;                              // parameter index
2596    private DBConnection conn = null;           // for execution of stmt
2597    // [john] Update support {begin} -----------------------------------------------
2598    private Vector   updateFields = new Vector  (); // fields for SET clauses
2599    public final static int SELECT_QUERY = 0;
2600    public final static int UPDATE_QUERY = 1;
2601    public final static int DELETE_QUERY = 2;
2602    private int iQueryType = SELECT_QUERY;
2603    public void setDeleteQuery() {
2604        iQueryType = DELETE_QUERY;
2605    }
2606    private Vector   doUnionAll = new Vector  ();  // 
2607    private Vector   unionQuerys= new Vector  ();  //
2608    /**
2609     * Add a column to set by update query
2610     *
2611     * @param column   The column (field) name to set.
2612     * @param value    The new value to set for.
2613     */
2614    public void addUpdateColumn(RDBColumn column, Object   value) {
2615        _addUpdateColumn(column.getFullColumnName(), value);
2616    }
2617
2618    /**
2619     * Add a column to set by update query
2620     *
2621     * @param column   The column (field) name to set.
2622     * @param value    The new value to set for.
2623     * @deprecated  Use addUpdateColumn(RDBColumn column, Object value) instead.
2624     */
2625    public void addUpdateColumn(String   column, Object   value) {
2626        _addUpdateColumn(column, value);
2627    }
2628
2629    /**
2630     * Add a column to set by update query
2631     *
2632     * @param column   The column (field) name to set.
2633     * @param value    The new value to set for.
2634     */
2635    private void _addUpdateColumn(RDBColumn column, Object   value) {
2636        _addUpdateColumn(column.getFullColumnName(), value);
2637    }
2638
2639    /**
2640     * Add a column to set by update query
2641     *
2642     * @param column   The column (field) name to set.
2643     * @param value    The new value to set for.
2644     */
2645    private void _addUpdateColumn(String   column, Object   value) {
2646        iQueryType = UPDATE_QUERY;
2647        storeTableNameForColumn(column);
2648        if (value != null) {
2649            updateFields.addElement(column + " = ?");
2650            // parms.addElement(value);
2651            parms.add(updateFieldPos++, value);
2652        } else {
2653            updateFields.addElement(column + " = null");
2654        }
2655    }
2656
2657    private int updateFieldPos = 0;
2658
2659    /**
2660     * addUpdateSQL
2661     */
2662    public void addUpdateSQL(RDBColumn column, String   sqlValue) {
2663        addUpdateSQL(column.getFullColumnName(), sqlValue);
2664    }
2665
2666    public void addUpdateSQL(String   column, String   sqlValue) {
2667        iQueryType = UPDATE_QUERY;
2668        storeTableNameForColumn(column);
2669        updateFields.addElement(column + " = " + sqlValue);
2670    }
2671
2672    /**
2673     * Executes the SQL INSERT, UPDATE or DELETE statement
2674     *
2675     * @return either the row count for INSERT, UPDATE or DELETE statements;
2676     * or 0 for SQL statements that return nothing
2677     * @exception SQLException if a database access error occurs
2678     */
2679    public int executeUpdate() throws SQLException   {
2680        return executeUpdate(null);
2681    }
2682
2683    public int executeUpdate(DBConnection dbConn) throws SQLException   {
2684        int rc = 0;
2685
2686        if (tableNames.size() > 1) {
2687            throw new SQLException  ("Too many tables specified for update/delete statement.");
2688        }
2689        try {
2690            if (null == dbConn) {
2691                if (conn == null) {
2692                    conn = DODS.getDatabaseManager().allocateConnection();
2693                }
2694                dbConn = conn;
2695            }
2696            prepareStatement(dbConn);
2697            rc = stmt.executeUpdate();
2698        } catch (SQLException   sqlExcept) {
2699            if (conn != null) {
2700                try {
2701                    conn.rollback();
2702                } catch (SQLException   rollbackExcept) {
2703                    System.out.println("Failed to rollback transaction."
2704                                           + "\n    SQLState = " + rollbackExcept.getSQLState()
2705                                           + "\n    SQLError = "
2706                                           + rollbackExcept.getErrorCode()
2707                                           + "\n    SQLMsg   = " + rollbackExcept.getMessage());
2708                }
2709                if (!conn.handleException(sqlExcept)) {
2710                    conn = null;
2711                }
2712            } else {
2713                throw sqlExcept;
2714            }
2715            System.out.println("ERR870: Exception in executeSQL " + sqlExcept);
2716        } catch (Exception   ex) {
2717            System.out.println("ERR871: Exception in executeSQL " + ex);
2718        }
2719        finally {
2720            if (conn != null) {
2721                try {
2722                    conn.reset();
2723                } catch (SQLException   sqlExcept) {
2724                    System.out.println("ObjectIdAllocator: "
2725                                           + "\n  Failed to reset connection."
2726                                           + "\n    SQLState = " + sqlExcept.getSQLState()
2727                                           + "\n    SQLError = " + sqlExcept.getErrorCode()
2728                                           + "\n    SQLMsg   = " + sqlExcept.getMessage());
2729                }
2730                finally {
2731                    conn.release();
2732                    conn = null;
2733                }
2734            }
2735        }
2736        return rc;
2737    }
2738
2739    // [john] Update support {end} -------------------------------------------------
2740    /**
2741     * Set the string matching details for inexact string matches
2742     * See dods.conf for details.
2743     * Code added by Chris Ryan (cryan@plugged.net.au)
2744     *
2745     * @param match_keyword  Whether the DB can use "MATCHES" or "LIKE"
2746     * @param wildcard  The wildcard character for string searches
2747     */
2748    public void setStringMatchDetails(String   match_keyword, String   wildcard) {
2749        this.likeKeyword = match_keyword;
2750        this.wildcard = wildcard;
2751    }
2752    // WebDocWf extension for extended wildcard support
2753    // All following lines have been added:
2754    //
2755    private String   singleWildcard = DEFAULT_SINGLE_WILDCARD;
2756    private String   singleWildcardEscape = DEFAULT_SINGLE_WILDCARD_ESCAPE;
2757    private String   wildcardEscape = DEFAULT_WILDCARD_ESCAPE;
2758    private String   wildcardEscapeClause = DEFAULT_WILDCARD_ESCAPE_CLAUSE;
2759
2760    /**
2761     * Initialize the extended wildcard support
2762     *
2763     * WebDocWf extension for extended wildcard support
2764     *
2765     * @param match_keyword The SQL keyword "matches" or "like"
2766     * @param multi_wildcard The SQL string char for 0 to n characters ("*" or "%")
2767     * @param single_wildcard The SQL string char for 1 character ("?" or "_")
2768     * @param single_wildcard_escape The SQL string char for escaping a single wildcard
2769     * @param multi_wildcard_escape The SQL string char for escaping a multi wildcard
2770     * @param wildcard_escape_clause The SQL string for defining escape charcters
2771     */
2772    public void setStringMatchDetails(String   match_keyword, String   multi_wildcard, String   single_wildcard,
2773                                      String   single_wildcard_escape, String   multi_wildcard_escape,
2774                                      String   wildcard_escape_clause) {
2775        this.likeKeyword = match_keyword;
2776        this.wildcard = multi_wildcard;
2777        this.singleWildcard = single_wildcard;
2778        this.singleWildcardEscape = single_wildcard_escape;
2779        this.wildcardEscape = multi_wildcard_escape;
2780        this.wildcardEscapeClause = wildcard_escape_clause;
2781    }
2782    private String   userWildcard = userConfigWildcard;
2783    private String   userSingleWildcard = userConfigSingleWildcard;
2784    private String   userSingleWildcardEscape = userConfigSingleWildcardEscape;
2785    private String   userWildcardEscape = userConfigWildcardEscape;
2786
2787
2788    private void initDefaultWildCards(){
2789        userWildcard = userConfigWildcard;
2790        userSingleWildcard = userConfigSingleWildcard;
2791        userSingleWildcardEscape = userConfigSingleWildcardEscape;
2792        userWildcardEscape = userConfigWildcardEscape;
2793    }
2794
2795
2796    /**
2797     * Change the user wildcard
2798     *
2799     * WebDocWf extension for extended wildcard support
2800     *
2801     * @param user_wildcard   The user wildcard (normally "*")
2802     */
2803    public void setUserStringWildcard(String   user_wildcard) {
2804        this.userWildcard = user_wildcard;
2805    }
2806
2807    /**
2808     * Change the user single wildcard
2809     *
2810     * WebDocWf extension for extended wildcard support
2811     *
2812     * @param user_single_wildcard   The user single wildcard (normally "?")
2813     */
2814    public void setUserStringSingleWildcard(String   user_single_wildcard) {
2815        this.userSingleWildcard = user_single_wildcard;
2816    }
2817
2818    /**
2819     * Change the user single wildcard esacpe char
2820     *
2821     * WebDocWf extension for extended wildcard support
2822     *
2823     * @param user_single_wildcard_escape   The user single wildcard escape char (normally "?")
2824     */
2825    public void setUserStringSingleWildcardEscape(String   user_single_wildcard_escape) {
2826        this.userSingleWildcardEscape = user_single_wildcard_escape;
2827    }
2828
2829    /**
2830     * Change the user wildcard esacpe char
2831     *
2832     * WebDocWf extension for extended wildcard support
2833     *
2834     * @param user_wildcard_escape   The user wildcard escape char (normally "?")
2835     */
2836    public void setUserStringWildcardEscape(String   user_wildcard_escape) {
2837        this.userWildcardEscape = user_wildcard_escape;
2838    }
2839
2840    /**
2841     * Get the user wildcard
2842     *
2843     * WebDocWf extension for extended wildcard support
2844     *
2845     * @return   The user wildcard (normally "*")
2846     */
2847    public String   getUserStringWildcard() {
2848        return userWildcard;
2849    }
2850
2851    /**
2852     * Get the user single wildcard
2853     *
2854     * WebDocWf extension for extended wildcard support
2855     *
2856     * @return   The user single wildcard (normally "?")
2857     */
2858    public String   getUserStringSingleWildcard() {
2859        return userSingleWildcard;
2860    }
2861
2862    /**
2863     * Get the user single wildcard escape char
2864     *
2865     * WebDocWf extension for extended wildcard support
2866     *
2867     * @return   The user single wildcard escape char (normally "?")
2868     */
2869    public String   getUserStringSingleWildcardEscape() {
2870        return userSingleWildcardEscape;
2871    }
2872
2873    /**
2874     * Get the user wildcard escape char
2875     *
2876     * WebDocWf extension for extended wildcard support
2877     *
2878     * @return   The user wildcard escape char (normally "?")
2879     */
2880    public String   getUserStringWildcardEscape() {
2881        return userWildcardEscape;
2882    }
2883    private boolean userAppendWildcard = DEFAULT_USER_APPEND_WILDCARD;
2884    private boolean userTrimString = DEFAULT_USER_TRIM_STRING;
2885
2886    /**
2887     * Set whether Querybuilder should append a wildcard at the end of match-strings
2888     *
2889     * WebDocWf extension for extended wildcard support
2890     *
2891     * @param user_append_wildcard Whether Querybuilder should append a wildcard at the end of match-strings
2892     */
2893    public void setUserStringAppendWildcard(boolean user_append_wildcard) {
2894        this.userAppendWildcard = user_append_wildcard;
2895    }
2896
2897    /**
2898     * Set whether Querybuilder should trim the match strings
2899     *
2900     * WebDocWf extension for extended wildcard support
2901     *
2902     * @param user_trim_string boolean user_trim_string Whether Querybuilder should trim the match strings
2903     */
2904    public void setUserStringTrim(boolean user_trim_string) {
2905        this.userTrimString = user_trim_string;
2906    }
2907
2908    /**
2909     * Get whether Querybuilder should append a wildcard at the end of match-strings
2910     *
2911     * WebDocWf extension for extended wildcard support
2912     *
2913     * @return Whether Querybuilder appends a wildcard at the end of match-strings
2914     */
2915    public boolean getUserStringAppendWildcard() {
2916        return userAppendWildcard;
2917    }
2918
2919    /**
2920     * Get whether Querybuilder trims the match strings
2921     *
2922     * WebDocWf extension for extended wildcard support
2923     *
2924     * @return Whether Querybuilder trims the match strings
2925     */
2926    public boolean getUserStringTrim() {
2927        return userTrimString;
2928    }
2929
2930    //
2931    // end of third part WebDocWf extension for extended wildcard support
2932    /**
2933     * Add a where-clause that compares a column against a date value
2934     * to the SQL command.
2935     * Contributed by: Victor Brilon
2936     *
2937     * @param column   The column (field) name to use as a search constraint.
2938     * @param date     Date.
2939     * @param cmp_op   The comparison operator to use:  QueryBuilder.EQUAL, ...
2940     * @deprecated  Use addWhere(RDBColumn c1, Date value, String cmp_op) instead.
2941     */
2942    public void addWhereClause(String   column, java.sql.Date   date,
2943                               String   cmp_op) {
2944        _addWhereClause(column, date, cmp_op);
2945    }
2946
2947    public void addWhereClause(String   column, java.sql.Timestamp   date,
2948                               String   cmp_op) {
2949        _addWhereClause(column, date, cmp_op);
2950    }
2951
2952    public void addWhereClause(String   column, java.sql.Time   date,
2953                               String   cmp_op) {
2954        _addWhereClause(column, date, cmp_op);
2955    }
2956
2957    /**
2958     * Add another field to the ORDER BY list.
2959     * NOTE: is used by SourceGenerator_Query and writeMemberStuff.template.
2960     *
2961     * This method added by Chris Ryan (cryan@plugged.net.au) to
2962     * allow easier access to the ORDER BY part of the SQL query.
2963     *
2964     * @param column     The column to order by
2965     * @param direction  ASCENDING or DESCENDING order
2966     */
2967    public void addOrderByColumn(String   column, String   direction) {
2968        orderFields.addElement(column);
2969        orderDirections.addElement(direction);
2970        preventPrimaryKeySelect = true;
2971    }
2972
2973    /**
2974     * Add another field to the ORDER BY list.
2975     * NOTE: is used by SourceGenerator_Query and writeMemberStuff.template.
2976     *
2977     * This method added by Chris Ryan (cryan@plugged.net.au) to
2978     * allow easier access to the ORDER BY part of the SQL query.
2979     *
2980     * @param column     The column to order by
2981     * @param direction  ASCENDING or DESCENDING order
2982     */
2983    public void addOrderByColumn(RDBColumn column, String   direction) {
2984        orderFields.addElement(column.getFullColumnName());
2985        orderDirections.addElement(direction);
2986        preventPrimaryKeySelect = true;
2987    }
2988
2989    public void setDatabaseVendor(String   vendor) throws DatabaseManagerException {
2990        String   str = Common.getDodsConfProperty("LikeKeyword", vendor);
2991
2992        if (str != null) {
2993            likeKeyword = str;
2994        }
2995        str = Common.getDodsConfProperty("Wildcard", vendor);
2996        if (str != null) {
2997            wildcard = str;
2998        }
2999        str = Common.getDodsConfProperty("SingleWildcard", vendor);
3000        if (str != null) {
3001            singleWildcard = str;
3002        }
3003        str = Common.getDodsConfProperty("SingleWildcardEscape", vendor);
3004        if (str != null) {
3005            singleWildcardEscape = str;
3006        }
3007        str = Common.getDodsConfProperty("WildcardEscape", vendor);
3008        if (str != null) {
3009            wildcardEscape = str;
3010        }
3011        str = Common.getDodsConfProperty("WildcardEscapeClause", vendor);
3012        if (str!=null) {
3013            if (str.equalsIgnoreCase("none")){
3014                wildcardEscapeClause = "";
3015            }else{
3016                wildcardEscapeClause = str;
3017            }
3018        }
3019    }
3020
3021    /**
3022     * Set database vendor for the default logical database.
3023     */
3024    public void setDatabaseVendor() {
3025        try {
3026            String   vendor = DODS.getDatabaseManager().logicalDatabaseType();
3027            setDatabaseVendor(vendor);
3028        } catch (Exception   e) {
3029            e.printStackTrace();
3030        }
3031    }
3032
3033    /**
3034     * Set database name to QueryBuilder.
3035     * @param dbName     Database name
3036     */
3037    public void setDatabaseName(String   dbName) {
3038        databaseName = dbName;
3039    }
3040
3041    /**
3042     * Get database name.
3043     * @return String     Database name
3044     */
3045    public String   getDatabaseName() {
3046        if (databaseName == null) {
3047            try {
3048                databaseName = DODS.getDatabaseManager().getDefaultDB();
3049            } catch (Exception   e) {}
3050        }
3051        return databaseName;
3052    }
3053
3054    static private final int op_EQUAL = 1;
3055    static private final int op_NOT_EQUAL = 2;
3056    static private final int op_LESS_THAN = 3;
3057    static private final int op_LESS_THAN_OR_EQUAL = 4;
3058    static private final int op_GREATER_THAN = 5;
3059    static private final int op_GREATER_THAN_OR_EQUAL = 6;
3060    static private final int op_IS_NULL = 7;
3061    static private final int op_IS_NOT_NULL = 8;
3062    static private final int op_CASE_SENSITIVE_CONTAINS = 9;
3063    static private final int op_CASE_INSENSITIVE_CONTAINS = 10;
3064    static private final int op_CASE_SENSITIVE_STARTS_WITH = 11;
3065    static private final int op_CASE_INSENSITIVE_STARTS_WITH = 12;
3066    static private final int op_CASE_SENSITIVE_ENDS_WITH = 13;
3067    static private final int op_CASE_INSENSITIVE_ENDS_WITH = 14;
3068    static private final int op_CASE_INSENSITIVE_EQUAL = 15;
3069    static Hashtable   cmp_ops = new Hashtable  ();
3070    static {
3071        cmp_ops.put(EQUAL, new Integer  (op_EQUAL));
3072        cmp_ops.put(NOT_EQUAL, new Integer  (op_NOT_EQUAL));
3073        cmp_ops.put(LESS_THAN, new Integer  (op_LESS_THAN));
3074        cmp_ops.put(LESS_THAN_OR_EQUAL, new Integer  (op_LESS_THAN_OR_EQUAL));
3075        cmp_ops.put(GREATER_THAN, new Integer  (op_GREATER_THAN));
3076        cmp_ops.put(GREATER_THAN_OR_EQUAL, new Integer  (op_GREATER_THAN_OR_EQUAL));
3077        cmp_ops.put(IS_NULL, new Integer  (op_IS_NULL));
3078        cmp_ops.put(IS_NOT_NULL, new Integer  (op_IS_NOT_NULL));
3079        cmp_ops.put(CASE_SENSITIVE_CONTAINS,
3080                    new Integer  (op_CASE_SENSITIVE_CONTAINS));
3081        cmp_ops.put(CASE_INSENSITIVE_CONTAINS,
3082                    new Integer  (op_CASE_INSENSITIVE_CONTAINS));
3083        cmp_ops.put(CASE_SENSITIVE_STARTS_WITH,
3084                    new Integer  (op_CASE_SENSITIVE_STARTS_WITH));
3085        cmp_ops.put(CASE_INSENSITIVE_STARTS_WITH,
3086                    new Integer  (op_CASE_INSENSITIVE_STARTS_WITH));
3087        cmp_ops.put(CASE_SENSITIVE_ENDS_WITH,
3088                    new Integer  (op_CASE_SENSITIVE_ENDS_WITH));
3089        cmp_ops.put(CASE_INSENSITIVE_ENDS_WITH,
3090                    new Integer  (op_CASE_INSENSITIVE_ENDS_WITH));
3091        cmp_ops.put(CASE_INSENSITIVE_EQUAL,
3092                    new Integer  (op_CASE_INSENSITIVE_EQUAL));
3093    }
3094
3095    /**
3096     * Wrapper for compare() method that takes Objects for arguments.
3097     * This method takes doubles, and so will handle
3098     * shorts, ints, longs, floats, doubles, but not booleans.
3099     *
3100     * @return true if a compares to b according to cmp_op
3101     * @param a the DO member value
3102     * @param b the value to compare against
3103     * @exception QueryException if cmp_op is not a valid comparison operator.
3104     * @see #compare( Object a, Object b, String cmp_op )
3105     * author Jay Gunter
3106     */
3107    static public boolean compare(double a, double b, String   cmp_op)
3108        throws QueryException {
3109        return compare(new Double  (a), new Double  (b), cmp_op);
3110    }
3111
3112    /**
3113     * Wrapper for compare() method that takes Objects for arguments.
3114     * This method takes booleans.
3115     *
3116     * @return true if a compares to b according to cmp_op
3117     * @param a the DO member value
3118     * @param b the value to compare against
3119     * @exception QueryException if cmp_op is not a valid comparison operator.
3120     * @see #compare( Object a, Object b, String cmp_op )
3121     * author Jay Gunter
3122     */
3123    static public boolean compare(boolean a, boolean b, String   cmp_op)
3124        throws QueryException {
3125        return compare(new Double  (a ? 1 : 0), new Double  (b ? 1 : 0), cmp_op);
3126    }
3127
3128    /**
3129     * Wrapper for compare() method that takes Objects for arguments.
3130     * This method takes two arguments - first double, and second Object.
3131     *
3132     * @return true if a compares to b according to cmp_op
3133     * @param a the DO member value
3134     * @param b the value to compare against
3135     * @exception QueryException if cmp_op is not a valid comparison operator.
3136     * @see #compare( Object a, Object b, String cmp_op )
3137     */
3138    static public boolean compare(double a, Object   b, String   cmp_op)
3139        throws QueryException {
3140        return compare(new Double  (a), b, cmp_op);
3141    }
3142
3143    /**
3144     * Wrapper for compare() method that takes Objects for arguments.
3145     * This method takes two arguments - first boolean, and second Object.
3146     *
3147     * @return true if a compares to b according to cmp_op
3148     * @param a the DO member value
3149     * @param b the value to compare against
3150     * @exception QueryException if cmp_op is not a valid comparison operator.
3151     * @see #compare( Object a, Object b, String cmp_op )
3152     */
3153    static public boolean compare(boolean a, Object   b, String   cmp_op)
3154        throws QueryException {
3155        return compare(new Double  (a ? 1 : 0), b, cmp_op);
3156    }
3157
3158    /**
3159     * Compare two values according to a QueryBuilder comparison operator.
3160     * This method has some general applicability.
3161     * It is handy for writing sorting algorithms, etc.
3162     *
3163     * The Query classes generated by DODS use this method
3164     * to prune undesired values from the cache held by a DO class.
3165     *
3166     * @return true if a compares to b according to cmp_op
3167     * @param a the DO member value
3168     * @param b the value to compare against
3169     * @exception QueryException if cmp_op is not a valid comparison operator.
3170     * author Jay Gunter
3171     */
3172    static public boolean compare(Object   a, Object   b, String   cmp_op)
3173        throws QueryException {
3174        Integer   cmp = (Integer  ) cmp_ops.get(cmp_op);
3175
3176        if (null == cmp) {
3177            throw new QueryException("Unrecognized comparison operator: "
3178                                         + cmp_op);
3179        }
3180        int op = cmp.intValue();
3181
3182        switch (op) {
3183            case op_IS_NULL:
3184                return null == b;
3185
3186            case op_IS_NOT_NULL:
3187                return null != b;
3188
3189            case op_EQUAL:
3190                if (null == a && null == b) {
3191                    return true;
3192                }
3193                if (null == a || null == b) {
3194                    return false;
3195                }
3196                byte[] z = {};
3197
3198                if (a.getClass() == z.getClass()) {
3199                    byte[] ab = (byte[]) a;
3200                    byte[] bb = (byte[]) b;
3201
3202                    if (ab.length != bb.length) {
3203                        return false;
3204                    }
3205                    for (int i = 0; i < ab.length; i++) {
3206                        if (ab[i] != bb[i]) {
3207                            return false;
3208                        }
3209                    }
3210                    return true;
3211                } else if (a instanceof GenericDO) {
3212                    String   aoid = ((GenericDO) a).get_OId().toString();
3213                    String   boid = ((GenericDO) b).get_OId().toString();
3214                    boolean equals = aoid.equals(boid);
3215
3216                    // WebDocWf fix for cached lazyloading objects
3217                    // lazyloading objects are only put in the cache if they are loaded
3218                    // so there may be multiple instances for one row in memory
3219                    // the following lines have been put under comments
3220                    // if ( equals && a != b )
3221                    // System.err.println(
3222                    // "\n\nDATA OBJECT WARNING: Two DOs with the same OID: "
3223                    // + aoid );
3224                    // end of WebDocWf fix for cached lazyloading objects
3225                    return equals;
3226                } else if (a instanceof BigDecimal  &&b instanceof GenericDO) {
3227                    return ((GenericDO)b).get_OId().toString().equals(a.toString());
3228                } else if (a instanceof CoreDataStruct) {
3229                    String   aoid = ((CoreDataStruct) a).get_OId().toString();
3230                    String   boid = ((CoreDataStruct) b).get_OId().toString();
3231                    boolean equals = aoid.equals(boid);
3232
3233                    // WebDocWf fix for cached lazyloading objects
3234                    // lazyloading objects are only put in the cache if they are loaded
3235                    // so there may be multiple instances for one row in memory
3236                    // the following lines have been put under comments
3237                    // if ( equals && a != b )
3238                    // System.err.println(
3239                    // "\n\nDATA OBJECT WARNING: Two DOs with the same OID: "
3240                    // + aoid );
3241                    // end of WebDocWf fix for cached lazyloading objects
3242                    return equals;
3243                } else if (a instanceof BigDecimal  &&b instanceof CoreDataStruct) {
3244                    return ((CoreDataStruct)b).get_OId().toString().equals(a.toString());
3245                }
3246
3247                else {
3248                    return a.equals(b);
3249                }
3250
3251            case op_NOT_EQUAL:
3252                return !compare(a, b, EQUAL);
3253
3254            case op_GREATER_THAN:
3255                return !compare(a, b, LESS_THAN_OR_EQUAL);
3256
3257            case op_GREATER_THAN_OR_EQUAL:
3258                return !compare(a, b, LESS_THAN);
3259
3260            case op_LESS_THAN:
3261            case op_LESS_THAN_OR_EQUAL:
3262                if (null == a || null == b) {
3263                    return false;
3264                }
3265                if (a instanceof String  ) {
3266                    String   aStr = (String  ) a;
3267                    String   bStr = null;
3268
3269                    if (!(b instanceof String  )) {
3270                        bStr = b.toString();
3271                    }
3272                    int x = aStr.compareTo(bStr);
3273
3274                    return (-1 == x || (0 == x && op_LESS_THAN_OR_EQUAL == op));
3275                } else if (a instanceof java.util.Date  ) {
3276                    if (!(b instanceof java.util.Date  )) {
3277                        throw new QueryException("Second arg for " + cmp_op
3278                                                     + " is not a Date");
3279                    }
3280                    java.util.Date   ad = (java.util.Date  ) a;
3281                    java.util.Date   bd = (java.util.Date  ) b;
3282
3283                    return (ad.before(bd)
3284                                || (ad.equals(bd) && op_LESS_THAN_OR_EQUAL == op));
3285                } else if (a instanceof BigDecimal  ) {
3286                    if (!(b instanceof BigDecimal  )) {
3287                        throw new QueryException("First arg for " + cmp_op
3288                                                     + " is BigDecimal, " + "but second arg is not.");
3289                    }
3290                    int x = ((BigDecimal  ) a).compareTo((BigDecimal  )b);
3291
3292                    return (-1 == x || (0 == x && op_LESS_THAN_OR_EQUAL == op));
3293                } else if (a instanceof Number  ) {
3294                    if (!(b instanceof Number  )) {
3295                        throw new QueryException("First arg for " + cmp_op
3296                                                     + " is Number, " + "but second arg is not.");
3297                    }
3298                    double ad = ((Number  ) a).doubleValue();
3299                    double bd = ((Number  ) b).doubleValue();
3300
3301                    return (ad < bd || (ad == bd && op_LESS_THAN_OR_EQUAL == op));
3302                } else {
3303                    throw new QueryException("Comparison operator " + cmp_op
3304                                                 + " not supported for objects of type "
3305                                                 + a.getClass().getName());
3306                }
3307
3308            case op_CASE_INSENSITIVE_EQUAL:
3309                if (null == a && null == b) {
3310                    return true;
3311                }
3312                if (null == a || null == b) {
3313                    return false;
3314                }
3315                String   aStr = (String  ) a;
3316                String   bStr = (String  ) b;
3317                return aStr.equalsIgnoreCase(bStr);
3318
3319            case op_CASE_SENSITIVE_CONTAINS:
3320            case op_CASE_INSENSITIVE_CONTAINS:
3321            case op_CASE_SENSITIVE_STARTS_WITH:
3322            case op_CASE_INSENSITIVE_STARTS_WITH:
3323            case op_CASE_SENSITIVE_ENDS_WITH:
3324            case op_CASE_INSENSITIVE_ENDS_WITH:
3325                if (!(a instanceof String  )) {
3326                    throw new QueryException("First arg for " + cmp_op
3327                                                 + " is not a string.");
3328                }
3329                if (!(b instanceof String  )) {
3330                    throw new QueryException("Second arg for " + cmp_op
3331                                                 + " is not a string.");
3332                }
3333                String   as = (String  ) a;
3334                String   bs = (String  ) b;
3335
3336                if (-1 != cmp_op.indexOf('x')) {
3337                    as = as.toLowerCase();
3338                    bs = bs.toLowerCase();
3339                }
3340                switch (op) {
3341                    case op_CASE_SENSITIVE_CONTAINS:
3342                    case op_CASE_INSENSITIVE_CONTAINS:
3343                        return -1 != as.indexOf(bs);
3344
3345                    case op_CASE_SENSITIVE_STARTS_WITH:
3346                    case op_CASE_INSENSITIVE_STARTS_WITH:
3347                        return as.startsWith(bs);
3348
3349                    case op_CASE_SENSITIVE_ENDS_WITH:
3350                    case op_CASE_INSENSITIVE_ENDS_WITH:
3351                        return as.endsWith(bs);
3352                }
3353                throw new QueryException("QueryBuilder.compare bug in CASE_ processing");
3354
3355            default:
3356                throw new QueryException("Unchecked comparison operator: " + cmp_op);
3357        }
3358        // return false; // should never get here
3359    }
3360
3361    // WebDocWf extension for row counters
3362    // All following lines have been added:
3363    /**
3364     * Set the Query select clause (e.g. &quot;*&quot;) to something different (e.g. &quot;count(*)&quot;)
3365     * Hack for implementing row counters
3366     *
3367     * @param newClause The new select clause string
3368     */
3369    public void setSelectClause(String   newClause) {
3370        selectClause = newClause;
3371    }
3372
3373    /**
3374     * Get the Query select clause string
3375     * Hack for implementing row counters
3376     *
3377     * @return The current select clause string
3378     */
3379    public String   getSelectClause() {
3380        return selectClause;
3381    }
3382
3383    // end of WebDocWf extension for row counters
3384    // WebDocWf extension cursor-handling
3385    /**
3386     * sets the fetchsize for this query, overwrites the applicationfetchsize
3387     * @param iCurrentFetchSizeIn integer representing the fetchsize for this query
3388     */
3389    public void setCurrentFetchSize(int iCurrentFetchSizeIn) {
3390        iCurrentFetchSize = iCurrentFetchSizeIn;
3391    }
3392
3393    // WebDocWf extension cursor-handling
3394    /**
3395     * reads the current fetchsize for this query
3396     * @return the current fetchsize; if -1 the no fetchsize is defined, applicationfetchsize will be use if defined
3397     */
3398    public int getCurrentFetchSize() {
3399        return iCurrentFetchSize;
3400    }
3401
3402    /**
3403     * sets the default fetchsize
3404     * @param iDefaultFetchSizeIn integer representing the fetchsize for this query
3405     */
3406    static public void setDefaultFetchSize(int iDefaultFetchSizeIn) {
3407        iDefaultFetchSize = iDefaultFetchSizeIn;
3408    }
3409
3410    // WebDocWf extension cursor-handling
3411    /**
3412     * reads the default fetchsize
3413     * @return the current fetchsize; if -1 the no fetchsize is defined, applicationfetchsize will be use if defined
3414     */
3415    static public int getDefaultFetchSize() {
3416        return iDefaultFetchSize;
3417    }
3418
3419    // WebDocWf extension cursor-handling end
3420    /**
3421     * sets the default queryTimeout
3422     * @param iQueryTimeout integer representing the queryTimeout for this query
3423     */
3424    static public void setDefaultQueryTimeout(int iQueryTimeout) {
3425        iDefaultQueryTimeout = iQueryTimeout;
3426    }
3427
3428    /**
3429     * gets the default queryTimeout
3430     * @return the current queryTimeout;
3431     */
3432    static public int getDefaultQueryTimeout() {
3433        return iDefaultQueryTimeout;
3434    }
3435
3436    /**
3437     * reads the current queryTimeout for this query
3438     * @return the current queryTimeout;
3439     */
3440    public int getCurrentQueryTimeout() {
3441        return iCurrentQueryTimeout;
3442    }
3443
3444    /**
3445     * set the current queryTimeout for this query
3446     * @return the current queryTimeout;
3447     */
3448    public void setCurrentQueryTimeout(int iQueryTimeoutIn) {
3449        iCurrentQueryTimeout = iQueryTimeoutIn;
3450    }
3451
3452    public static synchronized String   getCountX(){
3453        if (countX<Long.MAX_VALUE){
3454            countX++;
3455        }else{
3456            countX=0;
3457        }
3458        return "cursorID"+Long.toString(countX);
3459    }
3460
3461
3462    public static long countX = 0;
3463
3464
3465    private boolean doSetFetchSize(DBConnection conn){
3466        boolean disableFetchSizeWithMaxRows;
3467        try {
3468            disableFetchSizeWithMaxRows = ((StandardLogicalDatabase) DODS
3469                                               .getDatabaseManager()
3470                                               .findLogicalDatabase(conn.getDatabaseName()))
3471                .getDisableFetchSizeWithMaxRows();
3472        } catch (DatabaseManagerException e) {
3473            DODS.getLogChannel().write(Logger.DEBUG,"Error unknown logical database. Using default value for 'DisableFetchSizeWithMaxRows' parameter");
3474            disableFetchSizeWithMaxRows = StandardLogicalDatabase.DEFAULT_DISABLE_FETCH_SIZE_WITH_MAX_ROWS;
3475        }
3476        if (disableFetchSizeWithMaxRows & (maxRows>0)){
3477            return false;
3478        }else{
3479            return true;
3480        }
3481    }
3482
3483
3484    private boolean doCursorName(DBConnection conn){
3485
3486        //if doSetFetchSize is false, do not perform check for cursor name
3487        if( !doSetFetchSize(conn) )
3488            return false;
3489
3490        boolean cursorName;
3491        try {
3492            cursorName =((StandardLogicalDatabase) DODS
3493                             .getDatabaseManager()
3494                             .findLogicalDatabase(conn.getDatabaseName()))
3495                .getUseCursorName();
3496        } catch (DatabaseManagerException e) {
3497            DODS.getLogChannel().write(Logger.DEBUG,"Error unknown logical database. Using default value for 'UseCursorName' parameter");
3498            cursorName = StandardLogicalDatabase.DEFAULT_USE_CURSOR_NAME;
3499        }
3500        return cursorName;
3501    }
3502
3503    //sinisa 15.06.2004
3504
3505    /**
3506     * set the current cursor type - overrides default value from dbVendorConf file.
3507     * @param resultSetType a result set type; one of ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
3508     * @param resultSetConcurrency a concurrency type; one of ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
3509     * @return the current queryTimeout;
3510     */
3511    public void setCursorType(int resultSetType, int resultSetConcurrency) {
3512
3513        iResultSetConcurrency = resultSetConcurrency;
3514        iResultSetType = resultSetType;
3515    }
3516    
3517} // end QueryBuilder class
3518
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags