SqlSelectStatement


1   package org.apache.ojb.broker.accesslayer.sql;
2   
3   /* Copyright 2002-2005 The Apache Software Foundation
4    *
5    * Licensed under the Apache License, Version 2.0 (the "License");
6    * you may not use this file except in compliance with the License.
7    * You may obtain a copy of the License at
8    *
9    *     http://www.apache.org/licenses/LICENSE-2.0
10   *
11   * Unless required by applicable law or agreed to in writing, software
12   * distributed under the License is distributed on an "AS IS" BASIS,
13   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14   * See the License for the specific language governing permissions and
15   * limitations under the License.
16   */
17  
18  import java.util.ArrayList  ;
19  import java.util.Iterator  ;
20  import java.util.List  ;
21  import java.util.Map  ;
22  import java.util.Set  ;
23  import java.lang.ref.WeakReference  ;
24  
25  import org.apache.commons.collections.set.ListOrderedSet;
26  import org.apache.ojb.broker.metadata.ClassDescriptor;
27  import org.apache.ojb.broker.metadata.DescriptorRepository;
28  import org.apache.ojb.broker.metadata.FieldDescriptor;
29  import org.apache.ojb.broker.metadata.JdbcType;
30  import org.apache.ojb.broker.platforms.Platform;
31  import org.apache.ojb.broker.query.Criteria;
32  import org.apache.ojb.broker.query.Query;
33  import org.apache.ojb.broker.query.ReportQuery;
34  import org.apache.ojb.broker.query.ReportQueryByCriteria;
35  import org.apache.ojb.broker.util.SqlHelper;
36  import org.apache.ojb.broker.util.logging.Logger;
37  
38  /**
39   * Model a SELECT Statement
40   *
41   * @author <a HREF="mailto:jbraeuchi@hotmail.com">Jakob Braeuchi</a>
42   * @version $Id: SqlSelectStatement.java,v 1.22.2.8 2005/12/22 18:25:51 brj Exp $
43   */
44  public class SqlSelectStatement extends SqlQueryStatement implements SelectStatement
45  {
46      private WeakReference   fieldsForSelect;
47  
48      /**
49       * Constructor for SqlSelectStatement.
50       * 
51       * @param pf
52       * @param cld
53       * @param query
54       * @param logger
55       */
56      public SqlSelectStatement(Platform pf, ClassDescriptor cld, Query query, Logger logger)
57      {
58          super(pf, cld, query, logger);
59      }
60  
61      /**
62       * Constructor for SqlSelectStatement.
63       *
64       * @param parent
65       * @param pf
66       * @param cld
67       * @param query
68       * @param logger
69       */
70      public SqlSelectStatement(SqlQueryStatement parent, Platform pf, ClassDescriptor cld, Query query, Logger logger)
71      {
72          super(parent, pf, cld, query, logger);
73      }
74  
75      /**
76       * Append a Column with alias: A0 name -> A0.name
77       * @param anAlias the TableAlias
78       * @param field
79       * @param buf
80       */
81      protected void appendColumn(TableAlias anAlias, FieldDescriptor field, StringBuffer   buf)
82      {
83          buf.append(anAlias.alias);
84          buf.append(".");
85          buf.append(field.getColumnName());
86      }
87  
88      /**
89       * Appends to the statement a comma separated list of column names.
90       *
91       * DO NOT use this if order of columns is important. The row readers build reflectively and look up
92       * column names to find values, so this is safe. In the case of update, you CANNOT use this as the
93       * order of columns is important.
94       *
95       * @return list of column names for the set of all unique columns for multiple classes mapped to the
96       * same table.
97       */
98      protected List   appendListOfColumnsForSelect(StringBuffer   buf)
99      {
100         FieldDescriptor[] fieldDescriptors = getFieldsForSelect();
101         ArrayList   columnList = new ArrayList  ();
102         TableAlias searchAlias = getSearchTable();
103         
104         for (int i = 0; i < fieldDescriptors.length; i++)
105         {
106             FieldDescriptor field = fieldDescriptors[i];
107             TableAlias alias = getTableAliasForClassDescriptor(field.getClassDescriptor());
108             if (alias == null)
109             {
110                 alias = searchAlias;
111             }
112             if (i > 0)
113             {
114                 buf.append(",");
115             }
116             appendColumn(alias, field, buf);
117             columnList.add(field.getAttributeName());
118         }
119         
120         appendClazzColumnForSelect(buf);
121         return columnList;
122     }
123  
124     /**
125      * Get MultiJoined ClassDescriptors
126      * @param cld
127      */
128     private ClassDescriptor[] getMultiJoinedClassDescriptors(ClassDescriptor cld)
129     {
130         DescriptorRepository repository = cld.getRepository();
131         Class  [] multiJoinedClasses = repository.getSubClassesMultipleJoinedTables(cld, true);
132         ClassDescriptor[] result = new ClassDescriptor[multiJoinedClasses.length];
133 
134         for (int i = 0 ; i < multiJoinedClasses.length; i++)
135         {
136             result[i] = repository.getDescriptorFor(multiJoinedClasses[i]);
137          }
138 
139         return result;
140     }
141 
142     /**
143      * Create the OJB_CLAZZ pseudo column based on CASE WHEN.
144      * This column defines the Class to be instantiated.
145      * @param buf
146      */
147     private void appendClazzColumnForSelect(StringBuffer   buf)
148     {
149         ClassDescriptor cld = getSearchClassDescriptor();
150         ClassDescriptor[] clds = getMultiJoinedClassDescriptors(cld);
151 
152         if (clds.length == 0)
153         {
154             return;
155         }
156         
157         buf.append(",CASE");
158 
159         for (int i = clds.length; i > 0; i--)
160         {
161             buf.append(" WHEN ");
162 
163             ClassDescriptor subCld = clds[i - 1];
164             FieldDescriptor[] fieldDescriptors = subCld.getPkFields();
165 
166             TableAlias alias = getTableAliasForClassDescriptor(subCld);
167             for (int j = 0; j < fieldDescriptors.length; j++)
168             {
169                 FieldDescriptor field = fieldDescriptors[j];
170                 if (j > 0)
171                 {
172                     buf.append(" AND ");
173                 }
174                 appendColumn(alias, field, buf);
175                 buf.append(" IS NOT NULL");
176             }
177             buf.append(" THEN '").append(subCld.getClassNameOfObject()).append("'");
178         }
179         buf.append(" ELSE '").append(cld.getClassNameOfObject()).append("'");
180         buf.append(" END AS " + SqlHelper.OJB_CLASS_COLUMN);
181     }
182     
183     /**
184      * Return the Fields to be selected.
185      *
186      * @return the Fields to be selected
187      */
188     protected FieldDescriptor[] getFieldsForSelect()
189     {
190         if (fieldsForSelect == null || fieldsForSelect.get() == null)
191         {
192             fieldsForSelect = new WeakReference  (buildFieldsForSelect(getSearchClassDescriptor()));
193         }
194         return (FieldDescriptor[]) fieldsForSelect.get();
195     }
196 
197     /**
198      * Return the Fields to be selected.
199      *
200      * @param cld the ClassDescriptor
201      * @return the Fields to be selected
202      */
203     protected FieldDescriptor[] buildFieldsForSelect(ClassDescriptor cld)
204     {
205         DescriptorRepository repository = cld.getRepository();
206         Set   fields = new ListOrderedSet();   // keep the order of the fields
207         
208         // add Standard Fields
209         // MBAIRD: if the object being queried on has multiple classes mapped to the table,
210         // then we will get all the fields that are a unique set across all those classes so if we need to
211         // we can materialize an extent
212         FieldDescriptor fds[] = repository.getFieldDescriptorsForMultiMappedTable(cld);
213         for (int i = 0; i < fds.length; i++)
214         {
215             fields.add(fds[i]);
216         }
217 
218         // add inherited Fields. This is important when querying for a class having a super-reference
219         fds = cld.getFieldDescriptor(true);
220         for (int i = 0; i < fds.length; i++)
221         {
222             fields.add(fds[i]);
223         }
224 
225         // add Fields of joined subclasses
226         Class  [] multiJoinedClasses = repository.getSubClassesMultipleJoinedTables(cld, true);
227         for (int c = 0; c < multiJoinedClasses.length; c++)
228         {
229             ClassDescriptor subCld = repository.getDescriptorFor(multiJoinedClasses[c]);
230             fds = subCld.getFieldDescriptions();
231             for (int i = 0; i < fds.length; i++)
232             {
233                 fields.add(fds[i]);
234             }
235         }
236 
237         FieldDescriptor[] result = new FieldDescriptor[fields.size()];
238         fields.toArray(result);
239         return result;
240     }
241 
242     /**
243      * Appends to the statement a comma separated list of column names.
244      *
245      * @param columns defines the columns to be selected (for reports)
246      * @return list of column names
247      */
248     protected List   appendListOfColumns(String  [] columns, StringBuffer   buf)
249     {
250         ArrayList   columnList = new ArrayList  ();
251 
252         for (int i = 0; i < columns.length; i++)
253         {
254             if (i > 0)
255             {
256                 buf.append(",");
257             }
258             appendColName(columns[i], false, null, buf);
259             columnList.add(columns[i]);
260         }
261         return columnList;
262     }
263 
264     /**
265      * @see org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement#buildStatement()
266      */
267     protected String   buildStatement()
268     {
269         StringBuffer   stmt = new StringBuffer  (1024);
270         Query query = getQuery();
271         boolean first = true;
272         List   orderByFields = null;
273         String  [] attributes = null;
274         String  [] joinAttributes = null;
275         Iterator   it = getJoinTreeToCriteria().entrySet().iterator();
276         List   columnList = new ArrayList  ();
277 
278         if (query instanceof ReportQuery)
279         {
280             attributes = ((ReportQuery) query).getAttributes();
281             joinAttributes = ((ReportQuery) query).getJoinAttributes();
282         }
283 
284         while (it.hasNext())
285         {
286             Map.Entry   entry = (Map.Entry  ) it.next();
287             Criteria whereCrit = (Criteria) entry.getValue();
288             Criteria havingCrit = query.getHavingCriteria();
289             StringBuffer   where = new StringBuffer  ();
290             StringBuffer   having = new StringBuffer  ();
291             List   groupByFields;
292 
293             // Set correct tree of joins for the current criteria
294             setRoot((TableAlias) entry.getKey());
295 
296             if (whereCrit != null && whereCrit.isEmpty())
297             {
298                 whereCrit = null;
299             }
300 
301             if (havingCrit != null && havingCrit.isEmpty())
302             {
303                 havingCrit = null;
304             }
305 
306             if (first)
307             {
308                 first = false;
309             }
310             else
311             {
312                 stmt.append(" UNION ");
313             }
314 
315             stmt.append("SELECT ");
316             if (query.isDistinct())
317             {
318                 stmt.append("DISTINCT ");
319             }
320 
321             if (attributes == null || attributes.length == 0)
322             {
323                 /**
324                  * MBAIRD: use the appendListofColumnsForSelect, as it finds
325                  * the union of select items for all object mapped to the same table. This
326                  * will allow us to load objects with unique mapping fields that are mapped
327                  * to the same table.
328                  */
329                 columnList.addAll(appendListOfColumnsForSelect(stmt));
330             }
331             else
332             {
333                 columnList.addAll(appendListOfColumns(attributes, stmt));
334             }
335 
336             // BRJ:
337             // joinColumns are only used to force the building of a join;
338             // they are not appended to the select-clause !
339             // these columns are used in COUNT-ReportQueries and
340             // are taken from the query the COUNT is based on 
341             if (joinAttributes != null && joinAttributes.length > 0)
342             {
343                 for (int i = 0; i < joinAttributes.length; i++)
344                 {
345                     getAttributeInfo(joinAttributes[i], false, null, getQuery().getPathClasses());
346                 }
347             }
348 
349             groupByFields = query.getGroupBy();
350             ensureColumns(groupByFields, columnList);
351             
352             orderByFields = query.getOrderBy();
353             columnList = ensureColumns(orderByFields, columnList, stmt);
354 /*
355 arminw:
356 TODO: this feature doesn't work, so remove this in future
357 */
358             /**
359              * treeder: going to map superclass tables here,
360              * not sure about the columns, just using all columns for now
361              */
362             ClassDescriptor cld = getBaseClassDescriptor();
363             ClassDescriptor cldSuper = null;
364             if (cld.getSuperClass() != null)
365             {
366                 // then we have a super class so join tables
367                 cldSuper = cld.getRepository().getDescriptorFor(cld.getSuperClass());
368                 appendSuperClassColumns(cldSuper, stmt);
369             }
370 
371             stmt.append(" FROM ");
372             appendTableWithJoins(getRoot(), where, stmt);
373 
374             if (cld.getSuperClass() != null)
375             {
376                 appendSuperClassJoin(cld, cldSuper, stmt, where);
377             }
378             
379             appendWhereClause(where, whereCrit, stmt);
380             appendGroupByClause(groupByFields, stmt);
381             appendHavingClause(having, havingCrit, stmt);
382         }
383 
384         appendOrderByClause(orderByFields, columnList, stmt);
385 
386         if (query instanceof ReportQueryByCriteria)
387         {
388              ((ReportQueryByCriteria) query).setAttributeFieldDescriptors(m_attrToFld);
389         }
390 
391         return stmt.toString();
392     }
393 
394 /*
395 arminw:
396 TODO: this feature doesn't work, so remove this in future
397 */
398     private void appendSuperClassJoin(ClassDescriptor cld, ClassDescriptor cldSuper, StringBuffer   stmt, StringBuffer   where)
399     {
400         stmt.append(",");
401         appendTable(cldSuper, stmt);
402 
403         if (where != null)
404         {
405             if (where.length() > 0)
406             {
407                 where.append(" AND ");
408             }
409 
410             // get reference field in super class
411             // TODO: do not use the superclassfield anymore, just assume that the id is the same in both tables - @see PBroker.storeToDb
412             int superFieldRef = cld.getSuperClassFieldRef();
413             FieldDescriptor refField = cld.getFieldDescriptorByIndex(superFieldRef);
414 
415             appendTable(cldSuper, where);
416             where.append(".");
417             appendField(cldSuper.getAutoIncrementFields()[0], where);
418             where.append(" = ");
419             appendTable(cld, where);
420             where.append(".");
421             appendField(refField, where);
422         }
423     }
424 
425     private void appendSuperClassColumns(ClassDescriptor cldSuper, StringBuffer   buf)
426     {
427         FieldDescriptor[] fields = cldSuper.getFieldDescriptions();
428         for (int i = 0; i < fields.length; i++)
429         {
430             FieldDescriptor field = fields[i];
431             if (i > 0)
432             {
433                 buf.append(",");
434             }
435             buf.append(cldSuper.getFullTableName());
436             buf.append(".");
437             buf.append(field.getColumnName());
438         }
439     }
440 
441     /**
442      * Append table name. Quote if necessary.
443      */
444     protected void appendTable(ClassDescriptor cld, StringBuffer   buf)
445     {
446         buf.append(cld.getFullTableName());
447     }
448 
449     /**
450      * Append column name. Quote if necessary.
451      */
452     protected void appendField(FieldDescriptor fld, StringBuffer   buf)
453     {
454         buf.append(fld.getColumnName());
455     }
456 
457     public Query getQueryInstance()
458     {
459         return getQuery();
460     }
461 
462     public int getColumnIndex(FieldDescriptor fld)
463     {
464         int index = JdbcType.MIN_INT;
465         FieldDescriptor[] fields = getFieldsForSelect();
466         if (fields != null)
467         {
468             for (int i = 0; i < fields.length; i++)
469             {
470                 if (fields[i].equals(fld))
471                 {
472                     index = i + 1;  // starts at 1
473                     break;
474                 }
475             }
476         }
477         return index;
478     }
479 }
480
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags