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}