    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}