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.Map;
20  
21  import org.springframework.batch.item.database.Order;
22  import org.springframework.util.StringUtils;
23  
24  /**
25   * Generic Paging Query Provider using standard SQL:2003 windowing functions.
26   * These features are supported by DB2, Oracle, SQL Server 2005, Sybase and
27   * Apache Derby version 10.4.1.3
28   * 
29   * @author Thomas Risberg
30   * @author Michael Minella
31   * @since 2.0
32   */
33  public class SqlWindowingPagingQueryProvider extends AbstractSqlPagingQueryProvider {
34  
35  	@Override
36  	public String generateFirstPageQuery(int pageSize) {
37  		StringBuilder sql = new StringBuilder();
38  		sql.append("SELECT * FROM ( ");
39  		sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, ");
40  		sql.append("ROW_NUMBER() OVER (").append(getOverClause());
41  		sql.append(") AS ROW_NUMBER");
42  		sql.append(getOverSubstituteClauseStart());
43  		sql.append(" FROM ").append(getFromClause()).append(
44  				getWhereClause() == null ? "" : " WHERE " + getWhereClause());
45  		sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
46  		sql.append(getOverSubstituteClauseEnd());
47  		sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
48  				"ROW_NUMBER <= ").append(pageSize);
49  		sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
50  		
51  		return sql.toString();
52  	}
53  
54  	protected String getOrderedQueryAlias() {
55  		return "";
56  	}
57  
58  	protected Object getSubQueryAlias() {
59  		return "AS TMP_SUB ";
60  	}
61  
62  	protected Object extractTableAlias() {
63  		String alias = "" + getSubQueryAlias();
64  		if (StringUtils.hasText(alias) && alias.toUpperCase().startsWith("AS")) {
65  			alias = alias.substring(3).trim() + ".";
66  		}
67  		return alias;
68  	}
69  
70  	@Override
71  	public String generateRemainingPagesQuery(int pageSize) {
72  		StringBuilder sql = new StringBuilder();
73  		sql.append("SELECT * FROM ( ");
74  		sql.append("SELECT ").append(StringUtils.hasText(getOrderedQueryAlias()) ? getOrderedQueryAlias() + ".*, " : "*, ");
75  		sql.append("ROW_NUMBER() OVER (").append(getOverClause());
76  		sql.append(") AS ROW_NUMBER");
77  		sql.append(getOverSubstituteClauseStart());
78  		sql.append(" FROM ").append(getFromClause());
79  		if (getWhereClause() != null) {
80  			sql.append(" WHERE ");
81  			sql.append(getWhereClause());
82  		}
83  		
84  		sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
85  		sql.append(getOverSubstituteClauseEnd());
86  		sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
87  				"ROW_NUMBER <= ").append(pageSize);
88  		sql.append(" AND ");
89  		SqlPagingQueryUtils.buildSortConditions(this, sql);
90  		sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
91  
92  		return sql.toString();
93  	}
94  
95  	@Override
96  	public String generateJumpToItemQuery(int itemIndex, int pageSize) {
97  		int page = itemIndex / pageSize;
98  		int lastRowNum = (page * pageSize);
99  		if (lastRowNum <= 0) {
100 			lastRowNum = 1;
101 		}
102 
103 		StringBuilder sql = new StringBuilder();
104 		sql.append("SELECT ");
105 		buildSortKeySelect(sql);
106 		sql.append(" FROM ( ");
107 		sql.append("SELECT ");
108 		buildSortKeySelect(sql);
109 		sql.append(", ROW_NUMBER() OVER (").append(getOverClause());
110 		sql.append(") AS ROW_NUMBER");
111 		sql.append(getOverSubstituteClauseStart());
112 		sql.append(" FROM ").append(getFromClause());
113 		sql.append(getWhereClause() == null ? "" : " WHERE " + getWhereClause());
114 		sql.append(getGroupClause() == null ? "" : " GROUP BY " + getGroupClause());
115 		sql.append(getOverSubstituteClauseEnd());
116 		sql.append(") ").append(getSubQueryAlias()).append("WHERE ").append(extractTableAlias()).append(
117 				"ROW_NUMBER = ").append(lastRowNum);
118 		sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
119 
120 		return sql.toString();
121 	}
122 
123 	private void buildSortKeySelect(StringBuilder sql) {
124 		String prefix = "";
125 		for (Map.Entry<String, Order> sortKey : getSortKeys().entrySet()) {
126 			sql.append(prefix);
127 			prefix = ", ";
128 			sql.append(sortKey.getKey());
129 		}
130 	}
131 
132 	protected String getOverClause() {
133 		StringBuilder sql = new StringBuilder();
134 		
135 		sql.append(" ORDER BY ").append(SqlPagingQueryUtils.buildSortClause(this));
136 		
137 		return sql.toString();
138 	}
139 
140 	protected String getOverSubstituteClauseStart() {
141 		return "";
142 	}
143 
144 	protected String getOverSubstituteClauseEnd() {
145 		return "";
146 	}
147 }