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.CallableStatement;
20  import java.sql.Connection;
21  import java.sql.PreparedStatement;
22  import java.sql.ResultSet;
23  import java.sql.SQLException;
24  import java.sql.Statement;
25  import java.util.LinkedList;
26  import java.util.List;
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 extends AbstractQueryRunner {
36  
37      /**
38       * Constructor for QueryRunner.
39       */
40      public QueryRunner() {
41          super();
42      }
43  
44      /**
45       * Constructor for QueryRunner that controls the use of <code>ParameterMetaData</code>.
46       *
47       * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
48       * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
49       * and if it breaks, we'll remember not to use it again.
50       */
51      public QueryRunner(boolean pmdKnownBroken) {
52          super(pmdKnownBroken);
53      }
54  
55      /**
56       * Constructor for QueryRunner that takes a <code>DataSource</code> to use.
57       *
58       * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
59       * <code>DataSource</code>.
60       *
61       * @param ds The <code>DataSource</code> to retrieve connections from.
62       */
63      public QueryRunner(DataSource ds) {
64          super(ds);
65      }
66  
67      /**
68       * Constructor for QueryRunner that takes a <code>StatementConfiguration</code> to configure statements when
69       * preparing them.
70       *
71       * @param stmtConfig The configuration to apply to statements when they are prepared.
72       */
73      public QueryRunner(StatementConfiguration stmtConfig) {
74          super(stmtConfig);
75      }
76  
77      /**
78       * Constructor for QueryRunner that takes a <code>DataSource</code> and controls the use of <code>ParameterMetaData</code>.
79       * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
80       * <code>DataSource</code>.
81       *
82       * @param ds The <code>DataSource</code> to retrieve connections from.
83       * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
84       * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
85       * and if it breaks, we'll remember not to use it again.
86       */
87      public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
88          super(ds, pmdKnownBroken);
89      }
90  
91      /**
92       * Constructor for QueryRunner that takes a <code>DataSource</code> to use and a <code>StatementConfiguration</code>.
93       *
94       * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
95       * <code>DataSource</code>.
96       *
97       * @param ds The <code>DataSource</code> to retrieve connections from.
98       * @param stmtConfig The configuration to apply to statements when they are prepared.
99       */
100     public QueryRunner(DataSource ds, StatementConfiguration stmtConfig) {
101         super(ds, stmtConfig);
102     }
103 
104     /**
105      * Constructor for QueryRunner that takes a <code>DataSource</code>, a <code>StatementConfiguration</code>, and
106      * controls the use of <code>ParameterMetaData</code>.  Methods that do not take a <code>Connection</code> parameter
107      * will retrieve connections from this <code>DataSource</code>.
108      *
109      * @param ds The <code>DataSource</code> to retrieve connections from.
110      * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
111      * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
112      * and if it breaks, we'll remember not to use it again.
113      * @param stmtConfig The configuration to apply to statements when they are prepared.
114      */
115     public QueryRunner(DataSource ds, boolean pmdKnownBroken, StatementConfiguration stmtConfig) {
116         super(ds, pmdKnownBroken, stmtConfig);
117     }
118 
119     /**
120      * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
121      *
122      * @param conn The Connection to use to run the query.  The caller is
123      * responsible for closing this Connection.
124      * @param sql The SQL to execute.
125      * @param params An array of query replacement parameters.  Each row in
126      * this array is one set of batch replacement values.
127      * @return The number of rows updated per statement.
128      * @throws SQLException if a database access error occurs
129      * @since DbUtils 1.1
130      */
131     public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
132         return this.batch(conn, false, sql, params);
133     }
134 
135     /**
136      * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.  The
137      * <code>Connection</code> is retrieved from the <code>DataSource</code>
138      * set in the constructor.  This <code>Connection</code> must be in
139      * auto-commit mode or the update will not be saved.
140      *
141      * @param sql The SQL to execute.
142      * @param params An array of query replacement parameters.  Each row in
143      * this array is one set of batch replacement values.
144      * @return The number of rows updated per statement.
145      * @throws SQLException if a database access error occurs
146      * @since DbUtils 1.1
147      */
148     public int[] batch(String sql, Object[][] params) throws SQLException {
149         Connection conn = this.prepareConnection();
150 
151         return this.batch(conn, true, sql, params);
152     }
153 
154     /**
155      * Calls update after checking the parameters to ensure nothing is null.
156      * @param conn The connection to use for the batch call.
157      * @param closeConn True if the connection should be closed, false otherwise.
158      * @param sql The SQL statement to execute.
159      * @param params An array of query replacement parameters.  Each row in
160      * this array is one set of batch replacement values.
161      * @return The number of rows updated in the batch.
162      * @throws SQLException If there are database or parameter errors.
163      */
164     private int[] batch(Connection conn, boolean closeConn, String sql, Object[][] params) throws SQLException {
165         if (conn == null) {
166             throw new SQLException("Null connection");
167         }
168 
169         if (sql == null) {
170             if (closeConn) {
171                 close(conn);
172             }
173             throw new SQLException("Null SQL statement");
174         }
175 
176         if (params == null) {
177             if (closeConn) {
178                 close(conn);
179             }
180             throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
181         }
182 
183         PreparedStatement stmt = null;
184         int[] rows = null;
185         try {
186             stmt = this.prepareStatement(conn, sql);
187 
188             for (int i = 0; i < params.length; i++) {
189                 this.fillStatement(stmt, params[i]);
190                 stmt.addBatch();
191             }
192             rows = stmt.executeBatch();
193 
194         } catch (SQLException e) {
195             this.rethrow(e, sql, (Object[])params);
196         } finally {
197             close(stmt);
198             if (closeConn) {
199                 close(conn);
200             }
201         }
202 
203         return rows;
204     }
205 
206     /**
207      * Execute an SQL SELECT query with a single replacement parameter. The
208      * caller is responsible for closing the connection.
209      * @param <T> The type of object that the handler returns
210      * @param conn The connection to execute the query in.
211      * @param sql The query to execute.
212      * @param param The replacement parameter.
213      * @param rsh The handler that converts the results into an object.
214      * @return The object returned by the handler.
215      * @throws SQLException if a database access error occurs
216      * @deprecated Use {@link #query(Connection, String, ResultSetHandler, Object...)}
217      */
218     @Deprecated
219     public <T> T query(Connection conn, String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
220         return this.<T>query(conn, false, sql, rsh, new Object[]{param});
221     }
222 
223     /**
224      * Execute an SQL SELECT query with replacement parameters.  The
225      * caller is responsible for closing the connection.
226      * @param <T> The type of object that the handler returns
227      * @param conn The connection to execute the query in.
228      * @param sql The query to execute.
229      * @param params The replacement parameters.
230      * @param rsh The handler that converts the results into an object.
231      * @return The object returned by the handler.
232      * @throws SQLException if a database access error occurs
233      * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object...)} instead
234      */
235     @Deprecated
236     public <T> T query(Connection conn, String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
237         return this.<T>query(conn, false, sql, rsh, params);
238     }
239 
240     /**
241      * Execute an SQL SELECT query with replacement parameters.  The
242      * caller is responsible for closing the connection.
243      * @param <T> The type of object that the handler returns
244      * @param conn The connection to execute the query in.
245      * @param sql The query to execute.
246      * @param rsh The handler that converts the results into an object.
247      * @param params The replacement parameters.
248      * @return The object returned by the handler.
249      * @throws SQLException if a database access error occurs
250      */
251     public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
252         return this.<T>query(conn, false, sql, rsh, params);
253     }
254 
255     /**
256      * Execute an SQL SELECT query without any replacement parameters.  The
257      * caller is responsible for closing the connection.
258      * @param <T> The type of object that the handler returns
259      * @param conn The connection to execute the query in.
260      * @param sql The query to execute.
261      * @param rsh The handler that converts the results into an object.
262      * @return The object returned by the handler.
263      * @throws SQLException if a database access error occurs
264      */
265     public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
266         return this.<T>query(conn, false, sql, rsh, (Object[]) null);
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      * @param <T> The type of object that the handler returns
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      * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
282      */
283     @Deprecated
284     public <T> T query(String sql, Object param, ResultSetHandler<T> rsh) throws SQLException {
285         Connection conn = this.prepareConnection();
286 
287         return this.<T>query(conn, true, sql, rsh, new Object[]{param});
288     }
289 
290     /**
291      * Executes the given SELECT SQL query and returns a result object.
292      * The <code>Connection</code> is retrieved from the
293      * <code>DataSource</code> set in the constructor.
294      * @param <T> The type of object that the handler returns
295      * @param sql The SQL statement to execute.
296      * @param params Initialize the PreparedStatement's IN parameters with
297      * this array.
298      *
299      * @param rsh The handler used to create the result object from
300      * the <code>ResultSet</code>.
301      *
302      * @return An object generated by the handler.
303      * @throws SQLException if a database access error occurs
304      * @deprecated Use {@link #query(String, ResultSetHandler, Object...)}
305      */
306     @Deprecated
307     public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh) throws SQLException {
308         Connection conn = this.prepareConnection();
309 
310         return this.<T>query(conn, true, sql, rsh, params);
311     }
312 
313     /**
314      * Executes the given SELECT SQL query and returns a result object.
315      * The <code>Connection</code> is retrieved from the
316      * <code>DataSource</code> set in the constructor.
317      * @param <T> The type of object that the handler returns
318      * @param sql The SQL statement to execute.
319      * @param rsh The handler used to create the result object from
320      * the <code>ResultSet</code>.
321      * @param params Initialize the PreparedStatement's IN parameters with
322      * this array.
323      * @return An object generated by the handler.
324      * @throws SQLException if a database access error occurs
325      */
326     public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
327         Connection conn = this.prepareConnection();
328 
329         return this.<T>query(conn, true, sql, rsh, params);
330     }
331 
332     /**
333      * Executes the given SELECT SQL without any replacement parameters.
334      * The <code>Connection</code> is retrieved from the
335      * <code>DataSource</code> set in the constructor.
336      * @param <T> The type of object that the handler returns
337      * @param sql The SQL statement to execute.
338      * @param rsh The handler used to create the result object from
339      * the <code>ResultSet</code>.
340      *
341      * @return An object generated by the handler.
342      * @throws SQLException if a database access error occurs
343      */
344     public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
345         Connection conn = this.prepareConnection();
346 
347         return this.<T>query(conn, true, sql, rsh, (Object[]) null);
348     }
349 
350     /**
351      * Calls query after checking the parameters to ensure nothing is null.
352      * @param conn The connection to use for the query call.
353      * @param closeConn True if the connection should be closed, false otherwise.
354      * @param sql The SQL statement to execute.
355      * @param params An array of query replacement parameters.  Each row in
356      * this array is one set of batch replacement values.
357      * @return The results of the query.
358      * @throws SQLException If there are database or parameter errors.
359      */
360     private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
361             throws SQLException {
362         if (conn == null) {
363             throw new SQLException("Null connection");
364         }
365 
366         if (sql == null) {
367             if (closeConn) {
368                 close(conn);
369             }
370             throw new SQLException("Null SQL statement");
371         }
372 
373         if (rsh == null) {
374             if (closeConn) {
375                 close(conn);
376             }
377             throw new SQLException("Null ResultSetHandler");
378         }
379 
380         PreparedStatement stmt = null;
381         ResultSet rs = null;
382         T result = null;
383 
384         try {
385             stmt = this.prepareStatement(conn, sql);
386             this.fillStatement(stmt, params);
387             rs = this.wrap(stmt.executeQuery());
388             result = rsh.handle(rs);
389 
390         } catch (SQLException e) {
391             this.rethrow(e, sql, params);
392 
393         } finally {
394             try {
395                 close(rs);
396             } finally {
397                 close(stmt);
398                 if (closeConn) {
399                     close(conn);
400                 }
401             }
402         }
403 
404         return result;
405     }
406 
407     /**
408      * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
409      * parameters.
410      *
411      * @param conn The connection to use to run the query.
412      * @param sql The SQL to execute.
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) throws SQLException {
417         return this.update(conn, false, sql, (Object[]) null);
418     }
419 
420     /**
421      * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
422      * parameter.
423      *
424      * @param conn The connection to use to run the query.
425      * @param sql The SQL to execute.
426      * @param param The replacement parameter.
427      * @return The number of rows updated.
428      * @throws SQLException if a database access error occurs
429      */
430     public int update(Connection conn, String sql, Object param) throws SQLException {
431         return this.update(conn, false, sql, new Object[]{param});
432     }
433 
434     /**
435      * Execute an SQL INSERT, UPDATE, or DELETE query.
436      *
437      * @param conn The connection to use to run the query.
438      * @param sql The SQL to execute.
439      * @param params The query replacement parameters.
440      * @return The number of rows updated.
441      * @throws SQLException if a database access error occurs
442      */
443     public int update(Connection conn, String sql, Object... params) throws SQLException {
444         return update(conn, false, sql, params);
445     }
446 
447     /**
448      * Executes the given INSERT, UPDATE, or DELETE SQL statement without
449      * any replacement parameters. The <code>Connection</code> is retrieved
450      * from the <code>DataSource</code> set in the constructor.  This
451      * <code>Connection</code> must be in auto-commit mode or the update will
452      * not be saved.
453      *
454      * @param sql The SQL statement to execute.
455      * @throws SQLException if a database access error occurs
456      * @return The number of rows updated.
457      */
458     public int update(String sql) throws SQLException {
459         Connection conn = this.prepareConnection();
460 
461         return this.update(conn, true, sql, (Object[]) null);
462     }
463 
464     /**
465      * Executes the given INSERT, UPDATE, or DELETE SQL statement with
466      * a single replacement parameter.  The <code>Connection</code> is
467      * retrieved from the <code>DataSource</code> set in the constructor.
468      * This <code>Connection</code> must be in auto-commit mode or the
469      * update will not be saved.
470      *
471      * @param sql The SQL statement to execute.
472      * @param param The replacement parameter.
473      * @throws SQLException if a database access error occurs
474      * @return The number of rows updated.
475      */
476     public int update(String sql, Object param) throws SQLException {
477         Connection conn = this.prepareConnection();
478 
479         return this.update(conn, true, sql, new Object[]{param});
480     }
481 
482     /**
483      * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The
484      * <code>Connection</code> is retrieved from the <code>DataSource</code>
485      * set in the constructor.  This <code>Connection</code> must be in
486      * auto-commit mode or the update will not be saved.
487      *
488      * @param sql The SQL statement to execute.
489      * @param params Initializes the PreparedStatement's IN (i.e. '?')
490      * parameters.
491      * @throws SQLException if a database access error occurs
492      * @return The number of rows updated.
493      */
494     public int update(String sql, Object... params) throws SQLException {
495         Connection conn = this.prepareConnection();
496 
497         return this.update(conn, true, sql, params);
498     }
499 
500     /**
501      * Calls update after checking the parameters to ensure nothing is null.
502      * @param conn The connection to use for the update call.
503      * @param closeConn True if the connection should be closed, false otherwise.
504      * @param sql The SQL statement to execute.
505      * @param params An array of update replacement parameters.  Each row in
506      * this array is one set of update replacement values.
507      * @return The number of rows updated.
508      * @throws SQLException If there are database or parameter errors.
509      */
510     private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
511         if (conn == null) {
512             throw new SQLException("Null connection");
513         }
514 
515         if (sql == null) {
516             if (closeConn) {
517                 close(conn);
518             }
519             throw new SQLException("Null SQL statement");
520         }
521 
522         PreparedStatement stmt = null;
523         int rows = 0;
524 
525         try {
526             stmt = this.prepareStatement(conn, sql);
527             this.fillStatement(stmt, params);
528             rows = stmt.executeUpdate();
529 
530         } catch (SQLException e) {
531             this.rethrow(e, sql, params);
532 
533         } finally {
534             close(stmt);
535             if (closeConn) {
536                 close(conn);
537             }
538         }
539 
540         return rows;
541     }
542 
543     /**
544      * Executes the given INSERT SQL without any replacement parameters.
545      * The <code>Connection</code> is retrieved from the
546      * <code>DataSource</code> set in the constructor.
547      * @param <T> The type of object that the handler returns
548      * @param sql The SQL statement to execute.
549      * @param rsh The handler used to create the result object from
550      * the <code>ResultSet</code> of auto-generated keys.
551      * @return An object generated by the handler.
552      * @throws SQLException if a database access error occurs
553      * @since 1.6
554      */
555     public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
556         return insert(this.prepareConnection(), true, sql, rsh, (Object[]) null);
557     }
558 
559     /**
560      * Executes the given INSERT SQL statement. The
561      * <code>Connection</code> is retrieved from the <code>DataSource</code>
562      * set in the constructor.  This <code>Connection</code> must be in
563      * auto-commit mode or the insert will not be saved.
564      * @param <T> The type of object that the handler returns
565      * @param sql The SQL statement to execute.
566      * @param rsh The handler used to create the result object from
567      * the <code>ResultSet</code> of auto-generated keys.
568      * @param params Initializes the PreparedStatement's IN (i.e. '?')
569      * @return An object generated by the handler.
570      * @throws SQLException if a database access error occurs
571      * @since 1.6
572      */
573     public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
574         return insert(this.prepareConnection(), true, sql, rsh, params);
575     }
576 
577     /**
578      * Execute an SQL INSERT query without replacement parameters.
579      * @param <T> The type of object that the handler returns
580      * @param conn The connection to use to run the query.
581      * @param sql The SQL to execute.
582      * @param rsh The handler used to create the result object from
583      * the <code>ResultSet</code> of auto-generated keys.
584      * @return An object generated by the handler.
585      * @throws SQLException if a database access error occurs
586      * @since 1.6
587      */
588     public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
589         return insert(conn, false, sql, rsh, (Object[]) null);
590     }
591 
592     /**
593      * Execute an SQL INSERT query.
594      * @param <T> The type of object that the handler returns
595      * @param conn The connection to use to run the query.
596      * @param sql The SQL to execute.
597      * @param rsh The handler used to create the result object from
598      * the <code>ResultSet</code> of auto-generated keys.
599      * @param params The query replacement parameters.
600      * @return An object generated by the handler.
601      * @throws SQLException if a database access error occurs
602      * @since 1.6
603      */
604     public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
605         return insert(conn, false, sql, rsh, params);
606     }
607 
608     /**
609      * Executes the given INSERT SQL statement.
610      * @param conn The connection to use for the query call.
611      * @param closeConn True if the connection should be closed, false otherwise.
612      * @param sql The SQL statement to execute.
613      * @param rsh The handler used to create the result object from
614      * the <code>ResultSet</code> of auto-generated keys.
615      * @param params The query replacement parameters.
616      * @return An object generated by the handler.
617      * @throws SQLException If there are database or parameter errors.
618      * @since 1.6
619      */
620     private <T> T insert(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
621             throws SQLException {
622         if (conn == null) {
623             throw new SQLException("Null connection");
624         }
625 
626         if (sql == null) {
627             if (closeConn) {
628                 close(conn);
629             }
630             throw new SQLException("Null SQL statement");
631         }
632 
633         if (rsh == null) {
634             if (closeConn) {
635                 close(conn);
636             }
637             throw new SQLException("Null ResultSetHandler");
638         }
639 
640         PreparedStatement stmt = null;
641         T generatedKeys = null;
642 
643         try {
644             stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
645             this.fillStatement(stmt, params);
646             stmt.executeUpdate();
647             ResultSet resultSet = stmt.getGeneratedKeys();
648             generatedKeys = rsh.handle(resultSet);
649         } catch (SQLException e) {
650             this.rethrow(e, sql, params);
651         } finally {
652             close(stmt);
653             if (closeConn) {
654                 close(conn);
655             }
656         }
657 
658         return generatedKeys;
659     }
660 
661     /**
662      * Executes the given batch of INSERT SQL statements. The
663      * <code>Connection</code> is retrieved from the <code>DataSource</code>
664      * set in the constructor.  This <code>Connection</code> must be in
665      * auto-commit mode or the insert will not be saved.
666      * @param <T> The type of object that the handler returns
667      * @param sql The SQL statement to execute.
668      * @param rsh The handler used to create the result object from
669      * the <code>ResultSet</code> of auto-generated keys.
670      * @param params Initializes the PreparedStatement's IN (i.e. '?')
671      * @return The result generated by the handler.
672      * @throws SQLException if a database access error occurs
673      * @since 1.6
674      */
675     public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
676         return insertBatch(this.prepareConnection(), true, sql, rsh, params);
677     }
678 
679     /**
680      * Executes the given batch of INSERT SQL statements.
681      * @param <T> The type of object that the handler returns
682      * @param conn The connection to use to run the query.
683      * @param sql The SQL to execute.
684      * @param rsh The handler used to create the result object from
685      * the <code>ResultSet</code> of auto-generated keys.
686      * @param params The query replacement parameters.
687      * @return The result generated by the handler.
688      * @throws SQLException if a database access error occurs
689      * @since 1.6
690      */
691     public <T> T insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
692         return insertBatch(conn, false, sql, rsh, params);
693     }
694 
695     /**
696      * Executes the given batch of INSERT SQL statements.
697      * @param conn The connection to use for the query call.
698      * @param closeConn True if the connection should be closed, false otherwise.
699      * @param sql The SQL statement to execute.
700      * @param rsh The handler used to create the result object from
701      * the <code>ResultSet</code> of auto-generated keys.
702      * @param params The query replacement parameters.
703      * @return The result generated by the handler.
704      * @throws SQLException If there are database or parameter errors.
705      * @since 1.6
706      */
707     private <T> T insertBatch(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object[][] params)
708             throws SQLException {
709         if (conn == null) {
710             throw new SQLException("Null connection");
711         }
712 
713         if (sql == null) {
714             if (closeConn) {
715                 close(conn);
716             }
717             throw new SQLException("Null SQL statement");
718         }
719 
720         if (params == null) {
721             if (closeConn) {
722                 close(conn);
723             }
724             throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
725         }
726 
727         PreparedStatement stmt = null;
728         T generatedKeys = null;
729         try {
730             stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);
731 
732             for (int i = 0; i < params.length; i++) {
733                 this.fillStatement(stmt, params[i]);
734                 stmt.addBatch();
735             }
736             stmt.executeBatch();
737             ResultSet rs = stmt.getGeneratedKeys();
738             generatedKeys = rsh.handle(rs);
739 
740         } catch (SQLException e) {
741             this.rethrow(e, sql, (Object[])params);
742         } finally {
743             close(stmt);
744             if (closeConn) {
745                 close(conn);
746             }
747         }
748 
749         return generatedKeys;
750     }
751 
752     /**
753      * Execute an SQL statement, including a stored procedure call, which does
754      * not return any result sets.
755      * Any parameters which are instances of {@link OutParameter} will be
756      * registered as OUT parameters.
757      * <p>
758      * Use this method when invoking a stored procedure with OUT parameters
759      * that does not return any result sets.  If you are not invoking a stored
760      * procedure, or the stored procedure has no OUT parameters, consider using
761      * {@link #update(java.sql.Connection, java.lang.String, java.lang.Object...) }.
762      * If the stored procedure returns result sets, use
763      * {@link #execute(java.sql.Connection, java.lang.String, org.apache.commons.dbutils.ResultSetHandler, java.lang.Object...) }.
764      *
765      * @param conn The connection to use to run the query.
766      * @param sql The SQL to execute.
767      * @param params The query replacement parameters.
768      * @return The number of rows updated.
769      * @throws SQLException if a database access error occurs
770      */
771     public int execute(Connection conn, String sql, Object... params) throws SQLException {
772         return this.execute(conn, false, sql, params);
773     }
774 
775     /**
776      * Execute an SQL statement, including a stored procedure call, which does
777      * not return any result sets.
778      * Any parameters which are instances of {@link OutParameter} will be
779      * registered as OUT parameters.
780      * <p>
781      * Use this method when invoking a stored procedure with OUT parameters
782      * that does not return any result sets.  If you are not invoking a stored
783      * procedure, or the stored procedure has no OUT parameters, consider using
784      * {@link #update(java.lang.String, java.lang.Object...) }.
785      * If the stored procedure returns result sets, use
786      * {@link #execute(java.lang.String, org.apache.commons.dbutils.ResultSetHandler, java.lang.Object...) }.
787      * <p>
788      * The <code>Connection</code> is retrieved from the <code>DataSource</code>
789      * set in the constructor.  This <code>Connection</code> must be in
790      * auto-commit mode or the update will not be saved.
791      *
792      * @param sql The SQL statement to execute.
793      * @param params Initializes the CallableStatement's parameters (i.e. '?').
794      * @throws SQLException if a database access error occurs
795      * @return The number of rows updated.
796      */
797     public int execute(String sql, Object... params) throws SQLException {
798         Connection conn = this.prepareConnection();
799 
800         return this.execute(conn, true, sql, params);
801     }
802 
803     /**
804      * Execute an SQL statement, including a stored procedure call, which
805      * returns one or more result sets.
806      * Any parameters which are instances of {@link OutParameter} will be
807      * registered as OUT parameters.
808      * <p>
809      * Use this method when: a) running SQL statements that return multiple
810      * result sets; b) invoking a stored procedure that return result
811      * sets and OUT parameters.  Otherwise you may wish to use
812      * {@link #query(java.sql.Connection, java.lang.String, org.apache.commons.dbutils.ResultSetHandler, java.lang.Object...) }
813      * (if there are no OUT parameters) or
814      * {@link #execute(java.sql.Connection, java.lang.String, java.lang.Object...) }
815      * (if there are no result sets).
816      *
817      * @param <T> The type of object that the handler returns
818      * @param conn The connection to use to run the query.
819      * @param sql The SQL to execute.
820      * @param rsh The result set handler
821      * @param params The query replacement parameters.
822      * @return A list of objects generated by the handler
823      * @throws SQLException if a database access error occurs
824      */
825     public <T> List<T> execute(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
826         return this.execute(conn, false, sql, rsh, params);
827     }
828 
829     /**
830      * Execute an SQL statement, including a stored procedure call, which
831      * returns one or more result sets.
832      * Any parameters which are instances of {@link OutParameter} will be
833      * registered as OUT parameters.
834      * <p>
835      * Use this method when: a) running SQL statements that return multiple
836      * result sets; b) invoking a stored procedure that return result
837      * sets and OUT parameters.  Otherwise you may wish to use
838      * {@link #query(java.lang.String, org.apache.commons.dbutils.ResultSetHandler, java.lang.Object...) }
839      * (if there are no OUT parameters) or
840      * {@link #execute(java.lang.String, java.lang.Object...) }
841      * (if there are no result sets).
842      *
843      * @param <T> The type of object that the handler returns
844      * @param sql The SQL to execute.
845      * @param rsh The result set handler
846      * @param params The query replacement parameters.
847      * @return A list of objects generated by the handler
848      * @throws SQLException if a database access error occurs
849      */
850     public <T> List<T> execute(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
851         Connection conn = this.prepareConnection();
852 
853         return this.execute(conn, true, sql, rsh, params);
854     }
855 
856     /**
857      * Invokes the stored procedure via update after checking the parameters to
858      * ensure nothing is null.
859      * @param conn The connection to use for the update call.
860      * @param closeConn True if the connection should be closed, false otherwise.
861      * @param sql The SQL statement to execute.
862      * @param params An array of update replacement parameters.  Each row in
863      * this array is one set of update replacement values.
864      * @return The number of rows updated.
865      * @throws SQLException If there are database or parameter errors.
866      */
867     private int execute(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
868         if (conn == null) {
869             throw new SQLException("Null connection");
870         }
871 
872         if (sql == null) {
873             if (closeConn) {
874                 close(conn);
875             }
876             throw new SQLException("Null SQL statement");
877         }
878 
879         CallableStatement stmt = null;
880         int rows = 0;
881 
882         try {
883             stmt = this.prepareCall(conn, sql);
884             this.fillStatement(stmt, params);
885             stmt.execute();
886             rows = stmt.getUpdateCount();
887             this.retrieveOutParameters(stmt, params);
888 
889         } catch (SQLException e) {
890             this.rethrow(e, sql, params);
891 
892         } finally {
893             close(stmt);
894             if (closeConn) {
895                 close(conn);
896             }
897         }
898 
899         return rows;
900     }
901 
902     /**
903      * Invokes the stored procedure via update after checking the parameters to
904      * ensure nothing is null.
905      * @param conn The connection to use for the update call.
906      * @param closeConn True if the connection should be closed, false otherwise.
907      * @param sql The SQL statement to execute.
908      * @param rsh The result set handler
909      * @param params An array of update replacement parameters.  Each row in
910      * this array is one set of update replacement values.
911      * @return List of all objects generated by the ResultSetHandler for all result sets handled.
912      * @throws SQLException If there are database or parameter errors.
913      */
914     private <T> List<T> execute(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
915         if (conn == null) {
916             throw new SQLException("Null connection");
917         }
918 
919         if (sql == null) {
920             if (closeConn) {
921                 close(conn);
922             }
923             throw new SQLException("Null SQL statement");
924         }
925 
926         if (rsh == null) {
927             if (closeConn) {
928                 close(conn);
929             }
930             throw new SQLException("Null ResultSetHandler");
931         }
932 
933         CallableStatement stmt = null;
934         List<T> results = new LinkedList<T>();
935 
936         try {
937             stmt = this.prepareCall(conn, sql);
938             this.fillStatement(stmt, params);
939             boolean moreResultSets = stmt.execute();
940             // Handle multiple result sets by passing them through the handler
941             // retaining the final result
942             ResultSet rs = null;
943             while (moreResultSets) {
944                 try {
945                     rs = this.wrap(stmt.getResultSet());
946                     results.add(rsh.handle(rs));
947                     moreResultSets = stmt.getMoreResults();
948 
949                 } finally {
950                     close(rs);
951                 }
952             }
953             this.retrieveOutParameters(stmt, params);
954 
955         } catch (SQLException e) {
956             this.rethrow(e, sql, params);
957 
958         } finally {
959             close(stmt);
960             if (closeConn) {
961                 close(conn);
962             }
963         }
964 
965         return results;
966     }
967 
968     /**
969      * Set the value on all the {@link OutParameter} instances in the
970      * <code>params</code> array using the OUT parameter values from the
971      * <code>stmt</code>.
972      * @param stmt the statement from which to retrieve OUT parameter values
973      * @param params the parameter array for the statement invocation
974      * @throws SQLException when the value could not be retrieved from the
975      * statement.
976      */
977     private void retrieveOutParameters(CallableStatement stmt, Object[] params) throws SQLException {
978         if (params != null) {
979             for (int i = 0; i < params.length; i++) {
980                 if (params[i] instanceof OutParameter) {
981                     ((OutParameter)params[i]).setValue(stmt, i + 1);
982                 }
983             }
984         }
985     }
986 }