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.beans.IntrospectionException;
020 import java.beans.Introspector;
021 import java.beans.PropertyDescriptor;
022 import java.lang.reflect.InvocationTargetException;
023 import java.lang.reflect.Method;
024 import java.sql.Connection;
025 import java.sql.ParameterMetaData;
026 import java.sql.PreparedStatement;
027 import java.sql.ResultSet;
028 import java.sql.SQLException;
029 import java.sql.Statement;
030 import java.sql.Types;
031 import java.util.Arrays;
032
033 import javax.sql.DataSource;
034
035 /**
036 * Executes SQL queries with pluggable strategies for handling
037 * <code>ResultSet</code>s. This class is thread safe.
038 *
039 * @see ResultSetHandler
040 */
041 public class QueryRunner {
042
043 /**
044 * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried it yet)?
045 */
046 private volatile boolean pmdKnownBroken = false;
047
048 /**
049 * The DataSource to retrieve connections from.
050 */
051 protected final DataSource ds;
052
053 /**
054 * Constructor for QueryRunner.
055 */
056 public QueryRunner() {
057 super();
058 ds = null;
059 }
060
061 /**
062 * Constructor for QueryRunner, allows workaround for Oracle drivers
063 * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) };
064 * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
065 * and if it breaks, we'll remember not to use it again.
066 */
067 public QueryRunner(boolean pmdKnownBroken) {
068 super();
069 this.pmdKnownBroken = pmdKnownBroken;
070 ds = null;
071 }
072
073 /**
074 * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a
075 * <code>Connection</code> parameter will retrieve connections from this
076 * <code>DataSource</code>.
077 *
078 * @param ds The <code>DataSource</code> to retrieve connections from.
079 */
080 public QueryRunner(DataSource ds) {
081 super();
082 this.ds = ds;
083 }
084
085 /**
086 * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a
087 * <code>Connection</code> parameter will retrieve connections from this
088 * <code>DataSource</code>.
089 *
090 * @param ds The <code>DataSource</code> to retrieve connections from.
091 * @param pmdKnownBroken Oracle drivers don't support {@link ParameterMetaData#getParameterType(int) };
092 * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,
093 * and if it breaks, we'll remember not to use it again.
094 */
095 public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
096 super();
097 this.pmdKnownBroken = pmdKnownBroken;
098 this.ds = ds;
099 }
100
101 /**
102 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
103 *
104 * @param conn The Connection to use to run the query. The caller is
105 * responsible for closing this Connection.
106 * @param sql The SQL to execute.
107 * @param params An array of query replacement parameters. Each row in
108 * this array is one set of batch replacement values.
109 * @return The number of rows updated per statement.
110 * @throws SQLException if a database access error occurs
111 * @since DbUtils 1.1
112 */
113 public int[] batch(Connection conn, String sql, Object[][] params)
114 throws SQLException {
115
116 PreparedStatement stmt = null;
117 int[] rows = null;
118 try {
119 stmt = this.prepareStatement(conn, sql);
120
121 for (int i = 0; i < params.length; i++) {
122 this.fillStatement(stmt, params[i]);
123 stmt.addBatch();
124 }
125 rows = stmt.executeBatch();
126
127 } catch (SQLException e) {
128 this.rethrow(e, sql, params);
129 } finally {
130 close(stmt);
131 }
132
133 return rows;
134 }
135
136 /**
137 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
138 * <code>Connection</code> is retrieved from the <code>DataSource</code>
139 * set in the constructor. This <code>Connection</code> must be in
140 * auto-commit mode or the update will not be saved.
141 *
142 * @param sql The SQL to execute.
143 * @param params An array of query replacement parameters. Each row in
144 * this array is one set of batch replacement values.
145 * @return The number of rows updated per statement.
146 * @throws SQLException if a database access error occurs
147 * @since DbUtils 1.1
148 */
149 public int[] batch(String sql, Object[][] params) throws SQLException {
150 Connection conn = this.prepareConnection();
151
152 try {
153 return this.batch(conn, sql, params);
154 } finally {
155 close(conn);
156 }
157 }
158
159 /**
160 * Fill the <code>PreparedStatement</code> replacement parameters with
161 * the given objects.
162 * @param stmt PreparedStatement to fill
163 * @param params Query replacement parameters; <code>null</code> is a valid
164 * value to pass in.
165 * @throws SQLException if a database access error occurs
166 */
167 public void fillStatement(PreparedStatement stmt, Object[] params)
168 throws SQLException {
169
170 if (params == null) {
171 return;
172 }
173
174 ParameterMetaData pmd = stmt.getParameterMetaData();
175 if (pmd.getParameterCount() < params.length) {
176 throw new SQLException("Too many parameters: expected "
177 + pmd.getParameterCount() + ", was given " + params.length);
178 }
179 for (int i = 0; i < params.length; i++) {
180 if (params[i] != null) {
181 stmt.setObject(i + 1, params[i]);
182 } else {
183 // VARCHAR works with many drivers regardless
184 // of the actual column type. Oddly, NULL and
185 // OTHER don't work with Oracle's drivers.
186 int sqlType = Types.VARCHAR;
187 if (!pmdKnownBroken) {
188 try {
189 sqlType = pmd.getParameterType(i + 1);
190 } catch (SQLException e) {
191 pmdKnownBroken = true;
192 }
193 }
194 stmt.setNull(i + 1, sqlType);
195 }
196 }
197 }
198
199 /**
200 * Fill the <code>PreparedStatement</code> replacement parameters with the
201 * given object's bean property values.
202 *
203 * @param stmt
204 * PreparedStatement to fill
205 * @param bean
206 * a JavaBean object
207 * @param properties
208 * an ordered array of properties; this gives the order to insert
209 * values in the statement
210 * @throws SQLException
211 * if a database access error occurs
212 */
213 public void fillStatementWithBean(PreparedStatement stmt, Object bean,
214 PropertyDescriptor[] properties) throws SQLException {
215 Object[] params = new Object[properties.length];
216 for (int i = 0; i < properties.length; i++) {
217 PropertyDescriptor property = properties[i];
218 Object value = null;
219 Method method = property.getReadMethod();
220 if (method == null) {
221 throw new RuntimeException("No read method for bean property "
222 + bean.getClass() + " " + property.getName());
223 }
224 try {
225 value = method.invoke(bean, new Object[0]);
226 } catch (InvocationTargetException e) {
227 throw new RuntimeException("Couldn't invoke method: " + method, e);
228 } catch (IllegalArgumentException e) {
229 throw new RuntimeException("Couldn't invoke method with 0 arguments: " + method, e);
230 } catch (IllegalAccessException e) {
231 throw new RuntimeException("Couldn't invoke method: " + method, e);
232 }
233 params[i] = value;
234 }
235 fillStatement(stmt, params);
236 }
237
238 /**
239 * Fill the <code>PreparedStatement</code> replacement parameters with the
240 * given object's bean property values.
241 *
242 * @param stmt
243 * PreparedStatement to fill
244 * @param bean
245 * a JavaBean object
246 * @param propertyNames
247 * an ordered array of property names (these should match the
248 * getters/setters); this gives the order to insert values in the
249 * statement
250 * @throws SQLException
251 * if a database access error occurs
252 */
253 public void fillStatementWithBean(PreparedStatement stmt, Object bean,
254 String[] propertyNames) throws SQLException {
255 PropertyDescriptor[] descriptors;
256 try {
257 descriptors = Introspector.getBeanInfo(bean.getClass())
258 .getPropertyDescriptors();
259 } catch (IntrospectionException e) {
260 throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
261 }
262 PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
263 for (int i = 0; i < propertyNames.length; i++) {
264 String propertyName = propertyNames[i];
265 if (propertyName == null) {
266 throw new NullPointerException("propertyName can't be null: " + i);
267 }
268 boolean found = false;
269 for (int j = 0; j < descriptors.length; j++) {
270 PropertyDescriptor descriptor = descriptors[j];
271 if (propertyName.equals(descriptor.getName())) {
272 sorted[i] = descriptor;
273 found = true;
274 break;
275 }
276 }
277 if (!found) {
278 throw new RuntimeException("Couldn't find bean property: "
279 + bean.getClass() + " " + propertyName);
280 }
281 }
282 fillStatementWithBean(stmt, bean, sorted);
283 }
284
285 /**
286 * Returns the <code>DataSource</code> this runner is using.
287 * <code>QueryRunner</code> methods always call this method to get the
288 * <code>DataSource</code> so subclasses can provide specialized
289 * behavior.
290 *
291 * @return DataSource the runner is using
292 */
293 public DataSource getDataSource() {
294 return this.ds;
295 }
296
297 /**
298 * Factory method that creates and initializes a
299 * <code>PreparedStatement</code> object for the given SQL.
300 * <code>QueryRunner</code> methods always call this method to prepare
301 * statements for them. Subclasses can override this method to provide
302 * special PreparedStatement configuration if needed. This implementation
303 * simply calls <code>conn.prepareStatement(sql)</code>.
304 *
305 * @param conn The <code>Connection</code> used to create the
306 * <code>PreparedStatement</code>
307 * @param sql The SQL statement to prepare.
308 * @return An initialized <code>PreparedStatement</code>.
309 * @throws SQLException if a database access error occurs
310 */
311 protected PreparedStatement prepareStatement(Connection conn, String sql)
312 throws SQLException {
313
314 return conn.prepareStatement(sql);
315 }
316
317 /**
318 * Factory method that creates and initializes a
319 * <code>Connection</code> object. <code>QueryRunner</code> methods
320 * always call this method to retrieve connections from its DataSource.
321 * Subclasses can override this method to provide
322 * special <code>Connection</code> configuration if needed. This
323 * implementation simply calls <code>ds.getConnection()</code>.
324 *
325 * @return An initialized <code>Connection</code>.
326 * @throws SQLException if a database access error occurs
327 * @since DbUtils 1.1
328 */
329 protected Connection prepareConnection() throws SQLException {
330 if(this.getDataSource() == null) {
331 throw new SQLException("QueryRunner requires a DataSource to be " +
332 "invoked in this way, or a Connection should be passed in");
333 }
334 return this.getDataSource().getConnection();
335 }
336
337 /**
338 * Execute an SQL SELECT query with a single replacement parameter. The
339 * caller is responsible for closing the connection.
340 *
341 * @param conn The connection to execute the query in.
342 * @param sql The query to execute.
343 * @param param The replacement parameter.
344 * @param rsh The handler that converts the results into an object.
345 * @return The object returned by the handler.
346 * @throws SQLException if a database access error occurs
347 * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead
348 */
349 public Object query(Connection conn, String sql, Object param,
350 ResultSetHandler rsh) throws SQLException {
351
352 return this.query(conn, sql, rsh, new Object[] { param });
353 }
354
355 /**
356 * Execute an SQL SELECT query with replacement parameters. The
357 * caller is responsible for closing the connection.
358 *
359 * @param conn The connection to execute the query in.
360 * @param sql The query to execute.
361 * @param params The replacement parameters.
362 * @param rsh The handler that converts the results into an object.
363 * @return The object returned by the handler.
364 * @throws SQLException if a database access error occurs
365 * @deprecated Use {@link #query(Connection,String,ResultSetHandler,Object[])} instead
366 */
367 public Object query(Connection conn, String sql, Object[] params,
368 ResultSetHandler rsh) throws SQLException {
369 return query(conn, sql, rsh, params);
370 }
371
372 /**
373 * Execute an SQL SELECT query with replacement parameters. The
374 * caller is responsible for closing the connection.
375 *
376 * @param conn The connection to execute the query in.
377 * @param sql The query to execute.
378 * @param rsh The handler that converts the results into an object.
379 * @param params The replacement parameters.
380 * @return The object returned by the handler.
381 * @throws SQLException if a database access error occurs
382 */
383 public Object query(Connection conn, String sql, ResultSetHandler rsh,
384 Object[] params) throws SQLException {
385
386 PreparedStatement stmt = null;
387 ResultSet rs = null;
388 Object result = null;
389
390 try {
391 stmt = this.prepareStatement(conn, sql);
392 this.fillStatement(stmt, params);
393 rs = this.wrap(stmt.executeQuery());
394 result = rsh.handle(rs);
395
396 } catch (SQLException e) {
397 this.rethrow(e, sql, params);
398
399 } finally {
400 try {
401 close(rs);
402 } finally {
403 close(stmt);
404 }
405 }
406
407 return result;
408 }
409
410 /**
411 * Execute an SQL SELECT query without any replacement parameters. The
412 * caller is responsible for closing the connection.
413 *
414 * @param conn The connection to execute the query in.
415 * @param sql The query to execute.
416 * @param rsh The handler that converts the results into an object.
417 * @return The object returned by the handler.
418 * @throws SQLException if a database access error occurs
419 */
420 public Object query(Connection conn, String sql, ResultSetHandler rsh)
421 throws SQLException {
422
423 return this.query(conn, sql, rsh, (Object[]) null);
424 }
425
426 /**
427 * Executes the given SELECT SQL with a single replacement parameter.
428 * The <code>Connection</code> is retrieved from the
429 * <code>DataSource</code> set in the constructor.
430 *
431 * @param sql The SQL statement to execute.
432 * @param param The replacement parameter.
433 * @param rsh The handler used to create the result object from
434 * the <code>ResultSet</code>.
435 *
436 * @return An object generated by the handler.
437 * @throws SQLException if a database access error occurs
438 * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead
439 */
440 public Object query(String sql, Object param, ResultSetHandler rsh)
441 throws SQLException {
442
443 return this.query(sql, rsh, new Object[] { param });
444 }
445
446 /**
447 * Executes the given SELECT SQL query and returns a result object.
448 * The <code>Connection</code> is retrieved from the
449 * <code>DataSource</code> set in the constructor.
450 *
451 * @param sql The SQL statement to execute.
452 * @param params Initialize the PreparedStatement's IN parameters with
453 * this array.
454 *
455 * @param rsh The handler used to create the result object from
456 * the <code>ResultSet</code>.
457 *
458 * @return An object generated by the handler.
459 * @throws SQLException if a database access error occurs
460 * @deprecated Use {@link #query(String,ResultSetHandler,Object[])} instead
461 */
462 public Object query(String sql, Object[] params, ResultSetHandler rsh)
463 throws SQLException {
464 return query(sql, rsh, params);
465 }
466
467 /**
468 * Executes the given SELECT SQL query and returns a result object.
469 * The <code>Connection</code> is retrieved from the
470 * <code>DataSource</code> set in the constructor.
471 *
472 * @param sql The SQL statement to execute.
473 * @param rsh The handler used to create the result object from
474 * the <code>ResultSet</code>.
475 * @param params Initialize the PreparedStatement's IN parameters with
476 * this array.
477 * @return An object generated by the handler.
478 * @throws SQLException if a database access error occurs
479 */
480 public Object query(String sql, ResultSetHandler rsh, Object[] params)
481 throws SQLException {
482
483 Connection conn = this.prepareConnection();
484
485 try {
486 return this.query(conn, sql, rsh, params);
487 } finally {
488 close(conn);
489 }
490 }
491
492 /**
493 * Executes the given SELECT SQL without any replacement parameters.
494 * The <code>Connection</code> is retrieved from the
495 * <code>DataSource</code> set in the constructor.
496 *
497 * @param sql The SQL statement to execute.
498 * @param rsh The handler used to create the result object from
499 * the <code>ResultSet</code>.
500 *
501 * @return An object generated by the handler.
502 * @throws SQLException if a database access error occurs
503 */
504 public Object query(String sql, ResultSetHandler rsh) throws SQLException {
505 return this.query(sql, rsh, (Object[]) null);
506 }
507
508 /**
509 * Throws a new exception with a more informative error message.
510 *
511 * @param cause The original exception that will be chained to the new
512 * exception when it's rethrown.
513 *
514 * @param sql The query that was executing when the exception happened.
515 *
516 * @param params The query replacement parameters; <code>null</code> is a
517 * valid value to pass in.
518 *
519 * @throws SQLException if a database access error occurs
520 */
521 protected void rethrow(SQLException cause, String sql, Object[] params)
522 throws SQLException {
523
524 String causeMessage = cause.getMessage();
525 if (causeMessage == null) {
526 causeMessage = "";
527 }
528 StringBuffer msg = new StringBuffer(causeMessage);
529
530 msg.append(" Query: ");
531 msg.append(sql);
532 msg.append(" Parameters: ");
533
534 if (params == null) {
535 msg.append("[]");
536 } else {
537 msg.append(Arrays.asList(params));
538 }
539
540 SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
541 cause.getErrorCode());
542 e.setNextException(cause);
543
544 throw e;
545 }
546
547 /**
548 * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
549 * parameters.
550 *
551 * @param conn The connection to use to run the query.
552 * @param sql The SQL to execute.
553 * @return The number of rows updated.
554 * @throws SQLException if a database access error occurs
555 */
556 public int update(Connection conn, String sql) throws SQLException {
557 return this.update(conn, sql, (Object[]) null);
558 }
559
560 /**
561 * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
562 * parameter.
563 *
564 * @param conn The connection to use to run the query.
565 * @param sql The SQL to execute.
566 * @param param The replacement parameter.
567 * @return The number of rows updated.
568 * @throws SQLException if a database access error occurs
569 */
570 public int update(Connection conn, String sql, Object param)
571 throws SQLException {
572
573 return this.update(conn, sql, new Object[] { param });
574 }
575
576 /**
577 * Execute an SQL INSERT, UPDATE, or DELETE query.
578 *
579 * @param conn The connection to use to run the query.
580 * @param sql The SQL to execute.
581 * @param params The query replacement parameters.
582 * @return The number of rows updated.
583 * @throws SQLException if a database access error occurs
584 */
585 public int update(Connection conn, String sql, Object[] params)
586 throws SQLException {
587
588 PreparedStatement stmt = null;
589 int rows = 0;
590
591 try {
592 stmt = this.prepareStatement(conn, sql);
593 this.fillStatement(stmt, params);
594 rows = stmt.executeUpdate();
595
596 } catch (SQLException e) {
597 this.rethrow(e, sql, params);
598
599 } finally {
600 close(stmt);
601 }
602
603 return rows;
604 }
605
606 /**
607 * Executes the given INSERT, UPDATE, or DELETE SQL statement without
608 * any replacement parameters. The <code>Connection</code> is retrieved
609 * from the <code>DataSource</code> set in the constructor. This
610 * <code>Connection</code> must be in auto-commit mode or the update will
611 * not be saved.
612 *
613 * @param sql The SQL statement to execute.
614 * @throws SQLException if a database access error occurs
615 * @return The number of rows updated.
616 */
617 public int update(String sql) throws SQLException {
618 return this.update(sql, (Object[]) null);
619 }
620
621 /**
622 * Executes the given INSERT, UPDATE, or DELETE SQL statement with
623 * a single replacement parameter. The <code>Connection</code> is
624 * retrieved from the <code>DataSource</code> set in the constructor.
625 * This <code>Connection</code> must be in auto-commit mode or the
626 * update will not be saved.
627 *
628 * @param sql The SQL statement to execute.
629 * @param param The replacement parameter.
630 * @throws SQLException if a database access error occurs
631 * @return The number of rows updated.
632 */
633 public int update(String sql, Object param) throws SQLException {
634 return this.update(sql, new Object[] { param });
635 }
636
637 /**
638 * Executes the given INSERT, UPDATE, or DELETE SQL statement. The
639 * <code>Connection</code> is retrieved from the <code>DataSource</code>
640 * set in the constructor. This <code>Connection</code> must be in
641 * auto-commit mode or the update will not be saved.
642 *
643 * @param sql The SQL statement to execute.
644 * @param params Initializes the PreparedStatement's IN (i.e. '?')
645 * parameters.
646 * @throws SQLException if a database access error occurs
647 * @return The number of rows updated.
648 */
649 public int update(String sql, Object[] params) throws SQLException {
650 Connection conn = this.prepareConnection();
651
652 try {
653 return this.update(conn, sql, params);
654 } finally {
655 close(conn);
656 }
657 }
658
659 /**
660 * Wrap the <code>ResultSet</code> in a decorator before processing it.
661 * This implementation returns the <code>ResultSet</code> it is given
662 * without any decoration.
663 *
664 * <p>
665 * Often, the implementation of this method can be done in an anonymous
666 * inner class like this:
667 * </p>
668 * <pre>
669 * QueryRunner run = new QueryRunner() {
670 * protected ResultSet wrap(ResultSet rs) {
671 * return StringTrimmedResultSet.wrap(rs);
672 * }
673 * };
674 * </pre>
675 *
676 * @param rs The <code>ResultSet</code> to decorate; never
677 * <code>null</code>.
678 * @return The <code>ResultSet</code> wrapped in some decorator.
679 */
680 protected ResultSet wrap(ResultSet rs) {
681 return rs;
682 }
683
684 /**
685 * Close a <code>Connection</code>. This implementation avoids closing if
686 * null and does <strong>not</strong> suppress any exceptions. Subclasses
687 * can override to provide special handling like logging.
688 * @param conn Connection to close
689 * @throws SQLException if a database access error occurs
690 * @since DbUtils 1.1
691 */
692 protected void close(Connection conn) throws SQLException {
693 DbUtils.close(conn);
694 }
695
696 /**
697 * Close a <code>Statement</code>. This implementation avoids closing if
698 * null and does <strong>not</strong> suppress any exceptions. Subclasses
699 * can override to provide special handling like logging.
700 * @param stmt Statement to close
701 * @throws SQLException if a database access error occurs
702 * @since DbUtils 1.1
703 */
704 protected void close(Statement stmt) throws SQLException {
705 DbUtils.close(stmt);
706 }
707
708 /**
709 * Close a <code>ResultSet</code>. This implementation avoids closing if
710 * null and does <strong>not</strong> suppress any exceptions. Subclasses
711 * can override to provide special handling like logging.
712 * @param rs ResultSet to close
713 * @throws SQLException if a database access error occurs
714 * @since DbUtils 1.1
715 */
716 protected void close(ResultSet rs) throws SQLException {
717 DbUtils.close(rs);
718 }
719
720 }