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