Query


1   /*
2    * $Header: /cvsroot/jdbforms/dbforms/src/org/dbforms/config/Query.java,v 1.34 2005/02/19 21:26:27 hkollmann Exp $
3    * $Revision: 1.34 $
4    * $Date: 2005/02/19 21:26:27 $
5    *
6    * DbForms - a Rapid Application Development Framework
7    * Copyright (C) 2001 Joachim Peer <joepeer@excite.com>
8    *
9    * This library is free software; you can redistribute it and/or
10   * modify it under the terms of the GNU Lesser General Public
11   * License as published by the Free Software Foundation; either
12   * version 2.1 of the License, or (at your option) any later version.
13   *
14   * This library is distributed in the hope that it will be useful,
15   * but WITHOUT ANY WARRANTY; without even the implied warranty of
16   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
17   * Lesser General Public License for more details.
18   *
19   * You should have received a copy of the GNU Lesser General Public
20   * License along with this library; if not, write to the Free Software
21   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA
22   */
23  
24  package org.dbforms.config;
25  
26  import org.apache.commons.logging.Log;
27  import org.apache.commons.logging.LogFactory;
28  
29  import org.dbforms.util.Util;
30  
31  import java.sql.PreparedStatement  ;
32  import java.sql.SQLException  ;
33  
34  import java.util.Enumeration  ;
35  import java.util.Hashtable  ;
36  import java.util.Vector  ;
37  
38  
39  
40  /**
41   * <p>
42   * This class represents the query tag in dbforms-config.xml (dbforms config
43   * xml file)
44   * </p>
45   *
46   * <p>
47   * it's derived from the table class and overloads the necessary methods.
48   * </p>
49   *
50   * @author Henner Kollmann
51   */
52  public class Query extends Table {
53     /** log4j category */
54     private static Log       logCat           = LogFactory.getLog(Query.class);
55     private String             distinct         = "false";
56     private String             followAfterWhere = " AND ";
57     private String             from;
58     private String             groupBy;
59     private String             having;
60     private String             orderWithPos     = "false";
61     private String             where;
62     private Hashtable          searchNameHash  = new Hashtable  ();
63     private Vector             searchfields = new Vector  (); // the Field-Objects this table constists of
64  
65  
66     /**
67      * DOCUMENT ME!
68      *
69      * @param value the value to set
70      */
71     public void setDistinct(String   value) {
72        distinct = value;
73     }
74  
75  
76     /**
77      * returns the Field-Objet with specified id overloaded from Table Specials:
78      * 1. if fieldId is in range from search fields, get from search fields 2.
79      * if has fields try to find in fields 3. if not has fields try to find in
80      * parent table
81      *
82      * @param fieldId The id of the field to be returned
83      *
84      * @return the field
85      */
86     public Field getField(int fieldId) {
87        Field f = null;
88        if (checkFieldId(SEARCH_FIELD, fieldId)) {
89           f = (Field) searchfields.elementAt(decodeFieldId(SEARCH_FIELD, fieldId));
90        } else {
91           try {
92              f = super.getField(fieldId);
93           } catch (RuntimeException   e) {
94              f = null;
95           }
96  
97           if ((f == null) && !Util.isNull(from)) {
98              if (getConfig() != null) {
99                 Table t = getConfig().getTableByName(from);
100 
101                if (t != null) {
102                   f = t.getField(fieldId);
103                }
104             }
105          }
106       }
107 
108       return f;
109    }
110 
111 
112    /**
113     * returns the field-objects as specified by name (or null if no field with
114     * the specified name exists in this table) overloaded from Table Specials:
115     * 1. Try to find in fields 2. Try to find in search fields 3. Try to find
116     * in parent table
117     *
118     * @param name The name of the field
119     *
120     * @return the field
121     */
122    public Field getFieldByName(String   name) {
123       Field f = super.getFieldByName(name);
124 
125       if (f == null) {
126          f = (Field) searchNameHash.get(name);
127       }
128 
129       if ((f == null) && !Util.isNull(from)) {
130          if (getConfig() != null) {
131             Table t = getConfig().getTableByName(from);
132 
133             if (t != null) {
134                f = t.getFieldByName(name);
135             }
136          }
137       }
138 
139       return f;
140    }
141    
142 
143    /**
144     * returns the vector of fields this table constists of overloaded from
145     * Table Specials: if view has field defined, use this otherwise use fields
146     * from parent table
147     *
148     * @return the fields
149     */
150    public Vector   getFields() {
151       // In this case there are no fields listed. So use the fieldlist of the
152       // parent table!
153       Vector   f = super.getFields();
154 
155       if ((f == null) || ((f.isEmpty()) && !Util.isNull(from))) {
156          if (getConfig() != null) {
157             Table t = getConfig().getTableByName(from);
158 
159             if (t != null) {
160                f = t.getFields();
161             }
162          }
163       }
164 
165       return f;
166    }
167 
168 
169    /**
170     * Sets the followAfterWhere.
171     *
172     * @param followAfterWhere The followAfterWhere to set
173     */
174    public void setFollowAfterWhere(String   followAfterWhere) {
175       this.followAfterWhere = followAfterWhere;
176    }
177 
178 
179    /**
180     * set from, if defined in dbforms-config-xml (this method gets called from
181     * XML-digester)
182     *
183     * @param value sql from
184     */
185    public void setFrom(String   value) {
186       this.from = value;
187    }
188 
189 
190    /**
191     * set groupBy, if defined in dbforms-config-xml (this method gets called
192     * from XML-digester)
193     *
194     * @param value sql group by
195     */
196    public void setGroupBy(String   value) {
197       this.groupBy = value;
198    }
199 
200 
201    /**
202     * DOCUMENT ME!
203     *
204     * @param value the value to set
205     */
206    public void setHaving(String   value) {
207       having = value;
208    }
209 
210 
211    /**
212     * DOCUMENT ME!
213     *
214     * @return DOCUMENT ME!
215     */
216    public String   getHaving() {
217       return having;
218    }
219 
220 
221    /**
222     * returns the key of this table (consisting of Field-Objects representing
223     * key-fields) overloaded from Table Specials: if key of view is not
224     * defined (if view has not defined fields) use keys from parent table
225     *
226     * @return the keys
227     */
228    public Vector   getKey() {
229       Vector   v = super.getKey();
230 
231       if (((v == null) || v.isEmpty()) && !Util.isNull(from)) {
232          if (getConfig() != null) {
233             Table t = getConfig().getTableByName(from);
234 
235             if (t != null) {
236                v = t.getKey();
237             }
238          }
239       }
240 
241       return v;
242    }
243 
244 
245    /**
246     * DOCUMENT ME!
247     *
248     * @param core starting tag for the fields
249     *
250     * @return the hash table. Hashtables are build from fields + searchfields!
251     */
252    public Hashtable   getNamesHashtable(String   core) {
253       Hashtable     result = super.getNamesHashtable(core);
254       Enumeration   e = getSearchFields()
255                             .elements();
256 
257       while (e.hasMoreElements()) {
258          Field f = (Field) e.nextElement();
259          result.put(f.getName(), f.getFieldName(core));
260 
261          // in PHP slang we would call that an "associative array" :=)
262       }
263 
264       return result;
265    }
266 
267 
268    /**
269     * set OrderWithPos, if defined in dbforms-config-xml (this method gets
270     * called from XML-digester) if set the ORDER BY statment will use position
271     * number instead of field names in ORDER BY
272     *
273     * @param value sets orderWithPos
274     */
275    public void setOrderWithPos(String   value) {
276       this.orderWithPos = value;
277    }
278 
279 
280    /**
281     * returns the from part of a query. overloaded from Table if from is defind
282     * in dbforms-config.xml use this, else method from Table
283     *
284     * @return sql from
285     */
286    public String   getQueryFrom() {
287       String   res;
288       if (!Util.isNull(from)) {
289          res = from;
290       } else {
291          res = super.getQueryFrom();
292       }
293       return res;
294    }
295 
296 
297    /**
298     * returns the select part of a query overloaded from Table extends
299     * fieldnames with getting expression
300     *
301     * @param fieldsToSelect fieldlist
302     *
303     * @return sql select part
304     */
305    public String   getQuerySelect(Vector   fieldsToSelect) {
306       if (fieldsToSelect != null) {
307          StringBuffer   buf                = new StringBuffer  ();
308          int          fieldsToSelectSize = fieldsToSelect.size();
309 
310          for (int i = 0; i < fieldsToSelectSize; i++) {
311             Field f = (Field) fieldsToSelect.elementAt(i);
312 
313             // if field has an expression use it!
314             if (!Util.isNull(f.getExpression())) {
315                buf.append(f.getExpression());
316                buf.append(" ");
317             }
318 
319             buf.append(f.getName());
320             buf.append(", ");
321          }
322 
323          if (buf.length() > 1) {
324             buf.deleteCharAt(buf.length() - 2);
325          }
326 
327          return buf.toString();
328       }
329 
330       return "*";
331    }
332 
333 
334    /**
335     * Returns the search fields search fields are fields in the query which are
336     * only used in the where part, not in the select part
337     *
338     * @return search field list
339     */
340    public Vector   getSearchFields() {
341       return searchfields;
342    }
343 
344 
345    /**
346     * Prepares the Querystring for the select statement Order of parts: 1.
347     * where condition from config (no params!) 2. sqlFilter (fild in
348     * getDoSelectResultSet!) 3. where condition generated from search fields
349     * (fild in overloaded populateWhereEqualsClause) 4. where condition
350     * generated from having / ordering fields (fild in overloaded
351     * populateWhereEqualsClause) Retrieving the parameters in
352     * getDoSelectResultSet() must match this order!
353     *
354     * @param fieldsToSelect vector of fields to be selected
355     * @param fvEqual fieldValues representing values we are looking for
356     * @param fvOrder fieldValues representing needs for order clauses
357     * @param sqlFilter sql condition to and with the where clause
358     * @param compareMode compare mode value for generating the order clause
359     *
360     * @return the query string
361     */
362    public String   getSelectQuery(Vector         fieldsToSelect,
363                                 FieldValue[] fvEqual,
364                                 FieldValue[] fvOrder,
365                                 String         sqlFilter,
366                                 int          compareMode) {
367       StringBuffer   buf                      = new StringBuffer  ();
368       String         s;
369       boolean      hatSchonWhere            = false;
370       boolean      hatSchonFollowAfterWhere = false;
371       boolean      hatSchonHaving           = false;
372       FieldValue[] fvHaving                 = getFieldValueHaving(fvEqual);
373       FieldValue[] fvWhere                  = getFieldValueWhere(fvEqual);
374 
375       buf.append("SELECT ");
376 
377       if (hasDistinctSet()) {
378          buf.append(" DISTINCT ");
379       }
380 
381       buf.append(getQuerySelect(fieldsToSelect));
382       buf.append(" FROM ");
383       buf.append(getQueryFrom());
384 
385       s = getQueryWhere(fvWhere, null, 0);
386 
387       if (!Util.isNull(s) || !Util.isNull(where) || !Util.isNull(sqlFilter)) {
388          hatSchonWhere = true;
389          buf.append(" WHERE ");
390 
391          // where condition part from config
392          if (!Util.isNull(where)) {
393             buf.append("( ");
394             buf.append(where);
395             buf.append(" ) ");
396          }
397 
398          // where condition part from DbFormTag's sqlFilter attribute
399          if (!Util.isNull(sqlFilter)) {
400             if (!Util.isNull(where)) {
401                hatSchonFollowAfterWhere = true;
402                buf.append(followAfterWhere);
403             }
404 
405             buf.append(" ( ");
406             buf.append(sqlFilter);
407             buf.append(" ) ");
408          }
409 
410          // where condition part generated from searching / ordering
411          if (!Util.isNull(s)) {
412             if (!Util.isNull(sqlFilter)) {
413                buf.append(" AND ");
414             } else if (!Util.isNull(where)) {
415                hatSchonFollowAfterWhere = true;
416                buf.append(followAfterWhere);
417             }
418 
419             // parents are inserted in getQueryWhere method
420             buf.append(" ( ");
421             buf.append(s);
422             buf.append(" ) ");
423          }
424       }
425 
426       if (!Util.isNull(groupBy)) {
427          buf.append(" GROUP BY ");
428          buf.append(groupBy);
429       }
430 
431       s = getQueryWhere(fvHaving, fvOrder, compareMode);
432 
433       if (!Util.isNull(s)) {
434          if (!Util.isNull(groupBy)) {
435             buf.append(" HAVING ( ");
436             hatSchonHaving = true;
437          } else if (!hatSchonWhere) {
438             buf.append(" WHERE ( ");
439          } else {
440             if (!Util.isNull(where) && !hatSchonFollowAfterWhere) {
441                buf.append(" ");
442                buf.append(followAfterWhere);
443                buf.append(" (");
444             } else {
445                buf.append(" AND (");
446             }
447          }
448 
449          buf.append(s);
450          buf.append(")");
451       }
452 
453       if (!Util.isNull(groupBy) && !Util.isNull(getHaving())) {
454          if (!hatSchonHaving) {
455             buf.append(" HAVING ");
456          } else {
457             buf.append(" AND ");
458          }
459 
460          buf.append("(");
461          buf.append(getHaving());
462          buf.append(") ");
463       }
464 
465       s = getQueryOrderBy(fvOrder);
466 
467       if (s.length() > 0) {
468          buf.append(" ORDER BY ");
469          buf.append(s);
470       }
471 
472       logCat.info("doSelect:" + buf.toString());
473 
474       return buf.toString();
475    }
476 
477 
478    /**
479     * set whereClause, if defined in dbforms-config-xml (this method gets
480     * called from XML-digester)
481     *
482     * @param value sql where
483     */
484    public void setWhere(String   value) {
485       this.where = value;
486    }
487 
488 
489    /**
490     * adds a Field-Object to this table and puts it into othere datastructure
491     * for further references (this method gets called from DbFormsConfig)
492     *
493     * @param field field to add
494     *
495     * @throws Exception DOCUMENT ME!
496     */
497    public void addSearchField(Field field) throws Exception   {
498       if (field.getType() == 0) {
499          throw new Exception  ("no type!");
500       }
501 
502       field.setId(encodeFieldId(SEARCH_FIELD, searchfields.size()));
503       field.setTable(this);
504       searchfields.addElement(field);
505 
506       // for quicker lookup by name:
507       searchNameHash.put(field.getName(), field);
508    }
509 
510 
511    /**
512     * DOCUMENT ME!
513     *
514     * @return DOCUMENT ME!
515     */
516    public boolean hasDistinctSet() {
517       return Util.getTrue(distinct);
518    }
519 
520 
521    /**
522     * return OrderWithPos OrderWithPos will be set if - groupBy is set -
523     * OrderWithPos is defined in dbforms-config.xml
524     *
525     * @return orderWithPos
526     */
527    public boolean needOrderWithPos() {
528       return !Util.isNull(groupBy) || Util.getTrue(orderWithPos);
529    }
530 
531 
532    /**
533     * situation: we have built a query (involving the getWhereEqualsClause()
534     * method) and now we want to prepare the statemtent - provide actual
535     * values for the the '?' placeholders
536     *
537     * @param fvEqual the array of FieldValue objects
538     * @param ps the PreparedStatement object
539     * @param curCol the current PreparedStatement column; points to a
540     *        PreparedStatement xxx value
541     *
542     * @return the current column value
543     *
544     * @exception SQLException if any error occurs
545     */
546    public int populateWhereEqualsClause(FieldValue[]      fvEqual,
547                                         PreparedStatement   ps,
548                                         int               curCol)
549                                  throws SQLException   {
550       curCol = super.populateWhereEqualsClause(getFieldValueWhere(fvEqual), ps,
551                                                curCol);
552       curCol = super.populateWhereEqualsClause(getFieldValueHaving(fvEqual),
553                                                ps, curCol);
554 
555       return curCol;
556    }
557 
558 
559    /**
560     * returns the part of the orderby-clause represented by this FieldValue
561     * object. FieldName [DESC] (ASC will be not printed because it is defined
562     * DEFAULT in SQL if there are RDBMS which do not tolerate this please let
563     * me know; then i'll change it) overloaded from Table if from is defind in
564     * dbforms-config.xml use this, else method from Table
565     *
566     * @param fvOrder order list
567     *
568     * @return sql order by
569     */
570    protected String   getQueryOrderBy(FieldValue[] fvOrder) {
571       String   res;
572       if (!needOrderWithPos()) {
573          res = super.getQueryOrderBy(fvOrder);
574       } else {
575          StringBuffer   buf = new StringBuffer  ();
576 
577          if (fvOrder != null) {
578             for (int i = 0; i < fvOrder.length; i++) {
579                buf.append(fvOrder[i].getField().getId() + 1);
580 
581                if (fvOrder[i].getSortDirection() == Constants.ORDER_DESCENDING) {
582                   buf.append(" DESC");
583                }
584 
585                if (i < (fvOrder.length - 1)) {
586                   buf.append(",");
587                }
588             }
589          }
590          res =  buf.toString();
591       }
592       return res;
593    }
594 
595 
596    private FieldValue[] getFieldValueHaving(FieldValue[] fvEqual) {
597       Vector   mode_having = new Vector  ();
598 
599       // Split fields in where and having part
600       if (fvEqual != null) {
601          for (int i = 0; i < fvEqual.length; i++) {
602             if (!checkFieldId(SEARCH_FIELD, fvEqual[i].getField().getId())) {
603                mode_having.add(fvEqual[i]);
604             }
605          }
606       }
607 
608       FieldValue[] fvHaving = new FieldValue[mode_having.size()];
609 
610       for (int i = 0; i < mode_having.size(); i++) {
611          fvHaving[i] = (FieldValue) mode_having.elementAt(i);
612       }
613 
614       return fvHaving;
615    }
616 
617 
618    private FieldValue[] getFieldValueWhere(FieldValue[] fvEqual) {
619       Vector   mode_where = new Vector  ();
620 
621       // Split fields in where and having part
622       if (fvEqual != null) {
623          for (int i = 0; i < fvEqual.length; i++) {
624             if (checkFieldId(SEARCH_FIELD, fvEqual[i].getField().getId())) {
625                mode_where.add(fvEqual[i]);
626             }
627          }
628       }
629 
630       FieldValue[] fvWhere = new FieldValue[mode_where.size()];
631 
632       for (int i = 0; i < mode_where.size(); i++) {
633          fvWhere[i] = (FieldValue) mode_where.elementAt(i);
634       }
635 
636       return fvWhere;
637    }
638 }
639
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags