1 package org.apache.torque.util;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
54
55
56
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 > 0
84 * or an LIMIT limit clause if limit is > 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 > 0
117 * or an LIMIT limit clause if limit is > 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 }