View Javadoc

1   package org.apache.torque.util;
2   
3   import org.apache.torque.Torque;
4   import org.apache.torque.TorqueException;
5   import org.apache.torque.adapter.DB;
6   
7   /*
8    * Copyright 2001-2004 The Apache Software Foundation.
9    *
10   * Licensed under the Apache License, Version 2.0 (the "License")
11   * you may not use this file except in compliance with the License.
12   * You may obtain a copy of the License at
13   *
14   *     http://www.apache.org/licenses/LICENSE-2.0
15   *
16   * Unless required by applicable law or agreed to in writing, software
17   * distributed under the License is distributed on an "AS IS" BASIS,
18   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
19   * See the License for the specific language governing permissions and
20   * limitations under the License.
21   */
22  
23  /***
24   * Factored out all the various "How to generate offset and limit
25   * for my personal database" from the BasePeer. And tried to get
26   * some sense it this.
27   *
28   * @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
29   * @version $Id: LimitHelper.java,v 1.2 2005/01/31 19:43:52 tfischer Exp $
30   */
31  
32  public abstract class LimitHelper
33  {
34  
35      /***
36       * Update the Query object according to the limiting information
37       * available in the Criteria
38       *
39       * @param critera the Criteria to read
40       * @param query The query object to update
41       */
42      public static final void buildLimit(Criteria criteria, Query query)
43          throws TorqueException
44      {
45          int limit = criteria.getLimit();
46          int offset = criteria.getOffset();
47  
48          DB db = Torque.getDB(criteria.getDbName());
49  
50          if (offset > 0 || limit > 0)
51          {
52              // If we hit a database type, that is able to do native
53              // limiting, we must set the criteria values to -1 and 0
54              // afterwards. Reason is, that else theexecuteQuery 
55              // method tries to do the limiting using Village
56              //
57              switch (db.getLimitStyle())
58              {
59              case DB.LIMIT_STYLE_MYSQL :
60                  LimitHelper.generateMySQLLimits(query, offset, limit);
61                  break;
62              case DB.LIMIT_STYLE_POSTGRES :
63                  LimitHelper.generatePostgreSQLLimits(query, offset, limit);
64                  break;
65              case DB.LIMIT_STYLE_ORACLE :
66                  LimitHelper.generateOracleLimits(query, offset, limit);
67                  break;
68              case DB.LIMIT_STYLE_DB2 :
69                  LimitHelper.generateDB2Limits(query, offset, limit);
70                  break;
71              default:
72                  if (db.supportsNativeLimit())
73                  {
74                      query.setLimit(String.valueOf(limit));
75                  }
76                  break;
77              }
78          }
79      }
80  
81      /***
82       * Generate a LIMIT offset, limit clause if offset &gt; 0
83       * or an LIMIT limit clause if limit is &gt; 0 and offset
84       * is 0.
85       *
86       * @param query The query to modify
87       * @param offset the offset Value
88       * @param limit the limit Value
89       */
90      private static final void generateMySQLLimits(Query query,
91              int offset, int limit)
92      {
93          StringBuffer limitStringBuffer = new StringBuffer();
94          
95          if (offset > 0)
96          {
97              limitStringBuffer.append(offset)
98                      .append(", ")
99                      .append(limit);
100         }
101         else
102         {
103             if (limit > 0)
104             {
105                 limitStringBuffer.append(limit);
106             }
107         }
108 
109         query.setLimit(limitStringBuffer.toString());
110         query.setPreLimit(null);
111         query.setPostLimit(null);
112     }
113 
114     /***
115      * Generate a LIMIT limit OFFSET offset clause if offset &gt; 0
116      * or an LIMIT limit clause if limit is &gt; 0 and offset
117      * is 0.
118      *
119      * @param query The query to modify
120      * @param offset the offset Value
121      * @param limit the limit Value
122      */
123     private static final void generatePostgreSQLLimits(Query query,
124             int offset, int limit)
125     {
126         StringBuffer limitStringBuffer = new StringBuffer();
127 
128         if (offset > 0)
129         {
130             limitStringBuffer.append(limit)
131                     .append(" offset ")
132                     .append(offset);
133         }
134         else
135         {
136             if (limit > 0)
137             {
138                 limitStringBuffer.append(limit);
139             }
140         }
141 
142         query.setLimit(limitStringBuffer.toString());
143         query.setPreLimit(null);
144         query.setPostLimit(null);
145     }
146 
147     /***
148      * Build Oracle-style query with limit or offset.
149      * If the original SQL is in variable: query then the requlting
150      * SQL looks like this:
151      * <pre>
152      * SELECT B.* FROM (
153      *          SELECT A.*, rownum as TORQUE$ROWNUM FROM (
154      *                  query
155      *          ) A
156      *     ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
157      *     <= offset + limit
158      * </pre>
159      * 
160      * @param query The query to modify
161      * @param offset the offset Value
162      * @param limit the limit Value
163      */ 
164     private static final void generateOracleLimits(Query query,
165             int offset, int limit)
166     {
167         StringBuffer preLimit = new StringBuffer()
168                 .append("SELECT B.* FROM ( ")
169                 .append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
170 
171         StringBuffer postLimit = new StringBuffer()
172                 .append(" ) A ")
173                 .append(" ) B WHERE ");
174 
175         if (offset > 0)
176         {
177             postLimit.append(" B.TORQUE$ROWNUM > ")
178                     .append(offset);
179 
180             if (limit > 0)
181             {
182                 postLimit.append(" AND B.TORQUE$ROWNUM <= ")
183                         .append(offset + limit);
184             }
185         }
186         else
187         {
188             postLimit.append(" B.TORQUE$ROWNUM <= ")
189                     .append(limit);
190         }
191 
192         query.setPreLimit(preLimit.toString());
193         query.setPostLimit(postLimit.toString());
194         query.setLimit(null);
195     }
196 
197     /***
198      * Build DB2 (OLAP) -style query with limit or offset.
199      * If the original SQL is in variable: query then the requlting
200      * SQL looks like this:
201      * <pre>
202      * SELECT B.* FROM (
203      *          SELECT A.*, row_number() over() as TORQUE$ROWNUM FROM (
204      *                  query
205      *          ) A
206      *     ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
207      *     <= offset + limit
208      * </pre>
209      * 
210      * @param query The query to modify
211      * @param offset the offset Value
212      * @param limit the limit Value
213      */ 
214     private static final void generateDB2Limits(Query query,
215             int offset, int limit)
216     {
217         StringBuffer preLimit = new StringBuffer()
218                 .append("SELECT B.* FROM ( ")
219                 .append("SELECT A.*, row_number() over() AS TORQUE$ROWNUM FROM ( ");
220 
221         StringBuffer postLimit = new StringBuffer()
222                 .append(" ) A ")
223                 .append(" ) B WHERE ");
224 
225         if (offset > 0)
226         {
227             postLimit.append(" B.TORQUE$ROWNUM > ")
228                     .append(offset);
229 
230             if (limit > 0)
231             {
232                 postLimit.append(" AND B.TORQUE$ROWNUM <= ")
233                         .append(offset + limit);
234             }
235         }
236         else
237         {
238             postLimit.append(" B.TORQUE$ROWNUM <= ")
239                     .append(limit);
240         }
241 
242         query.setPreLimit(preLimit.toString());
243         query.setPostLimit(postLimit.toString());
244         query.setLimit(null);
245     }
246 }