001/*
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements.  See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License.  You may obtain a copy of the License at
008 *
009 *      http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017package org.apache.commons.dbutils;
018
019import java.sql.CallableStatement;
020import java.sql.Connection;
021import java.sql.ParameterMetaData;
022import java.sql.PreparedStatement;
023import java.sql.ResultSet;
024import java.sql.SQLException;
025import java.sql.Statement;
026import java.util.LinkedList;
027import java.util.List;
028
029import javax.sql.DataSource;
030
031/**
032 * Executes SQL queries with pluggable strategies for handling
033 * {@code ResultSet}s.  This class is thread safe.
034 *
035 * @see ResultSetHandler
036 */
037public class QueryRunner extends AbstractQueryRunner {
038
039    /**
040     * Constructor for QueryRunner.
041     */
042    public QueryRunner() {
043    }
044
045    /**
046     * Constructor for QueryRunner that controls the use of {@code ParameterMetaData}.
047     *
048     * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
049     * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
050     * and if it breaks, we'll remember not to use it again.
051     */
052    public QueryRunner(final boolean pmdKnownBroken) {
053        super(pmdKnownBroken);
054    }
055
056    /**
057     * Constructor for QueryRunner that takes a {@code DataSource} to use.
058     *
059     * Methods that do not take a {@code Connection} parameter will retrieve connections from this
060     * {@code DataSource}.
061     *
062     * @param ds The {@code DataSource} to retrieve connections from.
063     */
064    public QueryRunner(final DataSource ds) {
065        super(ds);
066    }
067
068    /**
069     * Constructor for QueryRunner that takes a {@code DataSource} and controls the use of {@code ParameterMetaData}.
070     * Methods that do not take a {@code Connection} parameter will retrieve connections from this
071     * {@code DataSource}.
072     *
073     * @param ds The {@code DataSource} to retrieve connections from.
074     * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
075     * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
076     * and if it breaks, we'll remember not to use it again.
077     */
078    public QueryRunner(final DataSource ds, final boolean pmdKnownBroken) {
079        super(ds, pmdKnownBroken);
080    }
081
082    /**
083     * Constructor for QueryRunner that takes a {@code DataSource}, a {@code StatementConfiguration}, and
084     * controls the use of {@code ParameterMetaData}.  Methods that do not take a {@code Connection} parameter
085     * will retrieve connections from this {@code DataSource}.
086     *
087     * @param ds The {@code DataSource} to retrieve connections from.
088     * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
089     * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
090     * and if it breaks, we'll remember not to use it again.
091     * @param stmtConfig The configuration to apply to statements when they are prepared.
092     */
093    public QueryRunner(final DataSource ds, final boolean pmdKnownBroken, final StatementConfiguration stmtConfig) {
094        super(ds, pmdKnownBroken, stmtConfig);
095    }
096
097    /**
098     * Constructor for QueryRunner that takes a {@code DataSource} to use and a {@code StatementConfiguration}.
099     *
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}