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