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}