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