KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > mmbase > storage > search > implementation > database > InformixSqlHandler


1 /*
2
3 This software is OSI Certified Open Source Software.
4 OSI Certified is a certification mark of the Open Source Initiative.
5
6 The license (Mozilla version 1.0) can be read at the MMBase site.
7 See http://www.MMBase.org/license
8
9 */

10 package org.mmbase.storage.search.implementation.database;
11
12 import java.util.*;
13 import java.sql.*;
14 import java.lang.reflect.Method JavaDoc;
15
16 import org.mmbase.storage.search.*;
17 import org.mmbase.util.logging.Logger;
18 import org.mmbase.util.logging.Logging;
19
20 import org.mmbase.module.database.MultiConnection;
21
22 /**
23  * The Informix query handler, implements {@link
24  * org.mmbase.storage.search.implementation.database.SqlHandler SqlHandler} for standard
25  * Informix functionality.
26  * <br />
27  * Derived from {@link BasicSqlHandler BasicSqlHandler}, overrides
28  * <ul>
29  * <li>{@link #toSql toSql()}, implements {@link
30  * org.mmbase.storage.search.SearchQueryHandler#FEATURE_MAX_NUMBER
31  * FEATURE_MAX_NUMBER}, by adding a construct like "<code>SELECT FIRST 20</code>"
32  * in front of the body, when appropriate.
33  * <li>{@link #getSupportLevel(int,SearchQuery) getSupportLevel(int,SearchQuery)},
34  * returns {@link
35  * org.mmbase.storage.search.SearchQueryHandler#SUPPORT_OPTIMAL
36  * SUPPORT_OPTIMAL} for this feature, delegates to the superclass for
37  * other features.
38  * </ul>
39  *
40  * @author Rob van Maris
41  * @version $Id: InformixSqlHandler.java,v 1.25 2006/07/03 11:59:16 johannes Exp $
42  * @since MMBase-1.7
43  */

44 public class InformixSqlHandler extends BasicSqlHandler implements SqlHandler {
45
46     /**
47      * Logger instance.
48      */

49     private static Logger log
50             = Logging.getLoggerInstance(InformixSqlHandler.class.getName());
51
52     /**
53      * Constructor.
54      */

55     public InformixSqlHandler() {
56         super();
57     }
58
59     /**
60      * Dertermine if the given query will turn out to be a UNION query
61      *
62      * @param query
63      * @return <code>true</code> if the given query will contain a UNION
64      */

65     private boolean isUnionQuery(SearchQuery query) {
66         Iterator iSteps = query.getSteps().iterator();
67         while (iSteps.hasNext()) {
68             Step step = (Step) iSteps.next();
69             if (step instanceof RelationStep) {
70                 RelationStep relationStep = (RelationStep) step;
71                 // If the query contains RelationSteps that are bi-directional
72
// then the query will turn out to be a union query.
73
if (relationStep.getDirectionality() == RelationStep.DIRECTIONS_BOTH) {
74                     return true;
75                 }
76             }
77         }
78         return false;
79     }
80
81     // javadoc is inherited
82
public int getSupportLevel(int feature, SearchQuery query) throws SearchQueryException {
83         int result;
84         switch (feature) {
85             case SearchQueryHandler.FEATURE_MAX_NUMBER:
86
87                 if (isUnionQuery(query)) {
88                     result = SearchQueryHandler.SUPPORT_NONE;
89                 } else {
90                     result = SearchQueryHandler.SUPPORT_OPTIMAL;
91                 }
92                 break;
93
94             default:
95                 result = super.getSupportLevel(feature, query);
96         }
97         return result;
98     }
99
100     // javadoc is inherited
101
public String JavaDoc toSql(SearchQuery query, SqlHandler firstInChain) throws SearchQueryException {
102         // XXX should table and field aliases be tested for uniqueness?
103

104         // Test for at least 1 step and 1 field.
105
if (query.getSteps().isEmpty()) {
106             throw new IllegalStateException JavaDoc("Searchquery has no step (at least 1 step is required).");
107         }
108         if (query.getFields().isEmpty()) {
109             throw new IllegalStateException JavaDoc("Searchquery has no field (at least 1 field is required).");
110         }
111
112         // Test offset set to default (= 0).
113
if (query.getOffset() != SearchQuery.DEFAULT_OFFSET) {
114             throw new UnsupportedOperationException JavaDoc("Value of offset other than "
115                     + SearchQuery.DEFAULT_OFFSET + " not supported.");
116         }
117
118         // SELECT
119
StringBuffer JavaDoc sbQuery = new StringBuffer JavaDoc("SELECT ");
120
121         if (log.isDebugEnabled()) {
122             log.trace("query:" + query.toString());
123         }
124
125         if (!isUnionQuery(query)) {
126             /*
127                Optimizer directive {+ORDERED} may not be used when using UNIONS
128             */

129             if (query.getSteps().size() > 3) {
130                 sbQuery.append("{+ORDERED} ");
131             }
132
133             /*
134                FIRST may not be used when using UNIONS
135             */

136             if (query.getMaxNumber() != -1) {
137                 sbQuery.append("FIRST ").
138                         append(query.getMaxNumber()).
139                         append(" ");
140             }
141         }
142
143         // DISTINCT
144
if (query.isDistinct() && !query.isAggregating()) {
145             sbQuery.append("DISTINCT ");
146         }
147
148         firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain);
149
150         return sbQuery.toString();
151     }
152
153     // javadoc is inherited
154
public void appendQueryBodyToSql(StringBuffer JavaDoc sb, SearchQuery query, SqlHandler firstInChain)
155             throws SearchQueryException {
156
157         // Buffer expressions for included nodes, like
158
// "x.number in (...)".
159
StringBuffer JavaDoc sbNodes = new StringBuffer JavaDoc();
160
161         // Buffer expressions for relations, like
162
// "x.number = r.snumber AND y.number = r.dnumber".
163
StringBuffer JavaDoc sbRelations = new StringBuffer JavaDoc();
164
165         // Buffer fields to group by, like
166
// "alias1, alias2, ..."
167
StringBuffer JavaDoc sbGroups = new StringBuffer JavaDoc();
168
169         boolean multipleSteps = query.getSteps().size() > 1;
170
171         // Fields expression
172
List lFields = new ArrayList();
173         lFields.addAll(query.getFields());
174
175         // When 'distinct', make sure all fields used for sorting are
176
// included in the query.
177
// Some databases require this (including PostgreSQL).
178
// By fixing this here, the result of the query remains consistent
179
// across databases, while requiring no modification in the calling
180
// code.
181
if (query.isDistinct()) {
182             Iterator iSortOrder = query.getSortOrders().iterator();
183             while (iSortOrder.hasNext()) {
184                 SortOrder sortOrder = (SortOrder) iSortOrder.next();
185                 StepField field = sortOrder.getField();
186                 if (lFields.indexOf(field) == -1) {
187                     lFields.add(field);
188                 }
189             }
190         }
191
192         boolean storesAsFile = org.mmbase.module.core.MMBase.getMMBase().getStorageManagerFactory().hasOption(org.mmbase.storage.implementation.database.Attributes.STORES_BINARY_AS_FILE);
193         Iterator iFields = lFields.iterator();
194         boolean appended = false;
195         while (iFields.hasNext()) {
196             StepField field = (StepField) iFields.next();
197             if (field.getType() == org.mmbase.bridge.Field.TYPE_BINARY) continue;
198             if (appended) {
199                 sb.append(',');
200             }
201             appended = true;
202
203             // Fieldname prefixed by table alias.
204
Step step = field.getStep();
205             String JavaDoc fieldName = field.getFieldName();
206             String JavaDoc fieldAlias = field.getAlias();
207
208             if (field instanceof AggregatedField) {
209                 int aggregationType = ((AggregatedField) field).getAggregationType();
210                 if (aggregationType == AggregatedField.AGGREGATION_TYPE_GROUP_BY) {
211
212                     // Group by.
213
appendField(sb, step, fieldName, multipleSteps);
214
215                     // Append to "GROUP BY"-buffer.
216
if (sbGroups.length() > 0) {
217                         sbGroups.append(",");
218                     }
219                     if (fieldAlias != null) {
220                         sbGroups.append(getAllowedValue(fieldAlias));
221                     } else {
222                         appendField(sbGroups, step,
223                                 fieldName, multipleSteps);
224                     }
225                 } else {
226
227                     // Aggregate function.
228
switch (aggregationType) {
229                         case AggregatedField.AGGREGATION_TYPE_COUNT:
230                             sb.append("COUNT(");
231                             break;
232
233                         case AggregatedField.AGGREGATION_TYPE_COUNT_DISTINCT:
234                             sb.append("COUNT(DISTINCT ");
235                             break;
236
237                         case AggregatedField.AGGREGATION_TYPE_MIN:
238                             sb.append("MIN(");
239                             break;
240
241                         case AggregatedField.AGGREGATION_TYPE_MAX:
242                             sb.append("MAX(");
243                             break;
244
245                         default:
246                             throw new IllegalStateException JavaDoc("Invalid aggregationType value: " + aggregationType);
247                     }
248                     appendField(sb, step, fieldName, multipleSteps);
249                     sb.append(")");
250                 }
251
252             } else {
253
254                 // Non-aggregate field.
255
appendField(sb, step, fieldName, multipleSteps);
256             }
257
258             // Field alias.
259
if (fieldAlias != null) {
260                 sb.append(" AS ")
261                         .append(getAllowedValue(fieldAlias));
262             }
263
264         }
265
266         if (log.isDebugEnabled()) {
267             log.trace("Base field part of query : " + sb);
268         }
269
270         // vector to save OR-Elements (Searchdir=BOTH) for migration to UNION-query
271
List orElements = new ArrayList();
272
273         // save AND-Elements from relationString for migration to UNION-query
274
StringBuffer JavaDoc andElements = new StringBuffer JavaDoc();
275
276         // Tables
277
sb.append(" FROM ");
278         Iterator iSteps = query.getSteps().iterator();
279         while (iSteps.hasNext()) {
280             Step step = (Step) iSteps.next();
281             String JavaDoc tableName = step.getTableName();
282             String JavaDoc tableAlias = step.getAlias();
283
284             // Tablename, prefixed with basename and underscore
285
sb.append(org.mmbase.module.core.MMBase.getMMBase().getBaseName()).
286                     append("_").
287                     //Currently no replacement strategy is implemented for
288
//invalid tablenames.
289
//This would be useful, but requires modification to
290
//the insert/update/delete code as well.
291
//append(getAllowedValue(tableName));
292
append(tableName);
293
294             // Table alias (tablename when table alias not set).
295
if (tableAlias != null) {
296                 sb.append(" ").
297                         append(getAllowedValue(tableAlias));
298             } else {
299                 sb.append(" ").
300                         append(getAllowedValue(tableName));
301             }
302
303             if (iSteps.hasNext()) {
304                 sb.append(",");
305             }
306
307             // Included nodes.
308
SortedSet nodes = step.getNodes();
309             if (nodes.size() > 0) {
310                 if (sbNodes.length() > 0) {
311                     sbNodes.append(" AND ");
312                 }
313                 appendField(sbNodes, step, "number", multipleSteps);
314                 sbNodes.append(" IN (");
315                 Iterator iNodes = nodes.iterator();
316                 while (iNodes.hasNext()) {
317                     Integer JavaDoc node = (Integer JavaDoc) iNodes.next();
318                     sbNodes.append(node);
319                     if (iNodes.hasNext()) {
320                         sbNodes.append(",");
321                     }
322                 }
323                 sbNodes.append(")");
324             }
325             if (log.isDebugEnabled()) {
326                 log.trace("Node constraint string : " + sbNodes);
327             }
328
329             // Relation steps.
330
if (step instanceof RelationStep) {
331                 RelationStep relationStep = (RelationStep) step;
332                 Step previousStep = relationStep.getPrevious();
333                 Step nextStep = relationStep.getNext();
334                 if (sbRelations.length() > 0) {
335                     sbRelations.append(" AND ");
336                 }
337                 switch (relationStep.getDirectionality()) {
338                     case RelationStep.DIRECTIONS_SOURCE:
339                         sbRelations.append("(");
340                         appendField(sbRelations, previousStep, "number", multipleSteps);
341                         sbRelations.append("=");
342                         appendField(sbRelations, relationStep, "dnumber", multipleSteps);
343                         sbRelations.append(" AND ");
344                         appendField(sbRelations, nextStep, "number", multipleSteps);
345                         sbRelations.append("=");
346                         appendField(sbRelations, relationStep, "snumber", multipleSteps);
347                         if (relationStep.getCheckedDirectionality()) {
348                             sbRelations.append(" AND ");
349                             appendField(sbRelations, relationStep, "dir", multipleSteps);
350                             sbRelations.append("<>1");
351                         }
352
353                         // Gather the "And"-elements
354
if (andElements.length() > 0) {
355                             andElements.append(" AND ");
356                         }
357                         appendField(andElements, previousStep, "number", multipleSteps);
358                         andElements.append("=");
359                         appendField(andElements, relationStep, "dnumber", multipleSteps);
360                         andElements.append(" AND ");
361                         appendField(andElements, nextStep, "number", multipleSteps);
362                         andElements.append("=");
363                         appendField(andElements, relationStep, "snumber", multipleSteps);
364                         if (relationStep.getCheckedDirectionality()) {
365                             andElements.append(" AND ");
366                             appendField(andElements, relationStep, "dir", multipleSteps);
367                             andElements.append("<>1");
368                         }
369
370                         break;
371
372                     case RelationStep.DIRECTIONS_DESTINATION:
373                         sbRelations.append("(");
374                         appendField(sbRelations, previousStep, "number", multipleSteps);
375                         sbRelations.append("=");
376                         appendField(sbRelations, relationStep, "snumber", multipleSteps);
377                         sbRelations.append(" AND ");
378                         appendField(sbRelations, nextStep, "number", multipleSteps);
379                         sbRelations.append("=");
380                         appendField(sbRelations, relationStep, "dnumber", multipleSteps);
381
382                         // Gather the "And"-elements
383
if (andElements.length() > 0) {
384                             andElements.append(" AND ");
385                         }
386
387                         appendField(andElements, previousStep, "number", multipleSteps);
388                         andElements.append("=");
389                         appendField(andElements, relationStep, "snumber", multipleSteps);
390                         andElements.append(" AND ");
391                         appendField(andElements, nextStep, "number", multipleSteps);
392                         andElements.append("=");
393                         appendField(andElements, relationStep, "dnumber", multipleSteps);
394
395                         break;
396
397                     case RelationStep.DIRECTIONS_BOTH:
398
399                         if (relationStep.getRole() != null) {
400                             sbRelations.append("(((");
401                         } else {
402                             sbRelations.append("((");
403                         }
404
405                         appendField(sbRelations, previousStep, "number", multipleSteps);
406                         sbRelations.append("=");
407                         appendField(sbRelations, relationStep, "dnumber", multipleSteps);
408                         sbRelations.append(" AND ");
409                         appendField(sbRelations, nextStep, "number", multipleSteps);
410                         sbRelations.append("=");
411                         appendField(sbRelations, relationStep, "snumber", multipleSteps);
412                         if (relationStep.getCheckedDirectionality()) {
413                             sbRelations.append(" AND ");
414                             appendField(sbRelations, relationStep, "dir", multipleSteps);
415                             sbRelations.append("<>1");
416                         }
417                         sbRelations.append(") OR (");
418
419                         appendField(sbRelations, previousStep, "number", multipleSteps);
420                         sbRelations.append("=");
421                         appendField(sbRelations, relationStep, "snumber", multipleSteps);
422                         sbRelations.append(" AND ");
423                         appendField(sbRelations, nextStep, "number", multipleSteps);
424                         sbRelations.append("=");
425                         appendField(sbRelations, relationStep, "dnumber", multipleSteps);
426                         if (relationStep.getRole() != null) {
427                             sbRelations.append("))");
428                         } else {
429                             sbRelations.append(")");
430                         }
431
432                         // Gather al the OR- elements for the union
433
// start of First element
434
StringBuffer JavaDoc orElement = new StringBuffer JavaDoc();
435                         orElement.append("(");
436
437                         appendField(orElement, previousStep, "number", multipleSteps);
438                         orElement.append("=");
439                         appendField(orElement, relationStep, "dnumber", multipleSteps);
440                         orElement.append(" AND ");
441                         appendField(orElement, nextStep, "number", multipleSteps);
442                         orElement.append("=");
443                         appendField(orElement, relationStep, "snumber", multipleSteps);
444                         if (relationStep.getCheckedDirectionality()) {
445                             orElement.append(" AND ");
446                             appendField(orElement, relationStep, "dir", multipleSteps);
447                             orElement.append("<>1");
448                         }
449
450                         if (relationStep.getRole() != null) {
451                             // role is given lets add the rnumber
452
orElement.append(" AND ");
453                             appendField(orElement, relationStep, "rnumber", multipleSteps);
454                             orElement.append("=").
455                                     append(relationStep.getRole());
456                         }
457
458                         orElement.append(")");
459                         orElements.add(orElement);
460
461                         // Start of second element
462
orElement = new StringBuffer JavaDoc();
463                         orElement.append("(");
464
465                         appendField(orElement, previousStep, "number", multipleSteps);
466                         orElement.append("=");
467                         appendField(orElement, relationStep, "snumber", multipleSteps);
468                         orElement.append(" AND ");
469                         appendField(orElement, nextStep, "number", multipleSteps);
470                         orElement.append("=");
471                         appendField(orElement, relationStep, "dnumber", multipleSteps);
472
473                         if (relationStep.getRole() != null) {
474                             // ooops role is given lets add the rnumber
475
orElement.append(" AND ");
476                             appendField(orElement, relationStep, "rnumber", multipleSteps);
477                             orElement.append("=").
478                                     append(relationStep.getRole());
479                         }
480
481                         orElement.append(")");
482                         orElements.add(orElement);
483
484                         // end of hacking
485
break;
486
487                     case RelationStep.DIRECTIONS_ALL:
488                         throw new UnsupportedOperationException JavaDoc("Directionality 'ALL' is not (yet) supported");
489
490                     case RelationStep.DIRECTIONS_EITHER:
491                         throw new UnsupportedOperationException JavaDoc("Directionality 'EITHER' is not (yet) supported");
492
493                     default: // Invalid directionality value.
494
throw new IllegalStateException JavaDoc("Invalid directionality value: " + relationStep.getDirectionality());
495                 }
496                 if (relationStep.getRole() != null) {
497                     sbRelations.append(" AND ");
498                     appendField(sbRelations, relationStep, "rnumber", multipleSteps);
499                     sbRelations.append("=").
500                             append(relationStep.getRole());
501                 }
502                 sbRelations.append(")");
503             }
504         }
505
506         if (log.isDebugEnabled()) {
507             log.trace("Relation string : " + sbRelations);
508         }
509
510         // Constraints
511
StringBuffer JavaDoc sbConstraints = new StringBuffer JavaDoc();
512         sbConstraints.append(sbNodes); // Constraints by included nodes.
513
if (sbConstraints.length() > 0 && sbRelations.length() > 0) {
514             sbConstraints.append(" AND ");
515         }
516
517         /*
518            If there are relational Constraints in both directions
519            we need to
520            1) figure out the contraints that we use in each of the UNION-constraints
521            2) combine possible different OR-Elements
522            3) create the query with all the UNIONS
523            4) add the GROUP BY Clause
524            5) add the ORDER BY Clause
525         */

526         StringBuffer JavaDoc unionRelationConstraints = new StringBuffer JavaDoc();
527         if (isUnionQuery(query)) {
528             // 1)
529
// we first need to figure out the additional constraints in
530
// order to add them to the relational constraints
531
StringBuffer JavaDoc unionConstraints = new StringBuffer JavaDoc();
532             if (query.getConstraint() != null) {
533                 Constraint constraint = query.getConstraint();
534                 if (sbConstraints.length() > 0) {
535                     // Combine constraints.
536
// if sbConstraints allready ends with " AND " before adding " AND "
537
log.info("sbConstraints:" + sbConstraints);
538                     log.info("sbConstraints.length:" + sbConstraints.length());
539
540                     // have to check if the constraint end with "AND ", sometimes it's not :-(
541
if (sbConstraints.length() >= 4) {
542                         if (!sbConstraints.substring(sbConstraints.length() - 4, sbConstraints.length()).equals("AND ")) {
543                             unionConstraints.append(" AND ");
544                         }
545                     }
546
547                     if (constraint instanceof CompositeConstraint) {
548                         appendCompositeConstraintToSql(unionConstraints, (CompositeConstraint) constraint,
549                                 query, false, true, firstInChain);
550                     } else {
551                         firstInChain.appendConstraintToSql(unionConstraints, constraint, query,
552                                 false, true);
553                     }
554                 } else {
555                     // Only regular constraints.
556
if (constraint instanceof CompositeConstraint) {
557                         appendCompositeConstraintToSql(unionConstraints, (CompositeConstraint) constraint,
558                                 query, false, false, firstInChain);
559                     } else {
560                         firstInChain.appendConstraintToSql(unionConstraints, constraint, query,
561                                 false, false);
562                     }
563                 }
564             }
565
566             if (log.isDebugEnabled()) {
567                 log.trace("Union constraint : " + unionConstraints);
568             }
569
570             /*
571                2) Combine the OR-Elements
572                Nested Looping through the OR-Elements to make unique combinations
573             */

574
575             if (unionConstraints.length() > 0) {
576                 unionConstraints.insert(0, " AND ");
577             }
578
579             List combinedElements = new ArrayList();
580             boolean skipCombination = false;
581             for (int counter = 0; counter < orElements.size(); counter++) {
582                 for (int counter2 = counter; counter2 < orElements.size(); counter2++) {
583                     // determine if the combination is valid and may be added to the relational constraints
584
if (counter%2==0 && counter2-counter==1) {
585                        // this combination may not be added
586
skipCombination=true;
587                     } else {
588                        skipCombination=false;
589                     }
590                     // Don't combine with same element. That doesn't make sense
591
// If there are just two relation-constraint-elements, we don't need to combine
592
if (counter != counter2 && orElements.size() > 2) {
593                         // also add the additinal constraints
594
if (!skipCombination) {
595                             combinedElements.add(orElements.get(counter) + " AND " + orElements.get(counter2) + unionConstraints);
596                         }
597                     } else {
598                         // If there's just one OR (two OR-elements), add the elements seperately
599
// also add the additinal constraints
600
if (counter == counter2 && orElements.size() <= 2) {
601                             combinedElements.add(orElements.get(counter) + "" + unionConstraints);
602                         }
603                     }
604                 }
605             }
606
607             /*
608                3) Here we're going to create a new BaseQuery,
609                   we need that in order to re-use that for every union
610             */

611             Iterator e = combinedElements.iterator();
612             String JavaDoc combinedElement = "";
613             int teller = 1;
614
615             // New base-query. We need to reuse that for every union
616
StringBuffer JavaDoc baseQuery = new StringBuffer JavaDoc();
617             baseQuery.append(sb); // add "Select ... From ..."
618

619             // add the constraints
620
if (sbConstraints.length() + combinedElements.size() > 0) {
621                 baseQuery.append(" WHERE ").
622                         append(sbConstraints.toString());
623             }
624
625             if (log.isDebugEnabled()) {
626                 log.trace("Base query including fields and tables : " + sb);
627             }
628
629             // now add the combined relation-constraints as UNIONS
630
while (e.hasNext()) {
631                 combinedElement = (String JavaDoc) e.next();
632                 if (teller != 1) {
633                     if (sb.indexOf("COUNT") > -1) {
634                         unionRelationConstraints.append(" UNION ALL ").append(baseQuery);
635                     } else {
636                         unionRelationConstraints.append(" UNION ").append(baseQuery);
637                     }
638                 }
639
640                 // Make sure the unionRelationConstraint ends with " AND " or a " WHERE"
641
if (unionRelationConstraints.length() >= 4) {
642                     if (!unionRelationConstraints.substring(unionRelationConstraints.length() - 4, unionRelationConstraints.length()).equals("AND ") && !unionRelationConstraints.substring(unionRelationConstraints.length() - 6, unionRelationConstraints.length()).equals("WHERE ")) {
643                         unionRelationConstraints.append(" AND ");
644                     }
645                 }
646
647                 if (andElements.length() > 0) {
648                     unionRelationConstraints.append(andElements).append(" AND ");
649                 }
650
651                 unionRelationConstraints.append(" " + combinedElement + " ");
652
653                 if (log.isDebugEnabled()) {
654                     log.trace("Union relation constraint " + teller + " : " + unionRelationConstraints);
655                 }
656                 teller++;
657             }
658
659             // add the stuff to sb
660
if (sbConstraints.length() > 0) {
661                 sb.append(" WHERE ").append(sbConstraints.toString()).append(unionRelationConstraints);
662             } else {
663                 sb.append(" WHERE ").append(unionRelationConstraints);
664             }
665
666             /*
667                4) add GROUP BY Clause
668             */

669             /* ToDo: implement group by
670
671             if (sbGroups.length() > 0) {
672                 sb.append(" GROUP BY ").
673                         append(sbGroups.toString());
674             }
675             */

676
677             /*
678                 5) adding ORDER BY
679                    eg. ORDER BY 1,3,4
680             */

681             List sortOrders = query.getSortOrders();
682             if (sortOrders.size() > 0) {
683                 sb.append(" ORDER BY ");
684                 Iterator iSortOrders = sortOrders.iterator();
685                 while (iSortOrders.hasNext()) {
686                     SortOrder sortOrder = (SortOrder) iSortOrders.next();
687
688                     // Field alias.
689
String JavaDoc fieldAlias = sortOrder.getField().getAlias();
690                     Step step = sortOrder.getField().getStep();
691                     StringBuffer JavaDoc orderByField = new StringBuffer JavaDoc();
692                     if (fieldAlias != null) {
693                         orderByField.append(getAllowedValue(fieldAlias));
694                     } else {
695                         appendField(orderByField, step, sortOrder.getField().getFieldName(), multipleSteps);
696                     }
697
698                     // Loop through the fields until we find a match
699
boolean found = false;
700                     for (int i = 0; i < query.getFields().size(); i++) {
701                         StepField sf = (StepField) query.getFields().get(i);
702                         String JavaDoc field = sf.getStep().getAlias() + "." + sf.getFieldName();
703
704                         // search for the matching field to obtain the number of the field
705
if (field.equals(orderByField.toString())) {
706                             // match found
707
sb.append((i + 1) + " ");
708                             // prevent that the field is listed twice in this order-by
709
found = true;
710                             break;
711                         }
712                     }
713                       if (! found) {
714                           throw new RuntimeException JavaDoc("Could not find the field " + orderByField + " in " + query.getFields() + " !");
715                       }
716
717
718                     // Sort direction.
719
switch (sortOrder.getDirection()) {
720                         case SortOrder.ORDER_ASCENDING:
721                             sb.append(" ASC");
722                             break;
723
724                         case SortOrder.ORDER_DESCENDING:
725                             sb.append(" DESC");
726                             break;
727
728                         default: // Invalid direction value.
729
throw new IllegalStateException JavaDoc("Invalid direction value: " + sortOrder.getDirection());
730                     }
731
732                     if (iSortOrders.hasNext()) {
733                         sb.append(",");
734                     }
735                 }
736             }
737             log.debug("Completed generation of UNION query:" + sb.toString());
738         } else {
739             sbConstraints.append(sbRelations); // Constraints by relations.
740
if (query.getConstraint() != null) {
741                 Constraint constraint = query.getConstraint();
742                 if (sbConstraints.length() > 0) {
743                     // Combine constraints.
744
sbConstraints.append(" AND ");
745                     if (constraint instanceof CompositeConstraint) {
746                         appendCompositeConstraintToSql(sbConstraints, (CompositeConstraint) constraint,
747                                 query, false, true, firstInChain);
748                     } else {
749                         firstInChain.appendConstraintToSql(sbConstraints, constraint, query,
750                                 false, true);
751                     }
752                 } else {
753                     // Only regular constraints.
754
if (constraint instanceof CompositeConstraint) {
755                         appendCompositeConstraintToSql(sbConstraints, (CompositeConstraint) constraint,
756                                 query, false, false, firstInChain);
757                     } else {
758                         firstInChain.appendConstraintToSql(sbConstraints, constraint, query,
759                                 false, false);
760                     }
761                 }
762             }
763             if (sbConstraints.length() > 0) {
764                 sb.append(" WHERE ").
765                         append(sbConstraints.toString());
766             }
767
768             // GROUP BY
769
if (sbGroups.length() > 0) {
770                 sb.append(" GROUP BY ").
771                         append(sbGroups.toString());
772             }
773
774             // ORDER BY
775
List sortOrders = query.getSortOrders();
776             if (sortOrders.size() > 0) {
777                 sb.append(" ORDER BY ");
778                 Iterator iSortOrders = sortOrders.iterator();
779                 while (iSortOrders.hasNext()) {
780                     SortOrder sortOrder = (SortOrder) iSortOrders.next();
781
782                     // Field alias.
783
String JavaDoc fieldAlias = sortOrder.getField().getAlias();
784                     Step step = sortOrder.getField().getStep();
785                     if (fieldAlias != null) {
786                         sb.append(getAllowedValue(fieldAlias));
787                     } else {
788                         appendField(sb, step, sortOrder.getField().getFieldName(), multipleSteps);
789                     }
790
791                     // Sort direction.
792
switch (sortOrder.getDirection()) {
793                         case SortOrder.ORDER_ASCENDING:
794                             sb.append(" ASC");
795                             break;
796
797                         case SortOrder.ORDER_DESCENDING:
798                             sb.append(" DESC");
799                             break;
800
801                         default: // Invalid direction value.
802
throw new IllegalStateException JavaDoc("Invalid direction value: " + sortOrder.getDirection());
803                     }
804
805                     if (iSortOrders.hasNext()) {
806                         sb.append(",");
807                     }
808                 }
809             }
810             log.debug("Completed generation of query:" + sb.toString());
811         }
812     }
813
814     /**
815      * Safely close a database connection and/or a database statement.
816      * @param con The connection to close. Can be <code>null</code>.
817      * @param stmt The statement to close, prior to closing the connection. Can be <code>null</code>.
818      */

819     protected void closeConnection(Connection con, Statement stmt) {
820         try {
821             if (stmt != null) {
822                 stmt.close();
823             }
824         } catch (Exception JavaDoc g) {}
825         try {
826             if (con != null) {
827                 if (con instanceof MultiConnection) {
828                     closeInformix((MultiConnection)con);
829                 }
830                 con.close();
831             }
832         } catch (Exception JavaDoc g) {}
833     }
834
835     private void closeInformix(MultiConnection activeConnection) {
836         Connection con = activeConnection.getRealConnection();
837         try {
838             Method JavaDoc scrub = Class.forName("com.informix.jdbc.IfxConnection").getMethod("scrubConnection", null);
839             scrub.invoke(con, null);
840         } catch (Exception JavaDoc e) {
841             log.error("Exception while calling releaseBlob(): " + e.getMessage());
842         }
843     }
844 }
845
Popular Tags