View Javadoc

1   /*
2    * Copyright 2006-2012 the original author or authors.
3    *
4    * Licensed under the Apache License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    *
8    *      http://www.apache.org/licenses/LICENSE-2.0
9    *
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  
17  package org.springframework.batch.item.database.support;
18  
19  import java.util.ArrayList;
20  import java.util.List;
21  import java.util.Map;
22  import java.util.Map.Entry;
23  
24  import org.springframework.batch.item.database.Order;
25  import org.springframework.util.StringUtils;
26  
27  /**
28   * Utility class that generates the actual SQL statements used by query
29   * providers.
30   * 
31   * @author Thomas Risberg
32   * @author Dave Syer
33   * @author Michael Minella
34   * @since 2.0
35   */
36  public class SqlPagingQueryUtils {
37  
38  	/**
39  	 * Generate SQL query string using a LIMIT clause
40  	 * 
41  	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
42  	 * implementation specifics
43  	 * @param remainingPageQuery is this query for the ramining pages (true) as
44  	 * opposed to the first page (false)
45  	 * @param limitClause the implementation specific limit clause to be used
46  	 * @return the generated query
47  	 */
48  	public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
49  			String limitClause) {
50  		StringBuilder sql = new StringBuilder();
51  		sql.append("SELECT ").append(provider.getSelectClause());
52  		sql.append(" FROM ").append(provider.getFromClause());
53  		buildWhereClause(provider, remainingPageQuery, sql);
54  		buildGroupByClause(provider, sql);
55  		sql.append(" ORDER BY ").append(buildSortClause(provider));
56  		sql.append(" " + limitClause);
57  
58  		return sql.toString();
59  	}
60  
61  	/**
62  	 * Generate SQL query string using a LIMIT clause
63  	 * 
64  	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
65  	 * implementation specifics
66  	 * @param remainingPageQuery is this query for the ramining pages (true) as
67  	 * opposed to the first page (false)
68  	 * @param limitClause the implementation specific limit clause to be used
69  	 * @return the generated query
70  	 */
71  	public static String generateLimitGroupedSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
72  			String limitClause) {
73  		StringBuilder sql = new StringBuilder();
74  		sql.append("SELECT * ");
75  		sql.append(" FROM (");
76  		sql.append("SELECT ").append(provider.getSelectClause());
77  		sql.append(" FROM ").append(provider.getFromClause());
78  		sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
79  		buildGroupByClause(provider, sql);
80  		sql.append(") AS MAIN_QRY ");
81  		sql.append("WHERE ");
82  		buildSortConditions(provider, sql);
83  		sql.append(" ORDER BY ").append(buildSortClause(provider));
84  		sql.append(" " + limitClause);
85  
86  		return sql.toString();
87  	}
88  
89  	/**
90  	 * Generate SQL query string using a TOP clause
91  	 * 
92  	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
93  	 * implementation specifics
94  	 * @param remainingPageQuery is this query for the ramining pages (true) as
95  	 * opposed to the first page (false)
96  	 * @param topClause the implementation specific top clause to be used
97  	 * @return the generated query
98  	 */
99  	public static String generateTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
100 			String topClause) {
101 		StringBuilder sql = new StringBuilder();
102 		sql.append("SELECT ").append(topClause).append(" ").append(provider.getSelectClause());
103 		sql.append(" FROM ").append(provider.getFromClause());
104 		buildWhereClause(provider, remainingPageQuery, sql);
105 		buildGroupByClause(provider, sql);
106 		sql.append(" ORDER BY ").append(buildSortClause(provider));
107 
108 		return sql.toString();
109 	}
110 
111 	/**
112 	 * Generate SQL query string using a TOP clause
113 	 * 
114 	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
115 	 * implementation specifics
116 	 * @param remainingPageQuery is this query for the ramining pages (true) as
117 	 * opposed to the first page (false)
118 	 * @param topClause the implementation specific top clause to be used
119 	 * @return the generated query
120 	 */
121 	public static String generateGroupedTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
122 			String topClause) {
123 		StringBuilder sql = new StringBuilder();
124 		sql.append("SELECT ").append(topClause).append(" * FROM (");
125 		sql.append("SELECT ").append(provider.getSelectClause());
126 		sql.append(" FROM ").append(provider.getFromClause());
127 		sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
128 		buildGroupByClause(provider, sql);
129 		sql.append(") AS MAIN_QRY ");
130 		sql.append("WHERE ");
131 		buildSortConditions(provider, sql);
132 		sql.append(" ORDER BY ").append(buildSortClause(provider));
133 
134 		return sql.toString();
135 	}
136 
137 	/**
138 	 * Generate SQL query string using a ROW_NUM condition
139 	 * 
140 	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
141 	 * implementation specifics
142 	 * @param remainingPageQuery is this query for the remaining pages (true) as
143 	 * opposed to the first page (false)
144 	 * @param rowNumClause the implementation specific row num clause to be used
145 	 * @return the generated query
146 	 */
147 	public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
148 			String rowNumClause) {
149 
150 		return generateRowNumSqlQuery(provider, provider.getSelectClause(), remainingPageQuery, rowNumClause);
151 
152 	}
153 
154 	/**
155 	 * Generate SQL query string using a ROW_NUM condition
156 	 * 
157 	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
158 	 * implementation specifics
159 	 * @param remainingPageQuery is this query for the remaining pages (true) as
160 	 * opposed to the first page (false)
161 	 * @param rowNumClause the implementation specific row num clause to be used
162 	 * @return the generated query
163 	 */
164 	public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, String selectClause,
165 			boolean remainingPageQuery, String rowNumClause) {
166 		StringBuilder sql = new StringBuilder();
167 		sql.append("SELECT * FROM (SELECT ").append(selectClause);
168 		sql.append(" FROM ").append(provider.getFromClause());
169 		sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
170 		buildGroupByClause(provider, sql);
171 		sql.append(" ORDER BY ").append(buildSortClause(provider));
172 		sql.append(") WHERE ").append(rowNumClause);
173 		if(remainingPageQuery) {
174 			sql.append(" AND ");
175 			buildSortConditions(provider, sql);
176 		}
177 
178 		return sql.toString();
179 
180 	}
181 
182 	public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider,
183 			String selectClause, boolean remainingPageQuery, String rowNumClause) {
184 		return generateRowNumSqlQueryWithNesting(provider, selectClause, selectClause, remainingPageQuery, rowNumClause);
185 	}
186 
187 	public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider,
188 			String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) {
189 
190 		StringBuilder sql = new StringBuilder();
191 		sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause)
192 				.append(", ").append(StringUtils.hasText(provider.getGroupClause()) ? "MIN(ROWNUM) as TMP_ROW_NUM" : "ROWNUM as TMP_ROW_NUM");
193 		sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause());
194 		buildWhereClause(provider, remainingPageQuery, sql);
195 		buildGroupByClause(provider, sql);
196 		sql.append(" ORDER BY ").append(buildSortClause(provider));
197 		sql.append(")) WHERE ").append(rowNumClause);
198 
199 		return sql.toString();
200 
201 	}
202 
203 	/**
204 	 * Generate SQL query string using a LIMIT clause
205 	 * 
206 	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
207 	 * implementation specifics
208 	 * @param limitClause the implementation specific top clause to be used
209 	 * @return the generated query
210 	 */
211 	public static String generateLimitJumpToQuery(AbstractSqlPagingQueryProvider provider, String limitClause) {
212 		StringBuilder sql = new StringBuilder();
213 		sql.append("SELECT ").append(buildSortKeySelect(provider));
214 		sql.append(" FROM ").append(provider.getFromClause());
215 		sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
216 		buildGroupByClause(provider, sql);
217 		sql.append(" ORDER BY ").append(buildSortClause(provider));
218 		sql.append(" " + limitClause);
219 
220 		return sql.toString();
221 	}
222 
223 	/**
224 	 * Generate SQL query string using a TOP clause
225 	 * 
226 	 * @param provider {@link AbstractSqlPagingQueryProvider} providing the
227 	 * implementation specifics
228 	 * @param topClause the implementation specific top clause to be used
229 	 * @return the generated query
230 	 */
231 	public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) {
232 		StringBuilder sql = new StringBuilder();
233 		sql.append("SELECT ").append(topClause).append(" ").append(buildSortKeySelect(provider));
234 		sql.append(" FROM ").append(provider.getFromClause());
235 		sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
236 		buildGroupByClause(provider, sql);
237 		sql.append(" ORDER BY ").append(buildSortClause(provider));
238 
239 		return sql.toString();
240 	}
241 
242 	/**
243 	 * Generates ORDER BY attributes based on the sort keys.
244 	 * 
245 	 * @param provider
246 	 * @return a String that can be appended to an ORDER BY clause.
247 	 */
248 	public static String buildSortClause(AbstractSqlPagingQueryProvider provider) {
249 		StringBuilder builder = new StringBuilder();
250 		String prefix = "";
251 		
252 		for (Map.Entry<String, Order> sortKey : provider.getSortKeys().entrySet()) {
253 			builder.append(prefix);
254 			
255 			prefix = ", ";
256 			
257 			builder.append(sortKey.getKey());
258 			
259 			if(sortKey.getValue() != null && sortKey.getValue() == Order.DESCENDING) {
260 				builder.append(" DESC");
261 			}
262 			else {
263 				builder.append(" ASC");
264 			}
265 		}
266 		
267 		return builder.toString();
268 	}
269 
270 	/**
271 	 * Appends the where conditions required to query for the subsequent pages.
272 	 * 
273 	 * @param provider
274 	 * @param sql
275 	 */
276 	public static void buildSortConditions(
277 			AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
278 		List<Map.Entry<String, Order>> keys = new ArrayList<Map.Entry<String,Order>>(provider.getSortKeys().entrySet());
279 		List<String> clauses = new ArrayList<String>();
280 		
281 		for(int i = 0; i < keys.size(); i++) {
282 			StringBuilder clause = new StringBuilder();
283 			
284 			String prefix = "";
285 			for(int j = 0; j < i; j++) {
286 				clause.append(prefix);
287 				prefix = " AND ";
288 				Entry<String, Order> entry = keys.get(j);
289 				clause.append(entry.getKey());
290 				clause.append(" = ");
291 				clause.append(provider.getSortKeyPlaceHolder(entry.getKey()));
292 			}
293 			
294 			if(clause.length() > 0) {
295 				clause.append(" AND ");
296 			}
297 			clause.append(keys.get(i).getKey());
298 			
299 			if(keys.get(i).getValue() != null && keys.get(i).getValue() == Order.DESCENDING) {
300 				clause.append(" < ");
301 			}
302 			else {
303 				clause.append(" > ");
304 			}
305 
306 			clause.append(provider.getSortKeyPlaceHolder(keys.get(i).getKey()));
307 			
308 			clauses.add(clause.toString());
309 		}
310 		
311 		sql.append("(");
312 		String prefix = "";
313 		
314 		for (String curClause : clauses) {
315 			sql.append(prefix);
316 			prefix = " OR ";
317 			sql.append("(");
318 			sql.append(curClause);
319 			sql.append(")");
320 		}
321 		sql.append(")");
322 	}
323 
324 	private static String buildSortKeySelect(AbstractSqlPagingQueryProvider provider) {
325 		StringBuilder select = new StringBuilder();
326 		
327 		String prefix = "";
328 		
329 		for (Map.Entry<String, Order> sortKey : provider.getSortKeys().entrySet()) {
330 			select.append(prefix);
331 			
332 			prefix = ", ";
333 			
334 			select.append(sortKey.getKey());
335 		}
336 		
337 		return select.toString();
338 	}
339 
340 	private static void buildWhereClause(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
341 			StringBuilder sql) {
342 		if (remainingPageQuery) {
343 			sql.append(" WHERE ");
344 			if (provider.getWhereClause() != null) {
345 				sql.append(provider.getWhereClause());
346 				sql.append(" AND ");
347 			}
348 
349 			buildSortConditions(provider, sql);
350 		}
351 		else {
352 			sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
353 		}
354 	}
355 	
356 	private static void buildGroupByClause(AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
357 		if(StringUtils.hasText(provider.getGroupClause())) {
358 			sql.append(" GROUP BY ");
359 			sql.append(provider.getGroupClause());
360 		}
361 	}
362 
363 }