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.util.List; 020import java.util.Map; 021 022import org.springframework.dao.DataAccessException; 023import org.springframework.jdbc.core.JdbcOperations; 024import org.springframework.jdbc.core.PreparedStatementCallback; 025import org.springframework.jdbc.core.ResultSetExtractor; 026import org.springframework.jdbc.core.RowCallbackHandler; 027import org.springframework.jdbc.core.RowMapper; 028import org.springframework.jdbc.support.KeyHolder; 029import org.springframework.jdbc.support.rowset.SqlRowSet; 030 031/** 032 * Interface specifying a basic set of JDBC operations allowing the use 033 * of named parameters rather than the traditional '?' placeholders. 034 * 035 * <p>This is an alternative to the classic 036 * {@link org.springframework.jdbc.core.JdbcOperations} interface, 037 * implemented by {@link NamedParameterJdbcTemplate}. This interface is not 038 * often used directly, but provides a useful option to enhance testability, 039 * as it can easily be mocked or stubbed. 040 * 041 * @author Thomas Risberg 042 * @author Juergen Hoeller 043 * @since 2.0 044 * @see NamedParameterJdbcTemplate 045 * @see org.springframework.jdbc.core.JdbcOperations 046 */ 047public interface NamedParameterJdbcOperations { 048 049 /** 050 * Expose the classic Spring JdbcTemplate to allow invocation of 051 * classic JDBC operations. 052 */ 053 JdbcOperations getJdbcOperations(); 054 055 056 /** 057 * Execute a JDBC data access operation, implemented as callback action 058 * working on a JDBC PreparedStatement. This allows for implementing arbitrary 059 * data access operations on a single Statement, within Spring's managed 060 * JDBC environment: that is, participating in Spring-managed transactions 061 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 062 * <p>The callback action can return a result object, for example a 063 * domain object or a collection of domain objects. 064 * @param sql the SQL to execute 065 * @param paramSource container of arguments to bind to the query 066 * @param action callback object that specifies the action 067 * @return a result object returned by the action, or {@code null} 068 * @throws DataAccessException if there is any problem 069 */ 070 <T> T execute(String sql, SqlParameterSource paramSource, PreparedStatementCallback<T> action) 071 throws DataAccessException; 072 073 /** 074 * Execute a JDBC data access operation, implemented as callback action 075 * working on a JDBC PreparedStatement. This allows for implementing arbitrary 076 * data access operations on a single Statement, within Spring's managed 077 * JDBC environment: that is, participating in Spring-managed transactions 078 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 079 * <p>The callback action can return a result object, for example a 080 * domain object or a collection of domain objects. 081 * @param sql the SQL to execute 082 * @param paramMap map of parameters to bind to the query 083 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 084 * @param action callback object that specifies the action 085 * @return a result object returned by the action, or {@code null} 086 * @throws DataAccessException if there is any problem 087 */ 088 <T> T execute(String sql, Map<String, ?> paramMap, PreparedStatementCallback<T> action) 089 throws DataAccessException; 090 091 /** 092 * Execute a JDBC data access operation, implemented as callback action 093 * working on a JDBC PreparedStatement. This allows for implementing arbitrary 094 * data access operations on a single Statement, within Spring's managed 095 * JDBC environment: that is, participating in Spring-managed transactions 096 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 097 * <p>The callback action can return a result object, for example a 098 * domain object or a collection of domain objects. 099 * @param sql the SQL to execute 100 * @param action callback object that specifies the action 101 * @return a result object returned by the action, or {@code null} 102 * @throws DataAccessException if there is any problem 103 */ 104 <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException; 105 106 /** 107 * Query given SQL to create a prepared statement from SQL and a list 108 * of arguments to bind to the query, reading the ResultSet with a 109 * ResultSetExtractor. 110 * @param sql the SQL query to execute 111 * @param paramSource container of arguments to bind to the query 112 * @param rse object that will extract results 113 * @return an arbitrary result object, as returned by the ResultSetExtractor 114 * @throws DataAccessException if the query fails 115 */ 116 <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse) 117 throws DataAccessException; 118 119 /** 120 * Query given SQL to create a prepared statement from SQL and a list 121 * of arguments to bind to the query, reading the ResultSet with a 122 * ResultSetExtractor. 123 * @param sql the SQL query to execute 124 * @param paramMap map of parameters to bind to the query 125 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 126 * @param rse object that will extract results 127 * @return an arbitrary result object, as returned by the ResultSetExtractor 128 * @throws DataAccessException if the query fails 129 */ 130 <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse) 131 throws DataAccessException; 132 133 /** 134 * Query given SQL to create a prepared statement from SQL, 135 * reading the ResultSet with a ResultSetExtractor. 136 * <p>Note: In contrast to the JdbcOperations method with the same signature, 137 * this query variant always uses a PreparedStatement. It is effectively 138 * equivalent to a query call with an empty parameter Map. 139 * @param sql the SQL query to execute 140 * @param rse object that will extract results 141 * @return an arbitrary result object, as returned by the ResultSetExtractor 142 * @throws DataAccessException if the query fails 143 */ 144 <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException; 145 146 /** 147 * Query given SQL to create a prepared statement from SQL and a list of 148 * arguments to bind to the query, reading the ResultSet on a per-row basis 149 * with a RowCallbackHandler. 150 * @param sql the SQL query to execute 151 * @param paramSource container of arguments to bind to the query 152 * @param rch object that will extract results, one row at a time 153 * @throws DataAccessException if the query fails 154 */ 155 void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch) 156 throws DataAccessException; 157 158 /** 159 * Query given SQL to create a prepared statement from SQL and a list of 160 * arguments to bind to the query, reading the ResultSet on a per-row basis 161 * with a RowCallbackHandler. 162 * @param sql the SQL query to execute 163 * @param paramMap map of parameters to bind to the query 164 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 165 * @param rch object that will extract results, one row at a time 166 * @throws DataAccessException if the query fails 167 */ 168 void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch) throws DataAccessException; 169 170 /** 171 * Query given SQL to create a prepared statement from SQL, 172 * reading the ResultSet on a per-row basis with a RowCallbackHandler. 173 * <p>Note: In contrast to the JdbcOperations method with the same signature, 174 * this query variant always uses a PreparedStatement. It is effectively 175 * equivalent to a query call with an empty parameter Map. 176 * @param sql the SQL query to execute 177 * @param rch object that will extract results, one row at a time 178 * @throws DataAccessException if the query fails 179 */ 180 void query(String sql, RowCallbackHandler rch) throws DataAccessException; 181 182 /** 183 * Query given SQL to create a prepared statement from SQL and a list 184 * of arguments to bind to the query, mapping each row to a Java object 185 * via a RowMapper. 186 * @param sql the SQL query to execute 187 * @param paramSource container of arguments to bind to the query 188 * @param rowMapper object that will map one object per row 189 * @return the result List, containing mapped objects 190 * @throws DataAccessException if the query fails 191 */ 192 <T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper) 193 throws DataAccessException; 194 195 /** 196 * Query given SQL to create a prepared statement from SQL and a list 197 * of arguments to bind to the query, mapping each row to a Java object 198 * via a RowMapper. 199 * @param sql the SQL query to execute 200 * @param paramMap map of parameters to bind to the query 201 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 202 * @param rowMapper object that will map one object per row 203 * @return the result List, containing mapped objects 204 * @throws DataAccessException if the query fails 205 */ 206 <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) 207 throws DataAccessException; 208 209 /** 210 * Query given SQL to create a prepared statement from SQL, 211 * mapping each row to a Java object via a RowMapper. 212 * <p>Note: In contrast to the JdbcOperations method with the same signature, 213 * this query variant always uses a PreparedStatement. It is effectively 214 * equivalent to a query call with an empty parameter Map. 215 * @param sql the SQL query to execute 216 * @param rowMapper object that will map one object per row 217 * @return the result List, containing mapped objects 218 * @throws DataAccessException if the query fails 219 */ 220 <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException; 221 222 /** 223 * Query given SQL to create a prepared statement from SQL and a list 224 * of arguments to bind to the query, mapping a single result row to a 225 * Java object via a RowMapper. 226 * @param sql the SQL query to execute 227 * @param paramSource container of arguments to bind to the query 228 * @param rowMapper object that will map one object per row 229 * @return the single mapped object (may be {@code null} if the given 230 * {@link RowMapper} returned {@code} null) 231 * @throws org.springframework.dao.IncorrectResultSizeDataAccessException 232 * if the query does not return exactly one row, or does not return exactly 233 * one column in that row 234 * @throws DataAccessException if the query fails 235 */ 236 <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper) 237 throws DataAccessException; 238 239 /** 240 * Query given SQL to create a prepared statement from SQL and a list 241 * of arguments to bind to the query, mapping a single result row to a 242 * Java object via a RowMapper. 243 * @param sql the SQL query to execute 244 * @param paramMap map of parameters to bind to the query 245 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 246 * @param rowMapper object that will map one object per row 247 * @return the single mapped object (may be {@code null} if the given 248 * {@link RowMapper} returned {@code} null) 249 * @throws org.springframework.dao.IncorrectResultSizeDataAccessException 250 * if the query does not return exactly one row, or does not return exactly 251 * one column in that row 252 * @throws DataAccessException if the query fails 253 */ 254 <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) 255 throws DataAccessException; 256 257 /** 258 * Query given SQL to create a prepared statement from SQL and a 259 * list of arguments to bind to the query, expecting a result object. 260 * <p>The query is expected to be a single row/single column query; the returned 261 * result will be directly mapped to the corresponding object type. 262 * @param sql the SQL query to execute 263 * @param paramSource container of arguments to bind to the query 264 * @param requiredType the type that the result object is expected to match 265 * @return the result object of the required type, or {@code null} in case of SQL NULL 266 * @throws org.springframework.dao.IncorrectResultSizeDataAccessException 267 * if the query does not return exactly one row, or does not return exactly 268 * one column in that row 269 * @throws DataAccessException if the query fails 270 * @see org.springframework.jdbc.core.JdbcTemplate#queryForObject(String, Class) 271 */ 272 <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType) 273 throws DataAccessException; 274 275 /** 276 * Query given SQL to create a prepared statement from SQL and a 277 * list of arguments to bind to the query, expecting a result object. 278 * <p>The query is expected to be a single row/single column query; the returned 279 * result will be directly mapped to the corresponding object type. 280 * @param sql the SQL query to execute 281 * @param paramMap map of parameters to bind to the query 282 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 283 * @param requiredType the type that the result object is expected to match 284 * @return the result object of the required type, or {@code null} in case of SQL NULL 285 * @throws org.springframework.dao.IncorrectResultSizeDataAccessException 286 * if the query does not return exactly one row, or does not return exactly 287 * one column in that row 288 * @throws DataAccessException if the query fails 289 * @see org.springframework.jdbc.core.JdbcTemplate#queryForObject(String, Class) 290 */ 291 <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType) 292 throws DataAccessException; 293 294 /** 295 * Query given SQL to create a prepared statement from SQL and a 296 * list of arguments to bind to the query, expecting a result Map. 297 * <p>The query is expected to be a single row query; the result row will be 298 * mapped to a Map (one entry for each column, using the column name as the key). 299 * @param sql the SQL query to execute 300 * @param paramSource container of arguments to bind to the query 301 * @return the result Map (one entry for each column, using the column name as the key) 302 * @throws org.springframework.dao.IncorrectResultSizeDataAccessException 303 * if the query does not return exactly one row 304 * @throws DataAccessException if the query fails 305 * @see org.springframework.jdbc.core.JdbcTemplate#queryForMap(String) 306 * @see org.springframework.jdbc.core.ColumnMapRowMapper 307 */ 308 Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource) throws DataAccessException; 309 310 /** 311 * Query given SQL to create a prepared statement from SQL and a 312 * list of arguments to bind to the query, expecting a result Map. 313 * The queryForMap() methods defined by this interface are appropriate 314 * when you don't have a domain model. Otherwise, consider using 315 * one of the queryForObject() methods. 316 * <p>The query is expected to be a single row query; the result row will be 317 * mapped to a Map (one entry for each column, using the column name as the key). 318 * @param sql the SQL query to execute 319 * @param paramMap map of parameters to bind to the query 320 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 321 * @return the result Map (one entry for each column, using the column name as the key) 322 * @throws org.springframework.dao.IncorrectResultSizeDataAccessException 323 * if the query does not return exactly one row 324 * @throws DataAccessException if the query fails 325 * @see org.springframework.jdbc.core.JdbcTemplate#queryForMap(String) 326 * @see org.springframework.jdbc.core.ColumnMapRowMapper 327 */ 328 Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) throws DataAccessException; 329 330 /** 331 * Query given SQL to create a prepared statement from SQL and a 332 * list of arguments to bind to the query, expecting a result list. 333 * <p>The results will be mapped to a List (one entry for each row) of 334 * result objects, each of them matching the specified element type. 335 * @param sql the SQL query to execute 336 * @param paramSource container of arguments to bind to the query 337 * @param elementType the required type of element in the result list 338 * (for example, {@code Integer.class}) 339 * @return a List of objects that match the specified element type 340 * @throws DataAccessException if the query fails 341 * @see org.springframework.jdbc.core.JdbcTemplate#queryForList(String, Class) 342 * @see org.springframework.jdbc.core.SingleColumnRowMapper 343 */ 344 <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType) 345 throws DataAccessException; 346 347 /** 348 * Query given SQL to create a prepared statement from SQL and a 349 * list of arguments to bind to the query, expecting a result list. 350 * <p>The results will be mapped to a List (one entry for each row) of 351 * result objects, each of them matching the specified element type. 352 * @param sql the SQL query to execute 353 * @param paramMap map of parameters to bind to the query 354 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 355 * @param elementType the required type of element in the result list 356 * (for example, {@code Integer.class}) 357 * @return a List of objects that match the specified element type 358 * @throws DataAccessException if the query fails 359 * @see org.springframework.jdbc.core.JdbcTemplate#queryForList(String, Class) 360 * @see org.springframework.jdbc.core.SingleColumnRowMapper 361 */ 362 <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> elementType) 363 throws DataAccessException; 364 365 /** 366 * Query given SQL to create a prepared statement from SQL and a 367 * list of arguments to bind to the query, expecting a result list. 368 * <p>The results will be mapped to a List (one entry for each row) of 369 * Maps (one entry for each column, using the column name as the key). 370 * Each element in the list will be of the form returned by this interface's 371 * {@code queryForMap} methods. 372 * @param sql the SQL query to execute 373 * @param paramSource container of arguments to bind to the query 374 * @return a List that contains a Map per row 375 * @throws DataAccessException if the query fails 376 * @see org.springframework.jdbc.core.JdbcTemplate#queryForList(String) 377 */ 378 List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource) throws DataAccessException; 379 380 /** 381 * Query given SQL to create a prepared statement from SQL and a 382 * list of arguments to bind to the query, expecting a result list. 383 * <p>The results will be mapped to a List (one entry for each row) of 384 * Maps (one entry for each column, using the column name as the key). 385 * Each element in the list will be of the form returned by this interface's 386 * {@code queryForMap} methods. 387 * @param sql the SQL query to execute 388 * @param paramMap map of parameters to bind to the query 389 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 390 * @return a List that contains a Map per row 391 * @throws DataAccessException if the query fails 392 * @see org.springframework.jdbc.core.JdbcTemplate#queryForList(String) 393 */ 394 List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap) throws DataAccessException; 395 396 /** 397 * Query given SQL to create a prepared statement from SQL and a 398 * list of arguments to bind to the query, expecting a SqlRowSet. 399 * <p>The results will be mapped to an SqlRowSet which holds the data in a 400 * disconnected fashion. This wrapper will translate any SQLExceptions thrown. 401 * <p>Note that, for the default implementation, JDBC RowSet support needs to 402 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl} 403 * class is used, which is part of JDK 1.5+ and also available separately as part of 404 * Sun's JDBC RowSet Implementations download (rowset.jar). 405 * @param sql the SQL query to execute 406 * @param paramSource container of arguments to bind to the query 407 * @return a SqlRowSet representation (possibly a wrapper around a 408 * {@code javax.sql.rowset.CachedRowSet}) 409 * @throws DataAccessException if there is any problem executing the query 410 * @see org.springframework.jdbc.core.JdbcTemplate#queryForRowSet(String) 411 * @see org.springframework.jdbc.core.SqlRowSetResultSetExtractor 412 * @see javax.sql.rowset.CachedRowSet 413 */ 414 SqlRowSet queryForRowSet(String sql, SqlParameterSource paramSource) throws DataAccessException; 415 416 /** 417 * Query given SQL to create a prepared statement from SQL and a 418 * list of arguments to bind to the query, expecting a SqlRowSet. 419 * <p>The results will be mapped to an SqlRowSet which holds the data in a 420 * disconnected fashion. This wrapper will translate any SQLExceptions thrown. 421 * <p>Note that, for the default implementation, JDBC RowSet support needs to 422 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl} 423 * class is used, which is part of JDK 1.5+ and also available separately as part of 424 * Sun's JDBC RowSet Implementations download (rowset.jar). 425 * @param sql the SQL query to execute 426 * @param paramMap map of parameters to bind to the query 427 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 428 * @return a SqlRowSet representation (possibly a wrapper around a 429 * {@code javax.sql.rowset.CachedRowSet}) 430 * @throws DataAccessException if there is any problem executing the query 431 * @see org.springframework.jdbc.core.JdbcTemplate#queryForRowSet(String) 432 * @see org.springframework.jdbc.core.SqlRowSetResultSetExtractor 433 * @see javax.sql.rowset.CachedRowSet 434 */ 435 SqlRowSet queryForRowSet(String sql, Map<String, ?> paramMap) throws DataAccessException; 436 437 /** 438 * Issue an update via a prepared statement, binding the given arguments. 439 * @param sql the SQL containing named parameters 440 * @param paramSource container of arguments and SQL types to bind to the query 441 * @return the number of rows affected 442 * @throws DataAccessException if there is any problem issuing the update 443 */ 444 int update(String sql, SqlParameterSource paramSource) throws DataAccessException; 445 446 /** 447 * Issue an update via a prepared statement, binding the given arguments. 448 * @param sql the SQL containing named parameters 449 * @param paramMap map of parameters to bind to the query 450 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 451 * @return the number of rows affected 452 * @throws DataAccessException if there is any problem issuing the update 453 */ 454 int update(String sql, Map<String, ?> paramMap) throws DataAccessException; 455 456 /** 457 * Issue an update via a prepared statement, binding the given arguments, 458 * returning generated keys. 459 * @param sql the SQL containing named parameters 460 * @param paramSource container of arguments and SQL types to bind to the query 461 * @param generatedKeyHolder a {@link KeyHolder} that will hold the generated keys 462 * @return the number of rows affected 463 * @throws DataAccessException if there is any problem issuing the update 464 * @see MapSqlParameterSource 465 * @see org.springframework.jdbc.support.GeneratedKeyHolder 466 */ 467 int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder) 468 throws DataAccessException; 469 470 /** 471 * Issue an update via a prepared statement, binding the given arguments, 472 * returning generated keys. 473 * @param sql the SQL containing named parameters 474 * @param paramSource container of arguments and SQL types to bind to the query 475 * @param generatedKeyHolder a {@link KeyHolder} that will hold the generated keys 476 * @param keyColumnNames names of the columns that will have keys generated for them 477 * @return the number of rows affected 478 * @throws DataAccessException if there is any problem issuing the update 479 * @see MapSqlParameterSource 480 * @see org.springframework.jdbc.support.GeneratedKeyHolder 481 */ 482 int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames) 483 throws DataAccessException; 484 485 /** 486 * Executes a batch using the supplied SQL statement with the batch of supplied arguments. 487 * @param sql the SQL statement to execute 488 * @param batchValues the array of Maps containing the batch of arguments for the query 489 * @return an array containing the numbers of rows affected by each update in the batch 490 * @throws DataAccessException if there is any problem issuing the update 491 */ 492 int[] batchUpdate(String sql, Map<String, ?>[] batchValues); 493 494 /** 495 * Execute a batch using the supplied SQL statement with the batch of supplied arguments. 496 * @param sql the SQL statement to execute 497 * @param batchArgs the array of {@link SqlParameterSource} containing the batch of arguments for the query 498 * @return an array containing the numbers of rows affected by each update in the batch 499 * @throws DataAccessException if there is any problem issuing the update 500 */ 501 int[] batchUpdate(String sql, SqlParameterSource[] batchArgs); 502 503}