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.sql.Connection;
20  import java.sql.PreparedStatement;
21  import java.sql.ResultSet;
22  import java.sql.SQLException;
23  import java.sql.Statement;
24  import java.sql.Types;
25  import java.util.Arrays;
26  
27  import javax.sql.DataSource;
28  
29  /**
30   * Executes SQL queries with pluggable strategies for handling 
31   * <code>ResultSet</code>s.  This class is thread safe.
32   * 
33   * @see ResultSetHandler
34   */
35  public class QueryRunner {
36  
37      /**
38       * The DataSource to retrieve connections from.
39       */
40      protected DataSource ds = null;
41  
42      /**
43       * Constructor for QueryRunner.
44       */
45      public QueryRunner() {
46          super();
47      }
48  
49      /**
50       * Constructor for QueryRunner.  Methods that do not take a 
51       * <code>Connection</code> parameter will retrieve connections from this
52       * <code>DataSource</code>.
53       * 
54       * @param ds The <code>DataSource</code> to retrieve connections from.
55       */
56      public QueryRunner(DataSource ds) {
57          super();
58          setDataSource(ds);
59      }
60      
61      /**
62       * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
63       * 
64       * @param conn The Connection to use to run the query.  The caller is
65       * responsible for closing this Connection.
66       * @param sql The SQL to execute.
67       * @param params An array of query replacement parameters.  Each row in
68       * this array is one set of batch replacement values. 
69       * @return The number of rows updated per statement.
70       * @throws SQLException if a database access error occurs
71       * @since DbUtils 1.1
72       */
73      public int[] batch(Connection conn, String sql, Object[][] params)
74          throws SQLException {
75  
76          PreparedStatement stmt = null;
77          int[] rows = null;
78          try {
79              stmt = this.prepareStatement(conn, sql);
80  
81              for (int i = 0; i < params.length; i++) {
82                  this.fillStatement(stmt, params[i]);
83                  stmt.addBatch();
84              }
85              rows = stmt.executeBatch();
86  
87          } catch (SQLException e) {
88              this.rethrow(e, sql, params);
89          } finally {
90              close(stmt);
91          }
92  
93          return rows;
94      }
95  
96      /**
97       * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.  The 
98       * <code>Connection</code> is retrieved from the <code>DataSource</code> 
99       * set in the constructor.  This <code>Connection</code> must be in 
100      * auto-commit mode or the update will not be saved. 
101      * 
102      * @param sql The SQL to execute.
103      * @param params An array of query replacement parameters.  Each row in
104      * this array is one set of batch replacement values. 
105      * @return The number of rows updated per statement.
106      * @throws SQLException if a database access error occurs
107      * @since DbUtils 1.1
108      */
109     public int[] batch(String sql, Object[][] params) throws SQLException {
110         Connection conn = this.prepareConnection();
111 
112         try {
113             return this.batch(conn, sql, params);
114         } finally {
115             close(conn);
116         }
117     }
118 
119     /**
120      * Fill the <code>PreparedStatement</code> replacement parameters with 
121      * the given objects.
122      * @param stmt PreparedStatement to fill
123      * @param params Query replacement parameters; <code>null</code> is a valid
124      * value to pass in.
125      * @throws SQLException if a database access error occurs
126      */
127     protected void fillStatement(PreparedStatement stmt, Object[] params)
128         throws SQLException {
129 
130         if (params == null) {
131             return;
132         }
133 
134         for (int i = 0; i < params.length; i++) {
135             if (params[i] != null) {
136                 stmt.setObject(i + 1, params[i]);
137             } else {
138                 // VARCHAR works with many drivers regardless
139                 // of the actual column type.  Oddly, NULL and 
140                 // OTHER don't work with Oracle's drivers.
141                 stmt.setNull(i + 1, Types.VARCHAR);
142             }
143         }
144     }
145 
146     /**
147      * Returns the <code>DataSource</code> this runner is using.  
148      * <code>QueryRunner</code> methods always call this method to get the
149      * <code>DataSource</code> so subclasses can provide specialized
150      * behavior.
151      *
152      * @return DataSource the runner is using
153      */
154     public DataSource getDataSource() {
155         return this.ds;
156     }
157 
158     /**
159      * Factory method that creates and initializes a 
160      * <code>PreparedStatement</code> object for the given SQL.  
161      * <code>QueryRunner</code> methods always call this method to prepare 
162      * statements for them.  Subclasses can override this method to provide 
163      * special PreparedStatement configuration if needed.  This implementation
164      * simply calls <code>conn.prepareStatement(sql)</code>.
165      *  
166      * @param conn The <code>Connection</code> used to create the 
167      * <code>PreparedStatement</code>
168      * @param sql The SQL statement to prepare.
169      * @return An initialized <code>PreparedStatement</code>.
170      * @throws SQLException if a database access error occurs
171      */
172     protected PreparedStatement prepareStatement(Connection conn, String sql)
173         throws SQLException {
174             
175         return conn.prepareStatement(sql);
176     }
177     
178     /**
179      * Factory method that creates and initializes a 
180      * <code>Connection</code> object.  <code>QueryRunner</code> methods 
181      * always call this method to retrieve connections from its DataSource.  
182      * Subclasses can override this method to provide 
183      * special <code>Connection</code> configuration if needed.  This 
184      * implementation simply calls <code>ds.getConnection()</code>.
185      * 
186      * @return An initialized <code>Connection</code>.
187      * @throws SQLException if a database access error occurs
188      * @since DbUtils 1.1
189      */
190     protected Connection prepareConnection() throws SQLException {
191         if(this.getDataSource() == null) {
192             throw new SQLException("QueryRunner requires a DataSource to be " +
193                 "invoked in this way, or a Connection should be passed in");
194         }
195         return this.getDataSource().getConnection();
196     }
197 
198     /**
199      * Execute an SQL SELECT query with a single replacement parameter. The
200      * caller is responsible for closing the connection.
201      * 
202      * @param conn The connection to execute the query in.
203      * @param sql The query to execute.
204      * @param param The replacement parameter.
205      * @param rsh The handler that converts the results into an object.
206      * @return The object returned by the handler.
207      * @throws SQLException if a database access error occurs
208      */
209     public Object query(Connection conn, String sql, Object param,
210             ResultSetHandler rsh) throws SQLException {
211 
212         return this.query(conn, sql, new Object[] { param }, rsh);
213     }
214 
215     /**
216      * Execute an SQL SELECT query with replacement parameters.  The
217      * caller is responsible for closing the connection.
218      * 
219      * @param conn The connection to execute the query in.
220      * @param sql The query to execute.
221      * @param params The replacement parameters.
222      * @param rsh The handler that converts the results into an object.
223      * @return The object returned by the handler.
224      * @throws SQLException if a database access error occurs
225      */
226     public Object query(Connection conn, String sql, Object[] params,
227             ResultSetHandler rsh) throws SQLException {
228 
229         PreparedStatement stmt = null;
230         ResultSet rs = null;
231         Object result = null;
232 
233         try {
234             stmt = this.prepareStatement(conn, sql);
235             this.fillStatement(stmt, params);
236             rs = this.wrap(stmt.executeQuery());
237             result = rsh.handle(rs);
238 
239         } catch (SQLException e) {
240             this.rethrow(e, sql, params);
241 
242         } finally {
243             try {
244                 close(rs);
245             } finally {
246                 close(stmt);
247             }
248         }
249 
250         return result;
251     }
252 
253     /**
254      * Execute an SQL SELECT query without any replacement parameters.  The
255      * caller is responsible for closing the connection.
256      * 
257      * @param conn The connection to execute the query in.
258      * @param sql The query to execute.
259      * @param rsh The handler that converts the results into an object.
260      * @return The object returned by the handler.
261      * @throws SQLException if a database access error occurs
262      */
263     public Object query(Connection conn, String sql, ResultSetHandler rsh)
264         throws SQLException {
265 
266         return this.query(conn, sql, (Object[]) null, rsh);
267     }
268 
269     /**
270      * Executes the given SELECT SQL with a single replacement parameter.
271      * The <code>Connection</code> is retrieved from the
272      * <code>DataSource</code> set in the constructor.
273      * 
274      * @param sql The SQL statement to execute.
275      * @param param The replacement parameter.
276      * @param rsh The handler used to create the result object from 
277      * the <code>ResultSet</code>.
278      * 
279      * @return An object generated by the handler.
280      * @throws SQLException if a database access error occurs
281      */
282     public Object query(String sql, Object param, ResultSetHandler rsh)
283         throws SQLException {
284 
285         return this.query(sql, new Object[] { param }, rsh);
286     }
287 
288     /**
289      * Executes the given SELECT SQL query and returns a result object.
290      * The <code>Connection</code> is retrieved from the 
291      * <code>DataSource</code> set in the constructor.
292      * 
293      * @param sql The SQL statement to execute.
294      * @param params Initialize the PreparedStatement's IN parameters with 
295      * this array.
296      * 
297      * @param rsh The handler used to create the result object from 
298      * the <code>ResultSet</code>.
299      * 
300      * @return An object generated by the handler.
301      * @throws SQLException if a database access error occurs
302      */
303     public Object query(String sql, Object[] params, ResultSetHandler rsh)
304         throws SQLException {
305 
306         Connection conn = this.prepareConnection();
307 
308         try {
309             return this.query(conn, sql, params, rsh);
310         } finally {
311             close(conn);
312         }
313     }
314 
315     /**
316      * Executes the given SELECT SQL without any replacement parameters.
317      * The <code>Connection</code> is retrieved from the
318      * <code>DataSource</code> set in the constructor.
319      * 
320      * @param sql The SQL statement to execute.
321      * @param rsh The handler used to create the result object from 
322      * the <code>ResultSet</code>.
323      * 
324      * @return An object generated by the handler.
325      * @throws SQLException if a database access error occurs
326      */
327     public Object query(String sql, ResultSetHandler rsh) throws SQLException {
328         return this.query(sql, (Object[]) null, rsh);
329     }
330 
331     /**
332      * Throws a new exception with a more informative error message.
333      * 
334      * @param cause The original exception that will be chained to the new 
335      * exception when it's rethrown. 
336      * 
337      * @param sql The query that was executing when the exception happened.
338      * 
339      * @param params The query replacement parameters; <code>null</code> is a 
340      * valid value to pass in.
341      * 
342      * @throws SQLException if a database access error occurs
343      */
344     protected void rethrow(SQLException cause, String sql, Object[] params)
345         throws SQLException {
346 
347         StringBuffer msg = new StringBuffer(cause.getMessage());
348 
349         msg.append(" Query: ");
350         msg.append(sql);
351         msg.append(" Parameters: ");
352 
353         if (params == null) {
354             msg.append("[]");
355         } else {
356             msg.append(Arrays.asList(params));
357         }
358 
359         SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
360                 cause.getErrorCode());
361         e.setNextException(cause);
362 
363         throw e;
364     }
365 
366     /**
367      * Sets the <code>DataSource</code> this runner will use to get
368      * database connections from.  This should be called after creating a
369      * runner with the default constructor if you intend to use the
370      * execute methods without passing in a <code>Connection</code>.
371      * 
372      * @param dataSource The DataSource to use.
373      */
374     public void setDataSource(DataSource dataSource) {
375         this.ds = dataSource;
376     }
377 
378     /**
379      * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
380      * parameters.
381      * 
382      * @param conn The connection to use to run the query.
383      * @param sql The SQL to execute.
384      * @return The number of rows updated.
385      * @throws SQLException if a database access error occurs
386      */
387     public int update(Connection conn, String sql) throws SQLException {
388         return this.update(conn, sql, (Object[]) null);
389     }
390 
391     /**
392      * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
393      * parameter.
394      * 
395      * @param conn The connection to use to run the query.
396      * @param sql The SQL to execute.
397      * @param param The replacement parameter.
398      * @return The number of rows updated.
399      * @throws SQLException if a database access error occurs
400      */
401     public int update(Connection conn, String sql, Object param)
402         throws SQLException {
403 
404         return this.update(conn, sql, new Object[] { param });
405     }
406 
407     /**
408      * Execute an SQL INSERT, UPDATE, or DELETE query.
409      * 
410      * @param conn The connection to use to run the query.
411      * @param sql The SQL to execute.
412      * @param params The query replacement parameters.
413      * @return The number of rows updated.
414      * @throws SQLException if a database access error occurs
415      */
416     public int update(Connection conn, String sql, Object[] params)
417         throws SQLException {
418 
419         PreparedStatement stmt = null;
420         int rows = 0;
421 
422         try {
423             stmt = this.prepareStatement(conn, sql);
424             this.fillStatement(stmt, params);
425             rows = stmt.executeUpdate();
426 
427         } catch (SQLException e) {
428             this.rethrow(e, sql, params);
429 
430         } finally {
431             close(stmt);
432         }
433 
434         return rows;
435     }
436 
437     /**
438      * Executes the given INSERT, UPDATE, or DELETE SQL statement without
439      * any replacement parameters. The <code>Connection</code> is retrieved 
440      * from the <code>DataSource</code> set in the constructor.  This 
441      * <code>Connection</code> must be in auto-commit mode or the update will 
442      * not be saved. 
443      * 
444      * @param sql The SQL statement to execute.
445      * @throws SQLException if a database access error occurs
446      * @return The number of rows updated.
447      */
448     public int update(String sql) throws SQLException {
449         return this.update(sql, (Object[]) null);
450     }
451 
452     /**
453      * Executes the given INSERT, UPDATE, or DELETE SQL statement with
454      * a single replacement parameter.  The <code>Connection</code> is 
455      * retrieved from the <code>DataSource</code> set in the constructor.
456      * This <code>Connection</code> must be in auto-commit mode or the 
457      * update will not be saved. 
458      * 
459      * @param sql The SQL statement to execute.
460      * @param param The replacement parameter.
461      * @throws SQLException if a database access error occurs
462      * @return The number of rows updated.
463      */
464     public int update(String sql, Object param) throws SQLException {
465         return this.update(sql, new Object[] { param });
466     }
467 
468     /**
469      * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The 
470      * <code>Connection</code> is retrieved from the <code>DataSource</code> 
471      * set in the constructor.  This <code>Connection</code> must be in 
472      * auto-commit mode or the update will not be saved. 
473      * 
474      * @param sql The SQL statement to execute.
475      * @param params Initializes the PreparedStatement's IN (i.e. '?') 
476      * parameters.
477      * @throws SQLException if a database access error occurs
478      * @return The number of rows updated.
479      */
480     public int update(String sql, Object[] params) throws SQLException {
481         Connection conn = this.prepareConnection();
482 
483         try {
484             return this.update(conn, sql, params);
485         } finally {
486             close(conn);
487         }
488     }
489     
490     /**
491      * Wrap the <code>ResultSet</code> in a decorator before processing it.
492      * This implementation returns the <code>ResultSet</code> it is given
493      * without any decoration.
494      *
495      * <p>
496      * Often, the implementation of this method can be done in an anonymous 
497      * inner class like this:
498      * </p>
499      * <pre> 
500      * QueryRunner run = new QueryRunner() {
501      *     protected ResultSet wrap(ResultSet rs) {
502      *         return StringTrimmedResultSet.wrap(rs);
503      *     }
504      * };
505      * </pre>
506      * 
507      * @param rs The <code>ResultSet</code> to decorate; never 
508      * <code>null</code>.
509      * @return The <code>ResultSet</code> wrapped in some decorator. 
510      */
511     protected ResultSet wrap(ResultSet rs) {
512         return rs;
513     }
514     
515     /**
516      * Close a <code>Connection</code>.  This implementation avoids closing if 
517      * null and does <strong>not</strong> suppress any exceptions.  Subclasses
518      * can override to provide special handling like logging.
519      * @param conn Connection to close
520      * @throws SQLException if a database access error occurs
521      * @since DbUtils 1.1
522      */
523     protected void close(Connection conn) throws SQLException {
524         DbUtils.close(conn);
525     }
526     
527     /**
528      * Close a <code>Statement</code>.  This implementation avoids closing if 
529      * null and does <strong>not</strong> suppress any exceptions.  Subclasses
530      * can override to provide special handling like logging.
531      * @param stmt Statement to close
532      * @throws SQLException if a database access error occurs
533      * @since DbUtils 1.1
534      */
535     protected void close(Statement stmt) throws SQLException {
536         DbUtils.close(stmt);
537     }
538 
539     /**
540      * Close a <code>ResultSet</code>.  This implementation avoids closing if 
541      * null and does <strong>not</strong> suppress any exceptions.  Subclasses
542      * can override to provide special handling like logging.
543      * @throws SQLException if a database access error occurs
544      * @param rs ResultSet to close
545      * @since DbUtils 1.1
546      */
547     protected void close(ResultSet rs) throws SQLException {
548         DbUtils.close(rs);
549     }
550 
551 }