1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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();
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
152
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
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
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
392 if (!Util.isNull(where)) {
393 buf.append("( ");
394 buf.append(where);
395 buf.append(" ) ");
396 }
397
398
399 if (!Util.isNull(sqlFilter)) {
400 if (!Util.isNull(where)) {
401 hatSchonFollowAfterWhere = true;
402 buf.append(" ");
403 buf.append(followAfterWhere);
404 buf.append(" ");
405 }
406
407 buf.append(" ( ");
408 buf.append(sqlFilter);
409 buf.append(" ) ");
410 }
411
412
413 if (!Util.isNull(s)) {
414 if (!Util.isNull(sqlFilter)) {
415 buf.append(" AND ");
416 } else if (!Util.isNull(where)) {
417 hatSchonFollowAfterWhere = true;
418 buf.append(" ");
419 buf.append(followAfterWhere);
420 buf.append(" ");
421 }
422
423
424 buf.append(" ( ");
425 buf.append(s);
426 buf.append(" ) ");
427 }
428 }
429
430 if (!Util.isNull(groupBy)) {
431 buf.append(" GROUP BY ");
432 buf.append(groupBy);
433 }
434
435 s = getQueryWhere(fvHaving, fvOrder, compareMode);
436
437 if (!Util.isNull(s)) {
438 if (!Util.isNull(groupBy)) {
439 buf.append(" HAVING ( ");
440 hatSchonHaving = true;
441 } else if (!hatSchonWhere) {
442 buf.append(" WHERE ( ");
443 } else {
444 if (!Util.isNull(where) && !hatSchonFollowAfterWhere && !Util.isNull(followAfterWhere)) {
445 buf.append(" ");
446 buf.append(followAfterWhere);
447 buf.append(" (");
448 } else {
449 buf.append(" AND (");
450 }
451 }
452
453 buf.append(s);
454 buf.append(")");
455 }
456
457 if (!Util.isNull(groupBy) && !Util.isNull(getHaving())) {
458 if (!hatSchonHaving) {
459 buf.append(" HAVING ");
460 } else {
461 buf.append(" AND ");
462 }
463
464 buf.append("(");
465 buf.append(getHaving());
466 buf.append(") ");
467 }
468
469 s = getQueryOrderBy(fvOrder);
470
471 if (s.length() > 0) {
472 buf.append(" ORDER BY ");
473 buf.append(s);
474 }
475
476 logCat.info("doSelect:" + buf.toString());
477
478 return buf.toString();
479 }
480
481
482 /***
483 * set whereClause, if defined in dbforms-config-xml (this method gets
484 * called from XML-digester)
485 *
486 * @param value sql where
487 */
488 public void setWhere(String value) {
489 this.where = value;
490 }
491
492
493 /***
494 * adds a Field-Object to this table and puts it into othere datastructure
495 * for further references (this method gets called from DbFormsConfig)
496 *
497 * @param field field to add
498 *
499 * @throws Exception DOCUMENT ME!
500 */
501 public void addSearchField(Field field) throws Exception {
502 if (field.getType() == 0) {
503 throw new Exception("Table " + getName() + " Field " + field.getName() + ": no type!");
504 }
505
506 field.setId(encodeFieldId(SEARCH_FIELD, searchfields.size()));
507 field.setTable(this);
508 searchfields.addElement(field);
509
510
511 searchNameHash.put(field.getName(), field);
512 }
513
514
515 /***
516 * DOCUMENT ME!
517 *
518 * @return DOCUMENT ME!
519 */
520 public boolean hasDistinctSet() {
521 return Util.getTrue(distinct);
522 }
523
524
525 /***
526 * return OrderWithPos OrderWithPos will be set if - groupBy is set -
527 * OrderWithPos is defined in dbforms-config.xml
528 *
529 * @return orderWithPos
530 */
531 public boolean needOrderWithPos() {
532 return !Util.isNull(groupBy) || Util.getTrue(orderWithPos);
533 }
534
535
536 /***
537 * situation: we have built a query (involving the getWhereEqualsClause()
538 * method) and now we want to prepare the statemtent - provide actual
539 * values for the the '?' placeholders
540 *
541 * @param fvEqual the array of FieldValue objects
542 * @param ps the PreparedStatement object
543 * @param curCol the current PreparedStatement column; points to a
544 * PreparedStatement xxx value
545 *
546 * @return the current column value
547 *
548 * @exception SQLException if any error occurs
549 */
550 public int populateWhereEqualsClause(FieldValue[] fvEqual,
551 PreparedStatement ps,
552 int curCol)
553 throws SQLException {
554 curCol = super.populateWhereEqualsClause(getFieldValueWhere(fvEqual), ps,
555 curCol);
556 curCol = super.populateWhereEqualsClause(getFieldValueHaving(fvEqual),
557 ps, curCol);
558
559 return curCol;
560 }
561
562
563 /***
564 * returns the part of the orderby-clause represented by this FieldValue
565 * object. FieldName [DESC] (ASC will be not printed because it is defined
566 * DEFAULT in SQL if there are RDBMS which do not tolerate this please let
567 * me know; then i'll change it) overloaded from Table if from is defind in
568 * dbforms-config.xml use this, else method from Table
569 *
570 * @param fvOrder order list
571 *
572 * @return sql order by
573 */
574 protected String getQueryOrderBy(FieldValue[] fvOrder) {
575 String res;
576 if (!needOrderWithPos()) {
577 res = super.getQueryOrderBy(fvOrder);
578 } else {
579 StringBuffer buf = new StringBuffer();
580
581 if (fvOrder != null) {
582 for (int i = 0; i < fvOrder.length; i++) {
583 buf.append(fvOrder[i].getField().getId() + 1);
584
585 if (fvOrder[i].getSortDirection() == Constants.ORDER_DESCENDING) {
586 buf.append(" DESC");
587 }
588
589 if (i < (fvOrder.length - 1)) {
590 buf.append(",");
591 }
592 }
593 }
594 res = buf.toString();
595 }
596 return res;
597 }
598
599
600 private FieldValue[] getFieldValueHaving(FieldValue[] fvEqual) {
601 Vector mode_having = new Vector();
602
603
604 if (fvEqual != null) {
605 for (int i = 0; i < fvEqual.length; i++) {
606 if (!checkFieldId(SEARCH_FIELD, fvEqual[i].getField().getId())) {
607 mode_having.add(fvEqual[i]);
608 }
609 }
610 }
611
612 FieldValue[] fvHaving = new FieldValue[mode_having.size()];
613
614 for (int i = 0; i < mode_having.size(); i++) {
615 fvHaving[i] = (FieldValue) mode_having.elementAt(i);
616 }
617
618 return fvHaving;
619 }
620
621
622 private FieldValue[] getFieldValueWhere(FieldValue[] fvEqual) {
623 Vector mode_where = new Vector();
624
625
626 if (fvEqual != null) {
627 for (int i = 0; i < fvEqual.length; i++) {
628 if (checkFieldId(SEARCH_FIELD, fvEqual[i].getField().getId())) {
629 mode_where.add(fvEqual[i]);
630 }
631 }
632 }
633
634 FieldValue[] fvWhere = new FieldValue[mode_where.size()];
635
636 for (int i = 0; i < mode_where.size(); i++) {
637 fvWhere[i] = (FieldValue) mode_where.elementAt(i);
638 }
639
640 return fvWhere;
641 }
642 }