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