001: package org.apache.torque.util;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.util.Iterator;
023:
024: import org.apache.commons.lang.StringUtils;
025:
026: /**
027: * Used to assemble an SQL SELECT query. Attributes exist for the
028: * sections of a SELECT: modifiers, columns, from clause, where
029: * clause, and order by clause. The various parts of the query are
030: * appended to buffers which only accept unique entries. This class
031: * is used primarily by BasePeer.
032: *
033: * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
034: * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a>
035: * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
036: * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
037: * @version $Id: Query.java 522044 2007-03-24 16:00:57Z tfischer $
038: */
039: public class Query {
040: private static final String SELECT = "SELECT ";
041: private static final String FROM = " FROM ";
042: private static final String WHERE = " WHERE ";
043: private static final String AND = " AND ";
044: private static final String ORDER_BY = " ORDER BY ";
045: private static final String GROUP_BY = " GROUP BY ";
046: private static final String HAVING = " HAVING ";
047: private static final String LIMIT = " LIMIT ";
048: private static final String OFFSET = " OFFSET ";
049: private static final String ROWCOUNT = " SET ROWCOUNT ";
050:
051: private UniqueList selectModifiers = new UniqueList();
052: private UniqueList selectColumns = new UniqueList();
053: private UniqueList fromTables = new UniqueList();
054: private UniqueList whereCriteria = new UniqueList();
055: private UniqueList orderByColumns = new UniqueList();
056: private UniqueList groupByColumns = new UniqueList();
057: private String having;
058: private String limit;
059: private String preLimit;
060: private String postLimit;
061: private String offset;
062: private String rowcount;
063:
064: /**
065: * Retrieve the modifier buffer in order to add modifiers to this
066: * query. E.g. DISTINCT and ALL.
067: *
068: * @return An UniqueList used to add modifiers.
069: */
070: public UniqueList getSelectModifiers() {
071: return selectModifiers;
072: }
073:
074: /**
075: * Set the modifiers. E.g. DISTINCT and ALL.
076: *
077: * @param modifiers the modifiers
078: */
079: public void setSelectModifiers(UniqueList modifiers) {
080: selectModifiers = modifiers;
081: }
082:
083: /**
084: * Retrieve the columns buffer in order to specify which columns
085: * are returned in this query.
086: *
087: *
088: * @return An UniqueList used to add columns to be selected.
089: */
090: public UniqueList getSelectClause() {
091: return selectColumns;
092: }
093:
094: /**
095: * Set the columns.
096: *
097: * @param columns columns list
098: */
099: public void setSelectClause(UniqueList columns) {
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: return fromTables;
111: }
112:
113: /**
114: * Set the from clause.
115: *
116: * @param tables the tables
117: */
118: public void setFromClause(UniqueList tables) {
119: fromTables = tables;
120: }
121:
122: /**
123: * Retrieve the where buffer in order to specify the selection
124: * criteria E.g. column_a=3. Expressions added to the buffer will
125: * be separated using AND.
126: *
127: * @return An UniqueList used to add selection criteria.
128: */
129: public UniqueList getWhereClause() {
130: return whereCriteria;
131: }
132:
133: /**
134: * Set the where clause.
135: *
136: * @param where where clause
137: */
138: public void setWhereClause(UniqueList where) {
139: whereCriteria = where;
140: }
141:
142: /**
143: * Retrieve the order by columns buffer in order to specify which
144: * columns are used to sort the results of the query.
145: *
146: * @return An UniqueList used to add columns to sort on.
147: */
148: public UniqueList getOrderByClause() {
149: return orderByColumns;
150: }
151:
152: /**
153: * Retrieve the group by columns buffer in order to specify which
154: * columns are used to group the results of the query.
155: *
156: * @return An UniqueList used to add columns to group on.
157: */
158: public UniqueList getGroupByClause() {
159: return groupByColumns;
160: }
161:
162: /**
163: * Set the having clause. This is used to restrict which rows
164: * are returned.
165: *
166: * @param having A String.
167: */
168: public void setHaving(String having) {
169: this .having = having;
170: }
171:
172: /**
173: * Set the limit number. This is used to limit the number of rows
174: * returned by a query.
175: *
176: * @param limit A String.
177: */
178: public void setLimit(String limit) {
179: this .limit = limit;
180: }
181:
182: /**
183: * Get the Pre limit String. Oracle and DB2 want to encapsulate
184: * a query into a subquery for limiting.
185: *
186: * @param preLimit A String with the preLimit.
187: */
188: public void setPreLimit(String preLimit) {
189: this .preLimit = preLimit;
190: }
191:
192: /**
193: * Set the Post limit String. Oracle and DB2 want to encapsulate
194: * a query into a subquery for limiting.
195: *
196: * @param postLimit A String with the postLimit.
197: */
198: public void setPostLimit(String postLimit) {
199: this .postLimit = postLimit;
200: }
201:
202: /**
203: * Set the offset number. This is used to set the row where the
204: * resultset starts.
205: *
206: * @param offset A String.
207: */
208: public void setOffset(String offset) {
209: this .offset = offset;
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: this .rowcount = rowcount;
220: }
221:
222: /**
223: * Get the having clause. This is used to restrict which
224: * rows are returned based on some condition.
225: *
226: * @return A String that is the having clause.
227: */
228: public String getHaving() {
229: return having;
230: }
231:
232: /**
233: * Get the limit number. This is used to limit the number of
234: * returned by a query in Postgres.
235: *
236: * @return A String with the limit.
237: */
238: public String getLimit() {
239: return limit;
240: }
241:
242: /**
243: * Get the Post limit String. Oracle and DB2 want to encapsulate
244: * a query into a subquery for limiting.
245: *
246: * @return A String with the preLimit.
247: */
248: public String getPostLimit() {
249: return postLimit;
250: }
251:
252: /**
253: * Get the Pre limit String. Oracle and DB2 want to encapsulate
254: * a query into a subquery for limiting.
255: *
256: * @return A String with the preLimit.
257: */
258: public String getPreLimit() {
259: return preLimit;
260: }
261:
262: /**
263: * Get the offset number. This is used to set the row where the
264: * resultset starts.
265: *
266: * @return A String with the offset, or null if no offset is set.
267: */
268: public String getOffset() {
269: return offset;
270: }
271:
272: /**
273: * True if this query has a limit clause registered.
274: *
275: * @return true if a limit clause exists.
276: */
277: public boolean hasLimit() {
278: return ((preLimit != null) || (postLimit != null) || (limit != null));
279: }
280:
281: /**
282: * Get the rowcount number. This is used to limit the number of
283: * returned by a query in Sybase and MS SQL/Server.
284: *
285: * @return A String with the row count.
286: */
287: public String getRowcount() {
288: return rowcount;
289: }
290:
291: /**
292: * Outputs the query statement.
293: *
294: * @return A String with the query statement.
295: */
296: public String toString() {
297: return toStringBuffer(new StringBuffer()).toString();
298: }
299:
300: public StringBuffer toStringBuffer(StringBuffer stmt) {
301: if (preLimit != null) {
302: stmt.append(preLimit);
303: }
304:
305: if (rowcount != null) {
306: stmt.append(ROWCOUNT).append(rowcount).append(" ");
307: }
308: stmt.append(SELECT).append(
309: StringUtils.join(selectModifiers.iterator(), " "))
310: .append(
311: StringUtils
312: .join(selectColumns.iterator(), ", "))
313: .append(FROM);
314:
315: boolean first = true;
316: for (Iterator it = fromTables.iterator(); it.hasNext();) {
317: FromElement fromElement = (FromElement) it.next();
318:
319: if (!first && fromElement.getJoinCondition() == null) {
320: stmt.append(", ");
321: }
322: first = false;
323: stmt.append(fromElement.toString());
324: }
325:
326: if (!whereCriteria.isEmpty()) {
327: stmt.append(WHERE).append(
328: StringUtils.join(whereCriteria.iterator(), AND));
329: }
330: if (!groupByColumns.isEmpty()) {
331: stmt.append(GROUP_BY).append(
332: StringUtils.join(groupByColumns.iterator(), ", "));
333: }
334: if (having != null) {
335: stmt.append(HAVING).append(having);
336: }
337: if (!orderByColumns.isEmpty()) {
338: stmt.append(ORDER_BY).append(
339: StringUtils.join(orderByColumns.iterator(), ", "));
340: }
341: if (limit != null) {
342: stmt.append(LIMIT).append(limit);
343: }
344: if (offset != null) {
345: stmt.append(OFFSET).append(offset);
346: }
347: if (rowcount != null) {
348: stmt.append(ROWCOUNT).append("0");
349: }
350: if (postLimit != null) {
351: stmt.append(postLimit);
352: }
353:
354: return stmt;
355: }
356:
357: /**
358: * This class describes an Element in the From-part of a SQL clause.
359: * It must contain the name of the database table.
360: * It might contain an alias for the table name, a join type
361: * and a join condition.
362: * The class is package visible, as it is used in BasePeer,
363: * and is immutable.
364: */
365: public static class FromElement {
366:
367: /** the tablename, might contain an appended alias name */
368: private String tableName = null;
369:
370: /** the type of the join, e.g. SqlEnum.LEFT_JOIN */
371: private SqlEnum joinType = null;
372:
373: /** the join condition, e.g. table_a.id = table_b.a_id */
374: private String joinCondition = null;
375:
376: /**
377: * Constructor
378: * @param tableName the tablename, might contain an appended alias name
379: * e.g. <br />
380: * table_1<br />
381: * table_1 alias_for_table_1
382: * @param joinType the type of the join, e.g. SqlEnum.LEFT_JOIN,
383: * or null if no excplicit join is wanted
384: * @param joinCondition the join condition,
385: * e.g. table_a.id = table_b.a_id,
386: * or null if no explicit join is wanted
387: * (In this case, the join condition is appended to the
388: * whereClause instead)
389: */
390: public FromElement(String tableName, SqlEnum joinType,
391: String joinCondition) {
392: this .tableName = tableName;
393: this .joinType = joinType;
394: this .joinCondition = joinCondition;
395: }
396:
397: /**
398: * @return the join condition, e.g. table_a.id = table_b.a_id,
399: * or null if the join is not an explicit join
400: */
401: public String getJoinCondition() {
402: return joinCondition;
403: }
404:
405: /**
406: * @return the type of the join, e.g. SqlEnum.LEFT_JOIN,
407: * or null if the join is not an explicit join
408: */
409: public SqlEnum getJoinType() {
410: return joinType;
411: }
412:
413: /**
414: * @return the tablename, might contain an appended alias name,
415: * e.g. <br />
416: * table_1<br />
417: * table_1 alias_for_table_1
418: *
419: */
420: public String getTableName() {
421: return tableName;
422: }
423:
424: /**
425: * Returns a SQL representation of the element
426: * @return a SQL representation of the element
427: */
428: public String toString() {
429: StringBuffer result = new StringBuffer();
430: if (joinType != null) {
431: result.append(joinType);
432: }
433: result.append(tableName);
434: if (joinCondition != null) {
435: result.append(SqlEnum.ON);
436: result.append(joinCondition);
437: }
438: return result.toString();
439: }
440: } // end of inner class FromElement
441: }
|