View Javadoc
1   /*
2    * Licensed to the Apache Software Foundation (ASF) under one or more
3    * contributor license agreements.  See the NOTICE file distributed with
4    * this work for additional information regarding copyright ownership.
5    * The ASF licenses this file to You under the Apache License, Version 2.0
6    * (the "License"); you may not use this file except in compliance with
7    * the License.  You may obtain a copy of the License at
8    *
9    *      http://www.apache.org/licenses/LICENSE-2.0
10   *
11   * Unless required by applicable law or agreed to in writing, software
12   * distributed under the License is distributed on an "AS IS" BASIS,
13   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14   * See the License for the specific language governing permissions and
15   * limitations under the License.
16   */
17  package org.apache.commons.dbutils;
18  
19  import java.beans.IntrospectionException;
20  import java.beans.Introspector;
21  import java.beans.PropertyDescriptor;
22  import java.lang.reflect.InvocationTargetException;
23  import java.lang.reflect.Method;
24  import java.sql.CallableStatement;
25  import java.sql.Connection;
26  import java.sql.ParameterMetaData;
27  import java.sql.PreparedStatement;
28  import java.sql.ResultSet;
29  import java.sql.SQLException;
30  import java.sql.SQLFeatureNotSupportedException;
31  import java.sql.Statement;
32  import java.sql.Types;
33  import java.util.Arrays;
34  
35  import javax.sql.DataSource;
36  
37  /**
38   * The base class for QueryRunner & AsyncQueryRunner. This class is thread safe.
39   *
40   * @since 1.4 (mostly extracted from QueryRunner)
41   */
42  public abstract class AbstractQueryRunner {
43      /**
44       * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried
45       * it yet)?
46       */
47      private volatile boolean pmdKnownBroken = false;
48  
49      /**
50       * The DataSource to retrieve connections from.
51       * @deprecated Access to this field should be through {@link #getDataSource()}.
52       */
53      @Deprecated
54      protected final DataSource ds;
55  
56      /**
57       * Configuration to use when preparing statements.
58       */
59      private final StatementConfiguration stmtConfig;
60  
61      /**
62       * Default constructor, sets pmdKnownBroken to false, ds to null and stmtConfig to null.
63       */
64      public AbstractQueryRunner() {
65          ds = null;
66          this.stmtConfig = null;
67      }
68  
69      /**
70       * Constructor to control the use of {@code ParameterMetaData}.
71       *
72       * @param pmdKnownBroken
73       *            Some drivers don't support
74       *            {@link ParameterMetaData#getParameterType(int) }; if
75       *            {@code pmdKnownBroken} is set to true, we won't even try
76       *            it; if false, we'll try it, and if it breaks, we'll remember
77       *            not to use it again.
78       */
79      public AbstractQueryRunner(final boolean pmdKnownBroken) {
80          this.pmdKnownBroken = pmdKnownBroken;
81          ds = null;
82          this.stmtConfig = null;
83      }
84  
85      /**
86       * Constructor to provide a {@code DataSource}. Methods that do not
87       * take a {@code Connection} parameter will retrieve connections from
88       * this {@code DataSource}.
89       *
90       * @param ds
91       *            The {@code DataSource} to retrieve connections from.
92       */
93      public AbstractQueryRunner(final DataSource ds) {
94          this.ds = ds;
95          this.stmtConfig = null;
96      }
97  
98      /**
99       * 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 }