001 package org.apache.ojb.broker.accesslayer.sql;
002
003 /* Copyright 2002-2005 The Apache Software Foundation
004 *
005 * Licensed under the Apache License, Version 2.0 (the "License");
006 * you may not use this file except in compliance with the License.
007 * You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017
018 import java.util.ArrayList;
019 import java.util.Iterator;
020 import java.util.List;
021 import java.util.Map;
022 import java.util.Set;
023 import java.lang.ref.WeakReference;
024
025 import org.apache.commons.collections.set.ListOrderedSet;
026 import org.apache.ojb.broker.metadata.ClassDescriptor;
027 import org.apache.ojb.broker.metadata.DescriptorRepository;
028 import org.apache.ojb.broker.metadata.FieldDescriptor;
029 import org.apache.ojb.broker.metadata.JdbcType;
030 import org.apache.ojb.broker.platforms.Platform;
031 import org.apache.ojb.broker.query.Criteria;
032 import org.apache.ojb.broker.query.Query;
033 import org.apache.ojb.broker.query.ReportQuery;
034 import org.apache.ojb.broker.query.ReportQueryByCriteria;
035 import org.apache.ojb.broker.util.SqlHelper;
036 import org.apache.ojb.broker.util.logging.Logger;
037
038 /**
039 * Model a SELECT Statement
040 *
041 * @author <a href="mailto:jbraeuchi@hotmail.com">Jakob Braeuchi</a>
042 * @version $Id: SqlSelectStatement.java,v 1.1 2007-08-24 22:17:39 ewestfal Exp $
043 */
044 public class SqlSelectStatement extends SqlQueryStatement implements SelectStatement
045 {
046 private WeakReference fieldsForSelect;
047
048 /**
049 * Constructor for SqlSelectStatement.
050 *
051 * @param pf
052 * @param cld
053 * @param query
054 * @param logger
055 */
056 public SqlSelectStatement(Platform pf, ClassDescriptor cld, Query query, Logger logger)
057 {
058 super(pf, cld, query, logger);
059 }
060
061 /**
062 * Constructor for SqlSelectStatement.
063 *
064 * @param parent
065 * @param pf
066 * @param cld
067 * @param query
068 * @param logger
069 */
070 public SqlSelectStatement(SqlQueryStatement parent, Platform pf, ClassDescriptor cld, Query query, Logger logger)
071 {
072 super(parent, pf, cld, query, logger);
073 }
074
075 /**
076 * Append a Column with alias: A0 name -> A0.name
077 * @param anAlias the TableAlias
078 * @param field
079 * @param buf
080 */
081 protected void appendColumn(TableAlias anAlias, FieldDescriptor field, StringBuffer buf)
082 {
083 buf.append(anAlias.alias);
084 buf.append(".");
085 buf.append(field.getColumnName());
086 }
087
088 /**
089 * Appends to the statement a comma separated list of column names.
090 *
091 * DO NOT use this if order of columns is important. The row readers build reflectively and look up
092 * column names to find values, so this is safe. In the case of update, you CANNOT use this as the
093 * order of columns is important.
094 *
095 * @return list of column names for the set of all unique columns for multiple classes mapped to the
096 * same table.
097 */
098 protected List appendListOfColumnsForSelect(StringBuffer buf)
099 {
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 }