1 /*
2 * Copyright 2002-2014 the original author or authors.
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17 package org.springframework.jdbc.core;
18
19 import java.util.Collection;
20 import java.util.List;
21 import java.util.Map;
22
23 import org.springframework.dao.DataAccessException;
24 import org.springframework.dao.IncorrectResultSizeDataAccessException;
25 import org.springframework.jdbc.support.KeyHolder;
26 import org.springframework.jdbc.support.rowset.SqlRowSet;
27
28 /**
29 * Interface specifying a basic set of JDBC operations.
30 * Implemented by {@link JdbcTemplate}. Not often used directly, but a useful
31 * option to enhance testability, as it can easily be mocked or stubbed.
32 *
33 * <p>Alternatively, the standard JDBC infrastructure can be mocked.
34 * However, mocking this interface constitutes significantly less work.
35 * As an alternative to a mock objects approach to testing data access code,
36 * consider the powerful integration testing support provided in the
37 * {@code org.springframework.test} package, shipped in
38 * {@code spring-test.jar}.
39 *
40 * @author Rod Johnson
41 * @author Juergen Hoeller
42 * @see JdbcTemplate
43 */
44 public interface JdbcOperations {
45
46 //-------------------------------------------------------------------------
47 // Methods dealing with a plain java.sql.Connection
48 //-------------------------------------------------------------------------
49
50 /**
51 * Execute a JDBC data access operation, implemented as callback action
52 * working on a JDBC Connection. This allows for implementing arbitrary
53 * data access operations, within Spring's managed JDBC environment:
54 * that is, participating in Spring-managed transactions and converting
55 * JDBC SQLExceptions into Spring's DataAccessException hierarchy.
56 * <p>The callback action can return a result object, for example a
57 * domain object or a collection of domain objects.
58 * @param action the callback object that specifies the action
59 * @return a result object returned by the action, or {@code null}
60 * @throws DataAccessException if there is any problem
61 */
62 <T> T execute(ConnectionCallback<T> action) throws DataAccessException;
63
64
65 //-------------------------------------------------------------------------
66 // Methods dealing with static SQL (java.sql.Statement)
67 //-------------------------------------------------------------------------
68
69 /**
70 * Execute a JDBC data access operation, implemented as callback action
71 * working on a JDBC Statement. This allows for implementing arbitrary data
72 * access operations on a single Statement, within Spring's managed JDBC
73 * environment: that is, participating in Spring-managed transactions and
74 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
75 * <p>The callback action can return a result object, for example a
76 * domain object or a collection of domain objects.
77 * @param action callback object that specifies the action
78 * @return a result object returned by the action, or {@code null}
79 * @throws DataAccessException if there is any problem
80 */
81 <T> T execute(StatementCallback<T> action) throws DataAccessException;
82
83 /**
84 * Issue a single SQL execute, typically a DDL statement.
85 * @param sql static SQL to execute
86 * @throws DataAccessException if there is any problem
87 */
88 void execute(String sql) throws DataAccessException;
89
90 /**
91 * Execute a query given static SQL, reading the ResultSet with a
92 * ResultSetExtractor.
93 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
94 * execute a static query with a PreparedStatement, use the overloaded
95 * {@code query} method with {@code null} as argument array.
96 * @param sql SQL query to execute
97 * @param rse object that will extract all rows of results
98 * @return an arbitrary result object, as returned by the ResultSetExtractor
99 * @throws DataAccessException if there is any problem executing the query
100 * @see #query(String, Object[], ResultSetExtractor)
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 SQL query to execute
111 * @param rch object that will extract results, one row at a time
112 * @throws DataAccessException if there is any problem executing the query
113 * @see #query(String, Object[], RowCallbackHandler)
114 */
115 void query(String sql, RowCallbackHandler rch) throws DataAccessException;
116
117 /**
118 * Execute a query given static SQL, mapping each row to a Java 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 SQL query to execute
124 * @param rowMapper object 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, Object[], RowMapper)
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 Java
133 * 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 SQL query to execute
139 * @param rowMapper object that will map one object per row
140 * @return the single mapped object
141 * @throws IncorrectResultSizeDataAccessException if the query does not
142 * return exactly one row
143 * @throws DataAccessException if there is any problem executing the query
144 * @see #queryForObject(String, Object[], RowMapper)
145 */
146 <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException;
147
148 /**
149 * Execute a query for a result object, given static SQL.
150 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
151 * execute a static query with a PreparedStatement, use the overloaded
152 * {@link #queryForObject(String, Class, Object...)} method with
153 * {@code null} as argument array.
154 * <p>This method is useful for running static SQL with a known outcome.
155 * The query is expected to be a single row/single column query; the returned
156 * result will be directly mapped to the corresponding object type.
157 * @param sql SQL query to execute
158 * @param requiredType the type that the result object is expected to match
159 * @return the result object of the required type, or {@code null} in case of SQL NULL
160 * @throws IncorrectResultSizeDataAccessException if the query does not return
161 * exactly one row, or does not return exactly one column in that row
162 * @throws DataAccessException if there is any problem executing the query
163 * @see #queryForObject(String, Object[], Class)
164 */
165 <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException;
166
167 /**
168 * Execute a query for a result Map, given static SQL.
169 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
170 * execute a static query with a PreparedStatement, use the overloaded
171 * {@link #queryForMap(String, Object...)} method with {@code null}
172 * as argument array.
173 * <p>The query is expected to be a single row query; the result row will be
174 * mapped to a Map (one entry for each column, using the column name as the key).
175 * @param sql SQL query to execute
176 * @return the result Map (one entry for each column, using the
177 * column name as the 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 that results in a long value, 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 queryForLong} method with {@code null} as argument array.
191 * <p>This method is useful for running static SQL with a known outcome.
192 * The query is expected to be a single row/single column query that results
193 * in a long value.
194 * @param sql SQL query to execute
195 * @return the long value, or 0 in case of SQL NULL
196 * @throws IncorrectResultSizeDataAccessException if the query does not return
197 * exactly one row, or does not return exactly one column in that row
198 * @throws DataAccessException if there is any problem executing the query
199 * @see #queryForLong(String, Object[])
200 * @deprecated in favor of {@link #queryForObject(String, Class)}
201 */
202 @Deprecated
203 long queryForLong(String sql) throws DataAccessException;
204
205 /**
206 * Execute a query that results in an int value, given static SQL.
207 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
208 * execute a static query with a PreparedStatement, use the overloaded
209 * {@code queryForInt} method with {@code null} as argument array.
210 * <p>This method is useful for running static SQL with a known outcome.
211 * The query is expected to be a single row/single column query that results
212 * in an int value.
213 * @param sql SQL query to execute
214 * @return the int value, or 0 in case of SQL NULL
215 * @throws IncorrectResultSizeDataAccessException if the query does not return
216 * exactly one row, or does not return exactly one column in that row
217 * @throws DataAccessException if there is any problem executing the query
218 * @see #queryForInt(String, Object[])
219 * @deprecated in favor of {@link #queryForObject(String, Class)}
220 */
221 @Deprecated
222 int queryForInt(String sql) throws DataAccessException;
223
224 /**
225 * Execute a query for a result list, given static SQL.
226 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
227 * execute a static query with a PreparedStatement, use the overloaded
228 * {@code queryForList} method with {@code null} as argument array.
229 * <p>The results will be mapped to a List (one entry for each row) of
230 * result objects, each of them matching the specified element type.
231 * @param sql SQL query to execute
232 * @param elementType the required type of element in the result list
233 * (for example, {@code Integer.class})
234 * @return a List of objects that match the specified element type
235 * @throws DataAccessException if there is any problem executing the query
236 * @see #queryForList(String, Object[], Class)
237 * @see SingleColumnRowMapper
238 */
239 <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException;
240
241 /**
242 * Execute a query for a result list, given static SQL.
243 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
244 * execute a static query with a PreparedStatement, use the overloaded
245 * {@code queryForList} method with {@code null} as argument array.
246 * <p>The results will be mapped to a List (one entry for each row) of
247 * Maps (one entry for each column using the column name as the key).
248 * Each element in the list will be of the form returned by this interface's
249 * queryForMap() methods.
250 * @param sql SQL query to execute
251 * @return an List that contains a Map per row
252 * @throws DataAccessException if there is any problem executing the query
253 * @see #queryForList(String, Object[])
254 */
255 List<Map<String, Object>> queryForList(String sql) throws DataAccessException;
256
257 /**
258 * Execute a query for a SqlRowSet, given static SQL.
259 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
260 * execute a static query with a PreparedStatement, use the overloaded
261 * {@code queryForRowSet} method with {@code null} as argument array.
262 * <p>The results will be mapped to an SqlRowSet which holds the data in a
263 * disconnected fashion. This wrapper will translate any SQLExceptions thrown.
264 * <p>Note that that, for the default implementation, JDBC RowSet support needs to
265 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl}
266 * class is used, which is part of JDK 1.5+ and also available separately as part of
267 * Sun's JDBC RowSet Implementations download (rowset.jar).
268 * @param sql SQL query to execute
269 * @return a SqlRowSet representation (possibly a wrapper around a
270 * {@code javax.sql.rowset.CachedRowSet})
271 * @throws DataAccessException if there is any problem executing the query
272 * @see #queryForRowSet(String, Object[])
273 * @see SqlRowSetResultSetExtractor
274 * @see javax.sql.rowset.CachedRowSet
275 */
276 SqlRowSet queryForRowSet(String sql) throws DataAccessException;
277
278 /**
279 * Issue a single SQL update operation (such as an insert, update or delete statement).
280 * @param sql static SQL to execute
281 * @return the number of rows affected
282 * @throws DataAccessException if there is any problem.
283 */
284 int update(String sql) throws DataAccessException;
285
286 /**
287 * Issue multiple SQL updates on a single JDBC Statement using batching.
288 * <p>Will fall back to separate updates on a single Statement if the JDBC
289 * driver does not support batch updates.
290 * @param sql defining an array of SQL statements that will be executed.
291 * @return an array of the number of rows affected by each statement
292 * @throws DataAccessException if there is any problem executing the batch
293 */
294 int[] batchUpdate(String... sql) throws DataAccessException;
295
296
297 //-------------------------------------------------------------------------
298 // Methods dealing with prepared statements
299 //-------------------------------------------------------------------------
300
301 /**
302 * Execute a JDBC data access operation, implemented as callback action
303 * working on a JDBC PreparedStatement. This allows for implementing arbitrary
304 * data access operations on a single Statement, within Spring's managed
305 * JDBC environment: that is, participating in Spring-managed transactions
306 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
307 * <p>The callback action can return a result object, for example a
308 * domain object or a collection of domain objects.
309 * @param psc object that can create a PreparedStatement given a Connection
310 * @param action callback object that specifies the action
311 * @return a result object returned by the action, or {@code null}
312 * @throws DataAccessException if there is any problem
313 */
314 <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) throws DataAccessException;
315
316 /**
317 * Execute a JDBC data access operation, implemented as callback action
318 * working on a JDBC PreparedStatement. This allows for implementing arbitrary
319 * data access operations on a single Statement, within Spring's managed
320 * JDBC environment: that is, participating in Spring-managed transactions
321 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
322 * <p>The callback action can return a result object, for example a
323 * domain object or a collection of domain objects.
324 * @param sql SQL to execute
325 * @param action callback object that specifies the action
326 * @return a result object returned by the action, or {@code null}
327 * @throws DataAccessException if there is any problem
328 */
329 <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException;
330
331 /**
332 * Query using a prepared statement, reading the ResultSet with a
333 * ResultSetExtractor.
334 * <p>A PreparedStatementCreator can either be implemented directly or
335 * configured through a PreparedStatementCreatorFactory.
336 * @param psc object that can create a PreparedStatement given a Connection
337 * @param rse object that will extract results
338 * @return an arbitrary result object, as returned by the ResultSetExtractor
339 * @throws DataAccessException if there is any problem
340 * @see PreparedStatementCreatorFactory
341 */
342 <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException;
343
344 /**
345 * Query using a prepared statement, reading the ResultSet with a
346 * ResultSetExtractor.
347 * @param sql SQL query to execute
348 * @param pss object that knows how to set values on the prepared statement.
349 * If this is {@code null}, the SQL will be assumed to contain no bind parameters.
350 * Even if there are no bind parameters, this object may be used to
351 * set fetch size and other performance options.
352 * @param rse object that will extract results
353 * @return an arbitrary result object, as returned by the ResultSetExtractor
354 * @throws DataAccessException if there is any problem
355 */
356 <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException;
357
358 /**
359 * Query given SQL to create a prepared statement from SQL and a list
360 * of arguments to bind to the query, reading the ResultSet with a
361 * ResultSetExtractor.
362 * @param sql SQL query to execute
363 * @param args arguments to bind to the query
364 * @param argTypes SQL types of the arguments
365 * (constants from {@code java.sql.Types})
366 * @param rse object that will extract results
367 * @return an arbitrary result object, as returned by the ResultSetExtractor
368 * @throws DataAccessException if the query fails
369 * @see java.sql.Types
370 */
371 <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException;
372
373 /**
374 * Query given SQL to create a prepared statement from SQL and a list
375 * of arguments to bind to the query, reading the ResultSet with a
376 * ResultSetExtractor.
377 * @param sql SQL query to execute
378 * @param args arguments to bind to the query
379 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
380 * may also contain {@link SqlParameterValue} objects which indicate not
381 * only the argument value but also the SQL type and optionally the scale
382 * @param rse object that will extract results
383 * @return an arbitrary result object, as returned by the ResultSetExtractor
384 * @throws DataAccessException if the query fails
385 */
386 <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException;
387
388 /**
389 * Query given SQL to create a prepared statement from SQL and a list
390 * of arguments to bind to the query, reading the ResultSet with a
391 * ResultSetExtractor.
392 * @param sql SQL query to execute
393 * @param rse object that will extract results
394 * @param args arguments to bind to the query
395 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
396 * may also contain {@link SqlParameterValue} objects which indicate not
397 * only the argument value but also the SQL type and optionally the scale
398 * @return an arbitrary result object, as returned by the ResultSetExtractor
399 * @throws DataAccessException if the query fails
400 */
401 <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException;
402
403 /**
404 * Query using a prepared statement, reading the ResultSet on a per-row
405 * basis with a RowCallbackHandler.
406 * <p>A PreparedStatementCreator can either be implemented directly or
407 * configured through a PreparedStatementCreatorFactory.
408 * @param psc object that can create a PreparedStatement given a Connection
409 * @param rch object that will extract results, one row at a time
410 * @throws DataAccessException if there is any problem
411 * @see PreparedStatementCreatorFactory
412 */
413 void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException;
414
415 /**
416 * Query given SQL to create a prepared statement from SQL and a
417 * PreparedStatementSetter implementation that knows how to bind values
418 * to the query, reading the ResultSet on a per-row basis with a
419 * RowCallbackHandler.
420 * @param sql SQL query to execute
421 * @param pss object that knows how to set values on the prepared statement.
422 * If this is {@code null}, the SQL will be assumed to contain no bind parameters.
423 * Even if there are no bind parameters, this object may be used to
424 * set fetch size and other performance options.
425 * @param rch object that will extract results, one row at a time
426 * @throws DataAccessException if the query fails
427 */
428 void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException;
429
430 /**
431 * Query given SQL to create a prepared statement from SQL and a list of
432 * arguments to bind to the query, reading the ResultSet on a per-row basis
433 * with a RowCallbackHandler.
434 * @param sql SQL query to execute
435 * @param args arguments to bind to the query
436 * @param argTypes SQL types of the arguments
437 * (constants from {@code java.sql.Types})
438 * @param rch object that will extract results, one row at a time
439 * @throws DataAccessException if the query fails
440 * @see java.sql.Types
441 */
442 void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException;
443
444 /**
445 * Query given SQL to create a prepared statement from SQL and a list of
446 * arguments to bind to the query, reading the ResultSet on a per-row basis
447 * with a RowCallbackHandler.
448 * @param sql SQL query to execute
449 * @param args arguments to bind to the query
450 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
451 * may also contain {@link SqlParameterValue} objects which indicate not
452 * only the argument value but also the SQL type and optionally the scale
453 * @param rch object that will extract results, one row at a time
454 * @throws DataAccessException if the query fails
455 */
456 void query(String sql, Object[] args, RowCallbackHandler rch) 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, reading the ResultSet on a per-row basis
461 * with a RowCallbackHandler.
462 * @param sql SQL query to execute
463 * @param rch object that will extract results, one row at a time
464 * @param args arguments to bind to the query
465 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
466 * may also contain {@link SqlParameterValue} objects which indicate not
467 * only the argument value but also the SQL type and optionally the scale
468 * @throws DataAccessException if the query fails
469 */
470 void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException;
471
472 /**
473 * Query using a prepared statement, mapping each row to a Java object
474 * via a RowMapper.
475 * <p>A PreparedStatementCreator can either be implemented directly or
476 * configured through a PreparedStatementCreatorFactory.
477 * @param psc object that can create a PreparedStatement given a Connection
478 * @param rowMapper object that will map one object per row
479 * @return the result List, containing mapped objects
480 * @throws DataAccessException if there is any problem
481 * @see PreparedStatementCreatorFactory
482 */
483 <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException;
484
485 /**
486 * Query given SQL to create a prepared statement from SQL and a
487 * PreparedStatementSetter implementation that knows how to bind values
488 * to the query, mapping each row to a Java object via a RowMapper.
489 * @param sql SQL query to execute
490 * @param pss object that knows how to set values on the prepared statement.
491 * If this is {@code null}, the SQL will be assumed to contain no bind parameters.
492 * Even if there are no bind parameters, this object may be used to
493 * set fetch size and other performance options.
494 * @param rowMapper object that will map one object per row
495 * @return the result List, containing mapped objects
496 * @throws DataAccessException if the query fails
497 */
498 <T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException;
499
500 /**
501 * Query given SQL to create a prepared statement from SQL and a list
502 * of arguments to bind to the query, mapping each row to a Java object
503 * via a RowMapper.
504 * @param sql SQL query to execute
505 * @param args arguments to bind to the query
506 * @param argTypes SQL types of the arguments
507 * (constants from {@code java.sql.Types})
508 * @param rowMapper object that will map one object per row
509 * @return the result List, containing mapped objects
510 * @throws DataAccessException if the query fails
511 * @see java.sql.Types
512 */
513 <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException;
514
515 /**
516 * Query given SQL to create a prepared statement from SQL and a list
517 * of arguments to bind to the query, mapping each row to a Java object
518 * via a RowMapper.
519 * @param sql SQL query to execute
520 * @param args arguments to bind to the query
521 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
522 * may also contain {@link SqlParameterValue} objects which indicate not
523 * only the argument value but also the SQL type and optionally the scale
524 * @param rowMapper object that will map one object per row
525 * @return the result List, containing mapped objects
526 * @throws DataAccessException if the query fails
527 */
528 <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException;
529
530 /**
531 * Query given SQL to create a prepared statement from SQL and a list
532 * of arguments to bind to the query, mapping each row to a Java object
533 * via a RowMapper.
534 * @param sql SQL query to execute
535 * @param rowMapper object that will map one object per row
536 * @param args arguments to bind to the query
537 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
538 * may also contain {@link SqlParameterValue} objects which indicate not
539 * only the argument value but also the SQL type and optionally the scale
540 * @return the result List, containing mapped objects
541 * @throws DataAccessException if the query fails
542 */
543 <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException;
544
545 /**
546 * Query given SQL to create a prepared statement from SQL and a list
547 * of arguments to bind to the query, mapping a single result row to a
548 * Java object via a RowMapper.
549 * @param sql SQL query to execute
550 * @param args arguments to bind to the query
551 * (leaving it to the PreparedStatement to guess the corresponding SQL type)
552 * @param argTypes SQL types of the arguments
553 * (constants from {@code java.sql.Types})
554 * @param rowMapper object that will map one object per row
555 * @return the single mapped object
556 * @throws IncorrectResultSizeDataAccessException if the query does not
557 * return exactly one row
558 * @throws DataAccessException if the query fails
559 */
560 <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper)
561 throws DataAccessException;
562
563 /**
564 * Query given SQL to create a prepared statement from SQL and a list
565 * of arguments to bind to the query, mapping a single result row to a
566 * Java object via a RowMapper.
567 * @param sql SQL query to execute
568 * @param args arguments to bind to the query
569 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
570 * may also contain {@link SqlParameterValue} objects which indicate not
571 * only the argument value but also the SQL type and optionally the scale
572 * @param rowMapper object that will map one object per row
573 * @return the single mapped object
574 * @throws IncorrectResultSizeDataAccessException if the query does not
575 * return exactly one row
576 * @throws DataAccessException if the query fails
577 */
578 <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException;
579
580 /**
581 * Query given SQL to create a prepared statement from SQL and a list
582 * of arguments to bind to the query, mapping a single result row to a
583 * Java object via a RowMapper.
584 * @param sql SQL query to execute
585 * @param rowMapper object that will map one object per row
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 * @return the single mapped object
591 * @throws IncorrectResultSizeDataAccessException if the query does not
592 * return exactly one row
593 * @throws DataAccessException if the query fails
594 */
595 <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException;
596
597 /**
598 * Query given SQL to create a prepared statement from SQL and a
599 * list of arguments to bind to the query, expecting a result object.
600 * <p>The query is expected to be a single row/single column query; the returned
601 * result will be directly mapped to the corresponding object type.
602 * @param sql SQL query to execute
603 * @param args arguments to bind to the query
604 * @param argTypes SQL types of the arguments
605 * (constants from {@code java.sql.Types})
606 * @param requiredType the type that the result object is expected to match
607 * @return the result object of the required type, or {@code null} in case of SQL NULL
608 * @throws IncorrectResultSizeDataAccessException if the query does not return
609 * exactly one row, or does not return exactly one column in that row
610 * @throws DataAccessException if the query fails
611 * @see #queryForObject(String, Class)
612 * @see java.sql.Types
613 */
614 <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType)
615 throws DataAccessException;
616
617 /**
618 * Query given SQL to create a prepared statement from SQL and a
619 * list of arguments to bind to the query, expecting a result object.
620 * <p>The query is expected to be a single row/single column query; the returned
621 * result will be directly mapped to the corresponding object type.
622 * @param sql SQL query to execute
623 * @param args arguments to bind to the query
624 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
625 * may also contain {@link SqlParameterValue} objects which indicate not
626 * only the argument value but also the SQL type and optionally the scale
627 * @param requiredType the type that the result object is expected to match
628 * @return the result object of the required type, or {@code null} in case of SQL NULL
629 * @throws IncorrectResultSizeDataAccessException if the query does not return
630 * exactly one row, or does not return exactly one column in that row
631 * @throws DataAccessException if the query fails
632 * @see #queryForObject(String, Class)
633 */
634 <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException;
635
636 /**
637 * Query given SQL to create a prepared statement from SQL and a
638 * list of arguments to bind to the query, expecting a result object.
639 * <p>The query is expected to be a single row/single column query; the returned
640 * result will be directly mapped to the corresponding object type.
641 * @param sql SQL query to execute
642 * @param requiredType the type that the result object is expected to match
643 * @param args arguments to bind to the query
644 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
645 * may also contain {@link SqlParameterValue} objects which indicate not
646 * only the argument value but also the SQL type and optionally the scale
647 * @return the result object of the required type, or {@code null} in case of SQL NULL
648 * @throws IncorrectResultSizeDataAccessException if the query does not return
649 * exactly one row, or does not return exactly one column in that row
650 * @throws DataAccessException if the query fails
651 * @see #queryForObject(String, Class)
652 */
653 <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException;
654
655 /**
656 * Query given SQL to create a prepared statement from SQL and a
657 * list of arguments to bind to the query, expecting a result Map.
658 * <p>The query is expected to be a single row query; the result row will be
659 * mapped to a Map (one entry for each column, using the column name as the key).
660 * @param sql SQL query to execute
661 * @param args arguments to bind to the query
662 * @param argTypes SQL types of the arguments
663 * (constants from {@code java.sql.Types})
664 * @return the result Map (one entry for each column, using the
665 * column name as the key)
666 * @throws IncorrectResultSizeDataAccessException if the query does not
667 * return exactly one row
668 * @throws DataAccessException if the query fails
669 * @see #queryForMap(String)
670 * @see ColumnMapRowMapper
671 * @see java.sql.Types
672 */
673 Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException;
674
675 /**
676 * Query given SQL to create a prepared statement from SQL and a
677 * list of arguments to bind to the query, expecting a result Map.
678 * The queryForMap() methods defined by this interface are appropriate
679 * when you don't have a domain model. Otherwise, consider using
680 * one of the queryForObject() methods.
681 * <p>The query is expected to be a single row query; the result row will be
682 * mapped to a Map (one entry for each column, using the column name as the key).
683 * @param sql SQL query to execute
684 * @param args arguments to bind to the query
685 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
686 * may also contain {@link SqlParameterValue} objects which indicate not
687 * only the argument value but also the SQL type and optionally the scale
688 * @return the result Map (one entry for each column, using the
689 * column name as the key)
690 * @throws IncorrectResultSizeDataAccessException if the query does not
691 * return exactly one row
692 * @throws DataAccessException if the query fails
693 * @see #queryForMap(String)
694 * @see ColumnMapRowMapper
695 */
696 Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException;
697
698 /**
699 * Query given SQL to create a prepared statement from SQL and a
700 * list of arguments to bind to the query, resulting in a long value.
701 * <p>The query is expected to be a single row/single column query that
702 * results in a long value.
703 * @param sql SQL query to execute
704 * @param args arguments to bind to the query
705 * @param argTypes SQL types of the arguments
706 * (constants from {@code java.sql.Types})
707 * @return the long value, or 0 in case of SQL NULL
708 * @throws IncorrectResultSizeDataAccessException if the query does not return
709 * exactly one row, or does not return exactly one column in that row
710 * @throws DataAccessException if the query fails
711 * @see #queryForLong(String)
712 * @see java.sql.Types
713 * @deprecated in favor of {@link #queryForObject(String, Object[], int[], Class)} )}
714 */
715 @Deprecated
716 long queryForLong(String sql, Object[] args, int[] argTypes) throws DataAccessException;
717
718 /**
719 * Query given SQL to create a prepared statement from SQL and a
720 * list of arguments to bind to the query, resulting in a long value.
721 * <p>The query is expected to be a single row/single column query that
722 * results in a long value.
723 * @param sql SQL query to execute
724 * @param args arguments to bind to the query
725 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
726 * may also contain {@link SqlParameterValue} objects which indicate not
727 * only the argument value but also the SQL type and optionally the scale
728 * @return the long value, or 0 in case of SQL NULL
729 * @throws IncorrectResultSizeDataAccessException if the query does not return
730 * exactly one row, or does not return exactly one column in that row
731 * @throws DataAccessException if the query fails
732 * @see #queryForLong(String)
733 * @deprecated in favor of {@link #queryForObject(String, Class, Object[])} )}
734 */
735 @Deprecated
736 long queryForLong(String sql, Object... args) throws DataAccessException;
737
738 /**
739 * Query given SQL to create a prepared statement from SQL and a
740 * list of arguments to bind to the query, resulting in an int value.
741 * <p>The query is expected to be a single row/single column query that
742 * results in an int value.
743 * @param sql SQL query to execute
744 * @param args arguments to bind to the query
745 * @param argTypes SQL types of the arguments
746 * (constants from {@code java.sql.Types})
747 * @return the int value, or 0 in case of SQL NULL
748 * @throws IncorrectResultSizeDataAccessException if the query does not return
749 * exactly one row, or does not return exactly one column in that row
750 * @throws DataAccessException if the query fails
751 * @see #queryForInt(String)
752 * @see java.sql.Types
753 * @deprecated in favor of {@link #queryForObject(String, Object[], int[], Class)} )}
754 */
755 @Deprecated
756 int queryForInt(String sql, Object[] args, int[] argTypes) throws DataAccessException;
757
758 /**
759 * Query given SQL to create a prepared statement from SQL and a
760 * list of arguments to bind to the query, resulting in an int value.
761 * <p>The query is expected to be a single row/single column query that
762 * results in an int value.
763 * @param sql SQL query to execute
764 * @param args arguments to bind to the query
765 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
766 * may also contain {@link SqlParameterValue} objects which indicate not
767 * only the argument value but also the SQL type and optionally the scale
768 * @return the int value, or 0 in case of SQL NULL
769 * @throws IncorrectResultSizeDataAccessException if the query does not return
770 * exactly one row, or does not return exactly one column in that row
771 * @throws DataAccessException if the query fails
772 * @see #queryForInt(String)
773 * @deprecated in favor of {@link #queryForObject(String, Class, Object[])} )}
774 */
775 @Deprecated
776 int queryForInt(String sql, Object... args) throws DataAccessException;
777
778 /**
779 * Query given SQL to create a prepared statement from SQL and a
780 * list of arguments to bind to the query, expecting a result list.
781 * <p>The results will be mapped to a List (one entry for each row) of
782 * result objects, each of them matching the specified element type.
783 * @param sql SQL query to execute
784 * @param args arguments to bind to the query
785 * @param argTypes SQL types of the arguments
786 * (constants from {@code java.sql.Types})
787 * @param elementType the required type of element in the result list
788 * (for example, {@code Integer.class})
789 * @return a List of objects that match the specified element type
790 * @throws DataAccessException if the query fails
791 * @see #queryForList(String, Class)
792 * @see SingleColumnRowMapper
793 */
794 <T>List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType)
795 throws DataAccessException;
796
797 /**
798 * Query given SQL to create a prepared statement from SQL and a
799 * list of arguments to bind to the query, expecting a result list.
800 * <p>The results will be mapped to a List (one entry for each row) of
801 * result objects, each of them matching the specified element type.
802 * @param sql SQL query to execute
803 * @param args arguments to bind to the query
804 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
805 * may also contain {@link SqlParameterValue} objects which indicate not
806 * only the argument value but also the SQL type and optionally the scale
807 * @param elementType the required type of element in the result list
808 * (for example, {@code Integer.class})
809 * @return a List of objects that match the specified element type
810 * @throws DataAccessException if the query fails
811 * @see #queryForList(String, Class)
812 * @see SingleColumnRowMapper
813 */
814 <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException;
815
816 /**
817 * Query given SQL to create a prepared statement from SQL and a
818 * list of arguments to bind to the query, expecting a result list.
819 * <p>The results will be mapped to a List (one entry for each row) of
820 * result objects, each of them matching the specified element type.
821 * @param sql SQL query to execute
822 * @param elementType the required type of element in the result list
823 * (for example, {@code Integer.class})
824 * @param args arguments to bind to the query
825 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
826 * may also contain {@link SqlParameterValue} objects which indicate not
827 * only the argument value but also the SQL type and optionally the scale
828 * @return a List of objects that match the specified element type
829 * @throws DataAccessException if the query fails
830 * @see #queryForList(String, Class)
831 * @see SingleColumnRowMapper
832 */
833 <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) throws DataAccessException;
834
835 /**
836 * Query given SQL to create a prepared statement from SQL and a
837 * list of arguments to bind to the query, expecting a result list.
838 * <p>The results will be mapped to a List (one entry for each row) of
839 * Maps (one entry for each column, using the column name as the key).
840 * Thus Each element in the list will be of the form returned by this interface's
841 * queryForMap() methods.
842 * @param sql SQL query to execute
843 * @param args arguments to bind to the query
844 * @param argTypes SQL types of the arguments
845 * (constants from {@code java.sql.Types})
846 * @return a List that contains a Map per row
847 * @throws DataAccessException if the query fails
848 * @see #queryForList(String)
849 * @see java.sql.Types
850 */
851 List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException;
852
853 /**
854 * Query given SQL to create a prepared statement from SQL and a
855 * list of arguments to bind to the query, expecting a result list.
856 * <p>The results will be mapped to a List (one entry for each row) of
857 * Maps (one entry for each column, using the column name as the key).
858 * Each element in the list will be of the form returned by this interface's
859 * queryForMap() methods.
860 * @param sql SQL query to execute
861 * @param args arguments to bind to the query
862 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
863 * may also contain {@link SqlParameterValue} objects which indicate not
864 * only the argument value but also the SQL type and optionally the scale
865 * @return a List that contains a Map per row
866 * @throws DataAccessException if the query fails
867 * @see #queryForList(String)
868 */
869 List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException;
870
871 /**
872 * Query given SQL to create a prepared statement from SQL and a
873 * list of arguments to bind to the query, expecting a SqlRowSet.
874 * <p>The results will be mapped to an SqlRowSet which holds the data in a
875 * disconnected fashion. This wrapper will translate any SQLExceptions thrown.
876 * <p>Note that that, for the default implementation, JDBC RowSet support needs to
877 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl}
878 * class is used, which is part of JDK 1.5+ and also available separately as part of
879 * Sun's JDBC RowSet Implementations download (rowset.jar).
880 * @param sql SQL query to execute
881 * @param args arguments to bind to the query
882 * @param argTypes SQL types of the arguments
883 * (constants from {@code java.sql.Types})
884 * @return a SqlRowSet representation (possibly a wrapper around a
885 * {@code javax.sql.rowset.CachedRowSet})
886 * @throws DataAccessException if there is any problem executing the query
887 * @see #queryForRowSet(String)
888 * @see SqlRowSetResultSetExtractor
889 * @see javax.sql.rowset.CachedRowSet
890 * @see java.sql.Types
891 */
892 SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException;
893
894 /**
895 * Query given SQL to create a prepared statement from SQL and a
896 * list of arguments to bind to the query, expecting a SqlRowSet.
897 * <p>The results will be mapped to an SqlRowSet which holds the data in a
898 * disconnected fashion. This wrapper will translate any SQLExceptions thrown.
899 * <p>Note that that, for the default implementation, JDBC RowSet support needs to
900 * be available at runtime: by default, Sun's {@code com.sun.rowset.CachedRowSetImpl}
901 * class is used, which is part of JDK 1.5+ and also available separately as part of
902 * Sun's JDBC RowSet Implementations download (rowset.jar).
903 * @param sql SQL query to execute
904 * @param args arguments to bind to the query
905 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
906 * may also contain {@link SqlParameterValue} objects which indicate not
907 * only the argument value but also the SQL type and optionally the scale
908 * @return a SqlRowSet representation (possibly a wrapper around a
909 * {@code javax.sql.rowset.CachedRowSet})
910 * @throws DataAccessException if there is any problem executing the query
911 * @see #queryForRowSet(String)
912 * @see SqlRowSetResultSetExtractor
913 * @see javax.sql.rowset.CachedRowSet
914 */
915 SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException;
916
917 /**
918 * Issue a single SQL update operation (such as an insert, update or delete statement)
919 * using a PreparedStatementCreator to provide SQL and any required parameters.
920 * <p>A PreparedStatementCreator can either be implemented directly or
921 * configured through a PreparedStatementCreatorFactory.
922 * @param psc object that provides SQL and any necessary parameters
923 * @return the number of rows affected
924 * @throws DataAccessException if there is any problem issuing the update
925 * @see PreparedStatementCreatorFactory
926 */
927 int update(PreparedStatementCreator psc) throws DataAccessException;
928
929 /**
930 * Issue an update statement using a PreparedStatementCreator to provide SQL and
931 * any required parameters. Generated keys will be put into the given KeyHolder.
932 * <p>Note that the given PreparedStatementCreator has to create a statement
933 * with activated extraction of generated keys (a JDBC 3.0 feature). This can
934 * either be done directly or through using a PreparedStatementCreatorFactory.
935 * @param psc object that provides SQL and any necessary parameters
936 * @param generatedKeyHolder KeyHolder that will hold the generated keys
937 * @return the number of rows affected
938 * @throws DataAccessException if there is any problem issuing the update
939 * @see PreparedStatementCreatorFactory
940 * @see org.springframework.jdbc.support.GeneratedKeyHolder
941 */
942 int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException;
943
944 /**
945 * Issue an update statement using a PreparedStatementSetter to set bind parameters,
946 * with given SQL. Simpler than using a PreparedStatementCreator as this method
947 * will create the PreparedStatement: The PreparedStatementSetter just needs to
948 * set parameters.
949 * @param sql SQL containing bind parameters
950 * @param pss helper that sets bind parameters. If this is {@code null}
951 * we run an update with static SQL.
952 * @return the number of rows affected
953 * @throws DataAccessException if there is any problem issuing the update
954 */
955 int update(String sql, PreparedStatementSetter pss) throws DataAccessException;
956
957 /**
958 * Issue a single SQL update operation (such as an insert, update or delete statement)
959 * via a prepared statement, binding the given arguments.
960 * @param sql SQL containing bind parameters
961 * @param args arguments to bind to the query
962 * @param argTypes SQL types of the arguments
963 * (constants from {@code java.sql.Types})
964 * @return the number of rows affected
965 * @throws DataAccessException if there is any problem issuing the update
966 * @see java.sql.Types
967 */
968 int update(String sql, Object[] args, int[] argTypes) throws DataAccessException;
969
970 /**
971 * Issue a single SQL update operation (such as an insert, update or delete statement)
972 * via a prepared statement, binding the given arguments.
973 * @param sql SQL containing bind parameters
974 * @param args arguments to bind to the query
975 * (leaving it to the PreparedStatement to guess the corresponding SQL type);
976 * may also contain {@link SqlParameterValue} objects which indicate not
977 * only the argument value but also the SQL type and optionally the scale
978 * @return the number of rows affected
979 * @throws DataAccessException if there is any problem issuing the update
980 */
981 int update(String sql, Object... args) throws DataAccessException;
982
983 /**
984 * Issue multiple update statements on a single PreparedStatement,
985 * using batch updates and a BatchPreparedStatementSetter to set values.
986 * <p>Will fall back to separate updates on a single PreparedStatement
987 * if the JDBC driver does not support batch updates.
988 * @param sql defining PreparedStatement that will be reused.
989 * All statements in the batch will use the same SQL.
990 * @param pss object to set parameters on the PreparedStatement
991 * created by this method
992 * @return an array of the number of rows affected by each statement
993 * @throws DataAccessException if there is any problem issuing the update
994 */
995 int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException;
996
997 /**
998 * Execute a batch using the supplied SQL statement with the batch of supplied arguments.
999 * @param sql the SQL statement to execute
1000 * @param batchArgs the List of Object arrays containing the batch of arguments for the query
1001 * @return an array containing the numbers of rows affected by each update in the batch
1002 */
1003 public int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException;
1004
1005 /**
1006 * Execute a batch using the supplied SQL statement with the batch of supplied arguments.
1007 * @param sql the SQL statement to execute.
1008 * @param batchArgs the List of Object arrays containing the batch of arguments for the query
1009 * @param argTypes SQL types of the arguments
1010 * (constants from {@code java.sql.Types})
1011 * @return an array containing the numbers of rows affected by each update in the batch
1012 */
1013 public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException;
1014
1015 /**
1016 * Execute multiple batches using the supplied SQL statement with the collect of supplied arguments.
1017 * The arguments' values will be set using the ParameterizedPreparedStatementSetter.
1018 * Each batch should be of size indicated in 'batchSize'.
1019 * @param sql the SQL statement to execute.
1020 * @param batchArgs the List of Object arrays containing the batch of arguments for the query
1021 * @param batchSize batch size
1022 * @param pss ParameterizedPreparedStatementSetter to use
1023 * @return an array containing for each batch another array containing the numbers of rows affected
1024 * by each update in the batch
1025 */
1026 public <T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize,
1027 ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException;
1028
1029
1030 //-------------------------------------------------------------------------
1031 // Methods dealing with callable statements
1032 //-------------------------------------------------------------------------
1033
1034 /**
1035 * Execute a JDBC data access operation, implemented as callback action
1036 * working on a JDBC CallableStatement. This allows for implementing arbitrary
1037 * data access operations on a single Statement, within Spring's managed
1038 * JDBC environment: that is, participating in Spring-managed transactions
1039 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
1040 * <p>The callback action can return a result object, for example a
1041 * domain object or a collection of domain objects.
1042 * @param csc object that can create a CallableStatement given a Connection
1043 * @param action callback object that specifies the action
1044 * @return a result object returned by the action, or {@code null}
1045 * @throws DataAccessException if there is any problem
1046 */
1047 <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException;
1048
1049 /**
1050 * Execute a JDBC data access operation, implemented as callback action
1051 * working on a JDBC CallableStatement. This allows for implementing arbitrary
1052 * data access operations on a single Statement, within Spring's managed
1053 * JDBC environment: that is, participating in Spring-managed transactions
1054 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
1055 * <p>The callback action can return a result object, for example a
1056 * domain object or a collection of domain objects.
1057 * @param callString the SQL call string to execute
1058 * @param action callback object that specifies the action
1059 * @return a result object returned by the action, or {@code null}
1060 * @throws DataAccessException if there is any problem
1061 */
1062 <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException;
1063
1064 /**
1065 * Execute a SQL call using a CallableStatementCreator to provide SQL and any
1066 * required parameters.
1067 * @param csc object that provides SQL and any necessary parameters
1068 * @param declaredParameters list of declared SqlParameter objects
1069 * @return Map of extracted out parameters
1070 * @throws DataAccessException if there is any problem issuing the update
1071 */
1072 Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
1073 throws DataAccessException;
1074
1075 }