1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one or more
3 * contributor license agreements. See the NOTICE file distributed with
4 * this work for additional information regarding copyright ownership.
5 * The ASF licenses this file to You under the Apache License, Version 2.0
6 * (the "License"); you may not use this file except in compliance with
7 * the License. You may obtain a copy of the License at
8 *
9 * http://www.apache.org/licenses/LICENSE-2.0
10 *
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
16 */
17 package org.apache.commons.dbutils;
18
19 import java.sql.CallableStatement;
20 import java.sql.Connection;
21 import java.sql.ParameterMetaData;
22 import java.sql.PreparedStatement;
23 import java.sql.ResultSet;
24 import java.sql.SQLException;
25 import java.sql.Statement;
26 import java.util.LinkedList;
27 import java.util.List;
28
29 import javax.sql.DataSource;
30
31 /**
32 * Executes SQL queries with pluggable strategies for handling
33 * {@code ResultSet}s. This class is thread safe.
34 *
35 * @see ResultSetHandler
36 */
37 public class QueryRunner extends AbstractQueryRunner {
38
39 /**
40 * Constructor for QueryRunner.
41 */
42 public QueryRunner() {
43 }
44
45 /**
46 * Constructor for QueryRunner that controls the use of {@code ParameterMetaData}.
47 *
48 * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
49 * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
50 * and if it breaks, we'll remember not to use it again.
51 */
52 public QueryRunner(final boolean pmdKnownBroken) {
53 super(pmdKnownBroken);
54 }
55
56 /**
57 * Constructor for QueryRunner that takes a {@code DataSource} to use.
58 *
59 * Methods that do not take a {@code Connection} parameter will retrieve connections from this
60 * {@code DataSource}.
61 *
62 * @param ds The {@code DataSource} to retrieve connections from.
63 */
64 public QueryRunner(final DataSource ds) {
65 super(ds);
66 }
67
68 /**
69 * Constructor for QueryRunner that takes a {@code DataSource} and controls the use of {@code ParameterMetaData}.
70 * Methods that do not take a {@code Connection} parameter will retrieve connections from this
71 * {@code DataSource}.
72 *
73 * @param ds The {@code DataSource} to retrieve connections from.
74 * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
75 * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
76 * and if it breaks, we'll remember not to use it again.
77 */
78 public QueryRunner(final DataSource ds, final boolean pmdKnownBroken) {
79 super(ds, pmdKnownBroken);
80 }
81
82 /**
83 * Constructor for QueryRunner that takes a {@code DataSource}, a {@code StatementConfiguration}, and
84 * controls the use of {@code ParameterMetaData}. Methods that do not take a {@code Connection} parameter
85 * will retrieve connections from this {@code DataSource}.
86 *
87 * @param ds The {@code DataSource} to retrieve connections from.
88 * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
89 * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
90 * and if it breaks, we'll remember not to use it again.
91 * @param stmtConfig The configuration to apply to statements when they are prepared.
92 */
93 public QueryRunner(final DataSource ds, final boolean pmdKnownBroken, final StatementConfiguration stmtConfig) {
94 super(ds, pmdKnownBroken, stmtConfig);
95 }
96
97 /**
98 * Constructor for QueryRunner that takes a {@code DataSource} to use and a {@code StatementConfiguration}.
99 *
100 * Methods that do not take a {@code Connection} parameter will retrieve connections from this
101 * {@code DataSource}.
102 *
103 * @param ds The {@code DataSource} to retrieve connections from.
104 * @param stmtConfig The configuration to apply to statements when they are prepared.
105 */
106 public QueryRunner(final DataSource ds, final StatementConfiguration stmtConfig) {
107 super(ds, stmtConfig);
108 }
109
110 /**
111 * Constructor for QueryRunner that takes a {@code StatementConfiguration} to configure statements when
112 * preparing them.
113 *
114 * @param stmtConfig The configuration to apply to statements when they are prepared.
115 */
116 public QueryRunner(final StatementConfiguration stmtConfig) {
117 super(stmtConfig);
118 }
119
120 /**
121 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
122 *
123 * @param conn The Connection to use to run the query. The caller is
124 * responsible for closing this Connection.
125 * @param sql The SQL to execute.
126 * @param params An array of query replacement parameters. Each row in
127 * this array is one set of batch replacement values.
128 * @return The number of rows updated per statement.
129 * @throws SQLException if a database access error occurs
130 * @since 1.1
131 */
132 public int[] batch(final Connection conn, final String sql, final Object[][] params) throws SQLException {
133 if (conn == null) {
134 throw new SQLException("Null connection");
135 }
136
137 if (sql == null) {
138 throw new SQLException("Null SQL statement");
139 }
140
141 if (params == null) {
142 throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
143 }
144
145 PreparedStatement stmt = null;
146 ParameterMetaData pmd = null;
147 int[] rows = null;
148 try {
149 stmt = this.prepareStatement(conn, sql);
150 // When the batch size is large, prefetching parameter metadata before filling
151 // the statement can reduce lots of JDBC communications.
152 pmd = this.getParameterMetaData(stmt);
153
154 for (final Object[] param : params) {
155 this.fillStatement(stmt, pmd, param);
156 stmt.addBatch();
157 }
158 rows = stmt.executeBatch();
159
160 } catch (final SQLException e) {
161 this.rethrow(e, sql, (Object[])params);
162 } finally {
163 close(stmt);
164 }
165
166 return rows;
167 }
168
169 /**
170 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
171 * {@code Connection} is retrieved from the {@code DataSource}
172 * set in the constructor. This {@code Connection} must be in
173 * auto-commit mode or the update will not be saved.
174 *
175 * @param sql The SQL to execute.
176 * @param params An array of query replacement parameters. Each row in
177 * this array is one set of batch replacement values.
178 * @return The number of rows updated per statement.
179 * @throws SQLException if a database access error occurs
180 * @since 1.1
181 */
182 public int[] batch(final String sql, final Object[][] params) throws SQLException {
183 try (Connection conn = this.prepareConnection()) {
184 return this.batch(conn, sql, params);
185 }
186 }
187
188 /**
189 * Execute an SQL statement, including a stored procedure call, which does
190 * not return any result sets.
191 * Any parameters which are instances of {@link OutParameter} will be
192 * registered as OUT parameters.
193 * <p>
194 * Use this method when invoking a stored procedure with OUT parameters
195 * that does not return any result sets. If you are not invoking a stored
196 * procedure, or the stored procedure has no OUT parameters, consider using
197 * {@link #update(java.sql.Connection, String, Object...) }.
198 * If the stored procedure returns result sets, use
199 * {@link #execute(java.sql.Connection, String, org.apache.commons.dbutils.ResultSetHandler, Object...) }.
200 *
201 * @param conn The connection to use to run the query.
202 * @param sql The SQL to execute.
203 * @param params The query replacement parameters.
204 * @return The number of rows updated.
205 * @throws SQLException if a database access error occurs
206 */
207 public int execute(final Connection conn, final String sql, final Object... params) throws SQLException {
208 if (conn == null) {
209 throw new SQLException("Null connection");
210 }
211
212 if (sql == null) {
213 throw new SQLException("Null SQL statement");
214 }
215
216 CallableStatement stmt = null;
217 int rows = 0;
218
219 try {
220 stmt = this.prepareCall(conn, sql);
221 this.fillStatement(stmt, params);
222 stmt.execute();
223 rows = stmt.getUpdateCount();
224 this.retrieveOutParameters(stmt, params);
225
226 } catch (final SQLException e) {
227 this.rethrow(e, sql, params);
228
229 } finally {
230 close(stmt);
231 }
232
233 return rows;
234 }
235
236 /**
237 * Execute an SQL statement, including a stored procedure call, which
238 * returns one or more result sets.
239 * Any parameters which are instances of {@link OutParameter} will be
240 * registered as OUT parameters.
241 * <p>
242 * Use this method when: a) running SQL statements that return multiple
243 * result sets; b) invoking a stored procedure that return result
244 * sets and OUT parameters. Otherwise you may wish to use
245 * {@link #query(java.sql.Connection, String, org.apache.commons.dbutils.ResultSetHandler, Object...) }
246 * (if there are no OUT parameters) or
247 * {@link #execute(java.sql.Connection, String, Object...) }
248 * (if there are no result sets).
249 *
250 * @param <T> The type of object that the handler returns
251 * @param conn The connection to use to run the query.
252 * @param sql The SQL to execute.
253 * @param rsh The result set handler
254 * @param params The query replacement parameters.
255 * @return A list of objects generated by the handler
256 * @throws SQLException if a database access error occurs
257 */
258 public <T> List<T> execute(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
259 if (conn == null) {
260 throw new SQLException("Null connection");
261 }
262
263 if (sql == null) {
264 throw new SQLException("Null SQL statement");
265 }
266
267 if (rsh == null) {
268 throw new SQLException("Null ResultSetHandler");
269 }
270
271 CallableStatement stmt = null;
272 final List<T> results = new LinkedList<>();
273
274 try {
275 stmt = this.prepareCall(conn, sql);
276 this.fillStatement(stmt, params);
277 boolean moreResultSets = stmt.execute();
278 // Handle multiple result sets by passing them through the handler
279 // retaining the final result
280 while (moreResultSets) {
281 try (@SuppressWarnings("resource")
282 // assume the ResultSet wrapper properly closes
283 ResultSet resultSet = this.wrap(stmt.getResultSet())) {
284 results.add(rsh.handle(resultSet));
285 moreResultSets = stmt.getMoreResults();
286 }
287 }
288 this.retrieveOutParameters(stmt, params);
289
290 } catch (final SQLException e) {
291 this.rethrow(e, sql, params);
292
293 } finally {
294 close(stmt);
295 }
296
297 return results;
298 }
299
300 /**
301 * Execute an SQL statement, including a stored procedure call, which does
302 * not return any result sets.
303 * Any parameters which are instances of {@link OutParameter} will be
304 * registered as OUT parameters.
305 * <p>
306 * Use this method when invoking a stored procedure with OUT parameters
307 * that does not return any result sets. If you are not invoking a stored
308 * procedure, or the stored procedure has no OUT parameters, consider using
309 * {@link #update(String, Object...) }.
310 * If the stored procedure returns result sets, use
311 * {@link #execute(String, org.apache.commons.dbutils.ResultSetHandler, Object...) }.
312 * <p>
313 * The {@code Connection} is retrieved from the {@code DataSource}
314 * set in the constructor. This {@code Connection} must be in
315 * auto-commit mode or the update will not be saved.
316 *
317 * @param sql The SQL statement to execute.
318 * @param params Initializes the CallableStatement's parameters (i.e. '?').
319 * @throws SQLException if a database access error occurs
320 * @return The number of rows updated.
321 */
322 public int execute(final String sql, final Object... params) throws SQLException {
323 try (Connection conn = this.prepareConnection()) {
324 return this.execute(conn, sql, params);
325 }
326 }
327
328 /**
329 * Execute an SQL statement, including a stored procedure call, which
330 * returns one or more result sets.
331 * Any parameters which are instances of {@link OutParameter} will be
332 * registered as OUT parameters.
333 * <p>
334 * Use this method when: a) running SQL statements that return multiple
335 * result sets; b) invoking a stored procedure that return result
336 * sets and OUT parameters. Otherwise you may wish to use
337 * {@link #query(String, org.apache.commons.dbutils.ResultSetHandler, Object...) }
338 * (if there are no OUT parameters) or
339 * {@link #execute(String, Object...) }
340 * (if there are no result sets).
341 *
342 * @param <T> The type of object that the handler returns
343 * @param sql The SQL to execute.
344 * @param rsh The result set handler
345 * @param params The query replacement parameters.
346 * @return A list of objects generated by the handler
347 * @throws SQLException if a database access error occurs
348 */
349 public <T> List<T> execute(final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
350 try (Connection conn = this.prepareConnection()) {
351 return this.execute(conn, sql, rsh, params);
352 }
353 }
354
355 /**
356 * Execute an SQL INSERT query without replacement parameters.
357 * @param <T> The type of object that the handler returns
358 * @param conn The connection to use to run the query.
359 * @param sql The SQL to execute.
360 * @param rsh The handler used to create the result object from
361 * the {@code ResultSet} of auto-generated keys.
362 * @return An object generated by the handler.
363 * @throws SQLException if a database access error occurs
364 * @since 1.6
365 */
366 public <T> T insert(final Connection conn, final String sql, final ResultSetHandler<T> rsh) throws SQLException {
367 return insert(conn, sql, rsh, (Object[]) null);
368 }
369
370 /**
371 * Execute an SQL INSERT query.
372 * @param <T> The type of object that the handler returns
373 * @param conn The connection to use to run the query.
374 * @param sql The SQL to execute.
375 * @param rsh The handler used to create the result object from
376 * the {@code ResultSet} of auto-generated keys.
377 * @param params The query replacement parameters.
378 * @return An object generated by the handler.
379 * @throws SQLException if a database access error occurs
380 * @since 1.6
381 */
382 public <T> T insert(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
383 if (conn == null) {
384 throw new SQLException("Null connection");
385 }
386
387 if (sql == null) {
388 throw new SQLException("Null SQL statement");
389 }
390
391 if (rsh == null) {
392 throw new SQLException("Null ResultSetHandler");
393 }
394
395 Statement stmt = null;
396 T generatedKeys = null;
397
398 try {
399 if (params != null && params.length > 0) {
400 final PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
401 stmt = ps;
402 this.fillStatement(ps, params);
403 ps.executeUpdate();
404 } else {
405 stmt = conn.createStatement();
406 stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
407 }
408 try (ResultSet resultSet = stmt.getGeneratedKeys()) {
409 generatedKeys = rsh.handle(resultSet);
410 }
411 } catch (final SQLException e) {
412 this.rethrow(e, sql, params);
413 } finally {
414 close(stmt);
415 }
416
417 return generatedKeys;
418 }
419
420 /**
421 * Executes the given INSERT SQL without any replacement parameters.
422 * The {@code Connection} is retrieved from the
423 * {@code DataSource} set in the constructor.
424 * @param <T> The type of object that the handler returns
425 * @param sql The SQL statement to execute.
426 * @param rsh The handler used to create the result object from
427 * the {@code ResultSet} of auto-generated keys.
428 * @return An object generated by the handler.
429 * @throws SQLException if a database access error occurs
430 * @since 1.6
431 */
432 public <T> T insert(final String sql, final ResultSetHandler<T> rsh) throws SQLException {
433 try (Connection conn = this.prepareConnection()) {
434 return insert(conn, sql, rsh, (Object[]) null);
435 }
436 }
437
438 /**
439 * Executes the given INSERT SQL statement. The
440 * {@code Connection} is retrieved from the {@code DataSource}
441 * set in the constructor. This {@code Connection} must be in
442 * auto-commit mode or the insert will not be saved.
443 * @param <T> The type of object that the handler returns
444 * @param sql The SQL statement to execute.
445 * @param rsh The handler used to create the result object from
446 * the {@code ResultSet} of auto-generated keys.
447 * @param params Initializes the PreparedStatement's IN (i.e. '?')
448 * @return An object generated by the handler.
449 * @throws SQLException if a database access error occurs
450 * @since 1.6
451 */
452 public <T> T insert(final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
453 try (Connection conn = this.prepareConnection()) {
454 return insert(conn, sql, rsh, params);
455 }
456 }
457
458 /**
459 * Executes the given batch of INSERT SQL statements.
460 * @param <T> The type of object that the handler returns
461 * @param conn The connection to use to run the query.
462 * @param sql The SQL to execute.
463 * @param rsh The handler used to create the result object from
464 * the {@code ResultSet} of auto-generated keys.
465 * @param params The query replacement parameters.
466 * @return The result generated by the handler.
467 * @throws SQLException if a database access error occurs
468 * @since 1.6
469 */
470 public <T> T insertBatch(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object[][] params) throws SQLException {
471 if (conn == null) {
472 throw new SQLException("Null connection");
473 }
474
475 if (sql == null) {
476 throw new SQLException("Null SQL statement");
477 }
478
479 if (params == null) {
480 throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
481 }
482
483 PreparedStatement stmt = null;
484 T generatedKeys = null;
485 try {
486 stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);
487
488 for (final Object[] param : params) {
489 this.fillStatement(stmt, param);
490 stmt.addBatch();
491 }
492 stmt.executeBatch();
493 try (ResultSet resultSet = stmt.getGeneratedKeys()) {
494 generatedKeys = rsh.handle(resultSet);
495 }
496 } catch (final SQLException e) {
497 this.rethrow(e, sql, (Object[])params);
498 } finally {
499 close(stmt);
500 }
501
502 return generatedKeys;
503 }
504
505 /**
506 * Executes the given batch of INSERT SQL statements. The
507 * {@code Connection} is retrieved from the {@code DataSource}
508 * set in the constructor. This {@code Connection} must be in
509 * auto-commit mode or the insert will not be saved.
510 * @param <T> The type of object that the handler returns
511 * @param sql The SQL statement to execute.
512 * @param rsh The handler used to create the result object from
513 * the {@code ResultSet} of auto-generated keys.
514 * @param params Initializes the PreparedStatement's IN (i.e. '?')
515 * @return The result generated by the handler.
516 * @throws SQLException if a database access error occurs
517 * @since 1.6
518 */
519 public <T> T insertBatch(final String sql, final ResultSetHandler<T> rsh, final Object[][] params) throws SQLException {
520 try (Connection conn = this.prepareConnection()) {
521 return insertBatch(conn, sql, rsh, params);
522 }
523 }
524
525 /**
526 * Execute an SQL SELECT query with a single replacement parameter. The
527 * caller is responsible for closing the connection.
528 * @param <T> The type of object that the handler returns
529 * @param conn The connection to execute the query in.
530 * @param sql The query to execute.
531 * @param param The replacement parameter.
532 * @param rsh The handler that converts the results into an object.
533 * @return The object returned by the handler.
534 * @throws SQLException if a database access error occurs
535 * @deprecated Use {@link #query(Connection, String, ResultSetHandler, Object...)}
536 */
537 @Deprecated
538 public <T> T query(final Connection conn, final String sql, final Object param, final ResultSetHandler<T> rsh) throws SQLException {
539 return this.<T>query(conn, sql, rsh, param);
540 }
541
542 /**
543 * Execute an SQL SELECT query with replacement parameters. The
544 * caller is responsible for closing the connection.
545 * @param <T> The type of object that the handler returns
546 * @param conn The connection to execute the query in.
547 * @param sql The query to execute.
548 * @param params The replacement parameters.
549 * @param rsh The handler that converts the results into an object.
550 * @return The object returned by the handler.
551 * @throws SQLException if a database access error occurs
552 * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object...)} instead
553 */
554 @Deprecated
555 public <T> T query(final Connection conn, final String sql, final Object[] params, final ResultSetHandler<T> rsh) throws SQLException {
556 return this.<T>query(conn, sql, rsh, params);
557 }
558
559 /**
560 * Execute an SQL SELECT query without any replacement parameters. The
561 * caller is responsible for closing the connection.
562 * @param <T> The type of object that the handler returns
563 * @param conn The connection to execute the query in.
564 * @param sql The query to execute.
565 * @param rsh The handler that converts the results into an object.
566 * @return The object returned by the handler.
567 * @throws SQLException if a database access error occurs
568 */
569 public <T> T query(final Connection conn, final String sql, final ResultSetHandler<T> rsh) throws SQLException {
570 return this.<T>query(conn, sql, rsh, (Object[]) null);
571 }
572
573 /**
574 * Execute an SQL SELECT query with replacement parameters. The
575 * caller is responsible for closing the connection.
576 * @param <T> The type of object that the handler returns
577 * @param conn The connection to execute the query in.
578 * @param sql The query to execute.
579 * @param rsh The handler that converts the results into an object.
580 * @param params The replacement parameters.
581 * @return The object returned by the handler.
582 * @throws SQLException if a database access error occurs
583 */
584 public <T> T query(final Connection conn, final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
585 if (conn == null) {
586 throw new SQLException("Null connection");
587 }
588
589 if (sql == null) {
590 throw new SQLException("Null SQL statement");
591 }
592
593 if (rsh == null) {
594 throw new SQLException("Null ResultSetHandler");
595 }
596
597 Statement stmt = null;
598 ResultSet resultSet = null;
599 T result = null;
600
601 try {
602 if (params != null && params.length > 0) {
603 final PreparedStatement ps = this.prepareStatement(conn, sql);
604 stmt = ps;
605 this.fillStatement(ps, params);
606 resultSet = this.wrap(ps.executeQuery());
607 } else {
608 stmt = conn.createStatement();
609 resultSet = this.wrap(stmt.executeQuery(sql));
610 }
611 result = rsh.handle(resultSet);
612
613 } catch (final SQLException e) {
614 this.rethrow(e, sql, params);
615
616 } finally {
617 closeQuietly(resultSet);
618 closeQuietly(stmt);
619 }
620
621 return result;
622 }
623
624 /**
625 * Executes the given SELECT SQL with a single replacement parameter.
626 * The {@code Connection} is retrieved from the
627 * {@code DataSource} set in the constructor.
628 * @param <T> The type of object that the handler returns
629 * @param sql The SQL statement to execute.
630 * @param param The replacement parameter.
631 * @param rsh The handler used to create the result object from
632 * the {@code ResultSet}.
633 *
634 * @return An object generated by the handler.
635 * @throws SQLException if a database access error occurs
636 * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
637 */
638 @Deprecated
639 public <T> T query(final String sql, final Object param, final ResultSetHandler<T> rsh) throws SQLException {
640 try (Connection conn = this.prepareConnection()) {
641 return this.<T>query(conn, sql, rsh, param);
642 }
643 }
644
645 /**
646 * Executes the given SELECT SQL query and returns a result object.
647 * The {@code Connection} is retrieved from the
648 * {@code DataSource} set in the constructor.
649 * @param <T> The type of object that the handler returns
650 * @param sql The SQL statement to execute.
651 * @param params Initialize the PreparedStatement's IN parameters with
652 * this array.
653 *
654 * @param rsh The handler used to create the result object from
655 * the {@code ResultSet}.
656 *
657 * @return An object generated by the handler.
658 * @throws SQLException if a database access error occurs
659 * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
660 */
661 @Deprecated
662 public <T> T query(final String sql, final Object[] params, final ResultSetHandler<T> rsh) throws SQLException {
663 try (Connection conn = this.prepareConnection()) {
664 return this.<T>query(conn, sql, rsh, params);
665 }
666 }
667
668 /**
669 * Executes the given SELECT SQL without any replacement parameters.
670 * The {@code Connection} is retrieved from the
671 * {@code DataSource} set in the constructor.
672 * @param <T> The type of object that the handler returns
673 * @param sql The SQL statement to execute.
674 * @param rsh The handler used to create the result object from
675 * the {@code ResultSet}.
676 *
677 * @return An object generated by the handler.
678 * @throws SQLException if a database access error occurs
679 */
680 public <T> T query(final String sql, final ResultSetHandler<T> rsh) throws SQLException {
681 try (Connection conn = this.prepareConnection()) {
682 return this.<T>query(conn, sql, rsh, (Object[]) null);
683 }
684 }
685
686 /**
687 * Executes the given SELECT SQL query and returns a result object.
688 * The {@code Connection} is retrieved from the
689 * {@code DataSource} set in the constructor.
690 * @param <T> The type of object that the handler returns
691 * @param sql The SQL statement to execute.
692 * @param rsh The handler used to create the result object from
693 * the {@code ResultSet}.
694 * @param params Initialize the PreparedStatement's IN parameters with
695 * this array.
696 * @return An object generated by the handler.
697 * @throws SQLException if a database access error occurs
698 */
699 public <T> T query(final String sql, final ResultSetHandler<T> rsh, final Object... params) throws SQLException {
700 try (Connection conn = this.prepareConnection()) {
701 return this.<T>query(conn, sql, rsh, params);
702 }
703 }
704
705 /**
706 * Set the value on all the {@link OutParameter} instances in the
707 * {@code params} array using the OUT parameter values from the
708 * {@code stmt}.
709 * @param stmt the statement from which to retrieve OUT parameter values
710 * @param params the parameter array for the statement invocation
711 * @throws SQLException when the value could not be retrieved from the
712 * statement.
713 */
714 private void retrieveOutParameters(final CallableStatement stmt, final Object[] params) throws SQLException {
715 if (params != null) {
716 for (int i = 0; i < params.length; i++) {
717 if (params[i] instanceof OutParameter) {
718 ((OutParameter<?>) params[i]).setValue(stmt, i + 1);
719 }
720 }
721 }
722 }
723
724 /**
725 * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
726 * parameters.
727 *
728 * @param conn The connection to use to run the query.
729 * @param sql The SQL to execute.
730 * @return The number of rows updated.
731 * @throws SQLException if a database access error occurs
732 */
733 public int update(final Connection conn, final String sql) throws SQLException {
734 return this.update(conn, sql, (Object[]) null);
735 }
736
737 /**
738 * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
739 * parameter.
740 *
741 * @param conn The connection to use to run the query.
742 * @param sql The SQL to execute.
743 * @param param The replacement parameter.
744 * @return The number of rows updated.
745 * @throws SQLException if a database access error occurs
746 */
747 public int update(final Connection conn, final String sql, final Object param) throws SQLException {
748 return this.update(conn, sql, new Object[] { param });
749 }
750
751 /**
752 * Execute an SQL INSERT, UPDATE, or DELETE query.
753 *
754 * @param conn The connection to use to run the query.
755 * @param sql The SQL to execute.
756 * @param params The query replacement parameters.
757 * @return The number of rows updated.
758 * @throws SQLException if a database access error occurs
759 */
760 public int update(final Connection conn, final String sql, final Object... params) throws SQLException {
761 if (conn == null) {
762 throw new SQLException("Null connection");
763 }
764
765 if (sql == null) {
766 throw new SQLException("Null SQL statement");
767 }
768
769 Statement stmt = null;
770 int rows = 0;
771
772 try {
773 if (params != null && params.length > 0) {
774 final PreparedStatement ps = this.prepareStatement(conn, sql);
775 stmt = ps;
776 this.fillStatement(ps, params);
777 rows = ps.executeUpdate();
778 } else {
779 stmt = conn.createStatement();
780 rows = stmt.executeUpdate(sql);
781 }
782
783 } catch (final SQLException e) {
784 this.rethrow(e, sql, params);
785
786 } finally {
787 close(stmt);
788 }
789
790 return rows;
791 }
792
793 /**
794 * Executes the given INSERT, UPDATE, or DELETE SQL statement without
795 * any replacement parameters. The {@code Connection} is retrieved
796 * from the {@code DataSource} set in the constructor. This
797 * {@code Connection} must be in auto-commit mode or the update will
798 * not be saved.
799 *
800 * @param sql The SQL statement to execute.
801 * @throws SQLException if a database access error occurs
802 * @return The number of rows updated.
803 */
804 public int update(final String sql) throws SQLException {
805 try (Connection conn = this.prepareConnection()) {
806 return this.update(conn, sql, (Object[]) null);
807 }
808 }
809
810 /**
811 * Executes the given INSERT, UPDATE, or DELETE SQL statement with
812 * a single replacement parameter. The {@code Connection} is
813 * retrieved from the {@code DataSource} set in the constructor.
814 * This {@code Connection} must be in auto-commit mode or the
815 * update will not be saved.
816 *
817 * @param sql The SQL statement to execute.
818 * @param param The replacement parameter.
819 * @throws SQLException if a database access error occurs
820 * @return The number of rows updated.
821 */
822 public int update(final String sql, final Object param) throws SQLException {
823 try (Connection conn = this.prepareConnection()) {
824 return this.update(conn, sql, param);
825 }
826 }
827
828 /**
829 * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
830 * {@code Connection} is retrieved from the {@code DataSource}
831 * set in the constructor. This {@code Connection} must be in
832 * auto-commit mode or the update will not be saved.
833 *
834 * @param sql The SQL statement to execute.
835 * @param params Initializes the PreparedStatement's IN (i.e. '?')
836 * parameters.
837 * @throws SQLException if a database access error occurs
838 * @return The number of rows updated.
839 */
840 public int update(final String sql, final Object... params) throws SQLException {
841 try (Connection conn = this.prepareConnection()) {
842 return this.update(conn, sql, params);
843 }
844 }
845 }