org.springframework.batch.item.database.support.SqlPagingQueryUtils(源代码)

001/*
002 * Copyright 2006-2015 the original author or authors.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *      https://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016
017package org.springframework.batch.item.database.support;
018
019import java.util.ArrayList;
020import java.util.List;
021import java.util.Map;
022import java.util.Map.Entry;
023
024import org.springframework.batch.item.database.Order;
025import org.springframework.util.StringUtils;
026
027/**
028 * Utility class that generates the actual SQL statements used by query
029 * providers.
030 * 
031 * @author Thomas Risberg
032 * @author Dave Syer
033 * @author Michael Minella
034 * @since 2.0
035 */
036public class SqlPagingQueryUtils {
037
038        /**
039         * Generate SQL query string using a LIMIT clause
040         * 
041         * @param provider {@link AbstractSqlPagingQueryProvider} providing the
042         * implementation specifics
043         * @param remainingPageQuery is this query for the remaining pages (true) as
044         * opposed to the first page (false)
045         * @param limitClause the implementation specific limit clause to be used
046         * @return the generated query
047         */
048        public static String generateLimitSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
049                        String limitClause) {
050                StringBuilder sql = new StringBuilder();
051                sql.append("SELECT ").append(provider.getSelectClause());
052                sql.append(" FROM ").append(provider.getFromClause());
053                buildWhereClause(provider, remainingPageQuery, sql);
054                buildGroupByClause(provider, sql);
055                sql.append(" ORDER BY ").append(buildSortClause(provider));
056                sql.append(" " + limitClause);
057
058                return sql.toString();
059        }
060
061        /**
062         * Generate SQL query string using a LIMIT clause
063         * 
064         * @param provider {@link AbstractSqlPagingQueryProvider} providing the
065         * implementation specifics
066         * @param remainingPageQuery is this query for the remaining pages (true) as
067         * opposed to the first page (false)
068         * @param limitClause the implementation specific limit clause to be used
069         * @return the generated query
070         */
071        public static String generateLimitGroupedSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
072                        String limitClause) {
073                StringBuilder sql = new StringBuilder();
074                sql.append("SELECT * ");
075                sql.append(" FROM (");
076                sql.append("SELECT ").append(provider.getSelectClause());
077                sql.append(" FROM ").append(provider.getFromClause());
078                sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
079                buildGroupByClause(provider, sql);
080                sql.append(") AS MAIN_QRY ");
081                sql.append("WHERE ");
082                buildSortConditions(provider, sql);
083                sql.append(" ORDER BY ").append(buildSortClause(provider));
084                sql.append(" " + limitClause);
085
086                return sql.toString();
087        }
088
089        /**
090         * Generate SQL query string using a TOP clause
091         * 
092         * @param provider {@link AbstractSqlPagingQueryProvider} providing the
093         * implementation specifics
094         * @param remainingPageQuery is this query for the remaining pages (true) as
095         * opposed to the first page (false)
096         * @param topClause the implementation specific top clause to be used
097         * @return the generated query
098         */
099        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 remaining 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 selectClause {@link String} containing the select portion of the query.
160         * @param remainingPageQuery is this query for the remaining pages (true) as
161         * opposed to the first page (false)
162         * @param rowNumClause the implementation specific row num clause to be used
163         * @return the generated query
164         */
165        public static String generateRowNumSqlQuery(AbstractSqlPagingQueryProvider provider, String selectClause,
166                        boolean remainingPageQuery, String rowNumClause) {
167                StringBuilder sql = new StringBuilder();
168                sql.append("SELECT * FROM (SELECT ").append(selectClause);
169                sql.append(" FROM ").append(provider.getFromClause());
170                sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
171                buildGroupByClause(provider, sql);
172                sql.append(" ORDER BY ").append(buildSortClause(provider));
173                sql.append(") WHERE ").append(rowNumClause);
174                if(remainingPageQuery) {
175                        sql.append(" AND ");
176                        buildSortConditions(provider, sql);
177                }
178
179                return sql.toString();
180
181        }
182
183        public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider,
184                        String selectClause, boolean remainingPageQuery, String rowNumClause) {
185                return generateRowNumSqlQueryWithNesting(provider, selectClause, selectClause, remainingPageQuery, rowNumClause);
186        }
187
188        public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider,
189                        String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) {
190
191                StringBuilder sql = new StringBuilder();
192                sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause)
193                                .append(", ").append(StringUtils.hasText(provider.getGroupClause()) ? "MIN(ROWNUM) as TMP_ROW_NUM" : "ROWNUM as TMP_ROW_NUM");
194                sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause());
195                buildWhereClause(provider, remainingPageQuery, sql);
196                buildGroupByClause(provider, sql);
197                sql.append(" ORDER BY ").append(buildSortClause(provider));
198                sql.append(")) WHERE ").append(rowNumClause);
199
200                return sql.toString();
201
202        }
203
204        /**
205         * Generate SQL query string using a LIMIT clause
206         * 
207         * @param provider {@link AbstractSqlPagingQueryProvider} providing the
208         * implementation specifics
209         * @param limitClause the implementation specific top clause to be used
210         * @return the generated query
211         */
212        public static String generateLimitJumpToQuery(AbstractSqlPagingQueryProvider provider, String limitClause) {
213                StringBuilder sql = new StringBuilder();
214                sql.append("SELECT ").append(buildSortKeySelect(provider));
215                sql.append(" FROM ").append(provider.getFromClause());
216                sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
217                buildGroupByClause(provider, sql);
218                sql.append(" ORDER BY ").append(buildSortClause(provider));
219                sql.append(" " + limitClause);
220
221                return sql.toString();
222        }
223
224        /**
225         * Generate SQL query string using a TOP clause
226         * 
227         * @param provider {@link AbstractSqlPagingQueryProvider} providing the
228         * implementation specifics
229         * @param topClause the implementation specific top clause to be used
230         * @return the generated query
231         */
232        public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) {
233                StringBuilder sql = new StringBuilder();
234                sql.append("SELECT ").append(topClause).append(" ").append(buildSortKeySelect(provider));
235                sql.append(" FROM ").append(provider.getFromClause());
236                sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
237                buildGroupByClause(provider, sql);
238                sql.append(" ORDER BY ").append(buildSortClause(provider));
239
240                return sql.toString();
241        }
242
243        /**
244         * Generates ORDER BY attributes based on the sort keys.
245         * 
246         * @param provider the {@link AbstractSqlPagingQueryProvider} to be used for
247         * used for pagination.
248         * @return a String that can be appended to an ORDER BY clause.
249         */
250        public static String buildSortClause(AbstractSqlPagingQueryProvider provider) {
251                return buildSortClause(provider.getSortKeys());
252        }
253        
254        /**
255         * Generates ORDER BY attributes based on the sort keys.
256         * 
257         * @param sortKeys {@link Map} where the key is the name of the column to be
258         * sorted and the value contains the {@link Order}.
259         * @return a String that can be appended to an ORDER BY clause.
260         */
261        public static String buildSortClause(Map<String, Order> sortKeys) {
262                StringBuilder builder = new StringBuilder();
263                String prefix = "";
264                
265                for (Map.Entry<String, Order> sortKey : sortKeys.entrySet()) {
266                        builder.append(prefix);
267                        
268                        prefix = ", ";
269                        
270                        builder.append(sortKey.getKey());
271                        
272                        if(sortKey.getValue() != null && sortKey.getValue() == Order.DESCENDING) {
273                                builder.append(" DESC");
274                        }
275                        else {
276                                builder.append(" ASC");
277                        }
278                }
279                
280                return builder.toString();
281        }
282
283        /**
284         * Appends the where conditions required to query for the subsequent pages.
285         * 
286         * @param provider the {@link AbstractSqlPagingQueryProvider} to be used for
287         * pagination.
288         * @param sql {@link StringBuilder} containing the sql to be used for the
289         * query.
290         */
291        public static void buildSortConditions(
292                        AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
293                List<Map.Entry<String, Order>> keys = new ArrayList<Map.Entry<String,Order>>(provider.getSortKeys().entrySet());
294                List<String> clauses = new ArrayList<String>();
295                
296                for(int i = 0; i < keys.size(); i++) {
297                        StringBuilder clause = new StringBuilder();
298                        
299                        String prefix = "";
300                        for(int j = 0; j < i; j++) {
301                                clause.append(prefix);
302                                prefix = " AND ";
303                                Entry<String, Order> entry = keys.get(j);
304                                clause.append(entry.getKey());
305                                clause.append(" = ");
306                                clause.append(provider.getSortKeyPlaceHolder(entry.getKey()));
307                        }
308                        
309                        if(clause.length() > 0) {
310                                clause.append(" AND ");
311                        }
312                        clause.append(keys.get(i).getKey());
313                        
314                        if(keys.get(i).getValue() != null && keys.get(i).getValue() == Order.DESCENDING) {
315                                clause.append(" < ");
316                        }
317                        else {
318                                clause.append(" > ");
319                        }
320
321                        clause.append(provider.getSortKeyPlaceHolder(keys.get(i).getKey()));
322                        
323                        clauses.add(clause.toString());
324                }
325                
326                sql.append("(");
327                String prefix = "";
328                
329                for (String curClause : clauses) {
330                        sql.append(prefix);
331                        prefix = " OR ";
332                        sql.append("(");
333                        sql.append(curClause);
334                        sql.append(")");
335                }
336                sql.append(")");
337        }
338
339        private static String buildSortKeySelect(AbstractSqlPagingQueryProvider provider) {
340                StringBuilder select = new StringBuilder();
341                
342                String prefix = "";
343                
344                for (Map.Entry<String, Order> sortKey : provider.getSortKeys().entrySet()) {
345                        select.append(prefix);
346                        
347                        prefix = ", ";
348                        
349                        select.append(sortKey.getKey());
350                }
351                
352                return select.toString();
353        }
354
355        private static void buildWhereClause(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery,
356                        StringBuilder sql) {
357                if (remainingPageQuery) {
358                        sql.append(" WHERE ");
359                        if (provider.getWhereClause() != null) {
360                                sql.append("(");
361                                sql.append(provider.getWhereClause());
362                                sql.append(") AND ");
363                        }
364
365                        buildSortConditions(provider, sql);
366                }
367                else {
368                        sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause());
369                }
370        }
371        
372        private static void buildGroupByClause(AbstractSqlPagingQueryProvider provider, StringBuilder sql) {
373                if(StringUtils.hasText(provider.getGroupClause())) {
374                        sql.append(" GROUP BY ");
375                        sql.append(provider.getGroupClause());
376                }
377        }
378
379}