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
9
10
11
12
13
14
15
16
17
18
19
20
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
53
54
55
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 > 0
83 * or an LIMIT limit clause if limit is > 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 > 0
116 * or an LIMIT limit clause if limit is > 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 }