QueryRunner


1   /*
2    * $Header: /home/cvs/jakarta-commons/dbutils/src/java/org/apache/commons/dbutils/QueryRunner.java,v 1.6 2003/11/12 01:00:55 dgraham Exp $
3    * $Revision: 1.6 $
4    * $Date: 2003/11/12 01:00:55 $
5    * 
6    * ====================================================================
7    *
8    * The Apache Software License, Version 1.1
9    *
10   * Copyright (c) 2002-2003 The Apache Software Foundation.  All rights
11   * reserved.
12   *
13   * Redistribution and use in source and binary forms, with or without
14   * modification, are permitted provided that the following conditions
15   * are met:
16   *
17   * 1. Redistributions of source code must retain the above copyright
18   *    notice, this list of conditions and the following disclaimer.
19   *
20   * 2. Redistributions in binary form must reproduce the above copyright
21   *    notice, this list of conditions and the following disclaimer in
22   *    the documentation and/or other materials provided with the
23   *    distribution.
24   *
25   * 3. The end-user documentation included with the redistribution, if
26   *    any, must include the following acknowledgement:
27   *       "This product includes software developed by the
28   *        Apache Software Foundation (http://www.apache.org/)."
29   *    Alternately, this acknowledgement may appear in the software itself,
30   *    if and wherever such third-party acknowledgements normally appear.
31   *
32   * 4. The names "The Jakarta Project", "Commons", and "Apache Software
33   *    Foundation" must not be used to endorse or promote products derived
34   *    from this software without prior written permission. For written
35   *    permission, please contact apache@apache.org.
36   *
37   * 5. Products derived from this software may not be called "Apache"
38   *    nor may "Apache" appear in their names without prior written
39   *    permission of the Apache Software Foundation.
40   *
41   * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
42   * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
43   * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
44   * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
45   * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
46   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
47   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
48   * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
49   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
50   * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
51   * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
52   * SUCH DAMAGE.
53   * ====================================================================
54   *
55   * This software consists of voluntary contributions made by many
56   * individuals on behalf of the Apache Software Foundation.  For more
57   * information on the Apache Software Foundation, please see
58   * <http://www.apache.org/>.
59   *
60   */
61  
62  package org.apache.commons.dbutils;
63  
64  import java.sql.Connection  ;
65  import java.sql.PreparedStatement  ;
66  import java.sql.ResultSet  ;
67  import java.sql.SQLException  ;
68  import java.sql.Types  ;
69  import java.util.Arrays  ;
70  
71  import javax.sql.DataSource  ;
72  
73  /**
74   * Executes SQL queries with pluggable strategies for handling 
75   * <code>ResultSet</code>s.  This class is thread safe.
76   * 
77   * @see ResultSetHandler
78   * 
79   * @author Henri Yandell
80   * @author Juozas Baliuka
81   * @author Steven Caswell
82   * @author David Graham
83   */
84  public class QueryRunner {
85  
86      /**
87       * The DataSource to retrieve connections from.
88       */
89      protected DataSource   ds = null;
90  
91      /**
92       * Constructor for QueryRunner.
93       */
94      public QueryRunner() {
95          super();
96      }
97  
98      /**
99       * Constructor for QueryRunner.  Methods that do not take a 
100      * <code>Connection</code> parameter will retrieve connections from this
101      * <code>DataSource</code>.
102      * 
103      * @param ds The <code>DataSource</code> to retrieve connections from.
104      */
105     public QueryRunner(DataSource   ds) {
106         super();
107         this.ds = ds;
108     }
109 
110     /**
111      * Fill the <code>PreparedStatement</code> replacement parameters with 
112      * the given objects.
113      * @param stmt
114      * @param params Query replacement parameters; <code>null</code> is a valid
115      * value to pass in.
116      * @throws SQLException
117      */
118     protected void fillStatement(PreparedStatement   stmt, Object  [] params)
119         throws SQLException   {
120 
121         if (params == null) {
122             return;
123         }
124 
125         for (int i = 0; i < params.length; i++) {
126             if (params[i] != null) {
127                 stmt.setObject(i + 1, params[i]);
128             } else {
129                 stmt.setNull(i + 1, Types.OTHER);
130             }
131         }
132     }
133 
134     /**
135      * Returns the <code>DataSource</code> this runner is using.
136      */
137     public DataSource   getDataSource() {
138         return this.ds;
139     }
140 
141     /**
142      * Factory method that creates and initializes a 
143      * <code>PreparedStatement</code> object for the given SQL.  
144      * <code>QueryRunner</code> methods always call this method to prepare 
145      * statements for them.  Subclasses can override this method to provide 
146      * special PreparedStatement configuration if needed.  This implementation
147      * simply calls <code>conn.prepareStatement(sql)</code>.
148      *  
149      * @param conn The <code>Connection</code> used to create the 
150      * <code>PreparedStatement</code>
151      * @param sql The SQL statement to prepare.
152      * @return An initialized <code>PreparedStatement</code>.
153      * @throws SQLException
154      */
155     protected PreparedStatement   prepareStatement(Connection   conn, String   sql)
156         throws SQLException   {
157             
158         return conn.prepareStatement(sql);
159     }
160 
161     /**
162      * Execute an SQL SELECT query with a single replacement parameter.  The
163      * caller is responsible for connection cleanup.
164      * 
165      * @param conn The connection to execute the query in.
166      * @param sql The query to execute.
167      * @param param The replacement parameter.
168      * @param rsh The handler that converts the results into an object.
169      * @return The object returned by the handler.
170      * @throws SQLException
171      */
172     public Object   query(
173         Connection   conn,
174         String   sql,
175         Object   param,
176         ResultSetHandler rsh)
177         throws SQLException   {
178 
179         return this.query(conn, sql, new Object  [] { param }, rsh);
180     }
181 
182     /**
183      * Execute an SQL SELECT query with replacement parameters.  The
184      * caller is responsible for connection cleanup.
185      * 
186      * @param conn The connection to execute the query in.
187      * @param sql The query to execute.
188      * @param params The replacement parameters.
189      * @param rsh The handler that converts the results into an object.
190      * @return The object returned by the handler.
191      * @throws SQLException
192      */
193     public Object   query(
194         Connection   conn,
195         String   sql,
196         Object  [] params,
197         ResultSetHandler rsh)
198         throws SQLException   {
199 
200         PreparedStatement   stmt = null;
201         ResultSet   rs = null;
202         Object   result = null;
203 
204         try {
205             stmt = this.prepareStatement(conn, sql);
206             this.fillStatement(stmt, params);
207 
208             rs = this.wrap(stmt.executeQuery());
209 
210             result = rsh.handle(rs);
211 
212         } catch (SQLException   e) {
213             this.rethrow(e, sql, params);
214 
215         } finally {
216             DbUtils.close(rs);
217             DbUtils.close(stmt);
218         }
219 
220         return result;
221     }
222 
223     /**
224      * Execute an SQL SELECT query without any replacement parameters.  The
225      * caller is responsible for connection cleanup.
226      * 
227      * @param conn The connection to execute the query in.
228      * @param sql The query to execute.
229      * @param rsh The handler that converts the results into an object.
230      * @return The object returned by the handler.
231      * @throws SQLException
232      */
233     public Object   query(Connection   conn, String   sql, ResultSetHandler rsh)
234         throws SQLException   {
235 
236         return this.query(conn, sql, (Object  []) null, rsh);
237     }
238 
239     /**
240      * Executes the given SELECT SQL with a single replacement parameter.
241      * The <code>Connection</code> is retrieved from the
242      * <code>DataSource</code> set in the constructor.
243      * 
244      * @param sql The SQL statement to execute.
245      * @param param The replacement parameter.
246      * @param rsh The handler used to create the result object from 
247      * the <code>ResultSet</code>.
248      * 
249      * @return An object generated by the handler.
250      * @throws SQLException
251      */
252     public Object   query(String   sql, Object   param, ResultSetHandler rsh)
253         throws SQLException   {
254 
255         return this.query(sql, new Object  [] { param }, rsh);
256     }
257 
258     /**
259      * Executes the given SELECT SQL query and returns a result object.
260      * The <code>Connection</code> is retrieved from the 
261      * <code>DataSource</code> set in the constructor.
262      * 
263      * @param sql The SQL statement to execute.
264      * @param params Initialize the PreparedStatement's IN parameters with 
265      * this array.
266      * 
267      * @param rsh The handler used to create the result object from 
268      * the <code>ResultSet</code>.
269      * 
270      * @return An object generated by the handler.
271      * @throws SQLException
272      */
273     public Object   query(String   sql, Object  [] params, ResultSetHandler rsh)
274         throws SQLException   {
275 
276         Connection   conn = this.ds.getConnection();
277 
278         try {
279             return this.query(conn, sql, params, rsh);
280 
281         } finally {
282             DbUtils.close(conn);
283         }
284     }
285 
286     /**
287      * Executes the given SELECT SQL without any replacement parameters.
288      * The <code>Connection</code> is retrieved from the
289      * <code>DataSource</code> set in the constructor.
290      * 
291      * @param sql The SQL statement to execute.
292      * @param rsh The handler used to create the result object from 
293      * the <code>ResultSet</code>.
294      * 
295      * @return An object generated by the handler.
296      * @throws SQLException
297      */
298     public Object   query(String   sql, ResultSetHandler rsh) throws SQLException   {
299         return this.query(sql, (Object  []) null, rsh);
300     }
301 
302     /**
303      * Throws a new exception with a more informative error message.
304      * 
305      * @param cause The original exception that will be chained to the new 
306      * exception when it's rethrown. 
307      * 
308      * @param sql The query that was executing when the exception happened.
309      * 
310      * @param params The query replacement paramaters; <code>null</code> is a 
311      * valid value to pass in.
312      * 
313      * @throws SQLException
314      */
315     protected void rethrow(SQLException   cause, String   sql, Object  [] params)
316         throws SQLException   {
317 
318         StringBuffer   msg = new StringBuffer  (cause.getMessage());
319 
320         msg.append(" Query: ");
321         msg.append(sql);
322         msg.append(" Parameters: ");
323 
324         if (params == null) {
325             msg.append("[]");
326         } else {
327             msg.append(Arrays.asList(params));
328         }
329 
330         SQLException   e = new SQLException  (msg.toString());
331         e.setNextException(cause);
332 
333         throw e;
334     }
335 
336     /**
337      * Sets the <code>DataSource</code> this runner will use to get
338      * database connections from.  This should be called after creating a
339      * runner with the default constructor if you intend to use the
340      * execute methods without passing in a <code>Connection</code>.
341      * 
342      * @param dataSource The DataSource to use.
343      */
344     public void setDataSource(DataSource   dataSource) {
345         this.ds = dataSource;
346     }
347 
348     /**
349      * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
350      * parameters.
351      * 
352      * @param conn The connection to use to run the query.
353      * @param sql The SQL to execute.
354      * @return The number of rows updated.
355      * @throws SQLException
356      */
357     public int update(Connection   conn, String   sql) throws SQLException   {
358         return this.update(conn, sql, (Object  []) null);
359     }
360 
361     /**
362      * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
363      * parameter.
364      * 
365      * @param conn The connection to use to run the query.
366      * @param sql The SQL to execute.
367      * @param param The replacement parameter.
368      * @return The number of rows updated.
369      * @throws SQLException
370      */
371     public int update(Connection   conn, String   sql, Object   param)
372         throws SQLException   {
373 
374         return this.update(conn, sql, new Object  [] { param });
375     }
376 
377     /**
378      * Execute an SQL INSERT, UPDATE, or DELETE query.
379      * 
380      * @param conn The connection to use to run the query.
381      * @param sql The SQL to execute.
382      * @param params The query replacement parameters.
383      * @return The number of rows updated.
384      * @throws SQLException
385      */
386     public int update(Connection   conn, String   sql, Object  [] params)
387         throws SQLException   {
388 
389         PreparedStatement   stmt = null;
390         int rows = 0;
391 
392         try {
393             stmt = this.prepareStatement(conn, sql);
394             this.fillStatement(stmt, params);
395 
396             rows = stmt.executeUpdate();
397 
398         } catch (SQLException   e) {
399             this.rethrow(e, sql, params);
400 
401         } finally {
402             DbUtils.close(stmt);
403         }
404 
405         return rows;
406     }
407 
408     /**
409      * Executes the given INSERT, UPDATE, or DELETE SQL statement without
410      * any replacement parameters. The <code>Connection</code> is retrieved 
411      * from the <code>DataSource</code> set in the constructor.
412      * 
413      * @param sql The SQL statement to execute.
414      * @throws SQLException
415      * @return The number of rows updated.
416      */
417     public int update(String   sql) throws SQLException   {
418         return this.update(sql, (Object  []) null);
419     }
420 
421     /**
422      * Executes the given INSERT, UPDATE, or DELETE SQL statement with
423      * a single replacement parameter.  The <code>Connection</code> is 
424      * retrieved from the <code>DataSource</code> set in the constructor.
425      * 
426      * @param sql The SQL statement to execute.
427      * @param param The replacement parameter.
428      * @throws SQLException
429      * @return The number of rows updated.
430      */
431     public int update(String   sql, Object   param) throws SQLException   {
432         return this.update(sql, new Object  [] { param });
433     }
434 
435     /**
436      * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The 
437      * <code>Connection</code> is retrieved from the <code>DataSource</code> 
438      * set in the constructor.
439      * 
440      * @param sql The SQL statement to execute.
441      * @param params Initializes the PreparedStatement's IN (i.e. '?') 
442      * parameters.
443      * @throws SQLException
444      * @return The number of rows updated.
445      */
446     public int update(String   sql, Object  [] params) throws SQLException   {
447 
448         Connection   conn = this.ds.getConnection();
449 
450         try {
451             return this.update(conn, sql, params);
452 
453         } finally {
454             DbUtils.close(conn);
455         }
456     }
457 
458     /**
459      * Wrap the <code>ResultSet</code> in a decorator before processing it.
460      * This implementation returns the <code>ResultSet</code> it is given
461      * without any decoration.
462      *
463      * <p>
464      * Often, the implementation of this method can be done in an anonymous 
465      * inner class like this:
466      * </p>
467      * <pre> 
468      * QueryRunner run = new QueryRunner() {
469      *     protected ResultSet wrap(ResultSet rs) {
470      *         return StringTrimmedResultSet.wrap(rs);
471      *     }
472      * };
473      * </pre>
474      * 
475      * @param rs The <code>ResultSet</code> to decorate; never 
476      * <code>null</code>.
477      * @return The <code>ResultSet</code> wrapped in some decorator. 
478      */
479     protected ResultSet   wrap(ResultSet   rs) {
480         return rs;
481     }
482 
483 }
484
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags