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