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