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.Connection;
25  import java.sql.ParameterMetaData;
26  import java.sql.PreparedStatement;
27  import java.sql.ResultSet;
28  import java.sql.SQLException;
29  import java.sql.Statement;
30  import java.sql.Types;
31  import java.util.Arrays;
32  
33  import javax.sql.DataSource;
34  
35  /**
36   * Executes SQL queries with pluggable strategies for handling 
37   * <code>ResultSet</code>s.  This class is thread safe.
38   * 
39   * @see ResultSetHandler
40   */
41  public class QueryRunner {
42  
43      /**
44       * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried it yet)?
45       */
46      private volatile boolean pmdKnownBroken = false;
47      
48      /**
49       * The DataSource to retrieve connections from.
50       */
51      protected final DataSource ds;
52  
53      /**
54       * Constructor for QueryRunner.
55       */
56      public QueryRunner() {
57          super();
58          ds = null;
59      }
60  
61      /**
62       * Constructor for QueryRunner, allows workaround for Oracle drivers
63       * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) };
64       * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
65       * and if it breaks, we'll remember not to use it again.
66       */
67      public QueryRunner(boolean pmdKnownBroken) {
68          super();
69          this.pmdKnownBroken = pmdKnownBroken; 
70          ds = null;
71      }
72      
73      /**
74       * Constructor for QueryRunner, allows workaround for Oracle drivers.  Methods that do not take a 
75       * <code>Connection</code> parameter will retrieve connections from this
76       * <code>DataSource</code>.
77       * 
78       * @param ds The <code>DataSource</code> to retrieve connections from.
79       */
80      public QueryRunner(DataSource ds) {
81          super();
82          this.ds = ds;
83      }
84      
85      /**
86       * Constructor for QueryRunner, allows workaround for Oracle drivers.  Methods that do not take a 
87       * <code>Connection</code> parameter will retrieve connections from this
88       * <code>DataSource</code>.
89       * 
90       * @param ds The <code>DataSource</code> to retrieve connections from.
91       * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) };
92       * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
93       * and if it breaks, we'll remember not to use it again.
94       */
95      public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
96          super();
97          this.pmdKnownBroken = pmdKnownBroken;
98          this.ds = ds;
99      }
100     
101     /**
102      * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
103      * 
104      * @param conn The Connection to use to run the query.  The caller is
105      * responsible for closing this Connection.
106      * @param sql The SQL to execute.
107      * @param params An array of query replacement parameters.  Each row in
108      * this array is one set of batch replacement values. 
109      * @return The number of rows updated per statement.
110      * @throws SQLException if a database access error occurs
111      * @since DbUtils 1.1
112      */
113     public int[] batch(Connection conn, String sql, Object[][] params)
114         throws SQLException {
115 
116         PreparedStatement stmt = null;
117         int[] rows = null;
118         try {
119             stmt = this.prepareStatement(conn, sql);
120 
121             for (int i = 0; i < params.length; i++) {
122                 this.fillStatement(stmt, params[i]);
123                 stmt.addBatch();
124             }
125             rows = stmt.executeBatch();
126 
127         } catch (SQLException e) {
128             this.rethrow(e, sql, params);
129         } finally {
130             close(stmt);
131         }
132 
133         return rows;
134     }
135 
136     /**
137      * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.  The 
138      * <code>Connection</code> is retrieved from the <code>DataSource</code> 
139      * set in the constructor.  This <code>Connection</code> must be in 
140      * auto-commit mode or the update will not be saved. 
141      * 
142      * @param sql The SQL to execute.
143      * @param params An array of query replacement parameters.  Each row in
144      * this array is one set of batch replacement values. 
145      * @return The number of rows updated per statement.
146      * @throws SQLException if a database access error occurs
147      * @since DbUtils 1.1
148      */
149     public int[] batch(String sql, Object[][] params) throws SQLException {
150         Connection conn = this.prepareConnection();
151 
152         try {
153             return this.batch(conn, sql, params);
154         } finally {
155             close(conn);
156         }
157     }
158 
159     /**
160      * Fill the <code>PreparedStatement</code> replacement parameters with 
161      * the given objects.
162      * @param stmt PreparedStatement to fill
163      * @param params Query replacement parameters; <code>null</code> is a valid
164      * value to pass in.
165      * @throws SQLException if a database access error occurs
166      */
167     public void fillStatement(PreparedStatement stmt, Object[] params)
168         throws SQLException {
169 
170         if (params == null) {
171             return;
172         }
173         
174         ParameterMetaData pmd = stmt.getParameterMetaData();
175         if (pmd.getParameterCount() < params.length) {
176             throw new SQLException("Too many parameters: expected "
177                     + pmd.getParameterCount() + ", was given " + params.length);
178         }
179         for (int i = 0; i < params.length; i++) {
180             if (params[i] != null) {
181                 stmt.setObject(i + 1, params[i]);
182             } else {
183                 // VARCHAR works with many drivers regardless
184                 // of the actual column type.  Oddly, NULL and 
185                 // OTHER don't work with Oracle's drivers.
186                 int sqlType = Types.VARCHAR;
187                 if (!pmdKnownBroken) {
188                     try {
189                         sqlType = pmd.getParameterType(i + 1);
190                     } catch (SQLException e) {
191                         pmdKnownBroken = true;
192                     }
193                 }
194                 stmt.setNull(i + 1, sqlType);
195             }
196         }
197     }
198 
199     /**
200      * Fill the <code>PreparedStatement</code> replacement parameters with the
201      * given object's bean property values.
202      * 
203      * @param stmt
204      *            PreparedStatement to fill
205      * @param bean
206      *            a JavaBean object
207      * @param properties
208      *            an ordered array of properties; this gives the order to insert
209      *            values in the statement
210      * @throws SQLException
211      *             if a database access error occurs
212      */
213     public void fillStatementWithBean(PreparedStatement stmt, Object bean,
214             PropertyDescriptor[] properties) throws SQLException {
215         Object[] params = new Object[properties.length];
216         for (int i = 0; i < properties.length; i++) {
217             PropertyDescriptor property = properties[i];
218             Object value = null;
219             Method method = property.getReadMethod();
220             if (method == null) {
221                 throw new RuntimeException("No read method for bean property "
222                         + bean.getClass() + " " + property.getName());
223             }
224             try {
225                 value = method.invoke(bean, new Object[0]);
226             } catch (InvocationTargetException e) {
227                 throw new RuntimeException("Couldn't invoke method: " + method, e);
228             } catch (IllegalArgumentException e) {
229                 throw new RuntimeException("Couldn't invoke method with 0 arguments: " + method, e);
230             } catch (IllegalAccessException e) {
231                 throw new RuntimeException("Couldn't invoke method: " + method, e);
232             } 
233             params[i] = value;
234         }
235         fillStatement(stmt, params);
236     }
237 
238     /**
239      * Fill the <code>PreparedStatement</code> replacement parameters with the
240      * given object's bean property values.
241      * 
242      * @param stmt
243      *            PreparedStatement to fill
244      * @param bean
245      *            a JavaBean object
246      * @param propertyNames
247      *            an ordered array of property names (these should match the
248      *            getters/setters); this gives the order to insert values in the
249      *            statement
250      * @throws SQLException
251      *             if a database access error occurs
252      */
253     public void fillStatementWithBean(PreparedStatement stmt, Object bean,
254             String[] propertyNames) throws SQLException {
255         PropertyDescriptor[] descriptors;
256         try {
257             descriptors = Introspector.getBeanInfo(bean.getClass())
258                     .getPropertyDescriptors();
259         } catch (IntrospectionException e) {
260             throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
261         }
262         PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
263         for (int i = 0; i < propertyNames.length; i++) {
264             String propertyName = propertyNames[i];
265             if (propertyName == null) {
266                 throw new NullPointerException("propertyName can't be null: " + i);
267             }
268             boolean found = false;
269             for (int j = 0; j < descriptors.length; j++) {
270                 PropertyDescriptor descriptor = descriptors[j];
271                 if (propertyName.equals(descriptor.getName())) {
272                     sorted[i] = descriptor;
273                     found = true;
274                     break;
275                 }
276             }
277             if (!found) {
278                 throw new RuntimeException("Couldn't find bean property: "
279                         + bean.getClass() + " " + propertyName);
280             }
281         }
282         fillStatementWithBean(stmt, bean, sorted);
283     }
284 
285     /**
286      * Returns the <code>DataSource</code> this runner is using.  
287      * <code>QueryRunner</code> methods always call this method to get the
288      * <code>DataSource</code> so subclasses can provide specialized
289      * behavior.
290      *
291      * @return DataSource the runner is using
292      */
293     public DataSource getDataSource() {
294         return this.ds;
295     }
296 
297     /**
298      * Factory method that creates and initializes a 
299      * <code>PreparedStatement</code> object for the given SQL.  
300      * <code>QueryRunner</code> methods always call this method to prepare 
301      * statements for them.  Subclasses can override this method to provide 
302      * special PreparedStatement configuration if needed.  This implementation
303      * simply calls <code>conn.prepareStatement(sql)</code>.
304      *  
305      * @param conn The <code>Connection</code> used to create the 
306      * <code>PreparedStatement</code>
307      * @param sql The SQL statement to prepare.
308      * @return An initialized <code>PreparedStatement</code>.
309      * @throws SQLException if a database access error occurs
310      */
311     protected PreparedStatement prepareStatement(Connection conn, String sql)
312         throws SQLException {
313             
314         return conn.prepareStatement(sql);
315     }
316     
317     /**
318      * Factory method that creates and initializes a 
319      * <code>Connection</code> object.  <code>QueryRunner</code> methods 
320      * always call this method to retrieve connections from its DataSource.  
321      * Subclasses can override this method to provide 
322      * special <code>Connection</code> configuration if needed.  This 
323      * implementation simply calls <code>ds.getConnection()</code>.
324      * 
325      * @return An initialized <code>Connection</code>.
326      * @throws SQLException if a database access error occurs
327      * @since DbUtils 1.1
328      */
329     protected Connection prepareConnection() throws SQLException {
330         if(this.getDataSource() == null) {
331             throw new SQLException("QueryRunner requires a DataSource to be " +
332                 "invoked in this way, or a Connection should be passed in");
333         }
334         return this.getDataSource().getConnection();
335     }
336 
337     /**
338      * Execute an SQL SELECT query with a single replacement parameter. The
339      * caller is responsible for closing the connection.
340      * 
341      * @param conn The connection to execute the query in.
342      * @param sql The query to execute.
343      * @param param The replacement parameter.
344      * @param rsh The handler that converts the results into an object.
345      * @return The object returned by the handler.
346      * @throws SQLException if a database access error occurs
347      * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead
348      */
349     public Object query(Connection conn, String sql, Object param,
350             ResultSetHandler rsh) throws SQLException {
351 
352         return this.query(conn, sql, rsh, new Object[] { param });
353     }
354 
355     /**
356      * Execute an SQL SELECT query with replacement parameters.  The
357      * caller is responsible for closing the connection.
358      * 
359      * @param conn The connection to execute the query in.
360      * @param sql The query to execute.
361      * @param params The replacement parameters.
362      * @param rsh The handler that converts the results into an object.
363      * @return The object returned by the handler.
364      * @throws SQLException if a database access error occurs
365      * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead
366      */
367     public Object query(Connection conn, String sql, Object[] params,
368             ResultSetHandler rsh) throws SQLException {
369                 return query(conn, sql, rsh, params);
370             }
371 
372     /**
373      * Execute an SQL SELECT query with replacement parameters.  The
374      * caller is responsible for closing the connection.
375      * 
376      * @param conn The connection to execute the query in.
377      * @param sql The query to execute.
378      * @param rsh The handler that converts the results into an object.
379      * @param params The replacement parameters.
380      * @return The object returned by the handler.
381      * @throws SQLException if a database access error occurs
382      */
383     public Object query(Connection conn, String sql, ResultSetHandler rsh,
384             Object[] params) throws SQLException {
385 
386         PreparedStatement stmt = null;
387         ResultSet rs = null;
388         Object result = null;
389 
390         try {
391             stmt = this.prepareStatement(conn, sql);
392             this.fillStatement(stmt, params);
393             rs = this.wrap(stmt.executeQuery());
394             result = rsh.handle(rs);
395 
396         } catch (SQLException e) {
397             this.rethrow(e, sql, params);
398 
399         } finally {
400             try {
401                 close(rs);
402             } finally {
403                 close(stmt);
404             }
405         }
406 
407         return result;
408     }
409 
410     /**
411      * Execute an SQL SELECT query without any replacement parameters.  The
412      * caller is responsible for closing the connection.
413      * 
414      * @param conn The connection to execute the query in.
415      * @param sql The query to execute.
416      * @param rsh The handler that converts the results into an object.
417      * @return The object returned by the handler.
418      * @throws SQLException if a database access error occurs
419      */
420     public Object query(Connection conn, String sql, ResultSetHandler rsh)
421         throws SQLException {
422 
423         return this.query(conn, sql, rsh, (Object[]) null);
424     }
425 
426     /**
427      * Executes the given SELECT SQL with a single replacement parameter.
428      * The <code>Connection</code> is retrieved from the
429      * <code>DataSource</code> set in the constructor.
430      * 
431      * @param sql The SQL statement to execute.
432      * @param param The replacement parameter.
433      * @param rsh The handler used to create the result object from 
434      * the <code>ResultSet</code>.
435      * 
436      * @return An object generated by the handler.
437      * @throws SQLException if a database access error occurs
438      * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead
439      */
440     public Object query(String sql, Object param, ResultSetHandler rsh)
441         throws SQLException {
442 
443         return this.query(sql, rsh, new Object[] { param });
444     }
445 
446     /**
447      * Executes the given SELECT SQL query and returns a result object.
448      * The <code>Connection</code> is retrieved from the 
449      * <code>DataSource</code> set in the constructor.
450      * 
451      * @param sql The SQL statement to execute.
452      * @param params Initialize the PreparedStatement's IN parameters with 
453      * this array.
454      * 
455      * @param rsh The handler used to create the result object from 
456      * the <code>ResultSet</code>.
457      * 
458      * @return An object generated by the handler.
459      * @throws SQLException if a database access error occurs
460      * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead
461      */
462     public Object query(String sql, Object[] params, ResultSetHandler rsh)
463         throws SQLException {
464             return query(sql, rsh, params);
465         }
466 
467     /**
468      * Executes the given SELECT SQL query and returns a result object.
469      * The <code>Connection</code> is retrieved from the 
470      * <code>DataSource</code> set in the constructor.
471      * 
472      * @param sql The SQL statement to execute.
473      * @param rsh The handler used to create the result object from 
474      * the <code>ResultSet</code>.
475      * @param params Initialize the PreparedStatement's IN parameters with 
476      * this array.
477      * @return An object generated by the handler.
478      * @throws SQLException if a database access error occurs
479      */
480     public Object query(String sql, ResultSetHandler rsh, Object[] params)
481         throws SQLException {
482 
483         Connection conn = this.prepareConnection();
484 
485         try {
486             return this.query(conn, sql, rsh, params);
487         } finally {
488             close(conn);
489         }
490     }
491 
492     /**
493      * Executes the given SELECT SQL without any replacement parameters.
494      * The <code>Connection</code> is retrieved from the
495      * <code>DataSource</code> set in the constructor.
496      * 
497      * @param sql The SQL statement to execute.
498      * @param rsh The handler used to create the result object from 
499      * the <code>ResultSet</code>.
500      * 
501      * @return An object generated by the handler.
502      * @throws SQLException if a database access error occurs
503      */
504     public Object query(String sql, ResultSetHandler rsh) throws SQLException {
505         return this.query(sql, rsh, (Object[]) null);
506     }
507 
508     /**
509      * Throws a new exception with a more informative error message.
510      * 
511      * @param cause The original exception that will be chained to the new 
512      * exception when it's rethrown. 
513      * 
514      * @param sql The query that was executing when the exception happened.
515      * 
516      * @param params The query replacement parameters; <code>null</code> is a 
517      * valid value to pass in.
518      * 
519      * @throws SQLException if a database access error occurs
520      */
521     protected void rethrow(SQLException cause, String sql, Object[] params)
522         throws SQLException {
523 
524         String causeMessage = cause.getMessage();
525         if (causeMessage == null) {
526             causeMessage = "";
527         }
528         StringBuffer msg = new StringBuffer(causeMessage);
529 
530         msg.append(" Query: ");
531         msg.append(sql);
532         msg.append(" Parameters: ");
533 
534         if (params == null) {
535             msg.append("[]");
536         } else {
537             msg.append(Arrays.asList(params));
538         }
539 
540         SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
541                 cause.getErrorCode());
542         e.setNextException(cause);
543 
544         throw e;
545     }
546 
547     /**
548      * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
549      * parameters.
550      * 
551      * @param conn The connection to use to run the query.
552      * @param sql The SQL to execute.
553      * @return The number of rows updated.
554      * @throws SQLException if a database access error occurs
555      */
556     public int update(Connection conn, String sql) throws SQLException {
557         return this.update(conn, sql, (Object[]) null);
558     }
559 
560     /**
561      * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
562      * parameter.
563      * 
564      * @param conn The connection to use to run the query.
565      * @param sql The SQL to execute.
566      * @param param The replacement parameter.
567      * @return The number of rows updated.
568      * @throws SQLException if a database access error occurs
569      */
570     public int update(Connection conn, String sql, Object param)
571         throws SQLException {
572 
573         return this.update(conn, sql, new Object[] { param });
574     }
575 
576     /**
577      * Execute an SQL INSERT, UPDATE, or DELETE query.
578      * 
579      * @param conn The connection to use to run the query.
580      * @param sql The SQL to execute.
581      * @param params The query replacement parameters.
582      * @return The number of rows updated.
583      * @throws SQLException if a database access error occurs
584      */
585     public int update(Connection conn, String sql, Object[] params)
586         throws SQLException {
587 
588         PreparedStatement stmt = null;
589         int rows = 0;
590 
591         try {
592             stmt = this.prepareStatement(conn, sql);
593             this.fillStatement(stmt, params);
594             rows = stmt.executeUpdate();
595 
596         } catch (SQLException e) {
597             this.rethrow(e, sql, params);
598 
599         } finally {
600             close(stmt);
601         }
602 
603         return rows;
604     }
605 
606     /**
607      * Executes the given INSERT, UPDATE, or DELETE SQL statement without
608      * any replacement parameters. The <code>Connection</code> is retrieved 
609      * from the <code>DataSource</code> set in the constructor.  This 
610      * <code>Connection</code> must be in auto-commit mode or the update will 
611      * not be saved. 
612      * 
613      * @param sql The SQL statement to execute.
614      * @throws SQLException if a database access error occurs
615      * @return The number of rows updated.
616      */
617     public int update(String sql) throws SQLException {
618         return this.update(sql, (Object[]) null);
619     }
620 
621     /**
622      * Executes the given INSERT, UPDATE, or DELETE SQL statement with
623      * a single replacement parameter.  The <code>Connection</code> is 
624      * retrieved from the <code>DataSource</code> set in the constructor.
625      * This <code>Connection</code> must be in auto-commit mode or the 
626      * update will not be saved. 
627      * 
628      * @param sql The SQL statement to execute.
629      * @param param The replacement parameter.
630      * @throws SQLException if a database access error occurs
631      * @return The number of rows updated.
632      */
633     public int update(String sql, Object param) throws SQLException {
634         return this.update(sql, new Object[] { param });
635     }
636 
637     /**
638      * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The 
639      * <code>Connection</code> is retrieved from the <code>DataSource</code> 
640      * set in the constructor.  This <code>Connection</code> must be in 
641      * auto-commit mode or the update will not be saved. 
642      * 
643      * @param sql The SQL statement to execute.
644      * @param params Initializes the PreparedStatement's IN (i.e. '?') 
645      * parameters.
646      * @throws SQLException if a database access error occurs
647      * @return The number of rows updated.
648      */
649     public int update(String sql, Object[] params) throws SQLException {
650         Connection conn = this.prepareConnection();
651 
652         try {
653             return this.update(conn, sql, params);
654         } finally {
655             close(conn);
656         }
657     }
658     
659     /**
660      * Wrap the <code>ResultSet</code> in a decorator before processing it.
661      * This implementation returns the <code>ResultSet</code> it is given
662      * without any decoration.
663      *
664      * <p>
665      * Often, the implementation of this method can be done in an anonymous 
666      * inner class like this:
667      * </p>
668      * <pre> 
669      * QueryRunner run = new QueryRunner() {
670      *     protected ResultSet wrap(ResultSet rs) {
671      *         return StringTrimmedResultSet.wrap(rs);
672      *     }
673      * };
674      * </pre>
675      * 
676      * @param rs The <code>ResultSet</code> to decorate; never 
677      * <code>null</code>.
678      * @return The <code>ResultSet</code> wrapped in some decorator. 
679      */
680     protected ResultSet wrap(ResultSet rs) {
681         return rs;
682     }
683     
684     /**
685      * Close a <code>Connection</code>.  This implementation avoids closing if 
686      * null and does <strong>not</strong> suppress any exceptions.  Subclasses
687      * can override to provide special handling like logging.
688      * @param conn Connection to close
689      * @throws SQLException if a database access error occurs
690      * @since DbUtils 1.1
691      */
692     protected void close(Connection conn) throws SQLException {
693         DbUtils.close(conn);
694     }
695     
696     /**
697      * Close a <code>Statement</code>.  This implementation avoids closing if 
698      * null and does <strong>not</strong> suppress any exceptions.  Subclasses
699      * can override to provide special handling like logging.
700      * @param stmt Statement to close
701      * @throws SQLException if a database access error occurs
702      * @since DbUtils 1.1
703      */
704     protected void close(Statement stmt) throws SQLException {
705         DbUtils.close(stmt);
706     }
707 
708     /**
709      * Close a <code>ResultSet</code>.  This implementation avoids closing if 
710      * null and does <strong>not</strong> suppress any exceptions.  Subclasses
711      * can override to provide special handling like logging.
712      * @param rs ResultSet to close
713      * @throws SQLException if a database access error occurs
714      * @since DbUtils 1.1
715      */
716     protected void close(ResultSet rs) throws SQLException {
717         DbUtils.close(rs);
718     }
719 
720 }