View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2005 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
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     } // end of inner class FromElement
461 }