001/*
002 * Copyright 2002-2018 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 */
016
017package org.springframework.jdbc.core.namedparam;
018
019import java.util.LinkedHashMap;
020import java.util.List;
021import java.util.Map;
022import javax.sql.DataSource;
023
024import org.springframework.dao.DataAccessException;
025import org.springframework.dao.support.DataAccessUtils;
026import org.springframework.jdbc.core.ColumnMapRowMapper;
027import org.springframework.jdbc.core.JdbcOperations;
028import org.springframework.jdbc.core.JdbcTemplate;
029import org.springframework.jdbc.core.PreparedStatementCallback;
030import org.springframework.jdbc.core.PreparedStatementCreator;
031import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
032import org.springframework.jdbc.core.ResultSetExtractor;
033import org.springframework.jdbc.core.RowCallbackHandler;
034import org.springframework.jdbc.core.RowMapper;
035import org.springframework.jdbc.core.SingleColumnRowMapper;
036import org.springframework.jdbc.core.SqlParameter;
037import org.springframework.jdbc.core.SqlRowSetResultSetExtractor;
038import org.springframework.jdbc.support.KeyHolder;
039import org.springframework.jdbc.support.rowset.SqlRowSet;
040import org.springframework.util.Assert;
041
042/**
043 * Template class with a basic set of JDBC operations, allowing the use
044 * of named parameters rather than traditional '?' placeholders.
045 *
046 * <p>This class delegates to a wrapped {@link #getJdbcOperations() JdbcTemplate}
047 * once the substitution from named parameters to JDBC style '?' placeholders is
048 * done at execution time. It also allows for expanding a {@link java.util.List}
049 * of values to the appropriate number of placeholders.
050 *
051 * <p>The underlying {@link org.springframework.jdbc.core.JdbcTemplate} is
052 * exposed to allow for convenient access to the traditional
053 * {@link org.springframework.jdbc.core.JdbcTemplate} methods.
054 *
055 * <p><b>NOTE: An instance of this class is thread-safe once configured.</b>
056 *
057 * @author Thomas Risberg
058 * @author Juergen Hoeller
059 * @since 2.0
060 * @see NamedParameterJdbcOperations
061 * @see org.springframework.jdbc.core.JdbcTemplate
062 */
063public class NamedParameterJdbcTemplate implements NamedParameterJdbcOperations {
064
065        /** Default maximum number of entries for this template's SQL cache: 256 */
066        public static final int DEFAULT_CACHE_LIMIT = 256;
067
068
069        /** The JdbcTemplate we are wrapping */
070        private final JdbcOperations classicJdbcTemplate;
071
072        private volatile int cacheLimit = DEFAULT_CACHE_LIMIT;
073
074        /** Cache of original SQL String to ParsedSql representation */
075        @SuppressWarnings("serial")
076        private final Map<String, ParsedSql> parsedSqlCache =
077                        new LinkedHashMap<String, ParsedSql>(DEFAULT_CACHE_LIMIT, 0.75f, true) {
078                                @Override
079                                protected boolean removeEldestEntry(Map.Entry<String, ParsedSql> eldest) {
080                                        return size() > getCacheLimit();
081                                }
082                        };
083
084
085        /**
086         * Create a new NamedParameterJdbcTemplate for the given {@link DataSource}.
087         * <p>Creates a classic Spring {@link org.springframework.jdbc.core.JdbcTemplate} and wraps it.
088         * @param dataSource the JDBC DataSource to access
089         */
090        public NamedParameterJdbcTemplate(DataSource dataSource) {
091                Assert.notNull(dataSource, "DataSource must not be null");
092                this.classicJdbcTemplate = new JdbcTemplate(dataSource);
093        }
094
095        /**
096         * Create a new NamedParameterJdbcTemplate for the given classic
097         * Spring {@link org.springframework.jdbc.core.JdbcTemplate}.
098         * @param classicJdbcTemplate the classic Spring JdbcTemplate to wrap
099         */
100        public NamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate) {
101                Assert.notNull(classicJdbcTemplate, "JdbcTemplate must not be null");
102                this.classicJdbcTemplate = classicJdbcTemplate;
103        }
104
105
106        /**
107         * Expose the classic Spring JdbcTemplate operations to allow invocation
108         * of less commonly used methods.
109         */
110        @Override
111        public JdbcOperations getJdbcOperations() {
112                return this.classicJdbcTemplate;
113        }
114
115        /**
116         * Specify the maximum number of entries for this template's SQL cache.
117         * Default is 256.
118         */
119        public void setCacheLimit(int cacheLimit) {
120                this.cacheLimit = cacheLimit;
121        }
122
123        /**
124         * Return the maximum number of entries for this template's SQL cache.
125         */
126        public int getCacheLimit() {
127                return this.cacheLimit;
128        }
129
130
131        @Override
132        public <T> T execute(String sql, SqlParameterSource paramSource, PreparedStatementCallback<T> action)
133                        throws DataAccessException {
134
135                return getJdbcOperations().execute(getPreparedStatementCreator(sql, paramSource), action);
136        }
137
138        @Override
139        public <T> T execute(String sql, Map<String, ?> paramMap, PreparedStatementCallback<T> action)
140                        throws DataAccessException {
141
142                return execute(sql, new MapSqlParameterSource(paramMap), action);
143        }
144
145        @Override
146        public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException {
147                return execute(sql, EmptySqlParameterSource.INSTANCE, action);
148        }
149
150        @Override
151        public <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse)
152                        throws DataAccessException {
153
154                return getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rse);
155        }
156
157        @Override
158        public <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse)
159                        throws DataAccessException {
160
161                return query(sql, new MapSqlParameterSource(paramMap), rse);
162        }
163
164        @Override
165        public <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException {
166                return query(sql, EmptySqlParameterSource.INSTANCE, rse);
167        }
168
169        @Override
170        public void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch)
171                        throws DataAccessException {
172
173                getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rch);
174        }
175
176        @Override
177        public void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch)
178                        throws DataAccessException {
179
180                query(sql, new MapSqlParameterSource(paramMap), rch);
181        }
182
183        @Override
184        public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
185                query(sql, EmptySqlParameterSource.INSTANCE, rch);
186        }
187
188        @Override
189        public <T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
190                        throws DataAccessException {
191
192                return getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rowMapper);
193        }
194
195        @Override
196        public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)
197                        throws DataAccessException {
198
199                return query(sql, new MapSqlParameterSource(paramMap), rowMapper);
200        }
201
202        @Override
203        public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
204                return query(sql, EmptySqlParameterSource.INSTANCE, rowMapper);
205        }
206
207        @Override
208        public <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
209                        throws DataAccessException {
210
211                List<T> results = getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rowMapper);
212                return DataAccessUtils.requiredSingleResult(results);
213        }
214
215        @Override
216        public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T>rowMapper)
217                        throws DataAccessException {
218
219                return queryForObject(sql, new MapSqlParameterSource(paramMap), rowMapper);
220        }
221
222        @Override
223        public <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType)
224                        throws DataAccessException {
225
226                return queryForObject(sql, paramSource, new SingleColumnRowMapper<T>(requiredType));
227        }
228
229        @Override
230        public <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType)
231                        throws DataAccessException {
232
233                return queryForObject(sql, paramMap, new SingleColumnRowMapper<T>(requiredType));
234        }
235
236        @Override
237        public Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource) throws DataAccessException {
238                return queryForObject(sql, paramSource, new ColumnMapRowMapper());
239        }
240
241        @Override
242        public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) throws DataAccessException {
243                return queryForObject(sql, paramMap, new ColumnMapRowMapper());
244        }
245
246        @Override
247        public <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType)
248                        throws DataAccessException {
249
250                return query(sql, paramSource, new SingleColumnRowMapper<T>(elementType));
251        }
252
253        @Override
254        public <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> elementType)
255                        throws DataAccessException {
256
257                return queryForList(sql, new MapSqlParameterSource(paramMap), elementType);
258        }
259
260        @Override
261        public List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource)
262                        throws DataAccessException {
263
264                return query(sql, paramSource, new ColumnMapRowMapper());
265        }
266
267        @Override
268        public List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap)
269                        throws DataAccessException {
270
271                return queryForList(sql, new MapSqlParameterSource(paramMap));
272        }
273
274        @Override
275        public SqlRowSet queryForRowSet(String sql, SqlParameterSource paramSource) throws DataAccessException {
276                return getJdbcOperations().query(
277                                getPreparedStatementCreator(sql, paramSource), new SqlRowSetResultSetExtractor());
278        }
279
280        @Override
281        public SqlRowSet queryForRowSet(String sql, Map<String, ?> paramMap) throws DataAccessException {
282                return queryForRowSet(sql, new MapSqlParameterSource(paramMap));
283        }
284
285        @Override
286        public int update(String sql, SqlParameterSource paramSource) throws DataAccessException {
287                return getJdbcOperations().update(getPreparedStatementCreator(sql, paramSource));
288        }
289
290        @Override
291        public int update(String sql, Map<String, ?> paramMap) throws DataAccessException {
292                return update(sql, new MapSqlParameterSource(paramMap));
293        }
294
295        @Override
296        public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder)
297                        throws DataAccessException {
298
299                return update(sql, paramSource, generatedKeyHolder, null);
300        }
301
302        @Override
303        public int update(
304                        String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames)
305                        throws DataAccessException {
306
307                ParsedSql parsedSql = getParsedSql(sql);
308                String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
309                Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
310                List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
311                PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
312                if (keyColumnNames != null) {
313                        pscf.setGeneratedKeysColumnNames(keyColumnNames);
314                }
315                else {
316                        pscf.setReturnGeneratedKeys(true);
317                }
318                return getJdbcOperations().update(pscf.newPreparedStatementCreator(params), generatedKeyHolder);
319        }
320
321        @Override
322        public int[] batchUpdate(String sql, Map<String, ?>[] batchValues) {
323                return batchUpdate(sql, SqlParameterSourceUtils.createBatch(batchValues));
324        }
325
326        @Override
327        public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
328                return NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(
329                                getParsedSql(sql), batchArgs, getJdbcOperations());
330        }
331
332
333        /**
334         * Build a {@link PreparedStatementCreator} based on the given SQL and named parameters.
335         * <p>Note: Directly called from all {@code query} variants.
336         * Not used for the {@code update} variant with generated key handling.
337         * @param sql the SQL statement to execute
338         * @param paramSource container of arguments to bind
339         * @return the corresponding {@link PreparedStatementCreator}
340         */
341        protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
342                ParsedSql parsedSql = getParsedSql(sql);
343                String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
344                Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
345                List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
346                PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
347                return pscf.newPreparedStatementCreator(params);
348        }
349
350        /**
351         * Obtain a parsed representation of the given SQL statement.
352         * <p>The default implementation uses an LRU cache with an upper limit of 256 entries.
353         * @param sql the original SQL statement
354         * @return a representation of the parsed SQL statement
355         */
356        protected ParsedSql getParsedSql(String sql) {
357                if (getCacheLimit() <= 0) {
358                        return NamedParameterUtils.parseSqlStatement(sql);
359                }
360                synchronized (this.parsedSqlCache) {
361                        ParsedSql parsedSql = this.parsedSqlCache.get(sql);
362                        if (parsedSql == null) {
363                                parsedSql = NamedParameterUtils.parseSqlStatement(sql);
364                                this.parsedSqlCache.put(sql, parsedSql);
365                        }
366                        return parsedSql;
367                }
368        }
369
370}