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; 018 019import java.util.Collection; 020import java.util.List; 021import java.util.Map; 022 023import org.springframework.dao.DataAccessException; 024import org.springframework.dao.IncorrectResultSizeDataAccessException; 025import org.springframework.jdbc.support.KeyHolder; 026import org.springframework.jdbc.support.rowset.SqlRowSet; 027 028/** 029 * Interface specifying a basic set of JDBC operations. 030 * Implemented by {@link JdbcTemplate}. Not often used directly, but a useful 031 * option to enhance testability, as it can easily be mocked or stubbed. 032 * 033 * <p>Alternatively, the standard JDBC infrastructure can be mocked. 034 * However, mocking this interface constitutes significantly less work. 035 * As an alternative to a mock objects approach to testing data access code, 036 * consider the powerful integration testing support provided in the 037 * {@code org.springframework.test} package, shipped in 038 * {@code spring-test.jar}. 039 * 040 * @author Rod Johnson 041 * @author Juergen Hoeller 042 * @see JdbcTemplate 043 */ 044public interface JdbcOperations { 045 046 //------------------------------------------------------------------------- 047 // Methods dealing with a plain java.sql.Connection 048 //------------------------------------------------------------------------- 049 050 /** 051 * Execute a JDBC data access operation, implemented as callback action 052 * working on a JDBC Connection. This allows for implementing arbitrary 053 * data access operations, within Spring's managed JDBC environment: 054 * that is, participating in Spring-managed transactions and converting 055 * JDBC SQLExceptions into Spring's DataAccessException hierarchy. 056 * <p>The callback action can return a result object, for example a domain 057 * object or a collection of domain objects. 058 * @param action a callback object that specifies the action 059 * @return a result object returned by the action, or {@code null} if none 060 * @throws DataAccessException if there is any problem 061 */ 062 <T> T execute(ConnectionCallback<T> action) throws DataAccessException; 063 064 065 //------------------------------------------------------------------------- 066 // Methods dealing with static SQL (java.sql.Statement) 067 //------------------------------------------------------------------------- 068 069 /** 070 * Execute a JDBC data access operation, implemented as callback action 071 * working on a JDBC Statement. This allows for implementing arbitrary data 072 * access operations on a single Statement, within Spring's managed JDBC 073 * environment: that is, participating in Spring-managed transactions and 074 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 075 * <p>The callback action can return a result object, for example a domain 076 * object or a collection of domain objects. 077 * @param action a callback that specifies the action 078 * @return a result object returned by the action, or {@code null} if none 079 * @throws DataAccessException if there is any problem 080 */ 081 <T> T execute(StatementCallback<T> action) throws DataAccessException; 082 083 /** 084 * Issue a single SQL execute, typically a DDL statement. 085 * @param sql static SQL to execute 086 * @throws DataAccessException if there is any problem 087 */ 088 void execute(String sql) throws DataAccessException; 089 090 /** 091 * Execute a query given static SQL, reading the ResultSet with a 092 * ResultSetExtractor. 093 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 094 * execute a static query with a PreparedStatement, use the overloaded 095 * {@code query} method with {@code null} as argument array. 096 * @param sql the SQL query to execute 097 * @param rse a callback that will extract all rows of results 098 * @return an arbitrary result object, as returned by the ResultSetExtractor 099 * @throws DataAccessException if there is any problem executing the query 100 * @see #query(String, ResultSetExtractor, Object...) 101 */ 102 <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException; 103 104 /** 105 * Execute a query given static SQL, reading the ResultSet on a per-row 106 * basis with a RowCallbackHandler. 107 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 108 * execute a static query with a PreparedStatement, use the overloaded 109 * {@code query} method with {@code null} as argument array. 110 * @param sql the SQL query to execute 111 * @param rch a callback that will extract results, one row at a time 112 * @throws DataAccessException if there is any problem executing the query 113 * @see #query(String, RowCallbackHandler, Object...) 114 */ 115 void query(String sql, RowCallbackHandler rch) throws DataAccessException; 116 117 /** 118 * Execute a query given static SQL, mapping each row to a result object 119 * via a RowMapper. 120 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 121 * execute a static query with a PreparedStatement, use the overloaded 122 * {@code query} method with {@code null} as argument array. 123 * @param sql the SQL query to execute 124 * @param rowMapper a callback that will map one object per row 125 * @return the result List, containing mapped objects 126 * @throws DataAccessException if there is any problem executing the query 127 * @see #query(String, RowMapper, Object...) 128 */ 129 <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException; 130 131 /** 132 * Execute a query given static SQL, mapping a single result row to a 133 * result object via a RowMapper. 134 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 135 * execute a static query with a PreparedStatement, use the overloaded 136 * {@link #queryForObject(String, RowMapper, Object...)} method with 137 * {@code null} as argument array. 138 * @param sql the SQL query to execute 139 * @param rowMapper a callback that will map one object per row 140 * @return the single mapped object (may be {@code null} if the given 141 * {@link RowMapper} returned {@code} null) 142 * @throws IncorrectResultSizeDataAccessException if the query does not 143 * return exactly one row 144 * @throws DataAccessException if there is any problem executing the query 145 * @see #queryForObject(String, RowMapper, Object...) 146 */ 147 <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException; 148 149 /** 150 * Execute a query for a result object, given static SQL. 151 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 152 * execute a static query with a PreparedStatement, use the overloaded 153 * {@link #queryForObject(String, Class, Object...)} method with 154 * {@code null} as argument array. 155 * <p>This method is useful for running static SQL with a known outcome. 156 * The query is expected to be a single row/single column query; the returned 157 * result will be directly mapped to the corresponding object type. 158 * @param sql the SQL query to execute 159 * @param requiredType the type that the result object is expected to match 160 * @return the result object of the required type, or {@code null} in case of SQL NULL 161 * @throws IncorrectResultSizeDataAccessException if the query does not return 162 * exactly one row, or does not return exactly one column in that row 163 * @throws DataAccessException if there is any problem executing the query 164 * @see #queryForObject(String, Class, Object...) 165 */ 166 <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException; 167 168 /** 169 * Execute a query for a result map, given static SQL. 170 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 171 * execute a static query with a PreparedStatement, use the overloaded 172 * {@link #queryForMap(String, Object...)} method with {@code null} 173 * as argument array. 174 * <p>The query is expected to be a single row query; the result row will be 175 * mapped to a Map (one entry for each column, using the column name as the key). 176 * @param sql the SQL query to execute 177 * @return the result Map (one entry per column, with column name as key) 178 * @throws IncorrectResultSizeDataAccessException if the query does not 179 * return exactly one row 180 * @throws DataAccessException if there is any problem executing the query 181 * @see #queryForMap(String, Object...) 182 * @see ColumnMapRowMapper 183 */ 184 Map<String, Object> queryForMap(String sql) throws DataAccessException; 185 186 /** 187 * Execute a query for a result list, given static SQL. 188 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 189 * execute a static query with a PreparedStatement, use the overloaded 190 * {@code queryForList} method with {@code null} as argument array. 191 * <p>The results will be mapped to a List (one entry for each row) of 192 * result objects, each of them matching the specified element type. 193 * @param sql the SQL query to execute 194 * @param elementType the required type of element in the result list 195 * (for example, {@code Integer.class}) 196 * @return a List of objects that match the specified element type 197 * @throws DataAccessException if there is any problem executing the query 198 * @see #queryForList(String, Class, Object...) 199 * @see SingleColumnRowMapper 200 */ 201 <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException; 202 203 /** 204 * Execute a query for a result list, given static SQL. 205 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 206 * execute a static query with a PreparedStatement, use the overloaded 207 * {@code queryForList} method with {@code null} as argument array. 208 * <p>The results will be mapped to a List (one entry for each row) of 209 * Maps (one entry for each column using the column name as the key). 210 * Each element in the list will be of the form returned by this interface's 211 * {@code queryForMap} methods. 212 * @param sql the SQL query to execute 213 * @return an List that contains a Map per row 214 * @throws DataAccessException if there is any problem executing the query 215 * @see #queryForList(String, Object...) 216 */ 217 List<Map<String, Object>> queryForList(String sql) throws DataAccessException; 218 219 /** 220 * Execute a query for a SqlRowSet, given static SQL. 221 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to 222 * execute a static query with a PreparedStatement, use the overloaded 223 * {@code queryForRowSet} method with {@code null} as argument array. 224 * <p>The results will be mapped to an SqlRowSet which holds the data in a 225 * disconnected fashion. This wrapper will translate any SQLExceptions thrown. 226 * <p>Note that, for the default implementation, JDBC RowSet support needs to 227 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl} 228 * class is used, which is part of JDK 1.5+ and also available separately as part of 229 * Sun's JDBC RowSet Implementations download (rowset.jar). 230 * @param sql the SQL query to execute 231 * @return a SqlRowSet representation (possibly a wrapper around a 232 * {@code javax.sql.rowset.CachedRowSet}) 233 * @throws DataAccessException if there is any problem executing the query 234 * @see #queryForRowSet(String, Object...) 235 * @see SqlRowSetResultSetExtractor 236 * @see javax.sql.rowset.CachedRowSet 237 */ 238 SqlRowSet queryForRowSet(String sql) throws DataAccessException; 239 240 /** 241 * Issue a single SQL update operation (such as an insert, update or delete statement). 242 * @param sql static SQL to execute 243 * @return the number of rows affected 244 * @throws DataAccessException if there is any problem. 245 */ 246 int update(String sql) throws DataAccessException; 247 248 /** 249 * Issue multiple SQL updates on a single JDBC Statement using batching. 250 * <p>Will fall back to separate updates on a single Statement if the JDBC 251 * driver does not support batch updates. 252 * @param sql defining an array of SQL statements that will be executed. 253 * @return an array of the number of rows affected by each statement 254 * @throws DataAccessException if there is any problem executing the batch 255 */ 256 int[] batchUpdate(String... sql) throws DataAccessException; 257 258 259 //------------------------------------------------------------------------- 260 // Methods dealing with prepared statements 261 //------------------------------------------------------------------------- 262 263 /** 264 * Execute a JDBC data access operation, implemented as callback action 265 * working on a JDBC PreparedStatement. This allows for implementing arbitrary 266 * data access operations on a single Statement, within Spring's managed JDBC 267 * environment: that is, participating in Spring-managed transactions and 268 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 269 * <p>The callback action can return a result object, for example a domain 270 * object or a collection of domain objects. 271 * @param psc a callback that creates a PreparedStatement given a Connection 272 * @param action a callback that specifies the action 273 * @return a result object returned by the action, or {@code null} if none 274 * @throws DataAccessException if there is any problem 275 */ 276 <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) throws DataAccessException; 277 278 /** 279 * Execute a JDBC data access operation, implemented as callback action 280 * working on a JDBC PreparedStatement. This allows for implementing arbitrary 281 * data access operations on a single Statement, within Spring's managed JDBC 282 * environment: that is, participating in Spring-managed transactions and 283 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 284 * <p>The callback action can return a result object, for example a domain 285 * object or a collection of domain objects. 286 * @param sql the SQL to execute 287 * @param action a callback that specifies the action 288 * @return a result object returned by the action, or {@code null} if none 289 * @throws DataAccessException if there is any problem 290 */ 291 <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException; 292 293 /** 294 * Query using a prepared statement, reading the ResultSet with a ResultSetExtractor. 295 * <p>A PreparedStatementCreator can either be implemented directly or 296 * configured through a PreparedStatementCreatorFactory. 297 * @param psc a callback that creates a PreparedStatement given a Connection 298 * @param rse a callback that will extract results 299 * @return an arbitrary result object, as returned by the ResultSetExtractor 300 * @throws DataAccessException if there is any problem 301 * @see PreparedStatementCreatorFactory 302 */ 303 <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException; 304 305 /** 306 * Query using a prepared statement, reading the ResultSet with a ResultSetExtractor. 307 * @param sql the SQL query to execute 308 * @param pss a callback that knows how to set values on the prepared statement. 309 * If this is {@code null}, the SQL will be assumed to contain no bind parameters. 310 * Even if there are no bind parameters, this callback may be used to set the 311 * fetch size and other performance options. 312 * @param rse a callback that will extract results 313 * @return an arbitrary result object, as returned by the ResultSetExtractor 314 * @throws DataAccessException if there is any problem 315 */ 316 <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException; 317 318 /** 319 * Query given SQL to create a prepared statement from SQL and a list of arguments 320 * to bind to the query, reading the ResultSet with a ResultSetExtractor. 321 * @param sql the SQL query to execute 322 * @param args arguments to bind to the query 323 * @param argTypes the SQL types of the arguments 324 * (constants from {@code java.sql.Types}) 325 * @param rse a callback that will extract results 326 * @return an arbitrary result object, as returned by the ResultSetExtractor 327 * @throws DataAccessException if the query fails 328 * @see java.sql.Types 329 */ 330 <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException; 331 332 /** 333 * Query given SQL to create a prepared statement from SQL and a list of arguments 334 * to bind to the query, reading the ResultSet with a ResultSetExtractor. 335 * @param sql the SQL query to execute 336 * @param args arguments to bind to the query 337 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 338 * may also contain {@link SqlParameterValue} objects which indicate not 339 * only the argument value but also the SQL type and optionally the scale 340 * @param rse a callback that will extract results 341 * @return an arbitrary result object, as returned by the ResultSetExtractor 342 * @throws DataAccessException if the query fails 343 */ 344 <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException; 345 346 /** 347 * Query given SQL to create a prepared statement from SQL and a list of arguments 348 * to bind to the query, reading the ResultSet with a ResultSetExtractor. 349 * @param sql the SQL query to execute 350 * @param rse a callback that will extract results 351 * @param args arguments to bind to the query 352 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 353 * may also contain {@link SqlParameterValue} objects which indicate not 354 * only the argument value but also the SQL type and optionally the scale 355 * @return an arbitrary result object, as returned by the ResultSetExtractor 356 * @throws DataAccessException if the query fails 357 * @since 3.0.1 358 */ 359 <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException; 360 361 /** 362 * Query using a prepared statement, reading the ResultSet on a per-row basis 363 * with a RowCallbackHandler. 364 * <p>A PreparedStatementCreator can either be implemented directly or 365 * configured through a PreparedStatementCreatorFactory. 366 * @param psc a callback that creates a PreparedStatement given a Connection 367 * @param rch a callback that will extract results, one row at a time 368 * @throws DataAccessException if there is any problem 369 * @see PreparedStatementCreatorFactory 370 */ 371 void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException; 372 373 /** 374 * Query given SQL to create a prepared statement from SQL and a 375 * PreparedStatementSetter implementation that knows how to bind values to the 376 * query, reading the ResultSet on a per-row basis with a RowCallbackHandler. 377 * @param sql the SQL query to execute 378 * @param pss a callback that knows how to set values on the prepared statement. 379 * If this is {@code null}, the SQL will be assumed to contain no bind parameters. 380 * Even if there are no bind parameters, this callback may be used to set the 381 * fetch size and other performance options. 382 * @param rch a callback that will extract results, one row at a time 383 * @throws DataAccessException if the query fails 384 */ 385 void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException; 386 387 /** 388 * Query given SQL to create a prepared statement from SQL and a list of 389 * arguments to bind to the query, reading the ResultSet on a per-row basis 390 * with a RowCallbackHandler. 391 * @param sql the SQL query to execute 392 * @param args arguments to bind to the query 393 * @param argTypes the SQL types of the arguments 394 * (constants from {@code java.sql.Types}) 395 * @param rch a callback that will extract results, one row at a time 396 * @throws DataAccessException if the query fails 397 * @see java.sql.Types 398 */ 399 void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException; 400 401 /** 402 * Query given SQL to create a prepared statement from SQL and a list of 403 * arguments to bind to the query, reading the ResultSet on a per-row basis 404 * with a RowCallbackHandler. 405 * @param sql the SQL query to execute 406 * @param args arguments to bind to the query 407 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 408 * may also contain {@link SqlParameterValue} objects which indicate not 409 * only the argument value but also the SQL type and optionally the scale 410 * @param rch a callback that will extract results, one row at a time 411 * @throws DataAccessException if the query fails 412 */ 413 void query(String sql, Object[] args, RowCallbackHandler rch) throws DataAccessException; 414 415 /** 416 * Query given SQL to create a prepared statement from SQL and a list of 417 * arguments to bind to the query, reading the ResultSet on a per-row basis 418 * with a RowCallbackHandler. 419 * @param sql the SQL query to execute 420 * @param rch a callback that will extract results, one row at a time 421 * @param args arguments to bind to the query 422 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 423 * may also contain {@link SqlParameterValue} objects which indicate not 424 * only the argument value but also the SQL type and optionally the scale 425 * @throws DataAccessException if the query fails 426 * @since 3.0.1 427 */ 428 void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException; 429 430 /** 431 * Query using a prepared statement, mapping each row to a result object 432 * via a RowMapper. 433 * <p>A PreparedStatementCreator can either be implemented directly or 434 * configured through a PreparedStatementCreatorFactory. 435 * @param psc a callback that creates a PreparedStatement given a Connection 436 * @param rowMapper a callback that will map one object per row 437 * @return the result List, containing mapped objects 438 * @throws DataAccessException if there is any problem 439 * @see PreparedStatementCreatorFactory 440 */ 441 <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException; 442 443 /** 444 * Query given SQL to create a prepared statement from SQL and a 445 * PreparedStatementSetter implementation that knows how to bind values 446 * to the query, mapping each row to a result object via a RowMapper. 447 * @param sql the SQL query to execute 448 * @param pss a callback that knows how to set values on the prepared statement. 449 * If this is {@code null}, the SQL will be assumed to contain no bind parameters. 450 * Even if there are no bind parameters, this callback may be used to set the 451 * fetch size and other performance options. 452 * @param rowMapper a callback that will map one object per row 453 * @return the result List, containing mapped objects 454 * @throws DataAccessException if the query fails 455 */ 456 <T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException; 457 458 /** 459 * Query given SQL to create a prepared statement from SQL and a list of 460 * arguments to bind to the query, mapping each row to a result object 461 * via a RowMapper. 462 * @param sql the SQL query to execute 463 * @param args arguments to bind to the query 464 * @param argTypes the SQL types of the arguments 465 * (constants from {@code java.sql.Types}) 466 * @param rowMapper a callback that will map one object per row 467 * @return the result List, containing mapped objects 468 * @throws DataAccessException if the query fails 469 * @see java.sql.Types 470 */ 471 <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException; 472 473 /** 474 * Query given SQL to create a prepared statement from SQL and a list of 475 * arguments to bind to the query, mapping each row to a result object 476 * via a RowMapper. 477 * @param sql the SQL query to execute 478 * @param args arguments to bind to the query 479 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 480 * may also contain {@link SqlParameterValue} objects which indicate not 481 * only the argument value but also the SQL type and optionally the scale 482 * @param rowMapper a callback that will map one object per row 483 * @return the result List, containing mapped objects 484 * @throws DataAccessException if the query fails 485 */ 486 <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException; 487 488 /** 489 * Query given SQL to create a prepared statement from SQL and a list of 490 * arguments to bind to the query, mapping each row to a result object 491 * via a RowMapper. 492 * @param sql the SQL query to execute 493 * @param rowMapper a callback that will map one object per row 494 * @param args arguments to bind to the query 495 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 496 * may also contain {@link SqlParameterValue} objects which indicate not 497 * only the argument value but also the SQL type and optionally the scale 498 * @return the result List, containing mapped objects 499 * @throws DataAccessException if the query fails 500 * @since 3.0.1 501 */ 502 <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException; 503 504 /** 505 * Query given SQL to create a prepared statement from SQL and a list 506 * of arguments to bind to the query, mapping a single result row to a 507 * result object via a RowMapper. 508 * @param sql the SQL query to execute 509 * @param args arguments to bind to the query 510 * (leaving it to the PreparedStatement to guess the corresponding SQL type) 511 * @param argTypes the SQL types of the arguments 512 * (constants from {@code java.sql.Types}) 513 * @param rowMapper a callback that will map one object per row 514 * @return the single mapped object (may be {@code null} if the given 515 * {@link RowMapper} returned {@code} null) 516 * @throws IncorrectResultSizeDataAccessException if the query does not 517 * return exactly one row 518 * @throws DataAccessException if the query fails 519 */ 520 <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) 521 throws DataAccessException; 522 523 /** 524 * Query given SQL to create a prepared statement from SQL and a list 525 * of arguments to bind to the query, mapping a single result row to a 526 * result object via a RowMapper. 527 * @param sql the SQL query to execute 528 * @param args arguments to bind to the query 529 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 530 * may also contain {@link SqlParameterValue} objects which indicate not 531 * only the argument value but also the SQL type and optionally the scale 532 * @param rowMapper a callback that will map one object per row 533 * @return the single mapped object (may be {@code null} if the given 534 * {@link RowMapper} returned {@code} null) 535 * @throws IncorrectResultSizeDataAccessException if the query does not 536 * return exactly one row 537 * @throws DataAccessException if the query fails 538 */ 539 <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException; 540 541 /** 542 * Query given SQL to create a prepared statement from SQL and a list 543 * of arguments to bind to the query, mapping a single result row to a 544 * result object via a RowMapper. 545 * @param sql the SQL query to execute 546 * @param rowMapper a callback that will map one object per row 547 * @param args arguments to bind to the query 548 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 549 * may also contain {@link SqlParameterValue} objects which indicate not 550 * only the argument value but also the SQL type and optionally the scale 551 * @return the single mapped object (may be {@code null} if the given 552 * {@link RowMapper} returned {@code} null) 553 * @throws IncorrectResultSizeDataAccessException if the query does not 554 * return exactly one row 555 * @throws DataAccessException if the query fails 556 * @since 3.0.1 557 */ 558 <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException; 559 560 /** 561 * Query given SQL to create a prepared statement from SQL and a list of 562 * arguments to bind to the query, expecting a result object. 563 * <p>The query is expected to be a single row/single column query; the returned 564 * result will be directly mapped to the corresponding object type. 565 * @param sql the SQL query to execute 566 * @param args arguments to bind to the query 567 * @param argTypes the SQL types of the arguments 568 * (constants from {@code java.sql.Types}) 569 * @param requiredType the type that the result object is expected to match 570 * @return the result object of the required type, or {@code null} in case of SQL NULL 571 * @throws IncorrectResultSizeDataAccessException if the query does not return 572 * exactly one row, or does not return exactly one column in that row 573 * @throws DataAccessException if the query fails 574 * @see #queryForObject(String, Class) 575 * @see java.sql.Types 576 */ 577 <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType) 578 throws DataAccessException; 579 580 /** 581 * Query given SQL to create a prepared statement from SQL and a list of 582 * arguments to bind to the query, expecting a result object. 583 * <p>The query is expected to be a single row/single column query; the returned 584 * result will be directly mapped to the corresponding object type. 585 * @param sql the SQL query to execute 586 * @param args arguments to bind to the query 587 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 588 * may also contain {@link SqlParameterValue} objects which indicate not 589 * only the argument value but also the SQL type and optionally the scale 590 * @param requiredType the type that the result object is expected to match 591 * @return the result object of the required type, or {@code null} in case of SQL NULL 592 * @throws IncorrectResultSizeDataAccessException if the query does not return 593 * exactly one row, or does not return exactly one column in that row 594 * @throws DataAccessException if the query fails 595 * @see #queryForObject(String, Class) 596 */ 597 <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException; 598 599 /** 600 * Query given SQL to create a prepared statement from SQL and a list of 601 * arguments to bind to the query, expecting a result object. 602 * <p>The query is expected to be a single row/single column query; the returned 603 * result will be directly mapped to the corresponding object type. 604 * @param sql the SQL query to execute 605 * @param requiredType the type that the result object is expected to match 606 * @param args arguments to bind to the query 607 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 608 * may also contain {@link SqlParameterValue} objects which indicate not 609 * only the argument value but also the SQL type and optionally the scale 610 * @return the result object of the required type, or {@code null} in case of SQL NULL 611 * @throws IncorrectResultSizeDataAccessException if the query does not return 612 * exactly one row, or does not return exactly one column in that row 613 * @throws DataAccessException if the query fails 614 * @since 3.0.1 615 * @see #queryForObject(String, Class) 616 */ 617 <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException; 618 619 /** 620 * Query given SQL to create a prepared statement from SQL and a list of 621 * arguments to bind to the query, expecting a result map. 622 * <p>The query is expected to be a single row query; the result row will be 623 * mapped to a Map (one entry for each column, using the column name as the key). 624 * @param sql the SQL query to execute 625 * @param args arguments to bind to the query 626 * @param argTypes the SQL types of the arguments 627 * (constants from {@code java.sql.Types}) 628 * @return the result Map (one entry per column, with column name as key) 629 * @throws IncorrectResultSizeDataAccessException if the query does not 630 * return exactly one row 631 * @throws DataAccessException if the query fails 632 * @see #queryForMap(String) 633 * @see ColumnMapRowMapper 634 * @see java.sql.Types 635 */ 636 Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException; 637 638 /** 639 * Query given SQL to create a prepared statement from SQL and a list of 640 * arguments to bind to the query, expecting a result map. 641 * <p>The {@code queryForMap} methods defined by this interface are appropriate 642 * when you don't have a domain model. Otherwise, consider using one of the 643 * {@code queryForObject} methods. 644 * <p>The query is expected to be a single row query; the result row will be 645 * mapped to a Map (one entry for each column, using the column name as the key). 646 * @param sql the SQL query to execute 647 * @param args arguments to bind to the query 648 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 649 * may also contain {@link SqlParameterValue} objects which indicate not 650 * only the argument value but also the SQL type and optionally the scale 651 * @return the result Map (one entry for each column, using the 652 * column name as the key) 653 * @throws IncorrectResultSizeDataAccessException if the query does not 654 * return exactly one row 655 * @throws DataAccessException if the query fails 656 * @see #queryForMap(String) 657 * @see ColumnMapRowMapper 658 */ 659 Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException; 660 661 /** 662 * Query given SQL to create a prepared statement from SQL and a list of 663 * arguments to bind to the query, expecting a result list. 664 * <p>The results will be mapped to a List (one entry for each row) of 665 * result objects, each of them matching the specified element type. 666 * @param sql the SQL query to execute 667 * @param args arguments to bind to the query 668 * @param argTypes the SQL types of the arguments 669 * (constants from {@code java.sql.Types}) 670 * @param elementType the required type of element in the result list 671 * (for example, {@code Integer.class}) 672 * @return a List of objects that match the specified element type 673 * @throws DataAccessException if the query fails 674 * @see #queryForList(String, Class) 675 * @see SingleColumnRowMapper 676 */ 677 <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) 678 throws DataAccessException; 679 680 /** 681 * Query given SQL to create a prepared statement from SQL and a list of 682 * arguments to bind to the query, expecting a result list. 683 * <p>The results will be mapped to a List (one entry for each row) of 684 * result objects, each of them matching the specified element type. 685 * @param sql the SQL query to execute 686 * @param args arguments to bind to the query 687 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 688 * may also contain {@link SqlParameterValue} objects which indicate not 689 * only the argument value but also the SQL type and optionally the scale 690 * @param elementType the required type of element in the result list 691 * (for example, {@code Integer.class}) 692 * @return a List of objects that match the specified element type 693 * @throws DataAccessException if the query fails 694 * @see #queryForList(String, Class) 695 * @see SingleColumnRowMapper 696 */ 697 <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException; 698 699 /** 700 * Query given SQL to create a prepared statement from SQL and a list of 701 * arguments to bind to the query, expecting a result list. 702 * <p>The results will be mapped to a List (one entry for each row) of 703 * result objects, each of them matching the specified element type. 704 * @param sql the SQL query to execute 705 * @param elementType the required type of element in the result list 706 * (for example, {@code Integer.class}) 707 * @param args arguments to bind to the query 708 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 709 * may also contain {@link SqlParameterValue} objects which indicate not 710 * only the argument value but also the SQL type and optionally the scale 711 * @return a List of objects that match the specified element type 712 * @throws DataAccessException if the query fails 713 * @since 3.0.1 714 * @see #queryForList(String, Class) 715 * @see SingleColumnRowMapper 716 */ 717 <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) throws DataAccessException; 718 719 /** 720 * Query given SQL to create a prepared statement from SQL and a list of 721 * arguments to bind to the query, expecting a result list. 722 * <p>The results will be mapped to a List (one entry for each row) of 723 * Maps (one entry for each column, using the column name as the key). 724 * Each element in the list will be of the form returned by this interface's 725 * {@code queryForMap} methods. 726 * @param sql the SQL query to execute 727 * @param args arguments to bind to the query 728 * @param argTypes the SQL types of the arguments 729 * (constants from {@code java.sql.Types}) 730 * @return a List that contains a Map per row 731 * @throws DataAccessException if the query fails 732 * @see #queryForList(String) 733 * @see java.sql.Types 734 */ 735 List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException; 736 737 /** 738 * Query given SQL to create a prepared statement from SQL and a list of 739 * arguments to bind to the query, expecting a result list. 740 * <p>The results will be mapped to a List (one entry for each row) of 741 * Maps (one entry for each column, using the column name as the key). 742 * Each element in the list will be of the form returned by this interface's 743 * {@code queryForMap} methods. 744 * @param sql the SQL query to execute 745 * @param args arguments to bind to the query 746 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 747 * may also contain {@link SqlParameterValue} objects which indicate not 748 * only the argument value but also the SQL type and optionally the scale 749 * @return a List that contains a Map per row 750 * @throws DataAccessException if the query fails 751 * @see #queryForList(String) 752 */ 753 List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException; 754 755 /** 756 * Query given SQL to create a prepared statement from SQL and a list of 757 * arguments to bind to the query, expecting a SqlRowSet. 758 * <p>The results will be mapped to an SqlRowSet which holds the data in a 759 * disconnected fashion. This wrapper will translate any SQLExceptions thrown. 760 * <p>Note that, for the default implementation, JDBC RowSet support needs to 761 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl} 762 * class is used, which is part of JDK 1.5+ and also available separately as part of 763 * Sun's JDBC RowSet Implementations download (rowset.jar). 764 * @param sql the SQL query to execute 765 * @param args arguments to bind to the query 766 * @param argTypes the SQL types of the arguments 767 * (constants from {@code java.sql.Types}) 768 * @return a SqlRowSet representation (possibly a wrapper around a 769 * {@code javax.sql.rowset.CachedRowSet}) 770 * @throws DataAccessException if there is any problem executing the query 771 * @see #queryForRowSet(String) 772 * @see SqlRowSetResultSetExtractor 773 * @see javax.sql.rowset.CachedRowSet 774 * @see java.sql.Types 775 */ 776 SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException; 777 778 /** 779 * Query given SQL to create a prepared statement from SQL and a list of 780 * arguments to bind to the query, expecting a SqlRowSet. 781 * <p>The results will be mapped to an SqlRowSet which holds the data in a 782 * disconnected fashion. This wrapper will translate any SQLExceptions thrown. 783 * <p>Note that, for the default implementation, JDBC RowSet support needs to 784 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl} 785 * class is used, which is part of JDK 1.5+ and also available separately as part of 786 * Sun's JDBC RowSet Implementations download (rowset.jar). 787 * @param sql the SQL query to execute 788 * @param args arguments to bind to the query 789 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 790 * may also contain {@link SqlParameterValue} objects which indicate not 791 * only the argument value but also the SQL type and optionally the scale 792 * @return a SqlRowSet representation (possibly a wrapper around a 793 * {@code javax.sql.rowset.CachedRowSet}) 794 * @throws DataAccessException if there is any problem executing the query 795 * @see #queryForRowSet(String) 796 * @see SqlRowSetResultSetExtractor 797 * @see javax.sql.rowset.CachedRowSet 798 */ 799 SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException; 800 801 /** 802 * Issue a single SQL update operation (such as an insert, update or delete 803 * statement) using a PreparedStatementCreator to provide SQL and any 804 * required parameters. 805 * <p>A PreparedStatementCreator can either be implemented directly or 806 * configured through a PreparedStatementCreatorFactory. 807 * @param psc a callback that provides SQL and any necessary parameters 808 * @return the number of rows affected 809 * @throws DataAccessException if there is any problem issuing the update 810 * @see PreparedStatementCreatorFactory 811 */ 812 int update(PreparedStatementCreator psc) throws DataAccessException; 813 814 /** 815 * Issue an update statement using a PreparedStatementCreator to provide SQL and 816 * any required parameters. Generated keys will be put into the given KeyHolder. 817 * <p>Note that the given PreparedStatementCreator has to create a statement 818 * with activated extraction of generated keys (a JDBC 3.0 feature). This can 819 * either be done directly or through using a PreparedStatementCreatorFactory. 820 * @param psc a callback that provides SQL and any necessary parameters 821 * @param generatedKeyHolder a KeyHolder that will hold the generated keys 822 * @return the number of rows affected 823 * @throws DataAccessException if there is any problem issuing the update 824 * @see PreparedStatementCreatorFactory 825 * @see org.springframework.jdbc.support.GeneratedKeyHolder 826 */ 827 int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException; 828 829 /** 830 * Issue an update statement using a PreparedStatementSetter to set bind parameters, 831 * with given SQL. Simpler than using a PreparedStatementCreator as this method 832 * will create the PreparedStatement: The PreparedStatementSetter just needs to 833 * set parameters. 834 * @param sql the SQL containing bind parameters 835 * @param pss helper that sets bind parameters. If this is {@code null} 836 * we run an update with static SQL. 837 * @return the number of rows affected 838 * @throws DataAccessException if there is any problem issuing the update 839 */ 840 int update(String sql, PreparedStatementSetter pss) throws DataAccessException; 841 842 /** 843 * Issue a single SQL update operation (such as an insert, update or delete statement) 844 * via a prepared statement, binding the given arguments. 845 * @param sql the SQL containing bind parameters 846 * @param args arguments to bind to the query 847 * @param argTypes the SQL types of the arguments 848 * (constants from {@code java.sql.Types}) 849 * @return the number of rows affected 850 * @throws DataAccessException if there is any problem issuing the update 851 * @see java.sql.Types 852 */ 853 int update(String sql, Object[] args, int[] argTypes) throws DataAccessException; 854 855 /** 856 * Issue a single SQL update operation (such as an insert, update or delete statement) 857 * via a prepared statement, binding the given arguments. 858 * @param sql the SQL containing bind parameters 859 * @param args arguments to bind to the query 860 * (leaving it to the PreparedStatement to guess the corresponding SQL type); 861 * may also contain {@link SqlParameterValue} objects which indicate not 862 * only the argument value but also the SQL type and optionally the scale 863 * @return the number of rows affected 864 * @throws DataAccessException if there is any problem issuing the update 865 */ 866 int update(String sql, Object... args) throws DataAccessException; 867 868 /** 869 * Issue multiple update statements on a single PreparedStatement, 870 * using batch updates and a BatchPreparedStatementSetter to set values. 871 * <p>Will fall back to separate updates on a single PreparedStatement 872 * if the JDBC driver does not support batch updates. 873 * @param sql defining PreparedStatement that will be reused. 874 * All statements in the batch will use the same SQL. 875 * @param pss object to set parameters on the PreparedStatement 876 * created by this method 877 * @return an array of the number of rows affected by each statement 878 * @throws DataAccessException if there is any problem issuing the update 879 */ 880 int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException; 881 882 /** 883 * Execute a batch using the supplied SQL statement with the batch of supplied arguments. 884 * @param sql the SQL statement to execute 885 * @param batchArgs the List of Object arrays containing the batch of arguments for the query 886 * @return an array containing the numbers of rows affected by each update in the batch 887 * @throws DataAccessException if there is any problem issuing the update 888 */ 889 int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException; 890 891 /** 892 * Execute a batch using the supplied SQL statement with the batch of supplied arguments. 893 * @param sql the SQL statement to execute. 894 * @param batchArgs the List of Object arrays containing the batch of arguments for the query 895 * @param argTypes the SQL types of the arguments 896 * (constants from {@code java.sql.Types}) 897 * @return an array containing the numbers of rows affected by each update in the batch 898 * @throws DataAccessException if there is any problem issuing the update 899 */ 900 int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException; 901 902 /** 903 * Execute multiple batches using the supplied SQL statement with the collect of supplied arguments. 904 * The arguments' values will be set using the ParameterizedPreparedStatementSetter. 905 * Each batch should be of size indicated in 'batchSize'. 906 * @param sql the SQL statement to execute. 907 * @param batchArgs the List of Object arrays containing the batch of arguments for the query 908 * @param batchSize batch size 909 * @param pss the ParameterizedPreparedStatementSetter to use 910 * @return an array containing for each batch another array containing the numbers of rows affected 911 * by each update in the batch 912 * @throws DataAccessException if there is any problem issuing the update 913 * @since 3.1 914 */ 915 <T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, 916 ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException; 917 918 919 //------------------------------------------------------------------------- 920 // Methods dealing with callable statements 921 //------------------------------------------------------------------------- 922 923 /** 924 * Execute a JDBC data access operation, implemented as callback action 925 * working on a JDBC CallableStatement. This allows for implementing arbitrary 926 * data access operations on a single Statement, within Spring's managed JDBC 927 * environment: that is, participating in Spring-managed transactions and 928 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 929 * <p>The callback action can return a result object, for example a domain 930 * object or a collection of domain objects. 931 * @param csc a callback that creates a CallableStatement given a Connection 932 * @param action a callback that specifies the action 933 * @return a result object returned by the action, or {@code null} if none 934 * @throws DataAccessException if there is any problem 935 */ 936 <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException; 937 938 /** 939 * Execute a JDBC data access operation, implemented as callback action 940 * working on a JDBC CallableStatement. This allows for implementing arbitrary 941 * data access operations on a single Statement, within Spring's managed JDBC 942 * environment: that is, participating in Spring-managed transactions and 943 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy. 944 * <p>The callback action can return a result object, for example a domain 945 * object or a collection of domain objects. 946 * @param callString the SQL call string to execute 947 * @param action a callback that specifies the action 948 * @return a result object returned by the action, or {@code null} if none 949 * @throws DataAccessException if there is any problem 950 */ 951 <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException; 952 953 /** 954 * Execute a SQL call using a CallableStatementCreator to provide SQL and 955 * any required parameters. 956 * @param csc a callback that provides SQL and any necessary parameters 957 * @param declaredParameters list of declared SqlParameter objects 958 * @return a Map of extracted out parameters 959 * @throws DataAccessException if there is any problem issuing the update 960 */ 961 Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters) 962 throws DataAccessException; 963 964}