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.LinkedHashMap;
21  import java.util.List;
22  import java.util.Map;
23  
24  import javax.sql.DataSource;
25  
26  import org.springframework.batch.item.database.JdbcParameterUtils;
27  import org.springframework.batch.item.database.Order;
28  import org.springframework.batch.item.database.PagingQueryProvider;
29  import org.springframework.dao.InvalidDataAccessApiUsageException;
30  import org.springframework.util.Assert;
31  import org.springframework.util.StringUtils;
32  
33  /**
34   * Abstract SQL Paging Query Provider to serve as a base class for all provided
35   * SQL paging query providers.
36   * 
37   * Any implementation must provide a way to specify the select clause, from
38   * clause and optionally a where clause. In addition a way to specify a single
39   * column sort key must also be provided. This sort key will be used to provide
40   * the paging functionality. It is recommended that there should be an index for
41   * the sort key to provide better performance.
42   * 
43   * Provides properties and preparation for the mandatory "selectClause" and
44   * "fromClause" as well as for the optional "whereClause". Also provides
45   * property for the mandatory "sortKeys".  <b>Note:</b> The columns that make up 
46   * the sort key must be a true key and not just a column to order by.
47   * 
48   * @author Thomas Risberg
49   * @author Dave Syer
50   * @author Michael Minella
51   * @since 2.0
52   */
53  public abstract class AbstractSqlPagingQueryProvider implements PagingQueryProvider {
54  
55  	private String selectClause;
56  
57  	private String fromClause;
58  
59  	private String whereClause;
60  	
61  	private Map<String, Order> sortKeys = new LinkedHashMap<String, Order>();
62  
63  	private String groupClause;
64  
65  	private int parameterCount;
66  
67  	private boolean usingNamedParameters;
68  	
69  	/**
70  	 * The setter for the group by clause
71  	 * 
72  	 * @param groupClause SQL GROUP BY clause part of the SQL query string
73  	 */
74  	public void setGroupClause(String groupClause) {
75  		if (StringUtils.hasText(groupClause)) {
76  			this.groupClause = removeKeyWord("group by", groupClause);
77  		}
78  		else {
79  			this.groupClause = null;
80  		}
81  	}
82  	
83  	/**
84  	 * The getter for the group by clause
85  	 * 
86  	 * @return SQL GROUP BY clause part of the SQL query string
87  	 */
88  	public String getGroupClause() {
89  		return this.groupClause;
90  	}
91  
92  	/**
93  	 * @param selectClause SELECT clause part of SQL query string
94  	 */
95  	public void setSelectClause(String selectClause) {
96  		this.selectClause = removeKeyWord("select", selectClause);
97  	}
98  
99  	/**
100 	 * 
101 	 * @return SQL SELECT clause part of SQL query string
102 	 */
103 	protected String getSelectClause() {
104 		return selectClause;
105 	}
106 
107 	/**
108 	 * @param fromClause FROM clause part of SQL query string
109 	 */
110 	public void setFromClause(String fromClause) {
111 		this.fromClause = removeKeyWord("from", fromClause);
112 	}
113 
114 	/**
115 	 * 
116 	 * @return SQL FROM clause part of SQL query string
117 	 */
118 	protected String getFromClause() {
119 		return fromClause;
120 	}
121 
122 	/**
123 	 * @param whereClause WHERE clause part of SQL query string
124 	 */
125 	public void setWhereClause(String whereClause) {
126 		if (StringUtils.hasText(whereClause)) {
127 			this.whereClause = removeKeyWord("where", whereClause);
128 		}
129 		else {
130 			this.whereClause = null;
131 		}
132 	}
133 
134 	/**
135 	 * 
136 	 * @return SQL WHERE clause part of SQL query string
137 	 */
138 	protected String getWhereClause() {
139 		return whereClause;
140 	}
141 
142 	/**
143 	 * @param sortKeys key to use to sort and limit page content
144 	 */
145 	public void setSortKeys(Map<String, Order> sortKeys) {
146 		this.sortKeys = sortKeys;
147 	}
148 
149 	/**
150 	 * A Map<String, Boolean> of sort columns as the key and boolean for ascending/descending (assending = true).
151 	 * 
152 	 * @return sortKey key to use to sort and limit page content
153 	 */
154     @Override
155 	public Map<String, Order> getSortKeys() {
156 		return sortKeys;
157 	}
158 
159     @Override
160 	public int getParameterCount() {
161 		return parameterCount;
162 	}
163 
164     @Override
165 	public boolean isUsingNamedParameters() {
166 		return usingNamedParameters;
167 	}
168 
169 	/**
170 	 * The sort key placeholder will vary depending on whether named parameters
171 	 * or traditional placeholders are used in query strings.
172 	 * 
173 	 * @return place holder for sortKey.
174 	 */
175     @Override
176 	public String getSortKeyPlaceHolder(String keyName) {
177 		return usingNamedParameters ? ":_" + keyName : "?";
178 	}
179 
180 	/**
181 	 * Check mandatory properties.
182 	 * @see org.springframework.beans.factory.InitializingBean#afterPropertiesSet()
183 	 */
184     @Override
185 	public void init(DataSource dataSource) throws Exception {
186 		Assert.notNull(dataSource);
187 		Assert.hasLength(selectClause, "selectClause must be specified");
188 		Assert.hasLength(fromClause, "fromClause must be specified");
189 		Assert.notEmpty(sortKeys, "sortKey must be specified");
190 		StringBuilder sql = new StringBuilder();
191 		sql.append("SELECT ").append(selectClause);
192 		sql.append(" FROM ").append(fromClause);
193 		if (whereClause != null) {
194 			sql.append(" WHERE ").append(whereClause);
195 		}
196 		if(groupClause != null) {
197 			sql.append(" GROUP BY ").append(groupClause);
198 		}
199 		List<String> namedParameters = new ArrayList<String>();
200 		parameterCount = JdbcParameterUtils.countParameterPlaceholders(sql.toString(), namedParameters);
201 		if (namedParameters.size() > 0) {
202 			if (parameterCount != namedParameters.size()) {
203 				throw new InvalidDataAccessApiUsageException(
204 						"You can't use both named parameters and classic \"?\" placeholders: " + sql);
205 			}
206 			usingNamedParameters = true;
207 		}
208 	}
209 
210 	/**
211 	 * Method generating the query string to be used for retrieving the first
212 	 * page. This method must be implemented in sub classes.
213 	 * 
214 	 * @param pageSize number of rows to read per page
215 	 * @return query string
216 	 */
217     @Override
218 	public abstract String generateFirstPageQuery(int pageSize);
219 
220 	/**
221 	 * Method generating the query string to be used for retrieving the pages
222 	 * following the first page. This method must be implemented in sub classes.
223 	 * 
224 	 * @param pageSize number of rows to read per page
225 	 * @return query string
226 	 */
227     @Override
228 	public abstract String generateRemainingPagesQuery(int pageSize);
229 
230 	/**
231 	 * Method generating the query string to be used for jumping to a specific
232 	 * item position. This method must be implemented in sub classes.
233 	 * 
234 	 * @param itemIndex the index of the item to jump to
235 	 * @param pageSize number of rows to read per page
236 	 * @return query string
237 	 */
238     @Override
239 	public abstract String generateJumpToItemQuery(int itemIndex, int pageSize);
240 
241 	private String removeKeyWord(String keyWord, String clause) {
242 		String temp = clause.trim();
243 		String keyWordString = keyWord + " ";
244 		if (temp.toLowerCase().startsWith(keyWordString) && temp.length() > keyWordString.length()) {
245 			return temp.substring(keyWordString.length());
246 		}
247 		else {
248 			return temp;
249 		}
250 	}
251 
252 }