001/* 002 * Copyright 2017 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 */ 016package org.springframework.batch.item.database.builder; 017 018import java.util.Map; 019import javax.sql.DataSource; 020 021import org.springframework.batch.item.database.JdbcPagingItemReader; 022import org.springframework.batch.item.database.Order; 023import org.springframework.batch.item.database.PagingQueryProvider; 024import org.springframework.batch.item.database.support.AbstractSqlPagingQueryProvider; 025import org.springframework.batch.item.database.support.Db2PagingQueryProvider; 026import org.springframework.batch.item.database.support.DerbyPagingQueryProvider; 027import org.springframework.batch.item.database.support.H2PagingQueryProvider; 028import org.springframework.batch.item.database.support.HsqlPagingQueryProvider; 029import org.springframework.batch.item.database.support.MySqlPagingQueryProvider; 030import org.springframework.batch.item.database.support.OraclePagingQueryProvider; 031import org.springframework.batch.item.database.support.PostgresPagingQueryProvider; 032import org.springframework.batch.item.database.support.SqlServerPagingQueryProvider; 033import org.springframework.batch.item.database.support.SqlitePagingQueryProvider; 034import org.springframework.batch.item.database.support.SybasePagingQueryProvider; 035import org.springframework.batch.support.DatabaseType; 036import org.springframework.jdbc.core.RowMapper; 037import org.springframework.jdbc.support.MetaDataAccessException; 038import org.springframework.util.Assert; 039 040/** 041 * This is a builder for the {@link JdbcPagingItemReader}. When configuring, either a 042 * {@link PagingQueryProvider} or the SQL fragments should be provided. If the SQL 043 * fragments are provided, the metadata from the provided {@link DataSource} will be used 044 * to create a PagingQueryProvider for you. If both are provided, the PagingQueryProvider 045 * will be used. 046 * 047 * @author Michael Minella 048 * @author Glenn Renfro 049 * @since 4.0 050 * @see JdbcPagingItemReader 051 */ 052public class JdbcPagingItemReaderBuilder<T> { 053 054 private DataSource dataSource; 055 056 private int fetchSize = JdbcPagingItemReader.VALUE_NOT_SET; 057 058 private PagingQueryProvider queryProvider; 059 060 private RowMapper<T> rowMapper; 061 062 private Map<String, Object> parameterValues; 063 064 private int pageSize = 10; 065 066 private String groupClause; 067 068 private String selectClause; 069 070 private String fromClause; 071 072 private String whereClause; 073 074 private Map<String, Order> sortKeys; 075 076 private boolean saveState = true; 077 078 private String name; 079 080 private int maxItemCount = Integer.MAX_VALUE; 081 082 private int currentItemCount; 083 084 /** 085 * Configure if the state of the {@link org.springframework.batch.item.ItemStreamSupport} 086 * should be persisted within the {@link org.springframework.batch.item.ExecutionContext} 087 * for restart purposes. 088 * 089 * @param saveState defaults to true 090 * @return The current instance of the builder. 091 */ 092 public JdbcPagingItemReaderBuilder<T> saveState(boolean saveState) { 093 this.saveState = saveState; 094 095 return this; 096 } 097 098 /** 099 * The name used to calculate the key within the 100 * {@link org.springframework.batch.item.ExecutionContext}. Required if 101 * {@link #saveState(boolean)} is set to true. 102 * 103 * @param name name of the reader instance 104 * @return The current instance of the builder. 105 * @see org.springframework.batch.item.ItemStreamSupport#setName(String) 106 */ 107 public JdbcPagingItemReaderBuilder<T> name(String name) { 108 this.name = name; 109 110 return this; 111 } 112 113 /** 114 * Configure the max number of items to be read. 115 * 116 * @param maxItemCount the max items to be read 117 * @return The current instance of the builder. 118 * @see org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader#setMaxItemCount(int) 119 */ 120 public JdbcPagingItemReaderBuilder<T> maxItemCount(int maxItemCount) { 121 this.maxItemCount = maxItemCount; 122 123 return this; 124 } 125 126 /** 127 * Index for the current item. Used on restarts to indicate where to start from. 128 * 129 * @param currentItemCount current index 130 * @return this instance for method chaining 131 * @see org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader#setCurrentItemCount(int) 132 */ 133 public JdbcPagingItemReaderBuilder<T> currentItemCount(int currentItemCount) { 134 this.currentItemCount = currentItemCount; 135 136 return this; 137 } 138 139 /** 140 * The {@link DataSource} to query against. Required. 141 * 142 * @param dataSource the {@link DataSource} 143 * @return this instance for method chaining 144 * @see JdbcPagingItemReader#setDataSource(DataSource) 145 */ 146 public JdbcPagingItemReaderBuilder<T> dataSource(DataSource dataSource) { 147 this.dataSource = dataSource; 148 149 return this; 150 } 151 152 /** 153 * A hint to the underlying RDBMS as to how many records to return with each fetch. 154 * 155 * @param fetchSize number of records 156 * @return this instance for method chaining 157 * @see JdbcPagingItemReader#setFetchSize(int) 158 */ 159 public JdbcPagingItemReaderBuilder<T> fetchSize(int fetchSize) { 160 this.fetchSize = fetchSize; 161 162 return this; 163 } 164 165 /** 166 * The {@link RowMapper} used to map the query results to objects. Required. 167 * 168 * @param rowMapper a {@link RowMapper} implementation 169 * @return this instance for method chaining 170 * @see JdbcPagingItemReader#setRowMapper(RowMapper) 171 */ 172 public JdbcPagingItemReaderBuilder<T> rowMapper(RowMapper<T> rowMapper) { 173 this.rowMapper = rowMapper; 174 175 return this; 176 } 177 178 /** 179 * A {@link Map} of values to set on the SQL's prepared statement. 180 * 181 * @param parameterValues Map of values 182 * @return this instance for method chaining 183 * @see JdbcPagingItemReader#setParameterValues(Map) 184 */ 185 public JdbcPagingItemReaderBuilder<T> parameterValues(Map<String, Object> parameterValues) { 186 this.parameterValues = parameterValues; 187 188 return this; 189 } 190 191 /** 192 * The number of records to request per page/query. Defaults to 10. Must be greater 193 * than zero. 194 * 195 * @param pageSize number of items 196 * @return this instance for method chaining 197 * @see JdbcPagingItemReader#setPageSize(int) 198 */ 199 public JdbcPagingItemReaderBuilder<T> pageSize(int pageSize) { 200 this.pageSize = pageSize; 201 202 return this; 203 } 204 205 /** 206 * The SQL <code>GROUP BY</code> clause for a db specific @{@link PagingQueryProvider}. 207 * This is only used if a PagingQueryProvider is not provided. 208 * 209 * @param groupClause the SQL clause 210 * @return this instance for method chaining 211 * @see AbstractSqlPagingQueryProvider#setGroupClause(String) 212 */ 213 public JdbcPagingItemReaderBuilder<T> groupClause(String groupClause) { 214 this.groupClause = groupClause; 215 216 return this; 217 } 218 219 /** 220 * The SQL <code>SELECT</code> clause for a db specific {@link PagingQueryProvider}. 221 * This is only used if a PagingQueryProvider is not provided. 222 * 223 * @param selectClause the SQL clause 224 * @return this instance for method chaining 225 * @see AbstractSqlPagingQueryProvider#setSelectClause(String) 226 */ 227 public JdbcPagingItemReaderBuilder<T> selectClause(String selectClause) { 228 this.selectClause = selectClause; 229 230 return this; 231 } 232 233 /** 234 * The SQL <code>FROM</code> clause for a db specific {@link PagingQueryProvider}. 235 * This is only used if a PagingQueryProvider is not provided. 236 * 237 * @param fromClause the SQL clause 238 * @return this instance for method chaining 239 * @see AbstractSqlPagingQueryProvider#setFromClause(String) 240 */ 241 public JdbcPagingItemReaderBuilder<T> fromClause(String fromClause) { 242 this.fromClause = fromClause; 243 244 return this; 245 } 246 247 /** 248 * The SQL <code>WHERE</code> clause for a db specific {@link PagingQueryProvider}. 249 * This is only used if a PagingQueryProvider is not provided. 250 * 251 * @param whereClause the SQL clause 252 * @return this instance for method chaining 253 * @see AbstractSqlPagingQueryProvider#setWhereClause(String) 254 */ 255 public JdbcPagingItemReaderBuilder<T> whereClause(String whereClause) { 256 this.whereClause = whereClause; 257 258 return this; 259 } 260 261 /** 262 * The keys to sort by. These keys <em>must</em> create a unique key. 263 * 264 * @param sortKeys keys to sort by and the direction for each. 265 * @return this instance for method chaining 266 * @see AbstractSqlPagingQueryProvider#setSortKeys(Map) 267 */ 268 public JdbcPagingItemReaderBuilder<T> sortKeys(Map<String, Order> sortKeys) { 269 this.sortKeys = sortKeys; 270 271 return this; 272 } 273 274 /** 275 * A {@link PagingQueryProvider} to provide the queries required. If provided, the 276 * SQL fragments configured via {@link #selectClause(String)}, 277 * {@link #fromClause(String)}, {@link #whereClause(String)}, {@link #groupClause}, 278 * and {@link #sortKeys(Map)} are ignored. 279 * 280 * @param provider the db specific query provider 281 * @return this instance for method chaining 282 * @see JdbcPagingItemReader#setQueryProvider(PagingQueryProvider) 283 */ 284 public JdbcPagingItemReaderBuilder<T> queryProvider(PagingQueryProvider provider) { 285 this.queryProvider = provider; 286 287 return this; 288 } 289 290 /** 291 * Provides a completely built instance of the {@link JdbcPagingItemReader} 292 * 293 * @return a {@link JdbcPagingItemReader} 294 */ 295 public JdbcPagingItemReader<T> build() { 296 Assert.isTrue(this.pageSize > 0, "pageSize must be greater than zero"); 297 Assert.notNull(this.dataSource, "dataSource is required"); 298 299 if(this.saveState) { 300 Assert.hasText(this.name, 301 "A name is required when saveState is set to true"); 302 } 303 304 JdbcPagingItemReader<T> reader = new JdbcPagingItemReader<>(); 305 306 reader.setMaxItemCount(this.maxItemCount); 307 reader.setCurrentItemCount(this.currentItemCount); 308 reader.setName(this.name); 309 reader.setSaveState(this.saveState); 310 reader.setDataSource(this.dataSource); 311 reader.setFetchSize(this.fetchSize); 312 reader.setParameterValues(this.parameterValues); 313 314 if(this.queryProvider == null) { 315 Assert.hasLength(this.selectClause, "selectClause is required when not providing a PagingQueryProvider"); 316 Assert.hasLength(this.fromClause, "fromClause is required when not providing a PagingQueryProvider"); 317 Assert.notEmpty(this.sortKeys, "sortKeys are required when not providing a PagingQueryProvider"); 318 319 reader.setQueryProvider(determineQueryProvider(this.dataSource)); 320 } 321 else { 322 reader.setQueryProvider(this.queryProvider); 323 } 324 325 reader.setRowMapper(this.rowMapper); 326 reader.setPageSize(this.pageSize); 327 328 return reader; 329 } 330 331 private PagingQueryProvider determineQueryProvider(DataSource dataSource) { 332 333 try { 334 DatabaseType databaseType = DatabaseType.fromMetaData(dataSource); 335 336 AbstractSqlPagingQueryProvider provider; 337 338 switch (databaseType) { 339 340 case DERBY: provider = new DerbyPagingQueryProvider(); break; 341 case DB2: 342 case DB2VSE: 343 case DB2ZOS: 344 case DB2AS400: provider = new Db2PagingQueryProvider(); break; 345 case H2: provider = new H2PagingQueryProvider(); break; 346 case HSQL: provider = new HsqlPagingQueryProvider(); break; 347 case SQLSERVER: provider = new SqlServerPagingQueryProvider(); break; 348 case MYSQL: provider = new MySqlPagingQueryProvider(); break; 349 case ORACLE: provider = new OraclePagingQueryProvider(); break; 350 case POSTGRES: provider = new PostgresPagingQueryProvider(); break; 351 case SYBASE: provider = new SybasePagingQueryProvider(); break; 352 case SQLITE: provider = new SqlitePagingQueryProvider(); break; 353 default: 354 throw new IllegalArgumentException("Unable to determine PagingQueryProvider type " + 355 "from database type: " + databaseType); 356 } 357 358 provider.setSelectClause(this.selectClause); 359 provider.setFromClause(this.fromClause); 360 provider.setWhereClause(this.whereClause); 361 provider.setGroupClause(this.groupClause); 362 provider.setSortKeys(this.sortKeys); 363 364 return provider; 365 } 366 catch (MetaDataAccessException e) { 367 throw new IllegalArgumentException("Unable to determine PagingQueryProvider type", e); 368 } 369 } 370}