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 | } |