KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > torque > util > LimitHelper


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 JavaDoc limitStringBuffer = new StringBuffer JavaDoc();
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 JavaDoc limitStringBuffer = new StringBuffer JavaDoc();
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 JavaDoc preLimit = new StringBuffer JavaDoc()
168                 .append("SELECT B.* FROM ( ")
169                 .append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
170
171         StringBuffer JavaDoc postLimit = new StringBuffer JavaDoc()
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 JavaDoc preLimit = new StringBuffer JavaDoc()
218                 .append("SELECT B.* FROM ( ")
219                 .append("SELECT A.*, row_number() over() AS TORQUE$ROWNUM FROM ( ");
220
221         StringBuffer JavaDoc postLimit = new StringBuffer JavaDoc()
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 }
247
Popular Tags