View Javadoc

1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2004 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 org.apache.commons.lang.StringUtils;
20  
21  /***
22   * Used to assemble an SQL SELECT query.  Attributes exist for the
23   * sections of a SELECT: modifiers, columns, from clause, where
24   * clause, and order by clause.  The various parts of the query are
25   * appended to buffers which only accept unique entries.  This class
26   * is used primarily by BasePeer.
27   *
28   * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a>
29   * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a>
30   * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
31   * @version $Id: Query.java,v 1.12.2.3 2004/08/26 17:01:25 henning Exp $
32   */
33  public class Query
34  {
35      private static final String SELECT = "SELECT ";
36      private static final String FROM = " FROM ";
37      private static final String WHERE = " WHERE ";
38      private static final String AND = " AND ";
39      private static final String ORDER_BY = " ORDER BY ";
40      private static final String GROUP_BY = " GROUP BY ";
41      private static final String HAVING = " HAVING ";
42      private static final String LIMIT = " LIMIT ";
43      private static final String ROWCOUNT = " SET ROWCOUNT ";
44  
45      private UniqueList selectModifiers = new UniqueList();
46      private UniqueList selectColumns = new UniqueList();
47      private UniqueList fromTables = new UniqueList();
48      private UniqueList whereCriteria = new UniqueList();
49      private UniqueList orderByColumns = new UniqueList();
50      private UniqueList groupByColumns = new UniqueList();
51      private String having;
52      private String limit;
53      private String preLimit;
54      private String postLimit;
55      private String rowcount;
56  
57      /***
58       * Retrieve the modifier buffer in order to add modifiers to this
59       * query.  E.g. DISTINCT and ALL.
60       *
61       * @return An UniqueList used to add modifiers.
62       */
63      public UniqueList getSelectModifiers()
64      {
65          return selectModifiers;
66      }
67  
68      /***
69       * Set the modifiers. E.g. DISTINCT and ALL.
70       *
71       * @param modifiers the modifiers
72       */
73      public void setSelectModifiers(UniqueList modifiers)
74      {
75          selectModifiers = modifiers;
76      }
77  
78      /***
79       * Retrieve the columns buffer in order to specify which columns
80       * are returned in this query.
81       *
82       *
83       * @return An UniqueList used to add columns to be selected.
84       */
85      public UniqueList getSelectClause()
86      {
87          return selectColumns;
88      }
89  
90      /***
91       * Set the columns.
92       *
93       * @param columns columns list
94       */
95      public void setSelectClause(UniqueList columns)
96      {
97          selectColumns = columns;
98      }
99  
100     /***
101      * Retrieve the from buffer in order to specify which tables are
102      * involved in this query.
103      *
104      * @return An UniqueList used to add tables involved in the query.
105      */
106     public UniqueList getFromClause()
107     {
108         return fromTables;
109     }
110 
111     /***
112      * Set the from clause.
113      *
114      * @param tables the tables
115      */
116     public void setFromClause(UniqueList tables)
117     {
118         fromTables = tables;
119     }
120 
121     /***
122      * Retrieve the where buffer in order to specify the selection
123      * criteria E.g. column_a=3.  Expressions added to the buffer will
124      * be separated using AND.
125      *
126      * @return An UniqueList used to add selection criteria.
127      */
128     public UniqueList getWhereClause()
129     {
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     {
140         whereCriteria = where;
141     }
142 
143     /***
144      * Retrieve the order by columns buffer in order to specify which
145      * columns are used to sort the results of the query.
146      *
147      * @return An UniqueList used to add columns to sort on.
148      */
149     public UniqueList getOrderByClause()
150     {
151         return orderByColumns;
152     }
153 
154     /***
155      * Retrieve the group by columns buffer in order to specify which
156      * columns are used to group the results of the query.
157      *
158      * @return An UniqueList used to add columns to group on.
159      */
160     public UniqueList getGroupByClause()
161     {
162         return groupByColumns;
163     }
164 
165     /***
166      * Set the having clause.  This is used to restrict which rows
167      * are returned.
168      *
169      * @param having A String.
170      */
171     public void setHaving(String having)
172     {
173         this.having = having;
174     }
175 
176     /***
177      * Set the limit number.  This is used to limit the number of rows
178      * returned by a query, and the row where the resultset starts.
179      *
180      * @param limit A String.
181      */
182     public void setLimit(String limit)
183     {
184         this.limit = limit;
185     }
186 
187     /***
188      * Get the Pre limit String. Oracle and DB2 want to encapsulate
189      * a query into a subquery for limiting.
190      *
191      * @return A String with the preLimit.
192      */
193     public void setPreLimit(String preLimit)
194     {
195         this.preLimit = preLimit;
196     }
197 
198     /***
199      * Set the Post limit String. Oracle and DB2 want to encapsulate
200      * a query into a subquery for limiting.
201      *
202      * @return A String with the preLimit.
203      */
204     public void setPostLimit(String postLimit)
205     {
206         this.postLimit = postLimit;
207     }
208 
209     /***
210      * Set the rowcount number.  This is used to limit the number of
211      * rows returned by Sybase and MS SQL/Server.
212      *
213      * @param rowcount A String.
214      */
215     public void setRowcount(String rowcount)
216     {
217         this.rowcount = rowcount;
218     }
219 
220     /***
221      * Get the having clause.  This is used to restrict which
222      * rows are returned based on some condition.
223      *
224      * @return A String that is the having clause.
225      */
226     public String getHaving()
227     {
228         return having;
229     }
230 
231     /***
232      * Get the limit number.  This is used to limit the number of
233      * returned by a query in Postgres.
234      *
235      * @return A String with the limit.
236      */
237     public String getLimit()
238     {
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     {
250         return postLimit;
251     }
252 
253     /***
254      * Get the Pre limit String. Oracle and DB2 want to encapsulate
255      * a query into a subquery for limiting.
256      *
257      * @return A String with the preLimit.
258      */
259     public String getPreLimit()
260     {
261         return preLimit;
262     }
263 
264     /***
265      * True if this query has a limit clause registered.
266      *
267      * @return true if a limit clause exists.
268      */
269     public boolean hasLimit()
270     {
271         return ((preLimit != null)
272                 || (postLimit != null)
273                 || (limit != null));
274     }
275 
276     /***
277      * Get the rowcount number.  This is used to limit the number of
278      * returned by a query in Sybase and MS SQL/Server.
279      *
280      * @return A String with the row count.
281      */
282     public String getRowcount()
283     {
284         return rowcount;
285     }
286 
287     /***
288      * Outputs the query statement.
289      *
290      * @return A String with the query statement.
291      */
292     public String toString()
293     {
294         return toStringBuffer(new StringBuffer()).toString();
295     }
296 
297     public StringBuffer toStringBuffer(StringBuffer stmt)
298     {
299         if (preLimit != null)
300         {
301             stmt.append(preLimit);
302         }
303 
304         if (rowcount != null)
305         {
306             stmt.append(ROWCOUNT)
307                 .append(rowcount)
308                 .append(" ");
309         }
310         stmt.append(SELECT)
311             .append(StringUtils.join(selectModifiers.iterator(), " "))
312             .append(StringUtils.join(selectColumns.iterator(), ", "))
313             .append(FROM)
314             .append(StringUtils.join(fromTables.iterator(), ", "));
315         if (!whereCriteria.isEmpty())
316         {
317             stmt.append(WHERE)
318                 .append(StringUtils.join(whereCriteria.iterator(), AND));
319         }
320         if (!groupByColumns.isEmpty())
321         {
322             stmt.append(GROUP_BY)
323                 .append(StringUtils.join(groupByColumns.iterator(), ", "));
324         }
325         if (having != null)
326         {
327             stmt.append(HAVING)
328                 .append(having);
329         }
330         if (!orderByColumns.isEmpty())
331         {
332             stmt.append(ORDER_BY)
333                 .append(StringUtils.join(orderByColumns.iterator(), ", "));
334         }
335         if (limit != null)
336         {
337             stmt.append(LIMIT)
338                 .append(limit);
339         }
340         if (rowcount != null)
341         {
342             stmt.append(ROWCOUNT)
343                 .append("0");
344         }
345         if (postLimit != null)
346         {
347             stmt.append(postLimit);
348         }
349             
350         return stmt;
351     }
352 }