1 package org.apache.torque.util;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.util.Iterator;
23
24 import org.apache.commons.lang.StringUtils;
25
26 /***
27 * Used to assemble an SQL SELECT query. Attributes exist for the
28 * sections of a SELECT: modifiers, columns, from clause, where
29 * clause, and order by clause. The various parts of the query are
30 * appended to buffers which only accept unique entries. This class
31 * is used primarily by BasePeer.
32 *
33 * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
34 * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a>
35 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
36 * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
37 * @version $Id: Query.java 522044 2007-03-24 16:00:57Z tfischer $
38 */
39 public class Query
40 {
41 private static final String SELECT = "SELECT ";
42 private static final String FROM = " FROM ";
43 private static final String WHERE = " WHERE ";
44 private static final String AND = " AND ";
45 private static final String ORDER_BY = " ORDER BY ";
46 private static final String GROUP_BY = " GROUP BY ";
47 private static final String HAVING = " HAVING ";
48 private static final String LIMIT = " LIMIT ";
49 private static final String OFFSET = " OFFSET ";
50 private static final String ROWCOUNT = " SET ROWCOUNT ";
51
52 private UniqueList selectModifiers = new UniqueList();
53 private UniqueList selectColumns = new UniqueList();
54 private UniqueList fromTables = new UniqueList();
55 private UniqueList whereCriteria = new UniqueList();
56 private UniqueList orderByColumns = new UniqueList();
57 private UniqueList groupByColumns = new UniqueList();
58 private String having;
59 private String limit;
60 private String preLimit;
61 private String postLimit;
62 private String offset;
63 private String rowcount;
64
65 /***
66 * Retrieve the modifier buffer in order to add modifiers to this
67 * query. E.g. DISTINCT and ALL.
68 *
69 * @return An UniqueList used to add modifiers.
70 */
71 public UniqueList getSelectModifiers()
72 {
73 return selectModifiers;
74 }
75
76 /***
77 * Set the modifiers. E.g. DISTINCT and ALL.
78 *
79 * @param modifiers the modifiers
80 */
81 public void setSelectModifiers(UniqueList modifiers)
82 {
83 selectModifiers = modifiers;
84 }
85
86 /***
87 * Retrieve the columns buffer in order to specify which columns
88 * are returned in this query.
89 *
90 *
91 * @return An UniqueList used to add columns to be selected.
92 */
93 public UniqueList getSelectClause()
94 {
95 return selectColumns;
96 }
97
98 /***
99 * Set the columns.
100 *
101 * @param columns columns list
102 */
103 public void setSelectClause(UniqueList columns)
104 {
105 selectColumns = columns;
106 }
107
108 /***
109 * Retrieve the from buffer in order to specify which tables are
110 * involved in this query.
111 *
112 * @return An UniqueList used to add tables involved in the query.
113 */
114 public UniqueList getFromClause()
115 {
116 return fromTables;
117 }
118
119 /***
120 * Set the from clause.
121 *
122 * @param tables the tables
123 */
124 public void setFromClause(UniqueList tables)
125 {
126 fromTables = tables;
127 }
128
129 /***
130 * Retrieve the where buffer in order to specify the selection
131 * criteria E.g. column_a=3. Expressions added to the buffer will
132 * be separated using AND.
133 *
134 * @return An UniqueList used to add selection criteria.
135 */
136 public UniqueList getWhereClause()
137 {
138 return whereCriteria;
139 }
140
141 /***
142 * Set the where clause.
143 *
144 * @param where where clause
145 */
146 public void setWhereClause(UniqueList where)
147 {
148 whereCriteria = where;
149 }
150
151 /***
152 * Retrieve the order by columns buffer in order to specify which
153 * columns are used to sort the results of the query.
154 *
155 * @return An UniqueList used to add columns to sort on.
156 */
157 public UniqueList getOrderByClause()
158 {
159 return orderByColumns;
160 }
161
162 /***
163 * Retrieve the group by columns buffer in order to specify which
164 * columns are used to group the results of the query.
165 *
166 * @return An UniqueList used to add columns to group on.
167 */
168 public UniqueList getGroupByClause()
169 {
170 return groupByColumns;
171 }
172
173 /***
174 * Set the having clause. This is used to restrict which rows
175 * are returned.
176 *
177 * @param having A String.
178 */
179 public void setHaving(String having)
180 {
181 this.having = having;
182 }
183
184 /***
185 * Set the limit number. This is used to limit the number of rows
186 * returned by a query.
187 *
188 * @param limit A String.
189 */
190 public void setLimit(String limit)
191 {
192 this.limit = limit;
193 }
194
195 /***
196 * Get the Pre limit String. Oracle and DB2 want to encapsulate
197 * a query into a subquery for limiting.
198 *
199 * @param preLimit A String with the preLimit.
200 */
201 public void setPreLimit(String preLimit)
202 {
203 this.preLimit = preLimit;
204 }
205
206 /***
207 * Set the Post limit String. Oracle and DB2 want to encapsulate
208 * a query into a subquery for limiting.
209 *
210 * @param postLimit A String with the postLimit.
211 */
212 public void setPostLimit(String postLimit)
213 {
214 this.postLimit = postLimit;
215 }
216
217 /***
218 * Set the offset number. This is used to set the row where the
219 * resultset starts.
220 *
221 * @param offset A String.
222 */
223 public void setOffset(String offset)
224 {
225 this.offset = offset;
226 }
227
228 /***
229 * Set the rowcount number. This is used to limit the number of
230 * rows returned by Sybase and MS SQL/Server.
231 *
232 * @param rowcount A String.
233 */
234 public void setRowcount(String rowcount)
235 {
236 this.rowcount = rowcount;
237 }
238
239 /***
240 * Get the having clause. This is used to restrict which
241 * rows are returned based on some condition.
242 *
243 * @return A String that is the having clause.
244 */
245 public String getHaving()
246 {
247 return having;
248 }
249
250 /***
251 * Get the limit number. This is used to limit the number of
252 * returned by a query in Postgres.
253 *
254 * @return A String with the limit.
255 */
256 public String getLimit()
257 {
258 return limit;
259 }
260
261 /***
262 * Get the Post limit String. Oracle and DB2 want to encapsulate
263 * a query into a subquery for limiting.
264 *
265 * @return A String with the preLimit.
266 */
267 public String getPostLimit()
268 {
269 return postLimit;
270 }
271
272 /***
273 * Get the Pre limit String. Oracle and DB2 want to encapsulate
274 * a query into a subquery for limiting.
275 *
276 * @return A String with the preLimit.
277 */
278 public String getPreLimit()
279 {
280 return preLimit;
281 }
282
283 /***
284 * Get the offset number. This is used to set the row where the
285 * resultset starts.
286 *
287 * @return A String with the offset, or null if no offset is set.
288 */
289 public String getOffset()
290 {
291 return offset;
292 }
293
294 /***
295 * True if this query has a limit clause registered.
296 *
297 * @return true if a limit clause exists.
298 */
299 public boolean hasLimit()
300 {
301 return ((preLimit != null)
302 || (postLimit != null)
303 || (limit != null));
304 }
305
306 /***
307 * Get the rowcount number. This is used to limit the number of
308 * returned by a query in Sybase and MS SQL/Server.
309 *
310 * @return A String with the row count.
311 */
312 public String getRowcount()
313 {
314 return rowcount;
315 }
316
317 /***
318 * Outputs the query statement.
319 *
320 * @return A String with the query statement.
321 */
322 public String toString()
323 {
324 return toStringBuffer(new StringBuffer()).toString();
325 }
326
327 public StringBuffer toStringBuffer(StringBuffer stmt)
328 {
329 if (preLimit != null)
330 {
331 stmt.append(preLimit);
332 }
333
334 if (rowcount != null)
335 {
336 stmt.append(ROWCOUNT)
337 .append(rowcount)
338 .append(" ");
339 }
340 stmt.append(SELECT)
341 .append(StringUtils.join(selectModifiers.iterator(), " "))
342 .append(StringUtils.join(selectColumns.iterator(), ", "))
343 .append(FROM);
344
345 boolean first = true;
346 for (Iterator it = fromTables.iterator(); it.hasNext();)
347 {
348 FromElement fromElement = (FromElement) it.next();
349
350 if (!first && fromElement.getJoinCondition() == null)
351 {
352 stmt.append(", ");
353 }
354 first = false;
355 stmt.append(fromElement.toString());
356 }
357
358 if (!whereCriteria.isEmpty())
359 {
360 stmt.append(WHERE)
361 .append(StringUtils.join(whereCriteria.iterator(), AND));
362 }
363 if (!groupByColumns.isEmpty())
364 {
365 stmt.append(GROUP_BY)
366 .append(StringUtils.join(groupByColumns.iterator(), ", "));
367 }
368 if (having != null)
369 {
370 stmt.append(HAVING)
371 .append(having);
372 }
373 if (!orderByColumns.isEmpty())
374 {
375 stmt.append(ORDER_BY)
376 .append(StringUtils.join(orderByColumns.iterator(), ", "));
377 }
378 if (limit != null)
379 {
380 stmt.append(LIMIT)
381 .append(limit);
382 }
383 if (offset != null)
384 {
385 stmt.append(OFFSET)
386 .append(offset);
387 }
388 if (rowcount != null)
389 {
390 stmt.append(ROWCOUNT)
391 .append("0");
392 }
393 if (postLimit != null)
394 {
395 stmt.append(postLimit);
396 }
397
398 return stmt;
399 }
400
401 /***
402 * This class describes an Element in the From-part of a SQL clause.
403 * It must contain the name of the database table.
404 * It might contain an alias for the table name, a join type
405 * and a join condition.
406 * The class is package visible, as it is used in BasePeer,
407 * and is immutable.
408 */
409 public static class FromElement
410 {
411
412 /*** the tablename, might contain an appended alias name */
413 private String tableName = null;
414
415 /*** the type of the join, e.g. SqlEnum.LEFT_JOIN */
416 private SqlEnum joinType = null;
417
418 /*** the join condition, e.g. table_a.id = table_b.a_id */
419 private String joinCondition = null;
420
421 /***
422 * Constructor
423 * @param tableName the tablename, might contain an appended alias name
424 * e.g. <br />
425 * table_1<br />
426 * table_1 alias_for_table_1
427 * @param joinType the type of the join, e.g. SqlEnum.LEFT_JOIN,
428 * or null if no excplicit join is wanted
429 * @param joinCondition the join condition,
430 * e.g. table_a.id = table_b.a_id,
431 * or null if no explicit join is wanted
432 * (In this case, the join condition is appended to the
433 * whereClause instead)
434 */
435 public FromElement(String tableName,
436 SqlEnum joinType,
437 String joinCondition)
438 {
439 this.tableName = tableName;
440 this.joinType = joinType;
441 this.joinCondition = joinCondition;
442 }
443
444
445 /***
446 * @return the join condition, e.g. table_a.id = table_b.a_id,
447 * or null if the join is not an explicit join
448 */
449 public String getJoinCondition()
450 {
451 return joinCondition;
452 }
453
454 /***
455 * @return the type of the join, e.g. SqlEnum.LEFT_JOIN,
456 * or null if the join is not an explicit join
457 */
458 public SqlEnum getJoinType()
459 {
460 return joinType;
461 }
462
463 /***
464 * @return the tablename, might contain an appended alias name,
465 * e.g. <br />
466 * table_1<br />
467 * table_1 alias_for_table_1
468 *
469 */
470 public String getTableName()
471 {
472 return tableName;
473 }
474
475 /***
476 * Returns a SQL representation of the element
477 * @return a SQL representation of the element
478 */
479 public String toString()
480 {
481 StringBuffer result = new StringBuffer();
482 if (joinType != null)
483 {
484 result.append(joinType);
485 }
486 result.append(tableName);
487 if (joinCondition != null)
488 {
489 result.append(SqlEnum.ON);
490 result.append(joinCondition);
491 }
492 return result.toString();
493 }
494 }
495 }