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: */
017: package org.apache.commons.dbutils;
018:
019: import java.sql.Connection;
020: import java.sql.PreparedStatement;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Statement;
024: import java.sql.Types;
025: import java.util.Arrays;
026:
027: import 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: */
035: public class QueryRunner {
036:
037: /**
038: * The DataSource to retrieve connections from.
039: */
040: protected DataSource ds = null;
041:
042: /**
043: * Constructor for QueryRunner.
044: */
045: public QueryRunner() {
046: super ();
047: }
048:
049: /**
050: * Constructor for QueryRunner. Methods that do not take a
051: * <code>Connection</code> parameter will retrieve connections from this
052: * <code>DataSource</code>.
053: *
054: * @param ds The <code>DataSource</code> to retrieve connections from.
055: */
056: public QueryRunner(DataSource ds) {
057: super ();
058: setDataSource(ds);
059: }
060:
061: /**
062: * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
063: *
064: * @param conn The Connection to use to run the query. The caller is
065: * responsible for closing this Connection.
066: * @param sql The SQL to execute.
067: * @param params An array of query replacement parameters. Each row in
068: * this array is one set of batch replacement values.
069: * @return The number of rows updated per statement.
070: * @throws SQLException if a database access error occurs
071: * @since DbUtils 1.1
072: */
073: public int[] batch(Connection conn, String sql, Object[][] params)
074: throws SQLException {
075:
076: PreparedStatement stmt = null;
077: int[] rows = null;
078: try {
079: stmt = this .prepareStatement(conn, sql);
080:
081: for (int i = 0; i < params.length; i++) {
082: this .fillStatement(stmt, params[i]);
083: stmt.addBatch();
084: }
085: rows = stmt.executeBatch();
086:
087: } catch (SQLException e) {
088: this .rethrow(e, sql, params);
089: } finally {
090: close(stmt);
091: }
092:
093: return rows;
094: }
095:
096: /**
097: * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
098: * <code>Connection</code> is retrieved from the <code>DataSource</code>
099: * set in the constructor. This <code>Connection</code> must be in
100: * auto-commit mode or the update will not be saved.
101: *
102: * @param sql The SQL to execute.
103: * @param params An array of query replacement parameters. Each row in
104: * this array is one set of batch replacement values.
105: * @return The number of rows updated per statement.
106: * @throws SQLException if a database access error occurs
107: * @since DbUtils 1.1
108: */
109: public int[] batch(String sql, Object[][] params)
110: throws SQLException {
111: Connection conn = this .prepareConnection();
112:
113: try {
114: return this .batch(conn, sql, params);
115: } finally {
116: close(conn);
117: }
118: }
119:
120: /**
121: * Fill the <code>PreparedStatement</code> replacement parameters with
122: * the given objects.
123: * @param stmt PreparedStatement to fill
124: * @param params Query replacement parameters; <code>null</code> is a valid
125: * value to pass in.
126: * @throws SQLException if a database access error occurs
127: */
128: protected void fillStatement(PreparedStatement stmt, Object[] params)
129: throws SQLException {
130:
131: if (params == null) {
132: return;
133: }
134:
135: for (int i = 0; i < params.length; i++) {
136: if (params[i] != null) {
137: stmt.setObject(i + 1, params[i]);
138: } else {
139: // VARCHAR works with many drivers regardless
140: // of the actual column type. Oddly, NULL and
141: // OTHER don't work with Oracle's drivers.
142: stmt.setNull(i + 1, Types.VARCHAR);
143: }
144: }
145: }
146:
147: /**
148: * Returns the <code>DataSource</code> this runner is using.
149: * <code>QueryRunner</code> methods always call this method to get the
150: * <code>DataSource</code> so subclasses can provide specialized
151: * behavior.
152: *
153: * @return DataSource the runner is using
154: */
155: public DataSource getDataSource() {
156: return this .ds;
157: }
158:
159: /**
160: * Factory method that creates and initializes a
161: * <code>PreparedStatement</code> object for the given SQL.
162: * <code>QueryRunner</code> methods always call this method to prepare
163: * statements for them. Subclasses can override this method to provide
164: * special PreparedStatement configuration if needed. This implementation
165: * simply calls <code>conn.prepareStatement(sql)</code>.
166: *
167: * @param conn The <code>Connection</code> used to create the
168: * <code>PreparedStatement</code>
169: * @param sql The SQL statement to prepare.
170: * @return An initialized <code>PreparedStatement</code>.
171: * @throws SQLException if a database access error occurs
172: */
173: protected PreparedStatement prepareStatement(Connection conn,
174: String sql) throws SQLException {
175:
176: return conn.prepareStatement(sql);
177: }
178:
179: /**
180: * Factory method that creates and initializes a
181: * <code>Connection</code> object. <code>QueryRunner</code> methods
182: * always call this method to retrieve connections from its DataSource.
183: * Subclasses can override this method to provide
184: * special <code>Connection</code> configuration if needed. This
185: * implementation simply calls <code>ds.getConnection()</code>.
186: *
187: * @return An initialized <code>Connection</code>.
188: * @throws SQLException if a database access error occurs
189: * @since DbUtils 1.1
190: */
191: protected Connection prepareConnection() throws SQLException {
192: if (this .getDataSource() == null) {
193: throw new SQLException(
194: "QueryRunner requires a DataSource to be "
195: + "invoked in this way, or a Connection should be passed in");
196: }
197: return this .getDataSource().getConnection();
198: }
199:
200: /**
201: * Execute an SQL SELECT query with a single replacement parameter. The
202: * caller is responsible for closing the connection.
203: *
204: * @param conn The connection to execute the query in.
205: * @param sql The query to execute.
206: * @param param The replacement parameter.
207: * @param rsh The handler that converts the results into an object.
208: * @return The object returned by the handler.
209: * @throws SQLException if a database access error occurs
210: */
211: public Object query(Connection conn, String sql, Object param,
212: ResultSetHandler rsh) throws SQLException {
213:
214: return this .query(conn, sql, new Object[] { param }, rsh);
215: }
216:
217: /**
218: * Execute an SQL SELECT query with replacement parameters. The
219: * caller is responsible for closing the connection.
220: *
221: * @param conn The connection to execute the query in.
222: * @param sql The query to execute.
223: * @param params The replacement parameters.
224: * @param rsh The handler that converts the results into an object.
225: * @return The object returned by the handler.
226: * @throws SQLException if a database access error occurs
227: */
228: public Object query(Connection conn, String sql, Object[] params,
229: ResultSetHandler rsh) throws SQLException {
230:
231: PreparedStatement stmt = null;
232: ResultSet rs = null;
233: Object result = null;
234:
235: try {
236: stmt = this .prepareStatement(conn, sql);
237: this .fillStatement(stmt, params);
238: rs = this .wrap(stmt.executeQuery());
239: result = rsh.handle(rs);
240:
241: } catch (SQLException e) {
242: this .rethrow(e, sql, params);
243:
244: } finally {
245: try {
246: close(rs);
247: } finally {
248: close(stmt);
249: }
250: }
251:
252: return result;
253: }
254:
255: /**
256: * Execute an SQL SELECT query without any replacement parameters. The
257: * caller is responsible for closing the connection.
258: *
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 Object query(Connection conn, String sql,
266: ResultSetHandler rsh) throws SQLException {
267:
268: return this .query(conn, sql, (Object[]) null, rsh);
269: }
270:
271: /**
272: * Executes the given SELECT SQL with a single replacement parameter.
273: * The <code>Connection</code> is retrieved from the
274: * <code>DataSource</code> set in the constructor.
275: *
276: * @param sql The SQL statement to execute.
277: * @param param The replacement parameter.
278: * @param rsh The handler used to create the result object from
279: * the <code>ResultSet</code>.
280: *
281: * @return An object generated by the handler.
282: * @throws SQLException if a database access error occurs
283: */
284: public Object query(String sql, Object param, ResultSetHandler rsh)
285: throws SQLException {
286:
287: return this .query(sql, new Object[] { param }, rsh);
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: *
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: */
305: public Object query(String sql, Object[] params,
306: ResultSetHandler rsh) throws SQLException {
307:
308: Connection conn = this .prepareConnection();
309:
310: try {
311: return this .query(conn, sql, params, rsh);
312: } finally {
313: close(conn);
314: }
315: }
316:
317: /**
318: * Executes the given SELECT SQL without any replacement parameters.
319: * The <code>Connection</code> is retrieved from the
320: * <code>DataSource</code> set in the constructor.
321: *
322: * @param sql The SQL statement to execute.
323: * @param rsh The handler used to create the result object from
324: * the <code>ResultSet</code>.
325: *
326: * @return An object generated by the handler.
327: * @throws SQLException if a database access error occurs
328: */
329: public Object query(String sql, ResultSetHandler rsh)
330: throws SQLException {
331: return this .query(sql, (Object[]) null, rsh);
332: }
333:
334: /**
335: * Throws a new exception with a more informative error message.
336: *
337: * @param cause The original exception that will be chained to the new
338: * exception when it's rethrown.
339: *
340: * @param sql The query that was executing when the exception happened.
341: *
342: * @param params The query replacement parameters; <code>null</code> is a
343: * valid value to pass in.
344: *
345: * @throws SQLException if a database access error occurs
346: */
347: protected void rethrow(SQLException cause, String sql,
348: Object[] params) throws SQLException {
349:
350: StringBuffer msg = new StringBuffer(cause.getMessage());
351:
352: msg.append(" Query: ");
353: msg.append(sql);
354: msg.append(" Parameters: ");
355:
356: if (params == null) {
357: msg.append("[]");
358: } else {
359: msg.append(Arrays.asList(params));
360: }
361:
362: SQLException e = new SQLException(msg.toString(), cause
363: .getSQLState(), cause.getErrorCode());
364: e.setNextException(cause);
365:
366: throw e;
367: }
368:
369: /**
370: * Sets the <code>DataSource</code> this runner will use to get
371: * database connections from. This should be called after creating a
372: * runner with the default constructor if you intend to use the
373: * execute methods without passing in a <code>Connection</code>.
374: *
375: * @param dataSource The DataSource to use.
376: */
377: public void setDataSource(DataSource dataSource) {
378: this .ds = dataSource;
379: }
380:
381: /**
382: * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
383: * parameters.
384: *
385: * @param conn The connection to use to run the query.
386: * @param sql The SQL to execute.
387: * @return The number of rows updated.
388: * @throws SQLException if a database access error occurs
389: */
390: public int update(Connection conn, String sql) throws SQLException {
391: return this .update(conn, sql, (Object[]) null);
392: }
393:
394: /**
395: * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
396: * parameter.
397: *
398: * @param conn The connection to use to run the query.
399: * @param sql The SQL to execute.
400: * @param param The replacement parameter.
401: * @return The number of rows updated.
402: * @throws SQLException if a database access error occurs
403: */
404: public int update(Connection conn, String sql, Object param)
405: throws SQLException {
406:
407: return this .update(conn, sql, new Object[] { param });
408: }
409:
410: /**
411: * Execute an SQL INSERT, UPDATE, or DELETE query.
412: *
413: * @param conn The connection to use to run the query.
414: * @param sql The SQL to execute.
415: * @param params The query replacement parameters.
416: * @return The number of rows updated.
417: * @throws SQLException if a database access error occurs
418: */
419: public int update(Connection conn, String sql, Object[] params)
420: throws SQLException {
421:
422: PreparedStatement stmt = null;
423: int rows = 0;
424:
425: try {
426: stmt = this .prepareStatement(conn, sql);
427: this .fillStatement(stmt, params);
428: rows = stmt.executeUpdate();
429:
430: } catch (SQLException e) {
431: this .rethrow(e, sql, params);
432:
433: } finally {
434: close(stmt);
435: }
436:
437: return rows;
438: }
439:
440: /**
441: * Executes the given INSERT, UPDATE, or DELETE SQL statement without
442: * any replacement parameters. The <code>Connection</code> is retrieved
443: * from the <code>DataSource</code> set in the constructor. This
444: * <code>Connection</code> must be in auto-commit mode or the update will
445: * not be saved.
446: *
447: * @param sql The SQL statement to execute.
448: * @throws SQLException if a database access error occurs
449: * @return The number of rows updated.
450: */
451: public int update(String sql) throws SQLException {
452: return this .update(sql, (Object[]) null);
453: }
454:
455: /**
456: * Executes the given INSERT, UPDATE, or DELETE SQL statement with
457: * a single replacement parameter. The <code>Connection</code> is
458: * retrieved from the <code>DataSource</code> set in the constructor.
459: * This <code>Connection</code> must be in auto-commit mode or the
460: * update will not be saved.
461: *
462: * @param sql The SQL statement to execute.
463: * @param param The replacement parameter.
464: * @throws SQLException if a database access error occurs
465: * @return The number of rows updated.
466: */
467: public int update(String sql, Object param) throws SQLException {
468: return this .update(sql, new Object[] { param });
469: }
470:
471: /**
472: * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
473: * <code>Connection</code> is retrieved from the <code>DataSource</code>
474: * set in the constructor. This <code>Connection</code> must be in
475: * auto-commit mode or the update will not be saved.
476: *
477: * @param sql The SQL statement to execute.
478: * @param params Initializes the PreparedStatement's IN (i.e. '?')
479: * parameters.
480: * @throws SQLException if a database access error occurs
481: * @return The number of rows updated.
482: */
483: public int update(String sql, Object[] params) throws SQLException {
484: Connection conn = this .prepareConnection();
485:
486: try {
487: return this .update(conn, sql, params);
488: } finally {
489: close(conn);
490: }
491: }
492:
493: /**
494: * Wrap the <code>ResultSet</code> in a decorator before processing it.
495: * This implementation returns the <code>ResultSet</code> it is given
496: * without any decoration.
497: *
498: * <p>
499: * Often, the implementation of this method can be done in an anonymous
500: * inner class like this:
501: * </p>
502: * <pre>
503: * QueryRunner run = new QueryRunner() {
504: * protected ResultSet wrap(ResultSet rs) {
505: * return StringTrimmedResultSet.wrap(rs);
506: * }
507: * };
508: * </pre>
509: *
510: * @param rs The <code>ResultSet</code> to decorate; never
511: * <code>null</code>.
512: * @return The <code>ResultSet</code> wrapped in some decorator.
513: */
514: protected ResultSet wrap(ResultSet rs) {
515: return rs;
516: }
517:
518: /**
519: * Close a <code>Connection</code>. This implementation avoids closing if
520: * null and does <strong>not</strong> suppress any exceptions. Subclasses
521: * can override to provide special handling like logging.
522: * @param conn Connection to close
523: * @throws SQLException if a database access error occurs
524: * @since DbUtils 1.1
525: */
526: protected void close(Connection conn) throws SQLException {
527: DbUtils.close(conn);
528: }
529:
530: /**
531: * Close a <code>Statement</code>. This implementation avoids closing if
532: * null and does <strong>not</strong> suppress any exceptions. Subclasses
533: * can override to provide special handling like logging.
534: * @param stmt Statement to close
535: * @throws SQLException if a database access error occurs
536: * @since DbUtils 1.1
537: */
538: protected void close(Statement stmt) throws SQLException {
539: DbUtils.close(stmt);
540: }
541:
542: /**
543: * Close a <code>ResultSet</code>. This implementation avoids closing if
544: * null and does <strong>not</strong> suppress any exceptions. Subclasses
545: * can override to provide special handling like logging.
546: * @throws SQLException if a database access error occurs
547: * @param rs ResultSet to close
548: * @since DbUtils 1.1
549: */
550: protected void close(ResultSet rs) throws SQLException {
551: DbUtils.close(rs);
552: }
553:
554: }
|