001/*
002 * Copyright 2006-2012 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.LinkedHashMap;
020import java.util.Map;
021
022import org.springframework.batch.item.database.Order;
023import org.springframework.util.StringUtils;
024
025/**
026 * Generic Paging Query Provider using standard SQL:2003 windowing functions.
027 * These features are supported by DB2, Oracle, SQL Server 2005, Sybase and
028 * Apache Derby version 10.4.1.3
029 * 
030 * @author Thomas Risberg
031 * @author Michael Minella
032 * @since 2.0
033 */
034public class SqlWindowingPagingQueryProvider extends AbstractSqlPagingQueryProvider {
035
036        @Override
037        public String generateFirstPageQuery(int pageSize) {
038                StringBuilder sql = new StringBuilder();
039                sql.append("SELECT * FROM ( ");
040                sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, ");
041                sql.append("ROW_NUMBER() OVER (").append(getOverClause());
042                sql.append(") AS ROW_NUMBER");
043                sql.append(getOverSubstituteClauseStart());
044                sql.append(" FROM ").append(getFromClause()).append(
045                                getWhereClause() == null ? "" : " WHERE " + getWhereClause());
046                sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
047                sql.append(getOverSubstituteClauseEnd());
048                sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
049                                "ROW_NUMBER <= ").append(pageSize);
050                sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
051                
052                return sql.toString();
053        }
054
055        protected String getOrderedQueryAlias() {
056                return "";
057        }
058
059        protected Object getSubQueryAlias() {
060                return "AS TMP_SUB ";
061        }
062
063        protected Object extractTableAlias() {
064                String alias = "" + getSubQueryAlias();
065                if (StringUtils.hasText(alias) && alias.toUpperCase().startsWith("AS")) {
066                        alias = alias.substring(3).trim() + ".";
067                }
068                return alias;
069        }
070
071        @Override
072        public String generateRemainingPagesQuery(int pageSize) {
073                StringBuilder sql = new StringBuilder();
074                sql.append("SELECT * FROM ( ");
075                sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, ");
076                sql.append("ROW_NUMBER() OVER (").append(getOverClause());
077                sql.append(") AS ROW_NUMBER");
078                sql.append(getOverSubstituteClauseStart());
079                sql.append(" FROM ").append(getFromClause());
080                if (getWhereClause() != null) {
081                        sql.append(" WHERE ");
082                        sql.append(getWhereClause());
083                }
084                
085                sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
086                sql.append(getOverSubstituteClauseEnd());
087                sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
088                                "ROW_NUMBER <= ").append(pageSize);
089                sql.append(" AND ");
090                SqlPagingQueryUtils.buildSortConditions(this, sql);
091                sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
092
093                return sql.toString();
094        }
095
096        @Override
097        public String generateJumpToItemQuery(int itemIndex, int pageSize) {
098                int page = itemIndex / pageSize;
099                int lastRowNum = (page * pageSize);
100                if (lastRowNum <= 0) {
101                        lastRowNum = 1;
102                }
103
104                StringBuilder sql = new StringBuilder();
105                sql.append("SELECT ");
106                buildSortKeySelect(sql, getSortKeysReplaced(extractTableAlias()));
107                sql.append(" FROM ( ");
108                sql.append("SELECT ");
109                buildSortKeySelect(sql);
110                sql.append(", ROW_NUMBER() OVER (").append(getOverClause());
111                sql.append(") AS ROW_NUMBER");
112                sql.append(getOverSubstituteClauseStart());
113                sql.append(" FROM ").append(getFromClause());
114                sql.append(getWhereClause() == null ? "" : " WHERE " + getWhereClause());
115                sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
116                sql.append(getOverSubstituteClauseEnd());
117                sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
118                                "ROW_NUMBER = ").append(lastRowNum);
119                sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(getSortKeysReplaced(extractTableAlias())));
120
121                return sql.toString();
122        }
123
124        private Map<String, Order> getSortKeysReplaced(Object qualifierReplacement) {
125                final String newQualifier = "" + qualifierReplacement;
126                final Map<String, Order> sortKeys = new LinkedHashMap<String, Order>();
127                for (Map.Entry<String, Order> sortKey : getSortKeys().entrySet()) {
128                        sortKeys.put(sortKey.getKey().replaceFirst("^.*\\.", newQualifier), sortKey.getValue());
129                }
130                return sortKeys;
131        }
132        
133        private void buildSortKeySelect(StringBuilder sql) {
134                buildSortKeySelect(sql, null);
135        }
136        
137        private void buildSortKeySelect(StringBuilder sql, Map<String, Order> sortKeys) {
138                String prefix = "";
139                if (sortKeys == null) {
140                        sortKeys = getSortKeys();
141                }
142                for (Map.Entry<String, Order> sortKey : sortKeys.entrySet()) {
143                        sql.append(prefix);
144                        prefix = ", ";
145                        sql.append(sortKey.getKey());
146                }
147        }
148
149        protected String getOverClause() {
150                StringBuilder sql = new StringBuilder();
151                
152                sql.append(" ORDER BY ").append(buildSortClause(this));
153                
154                return sql.toString();
155        }
156
157        protected String getOverSubstituteClauseStart() {
158                return "";
159        }
160
161        protected String getOverSubstituteClauseEnd() {
162                return "";
163        }
164
165
166        /**
167         * Generates ORDER BY attributes based on the sort keys.
168         *
169         * @param provider
170         * @return a String that can be appended to an ORDER BY clause.
171         */
172        private String buildSortClause(AbstractSqlPagingQueryProvider provider) {
173                return SqlPagingQueryUtils.buildSortClause(provider.getSortKeysWithoutAliases());
174        }
175
176}