001/*
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements.  See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License.  You may obtain a copy of the License at
008 *
009 *      http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017package org.apache.commons.dbutils;
018
019import java.sql.CallableStatement;
020import java.sql.Connection;
021import java.sql.PreparedStatement;
022import java.sql.ResultSet;
023import java.sql.SQLException;
024import java.sql.Statement;
025import java.util.LinkedList;
026import java.util.List;
027import javax.sql.DataSource;
028
029/**
030 * Executes SQL queries with pluggable strategies for handling
031 * <code>ResultSet</code>s.  This class is thread safe.
032 *
033 * @see ResultSetHandler
034 */
035public class QueryRunner extends AbstractQueryRunner {
036
037    /**
038     * Constructor for QueryRunner.
039     */
040    public QueryRunner() {
041        super();
042    }
043
044    /**
045     * Constructor for QueryRunner that controls the use of <code>ParameterMetaData</code>.
046     *
047     * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
048     * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
049     * and if it breaks, we'll remember not to use it again.
050     */
051    public QueryRunner(boolean pmdKnownBroken) {
052        super(pmdKnownBroken);
053    }
054
055    /**
056     * Constructor for QueryRunner that takes a <code>DataSource</code> to use.
057     *
058     * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
059     * <code>DataSource</code>.
060     *
061     * @param ds The <code>DataSource</code> to retrieve connections from.
062     */
063    public QueryRunner(DataSource ds) {
064        super(ds);
065    }
066
067    /**
068     * Constructor for QueryRunner that takes a <code>StatementConfiguration</code> to configure statements when
069     * preparing them.
070     *
071     * @param stmtConfig The configuration to apply to statements when they are prepared.
072     */
073    public QueryRunner(StatementConfiguration stmtConfig) {
074        super(stmtConfig);
075    }
076
077    /**
078     * Constructor for QueryRunner that takes a <code>DataSource</code> and controls the use of <code>ParameterMetaData</code>.
079     * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
080     * <code>DataSource</code>.
081     *
082     * @param ds The <code>DataSource</code> to retrieve connections from.
083     * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
084     * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
085     * and if it breaks, we'll remember not to use it again.
086     */
087    public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
088        super(ds, pmdKnownBroken);
089    }
090
091    /**
092     * Constructor for QueryRunner that takes a <code>DataSource</code> to use and a <code>StatementConfiguration</code>.
093     *
094     * Methods that do not take a <code>Connection</code> parameter will retrieve connections from this
095     * <code>DataSource</code>.
096     *
097     * @param ds The <code>DataSource</code> to retrieve connections from.
098     * @param stmtConfig The configuration to apply to statements when they are prepared.
099     */
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}