View Javadoc
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 }