1 package org.apache.torque.sql;
2
3 /*
4 * Licensed to the Apache Software Foundation (ASF) under one
5 * or more contributor license agreements. See the NOTICE file
6 * distributed with this work for additional information
7 * regarding copyright ownership. The ASF licenses this file
8 * to you under the Apache License, Version 2.0 (the
9 * "License"); you may not use this file except in compliance
10 * with the License. You may obtain a copy of the License at
11 *
12 * http://www.apache.org/licenses/LICENSE-2.0
13 *
14 * Unless required by applicable law or agreed to in writing,
15 * software distributed under the License is distributed on an
16 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17 * KIND, either express or implied. See the License for the
18 * specific language governing permissions and limitations
19 * under the License.
20 */
21
22 import java.util.ArrayList;
23 import java.util.Collections;
24 import java.util.Iterator;
25 import java.util.List;
26
27 import org.apache.commons.lang.StringUtils;
28 import org.apache.torque.TorqueException;
29 import org.apache.torque.criteria.FromElement;
30 import org.apache.torque.util.UniqueList;
31
32 /**
33 * Contains the various parts of a SQL statement (select, update or delete).
34 * Attributes exist for the sections of these statements:
35 * modifiers, columns, from clause, where clause, and order by clause.
36 * Most parts of the query are appended to buffers which only accept
37 * unique entries.
38 *
39 * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
40 * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a>
41 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
42 * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
43 * @version $Id: Query.java 1351125 2012-06-17 16:51:03Z tv $
44 *
45 * TODO rename to SqlStatement
46 */
47 public class Query
48 {
49 /** All types of the statement. */
50 public enum Type
51 {
52 /** The instance contains the parts of a select statement. */
53 SELECT,
54 /** The instance contains the parts of an update statement. */
55 UPDATE,
56 /** The instance contains the parts of a delete statement. */
57 DELETE
58 }
59
60 /** Constant for text "SELECT ". */
61 private static final String SELECT = "SELECT ";
62 /** Constant for text "UPDATE ". */
63 private static final String UPDATE = "UPDATE ";
64 /** Constant for text "DELETE FROM ". */
65 private static final String DELETE_FROM = "DELETE FROM ";
66 /** Constant for text " FROM ". */
67 private static final String FROM = " FROM ";
68 /** Constant for text " SET ". */
69 private static final String SET = " SET ";
70 /** Constant for text " WHERE ". */
71 private static final String WHERE = " WHERE ";
72 /** Constant for text " AND ". */
73 private static final String AND = " AND ";
74 /** Constant for text " ORDER BY ". */
75 private static final String ORDER_BY = " ORDER BY ";
76 /** Constant for text " GROUP BY ". */
77 private static final String GROUP_BY = " GROUP BY ";
78 /** Constant for text " HAVING ". */
79 private static final String HAVING = " HAVING ";
80 /** Constant for text " LIMIT ". */
81 private static final String LIMIT = " LIMIT ";
82 /** Constant for text " OFFSET ". */
83 private static final String OFFSET = " OFFSET ";
84 /** Constant for text " SET ROWCOUNT ". */
85 private static final String SET_ROWCOUNT = " SET ROWCOUNT ";
86
87 /**
88 * The select modifiers. E.g. DISTINCT.
89 */
90 private final UniqueList<String> selectModifiers = new UniqueList<String>();
91
92 /**
93 * The select columns for a select statement, or the columns to update
94 * for an update statement.
95 */
96
97 private final UniqueList<String> columns = new UniqueList<String>();
98
99 /**
100 * The tables to select from (including join operators) for
101 * a select clause, or the tables to update or delete for update
102 * or delete statements.
103 */
104 private final UniqueList<FromElement> fromClause = new UniqueList<FromElement>();
105
106 /**
107 * The where clause identifying the rows to select/update/delete.
108 */
109 private final UniqueList<String> whereClause = new UniqueList<String>();
110
111 /**
112 * Contains all replacement objects which are inserted into the prepared
113 * statement ? placeholders for the where Clause.
114 */
115 private final List<Object> whereClausePreparedStatementReplacements
116 = new ArrayList<Object>();
117
118 /** The order by columns, possibly including direction (ASC or DESC). */
119 private final UniqueList<String> orderByColumns = new UniqueList<String>();
120
121 /** The group by columns. */
122 private final UniqueList<String> groupByColumns = new UniqueList<String>();
123
124 /** The having clause, or null for none. */
125 private String having;
126
127 /** The limit clause, or null for none. */
128 private String limit;
129
130 /**
131 * Some databases need a clause to wrap the statement in for limit;
132 * This field contains the starting part of the clause.
133 * Null if the clause is not wanted.
134 */
135 private String preLimit;
136
137 /**
138 * Some databases need a clause to wrap the statement in for limit;
139 * This field contains the end part of the clause.
140 * Null if the clause is not wanted.
141 */
142 private String postLimit;
143
144 /**
145 * The offset clause, or null for none.
146 */
147 private String offset;
148
149 /**
150 * The set rowcount clause, or null for none.
151 */
152 private String rowcount;
153
154 /** The type of the statement. */
155 private Type type = Type.SELECT;
156
157 /** The JDBC statement fetch size, if any. */
158 private Integer fetchSize;
159
160 /**
161 * Retrieve the modifier buffer in order to add modifiers to this
162 * query. E.g. DISTINCT and ALL.
163 *
164 * @return An UniqueList used to add modifiers.
165 */
166 public UniqueList<String> getSelectModifiers()
167 {
168 return selectModifiers;
169 }
170
171 /**
172 * Retrieve the columns buffer in order to specify which columns
173 * are returned in this query.
174 *
175 * @return An UniqueList used to add columns to be selected.
176 */
177 public UniqueList<String> getSelectClause()
178 {
179 return columns;
180 }
181
182 /**
183 * Retrieve the from buffer in order to specify which tables are
184 * involved in this query.
185 *
186 * @return An UniqueList used to add tables involved in the query.
187 */
188 public UniqueList<FromElement> getFromClause()
189 {
190 return fromClause;
191 }
192
193 /**
194 * Retrieve the where buffer in order to specify the selection
195 * criteria E.g. column_a=?. Expressions added to the buffer will
196 * be separated using AND.
197 *
198 * @return An UniqueList used to add selection criteria.
199 */
200 public UniqueList<String> getWhereClause()
201 {
202 return whereClause;
203 }
204
205 /**
206 * Retrieves the replacements which are inserted into prepared statement
207 * placeholders in the where clause.
208 * The number and order of the elements in the list must correspond
209 * to the order of the placeholders in the where clause string.
210 *
211 * @return A List containing all the replacements for the prepared
212 * statement placeholders, not null.
213 */
214 public List<Object> getWhereClausePreparedStatementReplacements()
215 {
216 return whereClausePreparedStatementReplacements;
217 }
218
219 /**
220 * Returns all preparedStatementReplacements in the query.
221 *
222 * @return an unmodifiable list of all preparedStatementReplacements.
223 */
224 public List<Object> getPreparedStatementReplacements()
225 {
226 ArrayList<Object> result = new ArrayList<Object>();
227 for (FromElement fromElement : fromClause)
228 {
229 result.addAll(fromElement.getPreparedStatementReplacements());
230 }
231 result.addAll(whereClausePreparedStatementReplacements);
232 return Collections.unmodifiableList(result);
233 }
234
235 /**
236 * Retrieve the order by columns buffer in order to specify which
237 * columns are used to sort the results of the query.
238 *
239 * @return An UniqueList used to add columns to sort on.
240 */
241 public UniqueList<String> getOrderByClause()
242 {
243 return orderByColumns;
244 }
245
246 /**
247 * Retrieve the group by columns buffer in order to specify which
248 * columns are used to group the results of the query.
249 *
250 * @return An UniqueList used to add columns to group on.
251 */
252 public UniqueList<String> getGroupByClause()
253 {
254 return groupByColumns;
255 }
256
257 /**
258 * Get the having clause. This is used to restrict which
259 * rows are returned based on some condition.
260 *
261 * @return A String that is the having clause.
262 */
263 public String getHaving()
264 {
265 return having;
266 }
267
268 /**
269 * Set the having clause. This is used to restrict which rows
270 * are returned.
271 *
272 * @param having A String.
273 */
274 public void setHaving(String having)
275 {
276 this.having = having;
277 }
278
279 /**
280 * Get the limit number. This is used to limit the number of
281 * returned by a query in Postgres.
282 *
283 * @return A String with the limit.
284 */
285 public String getLimit()
286 {
287 return limit;
288 }
289
290 /**
291 * Set the limit number. This is used to limit the number of rows
292 * returned by a query.
293 *
294 * @param limit A String.
295 */
296 public void setLimit(String limit)
297 {
298 this.limit = limit;
299 }
300
301 /**
302 * Get the Pre limit String. Oracle and DB2 want to encapsulate
303 * a query into a subquery for limiting.
304 *
305 * @return A String with the preLimit.
306 */
307 public String getPreLimit()
308 {
309 return preLimit;
310 }
311
312 /**
313 * Get the Pre limit String. Oracle and DB2 want to encapsulate
314 * a query into a subquery for limiting.
315 *
316 * @param preLimit A String with the preLimit.
317 */
318 public void setPreLimit(String preLimit)
319 {
320 this.preLimit = preLimit;
321 }
322
323 /**
324 * Get the Post limit String. Oracle and DB2 want to encapsulate
325 * a query into a subquery for limiting.
326 *
327 * @return A String with the preLimit.
328 */
329 public String getPostLimit()
330 {
331 return postLimit;
332 }
333
334 /**
335 * Set the Post limit String. Oracle and DB2 want to encapsulate
336 * a query into a subquery for limiting.
337 *
338 * @param postLimit A String with the postLimit.
339 */
340 public void setPostLimit(String postLimit)
341 {
342 this.postLimit = postLimit;
343 }
344
345 /**
346 * Get the offset number. This is used to set the row where the
347 * resultset starts.
348 *
349 * @return A String with the offset, or null if no offset is set.
350 */
351 public String getOffset()
352 {
353 return offset;
354 }
355
356 /**
357 * Set the offset number. This is used to set the row where the
358 * resultset starts.
359 *
360 * @param offset A String.
361 */
362 public void setOffset(String offset)
363 {
364 this.offset = offset;
365 }
366
367 /**
368 * Get the rowcount number. This is used to limit the number of
369 * returned by a query in Sybase and MS SQL/Server.
370 *
371 * @return A String with the row count.
372 */
373 public String getRowcount()
374 {
375 return rowcount;
376 }
377
378 /**
379 * Set the rowcount number. This is used to limit the number of
380 * rows returned by Sybase and MS SQL/Server.
381 *
382 * @param rowcount A String.
383 */
384 public void setRowcount(String rowcount)
385 {
386 this.rowcount = rowcount;
387 }
388
389 /**
390 * True if this query has a limit clause registered.
391 *
392 * @return true if a limit clause exists.
393 */
394 public boolean hasLimit()
395 {
396 return ((preLimit != null)
397 || (postLimit != null)
398 || (limit != null));
399 }
400
401 /**
402 * Returns the type of this SQL statement.
403 *
404 * @return type the new type, not null.
405 */
406 public Type getType()
407 {
408 return type;
409 }
410
411 /**
412 * Sets the type of this SQL statement.
413 *
414 * @param type the new type, not null.
415 *
416 * @throws NullPointerException if <code>type</code> is null.
417 */
418 public void setType(Type type)
419 {
420 if (type == null)
421 {
422 throw new NullPointerException("type is null");
423 }
424 this.type = type;
425 }
426
427 /**
428 * Returns the JDBC statement fetch size to use for queries.
429 *
430 * @return the fetch size, or null if none is set.
431 */
432 public Integer getFetchSize()
433 {
434 return fetchSize;
435 }
436
437 /**
438 * Sets the JDBC statement fetch size to use for queries.
439 *
440 * @param fetchSize the fetch size, or null for not set.
441 */
442 public void setFetchSize(Integer fetchSize)
443 {
444 this.fetchSize = fetchSize;
445 }
446
447 /**
448 * Outputs the query statement.
449 *
450 * @return A String with the query statement.
451 */
452 public String toString()
453 {
454 return toStringBuilder(new StringBuilder()).toString();
455 }
456
457 /**
458 * Appends the query to a string builder.
459 *
460 * @param stringBuilder the stringBuilder to append to, not null.
461 *
462 * @return the modified passed in string builder.
463 */
464 public StringBuilder toStringBuilder(StringBuilder stringBuilder)
465 {
466 if (preLimit != null)
467 {
468 stringBuilder.append(preLimit);
469 }
470
471 if (rowcount != null)
472 {
473 stringBuilder.append(SET_ROWCOUNT)
474 .append(rowcount)
475 .append(" ");
476 }
477
478 if (Type.SELECT == type)
479 {
480 stringBuilder.append(SELECT)
481 .append(StringUtils.join(selectModifiers.iterator(), " "))
482 .append(StringUtils.join(columns.iterator(), ", "))
483 .append(FROM);
484 }
485 else if (Type.UPDATE == type)
486 {
487 stringBuilder.append(UPDATE);
488 }
489 else if (Type.DELETE == type)
490 {
491 stringBuilder.append(DELETE_FROM);
492 }
493
494 boolean first = true;
495 for (Iterator<FromElement> it = fromClause.iterator(); it.hasNext();)
496 {
497 FromElement fromElement = it.next();
498
499 if (!first && fromElement.getJoinCondition() == null)
500 {
501 stringBuilder.append(", ");
502 }
503 first = false;
504 stringBuilder.append(fromElement.toString());
505 }
506
507 if (Type.UPDATE == type)
508 {
509 stringBuilder.append(SET)
510 .append(StringUtils.join(columns, "=?, "));
511 if (!columns.isEmpty())
512 {
513 stringBuilder.append("=?");
514 }
515 }
516
517 if (!whereClause.isEmpty())
518 {
519 stringBuilder.append(WHERE)
520 .append(StringUtils.join(whereClause.iterator(), AND));
521 }
522 if (!groupByColumns.isEmpty())
523 {
524 stringBuilder.append(GROUP_BY)
525 .append(StringUtils.join(groupByColumns.iterator(), ", "));
526 }
527 if (having != null)
528 {
529 stringBuilder.append(HAVING)
530 .append(having);
531 }
532 if (!orderByColumns.isEmpty())
533 {
534 stringBuilder.append(ORDER_BY)
535 .append(StringUtils.join(orderByColumns.iterator(), ", "));
536 }
537 if (limit != null)
538 {
539 stringBuilder.append(LIMIT)
540 .append(limit);
541 }
542 if (offset != null)
543 {
544 stringBuilder.append(OFFSET)
545 .append(offset);
546 }
547 if (rowcount != null)
548 {
549 stringBuilder.append(SET_ROWCOUNT)
550 .append("0");
551 }
552 if (postLimit != null)
553 {
554 stringBuilder.append(postLimit);
555 }
556
557 return stringBuilder;
558 }
559
560 /**
561 * Returns a String to display this query.
562 *
563 * @return the SQL query for display.
564 *
565 * @exception TorqueException Trouble creating the query string.
566 */
567 public String getDisplayString()
568 throws TorqueException
569 {
570 StringBuilder stringBuilder = new StringBuilder();
571 toStringBuilder(stringBuilder);
572 stringBuilder.append(" Replacements: [");
573 boolean first = true;
574 for (Object replacement : getPreparedStatementReplacements())
575 {
576 if (!first)
577 {
578 stringBuilder.append(",");
579 }
580 stringBuilder.append(replacement);
581 first = false;
582 }
583 stringBuilder.append("]");
584 return stringBuilder.toString();
585 }
586
587 }