    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.beans.IntrospectionException;
    020import java.beans.Introspector;
    021import java.beans.PropertyDescriptor;
    022import java.lang.reflect.InvocationTargetException;
    023import java.lang.reflect.Method;
    024import java.sql.CallableStatement;
    025import java.sql.Connection;
    026import java.sql.ParameterMetaData;
    027import java.sql.PreparedStatement;
    028import java.sql.ResultSet;
    029import java.sql.SQLException;
    030import java.sql.SQLFeatureNotSupportedException;
    031import java.sql.Statement;
    032import java.sql.Types;
    033import java.util.Arrays;
    034
    035import javax.sql.DataSource;
    036
    037/**
    038 * The base class for QueryRunner &amp; AsyncQueryRunner. This class is thread safe.
    039 *
    040 * @since 1.4 (mostly extracted from QueryRunner)
    041 */
    042public abstract class AbstractQueryRunner {
    043    /**
    044     * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried
    045     * it yet)?
    046     */
    047    private volatile boolean pmdKnownBroken = false;
    048
    049    /**
    050     * The DataSource to retrieve connections from.
    051     * @deprecated Access to this field should be through {@link #getDataSource()}.
    052     */
    053    @Deprecated
    054    protected final DataSource ds;
    055
    056    /**
    057     * Configuration to use when preparing statements.
    058     */
    059    private final StatementConfiguration stmtConfig;
    060
    061    /**
    062     * Default constructor, sets pmdKnownBroken to false, ds to null and stmtConfig to null.
    063     */
    064    public AbstractQueryRunner() {
    065        ds = null;
    066        this.stmtConfig = null;
    067    }
    068
    069    /**
    070     * Constructor to control the use of {@code ParameterMetaData}.
    071     *
    072     * @param pmdKnownBroken
    073     *            Some drivers don't support
    074     *            {@link ParameterMetaData#getParameterType(int) }; if
    075     *            {@code pmdKnownBroken} is set to true, we won't even try
    076     *            it; if false, we'll try it, and if it breaks, we'll remember
    077     *            not to use it again.
    078     */
    079    public AbstractQueryRunner(final boolean pmdKnownBroken) {
    080        this.pmdKnownBroken = pmdKnownBroken;
    081        ds = null;
    082        this.stmtConfig = null;
    083    }
    084
    085    /**
    086     * Constructor to provide a {@code DataSource}. Methods that do not
    087     * take a {@code Connection} parameter will retrieve connections from
    088     * this {@code DataSource}.
    089     *
    090     * @param ds
    091     *            The {@code DataSource} to retrieve connections from.
    092     */
    093    public AbstractQueryRunner(final DataSource ds) {
    094        this.ds = ds;
    095        this.stmtConfig = null;
    096    }
    097
    098    /**
    099     * Constructor to provide a {@code DataSource} and control the use of
    100     * {@code ParameterMetaData}. Methods that do not take a
    101     * {@code Connection} parameter will retrieve connections from this
    102     * {@code DataSource}.
    103     *
    104     * @param ds
    105     *            The {@code DataSource} to retrieve connections from.
    106     * @param pmdKnownBroken
    107     *            Some drivers don't support
    108     *            {@link ParameterMetaData#getParameterType(int) }; if
    109     *            {@code pmdKnownBroken} is set to true, we won't even try
    110     *            it; if false, we'll try it, and if it breaks, we'll remember
    111     *            not to use it again.
    112     */
    113    public AbstractQueryRunner(final DataSource ds, final boolean pmdKnownBroken) {
    114        this.pmdKnownBroken = pmdKnownBroken;
    115        this.ds = ds;
    116        this.stmtConfig = null;
    117    }
    118
    119    /**
    120     * Constructor for QueryRunner that takes a {@code DataSource}, a {@code StatementConfiguration}, and
    121     * controls the use of {@code ParameterMetaData}.  Methods that do not take a {@code Connection} parameter
    122     * will retrieve connections from this {@code DataSource}.
    123     *
    124     * @param ds The {@code DataSource} to retrieve connections from.
    125     * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
    126     * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
    127     * and if it breaks, we'll remember not to use it again.
    128     * @param stmtConfig The configuration to apply to statements when they are prepared.
    129     */
    130    public AbstractQueryRunner(final DataSource ds, final boolean pmdKnownBroken, final StatementConfiguration stmtConfig) {
    131        this.pmdKnownBroken = pmdKnownBroken;
    132        this.ds = ds;
    133        this.stmtConfig = stmtConfig;
    134    }
    135
    136    /**
    137     * Constructor for QueryRunner that takes a {@code DataSource} to use and a {@code StatementConfiguration}.
    138     *
    139     * Methods that do not take a {@code Connection} parameter will retrieve connections from this
    140     * {@code DataSource}.
    141     *
    142     * @param ds The {@code DataSource} to retrieve connections from.
    143     * @param stmtConfig The configuration to apply to statements when they are prepared.
    144     */
    145    public AbstractQueryRunner(final DataSource ds, final StatementConfiguration stmtConfig) {
    146        this.ds = ds;
    147        this.stmtConfig = stmtConfig;
    148    }
    149
    150    /**
    151     * Constructor for QueryRunner that takes a {@code StatementConfiguration} to configure statements when
    152     * preparing them.
    153     *
    154     * @param stmtConfig The configuration to apply to statements when they are prepared.
    155     */
    156    public AbstractQueryRunner(final StatementConfiguration stmtConfig) {
    157        this.ds = null;
    158        this.stmtConfig = stmtConfig;
    159    }
    160
    161    /**
    162     * Close a {@code Connection}. This implementation avoids closing if
    163     * null and does <strong>not</strong> suppress any exceptions. Subclasses
    164     * can override to provide special handling like logging.
    165     *
    166     * @param conn
    167     *            Connection to close
    168     * @throws SQLException
    169     *             if a database access error occurs
    170     * @since 1.1
    171     */
    172    protected void close(final Connection conn) throws SQLException {
    173        DbUtils.close(conn);
    174    }
    175
    176    /**
    177     * Close a {@code ResultSet}. This implementation avoids closing if
    178     * null and does <strong>not</strong> suppress any exceptions. Subclasses
    179     * can override to provide special handling like logging.
    180     *
    181     * @param resultSet
    182     *            ResultSet to close
    183     * @throws SQLException
    184     *             if a database access error occurs
    185     * @since 1.1
    186     */
    187    protected void close(final ResultSet resultSet) throws SQLException {
    188        DbUtils.close(resultSet);
    189    }
    190
    191    /**
    192     * Close a {@code Statement}. This implementation avoids closing if
    193     * null and does <strong>not</strong> suppress any exceptions. Subclasses
    194     * can override to provide special handling like logging.
    195     *
    196     * @param stmt
    197     *            Statement to close
    198     * @throws SQLException
    199     *             if a database access error occurs
    200     * @since 1.1
    201     */
    202    protected void close(final Statement stmt) throws SQLException {
    203        DbUtils.close(stmt);
    204    }
    205
    206    /**
    207     * Calls {@link DbUtils#closeQuietly(Connection)}.
    208     *
    209     * @param conn Connection to close.
    210     * @since 1.8.0
    211     */
    212    protected void closeQuietly(final Connection conn) {
    213        DbUtils.closeQuietly(conn);
    214    }
    215
    216    /**
    217     * Calls {@link DbUtils#closeQuietly(ResultSet)}.
    218     *
    219     * @param resultSet ResultSet to close.
    220     * @since 1.8.0
    221     */
    222    protected void closeQuietly(final ResultSet resultSet) {
    223        DbUtils.closeQuietly(resultSet);
    224    }
    225
    226    /**
    227     * Calls {@link DbUtils#closeQuietly(Statement)}.
    228     *
    229     * @param statement ResultSet to close.
    230     * @since 1.8.0
    231     */
    232    protected void closeQuietly(final Statement statement) {
    233        DbUtils.closeQuietly(statement);
    234    }
    235
    236    private void configureStatement(final Statement stmt) throws SQLException {
    237
    238        if (stmtConfig != null) {
    239            if (stmtConfig.isFetchDirectionSet()) {
    240                stmt.setFetchDirection(stmtConfig.getFetchDirection());
    241            }
    242
    243            if (stmtConfig.isFetchSizeSet()) {
    244                stmt.setFetchSize(stmtConfig.getFetchSize());
    245            }
    246
    247            if (stmtConfig.isMaxFieldSizeSet()) {
    248                stmt.setMaxFieldSize(stmtConfig.getMaxFieldSize());
    249            }
    250
    251            if (stmtConfig.isMaxRowsSet()) {
    252                stmt.setMaxRows(stmtConfig.getMaxRows());
    253            }
    254
    255            if (stmtConfig.isQueryTimeoutSet()) {
    256                stmt.setQueryTimeout(stmtConfig.getQueryTimeout());
    257            }
    258        }
    259    }
    260
    261    /**
    262     * Fill the {@code PreparedStatement} replacement parameters with the
    263     * given objects.
    264     *
    265     * @param stmt
    266     *            PreparedStatement to fill
    267     * @param params
    268     *            Query replacement parameters; {@code null} is a valid
    269     *            value to pass in.
    270     * @throws SQLException
    271     *             if a database access error occurs
    272     */
    273    public void fillStatement(final PreparedStatement stmt, final Object... params) throws SQLException {
    274        ParameterMetaData pmd = null;
    275        if (!pmdKnownBroken) {
    276            try {
    277                pmd = this.getParameterMetaData(stmt);
    278                if (pmd == null) { // can be returned by implementations that don't support the method
    279                    pmdKnownBroken = true;
    280                }
    281            } catch (final SQLFeatureNotSupportedException ex) {
    282                pmdKnownBroken = true;
    283            }
    284            // TODO see DBUTILS-117: would it make sense to catch any other SQLEx types here?
    285        }
    286        fillStatement(stmt, pmd, params);
    287    }
    288
    289    /**
    290     * Fill the {@code PreparedStatement} replacement parameters with the
    291     * given objects, and prefetched parameter metadata.
    292     *
    293     * @param stmt
    294     *            PreparedStatement to fill
    295     * @param pmd
    296     *            Prefetched parameter metadata
    297     * @param params
    298     *            Query replacement parameters; {@code null} is a valid
    299     *            value to pass in.
    300     * @throws SQLException
    301     *             if a database access error occurs
    302     */
    303    public void fillStatement(final PreparedStatement stmt, final ParameterMetaData pmd, final Object... params)
    304            throws SQLException {
    305
    306        // check the parameter count, if we can
    307        if (!pmdKnownBroken && pmd != null) {
    308            final int stmtCount = pmd.getParameterCount();
    309            final int paramsCount = params == null ? 0 : params.length;
    310
    311            if (stmtCount != paramsCount) {
    312                throw new SQLException("Wrong number of parameters: expected "
    313                        + stmtCount + ", was given " + paramsCount);
    314            }
    315        }
    316
    317        // nothing to do here
    318        if (params == null) {
    319            return;
    320        }
    321
    322        CallableStatement call = null;
    323        if (stmt instanceof CallableStatement) {
    324            call = (CallableStatement) stmt;
    325        }
    326
    327        for (int i = 0; i < params.length; i++) {
    328            if (params[i] != null) {
    329                if (call != null && params[i] instanceof OutParameter) {
    330                    ((OutParameter<?>) params[i]).register(call, i + 1);
    331                } else {
    332                    stmt.setObject(i + 1, params[i]);
    333                }
    334            } else {
    335                // VARCHAR works with many drivers regardless
    336                // of the actual column type. Oddly, NULL and
    337                // OTHER don't work with Oracle's drivers.
    338                int sqlType = Types.VARCHAR;
    339                if (!pmdKnownBroken) {
    340                    // TODO see DBUTILS-117: does it make sense to catch SQLEx here?
    341                    try {
    342                        /*
    343                         * It's not possible for pmdKnownBroken to change from true to false, (once true, always true) so pmd cannot be null here.
    344                         */
    345                        sqlType = pmd.getParameterType(i + 1);
    346                    } catch (final SQLException e) {
    347                        pmdKnownBroken = true;
    348                    }
    349                }
    350                stmt.setNull(i + 1, sqlType);
    351            }
    352        }
    353    }
    354
    355    /**
    356     * Fill the {@code PreparedStatement} replacement parameters with the
    357     * given object's bean property values.
    358     *
    359     * @param stmt
    360     *            PreparedStatement to fill
    361     * @param bean
    362     *            a JavaBean object
    363     * @param properties
    364     *            an ordered array of properties; this gives the order to insert
    365     *            values in the statement
    366     * @throws SQLException
    367     *             if a database access error occurs
    368     */
    369    public void fillStatementWithBean(final PreparedStatement stmt, final Object bean,
    370            final PropertyDescriptor[] properties) throws SQLException {
    371        final Object[] params = new Object[properties.length];
    372        for (int i = 0; i < properties.length; i++) {
    373            final PropertyDescriptor property = properties[i];
    374            Object value = null;
    375            final Method method = property.getReadMethod();
    376            if (method == null) {
    377                throw new IllegalArgumentException("No read method for bean property " + bean.getClass() + " " + property.getName());
    378            }
    379            try {
    380                value = method.invoke(bean);
    381            } catch (final IllegalArgumentException e) {
    382                throw new IllegalArgumentException("Couldn't invoke method with 0 arguments: " + method, e);
    383            } catch (final InvocationTargetException | IllegalAccessException e) {
    384                throw new IllegalArgumentException("Couldn't invoke method: " + method, e);
    385            }
    386            params[i] = value;
    387        }
    388        fillStatement(stmt, params);
    389    }
    390
    391    /**
    392     * Fill the {@code PreparedStatement} replacement parameters with the
    393     * given object's bean property values.
    394     *
    395     * @param stmt
    396     *            PreparedStatement to fill
    397     * @param bean
    398     *            A JavaBean object
    399     * @param propertyNames
    400     *            An ordered array of property names (these should match the
    401     *            getters/setters); this gives the order to insert values in the
    402     *            statement
    403     * @throws SQLException
    404     *             If a database access error occurs
    405     */
    406    public void fillStatementWithBean(final PreparedStatement stmt, final Object bean,
    407            final String... propertyNames) throws SQLException {
    408        PropertyDescriptor[] descriptors;
    409        try {
    410            descriptors = Introspector.getBeanInfo(bean.getClass()).getPropertyDescriptors();
    411        } catch (final IntrospectionException e) {
    412            throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
    413        }
    414        final PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
    415        for (int i = 0; i < propertyNames.length; i++) {
    416            final String propertyName = propertyNames[i];
    417            if (propertyName == null) {
    418                throw new NullPointerException("propertyName can't be null: " + i);
    419            }
    420            boolean found = false;
    421            for (final PropertyDescriptor descriptor : descriptors) {
    422                if (propertyName.equals(descriptor.getName())) {
    423                    sorted[i] = descriptor;
    424                    found = true;
    425                    break;
    426                }
    427            }
    428            if (!found) {
    429                throw new IllegalStateException("Couldn't find bean property: " + bean.getClass() + " " + propertyName);
    430            }
    431        }
    432        fillStatementWithBean(stmt, bean, sorted);
    433    }
    434
    435    /**
    436     * Returns the {@code DataSource} this runner is using.
    437     * {@code QueryRunner} methods always call this method to get the
    438     * {@code DataSource} so subclasses can provide specialized behavior.
    439     *
    440     * @return DataSource the runner is using
    441     */
    442    public DataSource getDataSource() {
    443        return this.ds;
    444    }
    445
    446    /**
    447     * Get the {@code ParameterMetaData} of the prepared statement, if the {@code pmdKnownBroken}
    448     * is set to false.
    449     *
    450     * @param stmt
    451     *            PreparedStatement of which to query the metadata of parameters
    452     * @return the metadata of parameters
    453     * @throws SQLException
    454     *            if a database access error occurs
    455     */
    456    public ParameterMetaData getParameterMetaData(final PreparedStatement stmt) throws SQLException {
    457        ParameterMetaData pmd = null;
    458        if (!pmdKnownBroken) {
    459            try {
    460                pmd = stmt.getParameterMetaData();
    461            } catch (final SQLFeatureNotSupportedException ex) {
    462                pmdKnownBroken = true;
    463            }
    464        }
    465        return pmd;
    466    }
    467
    468    /**
    469     * Some drivers don't support
    470     * {@link ParameterMetaData#getParameterType(int) }; if
    471     * {@code pmdKnownBroken} is set to true, we won't even try it; if
    472     * false, we'll try it, and if it breaks, we'll remember not to use it
    473     * again.
    474     *
    475     * @return the flag to skip (or not)
    476     *         {@link ParameterMetaData#getParameterType(int) }
    477     * @since 1.4
    478     */
    479    public boolean isPmdKnownBroken() {
    480        return pmdKnownBroken;
    481    }
    482
    483    /**
    484     * Factory method that creates and initializes a
    485     * {@code CallableStatement} object for the given SQL.
    486     * {@code QueryRunner} methods always call this method to prepare
    487     * callable statements for them. Subclasses can override this method to
    488     * provide special CallableStatement configuration if needed. This
    489     * implementation simply calls {@code conn.prepareCall(sql)}.
    490     *
    491     * @param conn
    492     *            The {@code Connection} used to create the
    493     *            {@code CallableStatement}
    494     * @param sql
    495     *            The SQL statement to prepare.
    496     * @return An initialized {@code CallableStatement}.
    497     * @throws SQLException
    498     *             if a database access error occurs
    499     */
    500    protected CallableStatement prepareCall(final Connection conn, final String sql)
    501            throws SQLException {
    502
    503        return conn.prepareCall(sql);
    504    }
    505
    506    /**
    507     * Factory method that creates and initializes a {@code Connection}
    508     * object. {@code QueryRunner} methods always call this method to
    509     * retrieve connections from its DataSource. Subclasses can override this
    510     * method to provide special {@code Connection} configuration if
    511     * needed. This implementation simply calls {@code ds.getConnection()}.
    512     *
    513     * @return An initialized {@code Connection}.
    514     * @throws SQLException
    515     *             if a database access error occurs
    516     * @since 1.1
    517     */
    518    protected Connection prepareConnection() throws SQLException {
    519        if (this.getDataSource() == null) {
    520            throw new SQLException(
    521                    "QueryRunner requires a DataSource to be "
    522                            + "invoked in this way, or a Connection should be passed in");
    523        }
    524        return this.getDataSource().getConnection();
    525    }
    526
    527    /**
    528     * Factory method that creates and initializes a
    529     * {@code PreparedStatement} object for the given SQL.
    530     * {@code QueryRunner} methods always call this method to prepare
    531     * statements for them. Subclasses can override this method to provide
    532     * special PreparedStatement configuration if needed. This implementation
    533     * simply calls {@code conn.prepareStatement(sql)}.
    534     *
    535     * @param conn
    536     *            The {@code Connection} used to create the
    537     *            {@code PreparedStatement}
    538     * @param sql
    539     *            The SQL statement to prepare.
    540     * @return An initialized {@code PreparedStatement}.
    541     * @throws SQLException
    542     *             if a database access error occurs
    543     */
    544    protected PreparedStatement prepareStatement(final Connection conn, final String sql)
    545            throws SQLException {
    546
    547        @SuppressWarnings("resource")
    548        final
    549        PreparedStatement ps = conn.prepareStatement(sql);
    550        try {
    551            configureStatement(ps);
    552        } catch (final SQLException e) {
    553            ps.close();
    554            throw e;
    555        }
    556        return ps;
    557    }
    558
    559    /**
    560     * Factory method that creates and initializes a
    561     * {@code PreparedStatement} object for the given SQL.
    562     * {@code QueryRunner} methods always call this method to prepare
    563     * statements for them. Subclasses can override this method to provide
    564     * special PreparedStatement configuration if needed. This implementation
    565     * simply calls {@code conn.prepareStatement(sql, returnedKeys)}
    566     * which will result in the ability to retrieve the automatically-generated
    567     * keys from an auto_increment column.
    568     *
    569     * @param conn
    570     *            The {@code Connection} used to create the
    571     *            {@code PreparedStatement}
    572     * @param sql
    573     *            The SQL statement to prepare.
    574     * @param returnedKeys
    575     *            Flag indicating whether to return generated keys or not.
    576     *
    577     * @return An initialized {@code PreparedStatement}.
    578     * @throws SQLException
    579     *             if a database access error occurs
    580     * @since 1.6
    581     */
    582    protected PreparedStatement prepareStatement(final Connection conn, final String sql, final int returnedKeys)
    583            throws SQLException {
    584
    585        @SuppressWarnings("resource")
    586        final
    587        PreparedStatement ps = conn.prepareStatement(sql, returnedKeys);
    588        try {
    589            configureStatement(ps);
    590        } catch (final SQLException e) {
    591            ps.close();
    592            throw e;
    593        }
    594        return ps;
    595    }
    596
    597    /**
    598     * Throws a new exception with a more informative error message.
    599     *
    600     * @param cause
    601     *            The original exception that will be chained to the new
    602     *            exception when it's rethrown.
    603     *
    604     * @param sql
    605     *            The query that was executing when the exception happened.
    606     *
    607     * @param params
    608     *            The query replacement parameters; {@code null} is a valid
    609     *            value to pass in.
    610     *
    611     * @throws SQLException
    612     *             if a database access error occurs
    613     */
    614    protected void rethrow(final SQLException cause, final String sql, final Object... params)
    615            throws SQLException {
    616
    617        String causeMessage = cause.getMessage();
    618        if (causeMessage == null) {
    619            causeMessage = "";
    620        }
    621        final StringBuilder msg = new StringBuilder(causeMessage);
    622
    623        msg.append(" Query: ");
    624        msg.append(sql);
    625        msg.append(" Parameters: ");
    626
    627        if (params == null) {
    628            msg.append("[]");
    629        } else {
    630            msg.append(Arrays.deepToString(params));
    631        }
    632
    633        final SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
    634                cause.getErrorCode());
    635        e.setNextException(cause);
    636
    637        throw e;
    638    }
    639
    640    /**
    641     * Wrap the {@code ResultSet} in a decorator before processing it. This
    642     * implementation returns the {@code ResultSet} it is given without any
    643     * decoration.
    644     *
    645     * <p>
    646     * Often, the implementation of this method can be done in an anonymous
    647     * inner class like this:
    648     * </p>
    649     *
    650     * <pre>
    651     * QueryRunner run = new QueryRunner() {
    652     *     protected ResultSet wrap(ResultSet rs) {
    653     *         return StringTrimmedResultSet.wrap(rs);
    654     *     }
    655     * };
    656     * </pre>
    657     *
    658     * @param rs
    659     *            The {@code ResultSet} to decorate; never
    660     *            {@code null}.
    661     * @return The {@code ResultSet} wrapped in some decorator.
    662     */
    663    protected ResultSet wrap(final ResultSet rs) {
    664        return rs;
    665    }
    666
    667}