Query


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