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}