1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one or more
3 * contributor license agreements. See the NOTICE file distributed with
4 * this work for additional information regarding copyright ownership.
5 * The ASF licenses this file to You under the Apache License, Version 2.0
6 * (the "License"); you may not use this file except in compliance with
7 * the License. You may obtain a copy of the License at
8 *
9 * http://www.apache.org/licenses/LICENSE-2.0
10 *
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
16 */
17 package org.apache.commons.dbutils;
18
19 import java.beans.IntrospectionException;
20 import java.beans.Introspector;
21 import java.beans.PropertyDescriptor;
22 import java.lang.reflect.InvocationTargetException;
23 import java.lang.reflect.Method;
24 import java.sql.CallableStatement;
25 import java.sql.Connection;
26 import java.sql.ParameterMetaData;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.sql.SQLFeatureNotSupportedException;
31 import java.sql.Statement;
32 import java.sql.Types;
33 import java.util.Arrays;
34
35 import javax.sql.DataSource;
36
37 /**
38 * The base class for QueryRunner & AsyncQueryRunner. This class is thread safe.
39 *
40 * @since 1.4 (mostly extracted from QueryRunner)
41 */
42 public abstract class AbstractQueryRunner {
43 /**
44 * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried
45 * it yet)?
46 */
47 private volatile boolean pmdKnownBroken = false;
48
49 /**
50 * The DataSource to retrieve connections from.
51 * @deprecated Access to this field should be through {@link #getDataSource()}.
52 */
53 @Deprecated
54 protected final DataSource ds;
55
56 /**
57 * Configuration to use when preparing statements.
58 */
59 private final StatementConfiguration stmtConfig;
60
61 /**
62 * Default constructor, sets pmdKnownBroken to false, ds to null and stmtConfig to null.
63 */
64 public AbstractQueryRunner() {
65 ds = null;
66 this.stmtConfig = null;
67 }
68
69 /**
70 * Constructor to control the use of {@code ParameterMetaData}.
71 *
72 * @param pmdKnownBroken
73 * Some drivers don't support
74 * {@link ParameterMetaData#getParameterType(int) }; if
75 * {@code pmdKnownBroken} is set to true, we won't even try
76 * it; if false, we'll try it, and if it breaks, we'll remember
77 * not to use it again.
78 */
79 public AbstractQueryRunner(final boolean pmdKnownBroken) {
80 this.pmdKnownBroken = pmdKnownBroken;
81 ds = null;
82 this.stmtConfig = null;
83 }
84
85 /**
86 * Constructor to provide a {@code DataSource}. Methods that do not
87 * take a {@code Connection} parameter will retrieve connections from
88 * this {@code DataSource}.
89 *
90 * @param ds
91 * The {@code DataSource} to retrieve connections from.
92 */
93 public AbstractQueryRunner(final DataSource ds) {
94 this.ds = ds;
95 this.stmtConfig = null;
96 }
97
98 /**
99 * Constructor to provide a {@code DataSource} and control the use of
100 * {@code ParameterMetaData}. Methods that do not take a
101 * {@code Connection} parameter will retrieve connections from this
102 * {@code DataSource}.
103 *
104 * @param ds
105 * The {@code DataSource} to retrieve connections from.
106 * @param pmdKnownBroken
107 * Some drivers don't support
108 * {@link ParameterMetaData#getParameterType(int) }; if
109 * {@code pmdKnownBroken} is set to true, we won't even try
110 * it; if false, we'll try it, and if it breaks, we'll remember
111 * not to use it again.
112 */
113 public AbstractQueryRunner(final DataSource ds, final boolean pmdKnownBroken) {
114 this.pmdKnownBroken = pmdKnownBroken;
115 this.ds = ds;
116 this.stmtConfig = null;
117 }
118
119 /**
120 * Constructor for QueryRunner that takes a {@code DataSource}, a {@code StatementConfiguration}, and
121 * controls the use of {@code ParameterMetaData}. Methods that do not take a {@code Connection} parameter
122 * will retrieve connections from this {@code DataSource}.
123 *
124 * @param ds The {@code DataSource} to retrieve connections from.
125 * @param pmdKnownBroken Some drivers don't support {@link java.sql.ParameterMetaData#getParameterType(int) };
126 * if {@code pmdKnownBroken} is set to true, we won't even try it; if false, we'll try it,
127 * and if it breaks, we'll remember not to use it again.
128 * @param stmtConfig The configuration to apply to statements when they are prepared.
129 */
130 public AbstractQueryRunner(final DataSource ds, final boolean pmdKnownBroken, final StatementConfiguration stmtConfig) {
131 this.pmdKnownBroken = pmdKnownBroken;
132 this.ds = ds;
133 this.stmtConfig = stmtConfig;
134 }
135
136 /**
137 * Constructor for QueryRunner that takes a {@code DataSource} to use and a {@code StatementConfiguration}.
138 *
139 * Methods that do not take a {@code Connection} parameter will retrieve connections from this
140 * {@code DataSource}.
141 *
142 * @param ds The {@code DataSource} to retrieve connections from.
143 * @param stmtConfig The configuration to apply to statements when they are prepared.
144 */
145 public AbstractQueryRunner(final DataSource ds, final StatementConfiguration stmtConfig) {
146 this.ds = ds;
147 this.stmtConfig = stmtConfig;
148 }
149
150 /**
151 * Constructor for QueryRunner that takes a {@code StatementConfiguration} to configure statements when
152 * preparing them.
153 *
154 * @param stmtConfig The configuration to apply to statements when they are prepared.
155 */
156 public AbstractQueryRunner(final StatementConfiguration stmtConfig) {
157 this.ds = null;
158 this.stmtConfig = stmtConfig;
159 }
160
161 /**
162 * Close a {@code Connection}. This implementation avoids closing if
163 * null and does <strong>not</strong> suppress any exceptions. Subclasses
164 * can override to provide special handling like logging.
165 *
166 * @param conn
167 * Connection to close
168 * @throws SQLException
169 * if a database access error occurs
170 * @since 1.1
171 */
172 protected void close(final Connection conn) throws SQLException {
173 DbUtils.close(conn);
174 }
175
176 /**
177 * Close a {@code ResultSet}. This implementation avoids closing if
178 * null and does <strong>not</strong> suppress any exceptions. Subclasses
179 * can override to provide special handling like logging.
180 *
181 * @param resultSet
182 * ResultSet to close
183 * @throws SQLException
184 * if a database access error occurs
185 * @since 1.1
186 */
187 protected void close(final ResultSet resultSet) throws SQLException {
188 DbUtils.close(resultSet);
189 }
190
191 /**
192 * Close a {@code Statement}. This implementation avoids closing if
193 * null and does <strong>not</strong> suppress any exceptions. Subclasses
194 * can override to provide special handling like logging.
195 *
196 * @param stmt
197 * Statement to close
198 * @throws SQLException
199 * if a database access error occurs
200 * @since 1.1
201 */
202 protected void close(final Statement stmt) throws SQLException {
203 DbUtils.close(stmt);
204 }
205
206 /**
207 * Calls {@link DbUtils#closeQuietly(Connection)}.
208 *
209 * @param conn Connection to close.
210 * @since 1.8.0
211 */
212 protected void closeQuietly(final Connection conn) {
213 DbUtils.closeQuietly(conn);
214 }
215
216 /**
217 * Calls {@link DbUtils#closeQuietly(ResultSet)}.
218 *
219 * @param resultSet ResultSet to close.
220 * @since 1.8.0
221 */
222 protected void closeQuietly(final ResultSet resultSet) {
223 DbUtils.closeQuietly(resultSet);
224 }
225
226 /**
227 * Calls {@link DbUtils#closeQuietly(Statement)}.
228 *
229 * @param statement ResultSet to close.
230 * @since 1.8.0
231 */
232 protected void closeQuietly(final Statement statement) {
233 DbUtils.closeQuietly(statement);
234 }
235
236 private void configureStatement(final Statement stmt) throws SQLException {
237
238 if (stmtConfig != null) {
239 if (stmtConfig.isFetchDirectionSet()) {
240 stmt.setFetchDirection(stmtConfig.getFetchDirection());
241 }
242
243 if (stmtConfig.isFetchSizeSet()) {
244 stmt.setFetchSize(stmtConfig.getFetchSize());
245 }
246
247 if (stmtConfig.isMaxFieldSizeSet()) {
248 stmt.setMaxFieldSize(stmtConfig.getMaxFieldSize());
249 }
250
251 if (stmtConfig.isMaxRowsSet()) {
252 stmt.setMaxRows(stmtConfig.getMaxRows());
253 }
254
255 if (stmtConfig.isQueryTimeoutSet()) {
256 stmt.setQueryTimeout(stmtConfig.getQueryTimeout());
257 }
258 }
259 }
260
261 /**
262 * Fill the {@code PreparedStatement} replacement parameters with the
263 * given objects.
264 *
265 * @param stmt
266 * PreparedStatement to fill
267 * @param params
268 * Query replacement parameters; {@code null} is a valid
269 * value to pass in.
270 * @throws SQLException
271 * if a database access error occurs
272 */
273 public void fillStatement(final PreparedStatement stmt, final Object... params) throws SQLException {
274 ParameterMetaData pmd = null;
275 if (!pmdKnownBroken) {
276 try {
277 pmd = this.getParameterMetaData(stmt);
278 if (pmd == null) { // can be returned by implementations that don't support the method
279 pmdKnownBroken = true;
280 }
281 } catch (final SQLFeatureNotSupportedException ex) {
282 pmdKnownBroken = true;
283 }
284 // TODO see DBUTILS-117: would it make sense to catch any other SQLEx types here?
285 }
286 fillStatement(stmt, pmd, params);
287 }
288
289 /**
290 * Fill the {@code PreparedStatement} replacement parameters with the
291 * given objects, and prefetched parameter metadata.
292 *
293 * @param stmt
294 * PreparedStatement to fill
295 * @param pmd
296 * Prefetched parameter metadata
297 * @param params
298 * Query replacement parameters; {@code null} is a valid
299 * value to pass in.
300 * @throws SQLException
301 * if a database access error occurs
302 */
303 public void fillStatement(final PreparedStatement stmt, final ParameterMetaData pmd, final Object... params)
304 throws SQLException {
305
306 // check the parameter count, if we can
307 if (!pmdKnownBroken && pmd != null) {
308 final int stmtCount = pmd.getParameterCount();
309 final int paramsCount = params == null ? 0 : params.length;
310
311 if (stmtCount != paramsCount) {
312 throw new SQLException("Wrong number of parameters: expected "
313 + stmtCount + ", was given " + paramsCount);
314 }
315 }
316
317 // nothing to do here
318 if (params == null) {
319 return;
320 }
321
322 CallableStatement call = null;
323 if (stmt instanceof CallableStatement) {
324 call = (CallableStatement) stmt;
325 }
326
327 for (int i = 0; i < params.length; i++) {
328 if (params[i] != null) {
329 if (call != null && params[i] instanceof OutParameter) {
330 ((OutParameter<?>) params[i]).register(call, i + 1);
331 } else {
332 stmt.setObject(i + 1, params[i]);
333 }
334 } else {
335 // VARCHAR works with many drivers regardless
336 // of the actual column type. Oddly, NULL and
337 // OTHER don't work with Oracle's drivers.
338 int sqlType = Types.VARCHAR;
339 if (!pmdKnownBroken) {
340 // TODO see DBUTILS-117: does it make sense to catch SQLEx here?
341 try {
342 /*
343 * It's not possible for pmdKnownBroken to change from true to false, (once true, always true) so pmd cannot be null here.
344 */
345 sqlType = pmd.getParameterType(i + 1);
346 } catch (final SQLException e) {
347 pmdKnownBroken = true;
348 }
349 }
350 stmt.setNull(i + 1, sqlType);
351 }
352 }
353 }
354
355 /**
356 * Fill the {@code PreparedStatement} replacement parameters with the
357 * given object's bean property values.
358 *
359 * @param stmt
360 * PreparedStatement to fill
361 * @param bean
362 * a JavaBean object
363 * @param properties
364 * an ordered array of properties; this gives the order to insert
365 * values in the statement
366 * @throws SQLException
367 * if a database access error occurs
368 */
369 public void fillStatementWithBean(final PreparedStatement stmt, final Object bean,
370 final PropertyDescriptor[] properties) throws SQLException {
371 final Object[] params = new Object[properties.length];
372 for (int i = 0; i < properties.length; i++) {
373 final PropertyDescriptor property = properties[i];
374 Object value = null;
375 final Method method = property.getReadMethod();
376 if (method == null) {
377 throw new IllegalArgumentException("No read method for bean property " + bean.getClass() + " " + property.getName());
378 }
379 try {
380 value = method.invoke(bean);
381 } catch (final IllegalArgumentException e) {
382 throw new IllegalArgumentException("Couldn't invoke method with 0 arguments: " + method, e);
383 } catch (final InvocationTargetException | IllegalAccessException e) {
384 throw new IllegalArgumentException("Couldn't invoke method: " + method, e);
385 }
386 params[i] = value;
387 }
388 fillStatement(stmt, params);
389 }
390
391 /**
392 * Fill the {@code PreparedStatement} replacement parameters with the
393 * given object's bean property values.
394 *
395 * @param stmt
396 * PreparedStatement to fill
397 * @param bean
398 * A JavaBean object
399 * @param propertyNames
400 * An ordered array of property names (these should match the
401 * getters/setters); this gives the order to insert values in the
402 * statement
403 * @throws SQLException
404 * If a database access error occurs
405 */
406 public void fillStatementWithBean(final PreparedStatement stmt, final Object bean,
407 final String... propertyNames) throws SQLException {
408 PropertyDescriptor[] descriptors;
409 try {
410 descriptors = Introspector.getBeanInfo(bean.getClass()).getPropertyDescriptors();
411 } catch (final IntrospectionException e) {
412 throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
413 }
414 final PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
415 for (int i = 0; i < propertyNames.length; i++) {
416 final String propertyName = propertyNames[i];
417 if (propertyName == null) {
418 throw new NullPointerException("propertyName can't be null: " + i);
419 }
420 boolean found = false;
421 for (final PropertyDescriptor descriptor : descriptors) {
422 if (propertyName.equals(descriptor.getName())) {
423 sorted[i] = descriptor;
424 found = true;
425 break;
426 }
427 }
428 if (!found) {
429 throw new IllegalStateException("Couldn't find bean property: " + bean.getClass() + " " + propertyName);
430 }
431 }
432 fillStatementWithBean(stmt, bean, sorted);
433 }
434
435 /**
436 * Returns the {@code DataSource} this runner is using.
437 * {@code QueryRunner} methods always call this method to get the
438 * {@code DataSource} so subclasses can provide specialized behavior.
439 *
440 * @return DataSource the runner is using
441 */
442 public DataSource getDataSource() {
443 return this.ds;
444 }
445
446 /**
447 * Get the {@code ParameterMetaData} of the prepared statement, if the {@code pmdKnownBroken}
448 * is set to false.
449 *
450 * @param stmt
451 * PreparedStatement of which to query the metadata of parameters
452 * @return the metadata of parameters
453 * @throws SQLException
454 * if a database access error occurs
455 */
456 public ParameterMetaData getParameterMetaData(final PreparedStatement stmt) throws SQLException {
457 ParameterMetaData pmd = null;
458 if (!pmdKnownBroken) {
459 try {
460 pmd = stmt.getParameterMetaData();
461 } catch (final SQLFeatureNotSupportedException ex) {
462 pmdKnownBroken = true;
463 }
464 }
465 return pmd;
466 }
467
468 /**
469 * Some drivers don't support
470 * {@link ParameterMetaData#getParameterType(int) }; if
471 * {@code pmdKnownBroken} is set to true, we won't even try it; if
472 * false, we'll try it, and if it breaks, we'll remember not to use it
473 * again.
474 *
475 * @return the flag to skip (or not)
476 * {@link ParameterMetaData#getParameterType(int) }
477 * @since 1.4
478 */
479 public boolean isPmdKnownBroken() {
480 return pmdKnownBroken;
481 }
482
483 /**
484 * Factory method that creates and initializes a
485 * {@code CallableStatement} object for the given SQL.
486 * {@code QueryRunner} methods always call this method to prepare
487 * callable statements for them. Subclasses can override this method to
488 * provide special CallableStatement configuration if needed. This
489 * implementation simply calls {@code conn.prepareCall(sql)}.
490 *
491 * @param conn
492 * The {@code Connection} used to create the
493 * {@code CallableStatement}
494 * @param sql
495 * The SQL statement to prepare.
496 * @return An initialized {@code CallableStatement}.
497 * @throws SQLException
498 * if a database access error occurs
499 */
500 protected CallableStatement prepareCall(final Connection conn, final String sql)
501 throws SQLException {
502
503 return conn.prepareCall(sql);
504 }
505
506 /**
507 * Factory method that creates and initializes a {@code Connection}
508 * object. {@code QueryRunner} methods always call this method to
509 * retrieve connections from its DataSource. Subclasses can override this
510 * method to provide special {@code Connection} configuration if
511 * needed. This implementation simply calls {@code ds.getConnection()}.
512 *
513 * @return An initialized {@code Connection}.
514 * @throws SQLException
515 * if a database access error occurs
516 * @since 1.1
517 */
518 protected Connection prepareConnection() throws SQLException {
519 if (this.getDataSource() == null) {
520 throw new SQLException(
521 "QueryRunner requires a DataSource to be "
522 + "invoked in this way, or a Connection should be passed in");
523 }
524 return this.getDataSource().getConnection();
525 }
526
527 /**
528 * Factory method that creates and initializes a
529 * {@code PreparedStatement} object for the given SQL.
530 * {@code QueryRunner} methods always call this method to prepare
531 * statements for them. Subclasses can override this method to provide
532 * special PreparedStatement configuration if needed. This implementation
533 * simply calls {@code conn.prepareStatement(sql)}.
534 *
535 * @param conn
536 * The {@code Connection} used to create the
537 * {@code PreparedStatement}
538 * @param sql
539 * The SQL statement to prepare.
540 * @return An initialized {@code PreparedStatement}.
541 * @throws SQLException
542 * if a database access error occurs
543 */
544 protected PreparedStatement prepareStatement(final Connection conn, final String sql)
545 throws SQLException {
546
547 @SuppressWarnings("resource")
548 final
549 PreparedStatement ps = conn.prepareStatement(sql);
550 try {
551 configureStatement(ps);
552 } catch (final SQLException e) {
553 ps.close();
554 throw e;
555 }
556 return ps;
557 }
558
559 /**
560 * Factory method that creates and initializes a
561 * {@code PreparedStatement} object for the given SQL.
562 * {@code QueryRunner} methods always call this method to prepare
563 * statements for them. Subclasses can override this method to provide
564 * special PreparedStatement configuration if needed. This implementation
565 * simply calls {@code conn.prepareStatement(sql, returnedKeys)}
566 * which will result in the ability to retrieve the automatically-generated
567 * keys from an auto_increment column.
568 *
569 * @param conn
570 * The {@code Connection} used to create the
571 * {@code PreparedStatement}
572 * @param sql
573 * The SQL statement to prepare.
574 * @param returnedKeys
575 * Flag indicating whether to return generated keys or not.
576 *
577 * @return An initialized {@code PreparedStatement}.
578 * @throws SQLException
579 * if a database access error occurs
580 * @since 1.6
581 */
582 protected PreparedStatement prepareStatement(final Connection conn, final String sql, final int returnedKeys)
583 throws SQLException {
584
585 @SuppressWarnings("resource")
586 final
587 PreparedStatement ps = conn.prepareStatement(sql, returnedKeys);
588 try {
589 configureStatement(ps);
590 } catch (final SQLException e) {
591 ps.close();
592 throw e;
593 }
594 return ps;
595 }
596
597 /**
598 * Throws a new exception with a more informative error message.
599 *
600 * @param cause
601 * The original exception that will be chained to the new
602 * exception when it's rethrown.
603 *
604 * @param sql
605 * The query that was executing when the exception happened.
606 *
607 * @param params
608 * The query replacement parameters; {@code null} is a valid
609 * value to pass in.
610 *
611 * @throws SQLException
612 * if a database access error occurs
613 */
614 protected void rethrow(final SQLException cause, final String sql, final Object... params)
615 throws SQLException {
616
617 String causeMessage = cause.getMessage();
618 if (causeMessage == null) {
619 causeMessage = "";
620 }
621 final StringBuilder msg = new StringBuilder(causeMessage);
622
623 msg.append(" Query: ");
624 msg.append(sql);
625 msg.append(" Parameters: ");
626
627 if (params == null) {
628 msg.append("[]");
629 } else {
630 msg.append(Arrays.deepToString(params));
631 }
632
633 final SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
634 cause.getErrorCode());
635 e.setNextException(cause);
636
637 throw e;
638 }
639
640 /**
641 * Wrap the {@code ResultSet} in a decorator before processing it. This
642 * implementation returns the {@code ResultSet} it is given without any
643 * decoration.
644 *
645 * <p>
646 * Often, the implementation of this method can be done in an anonymous
647 * inner class like this:
648 * </p>
649 *
650 * <pre>
651 * QueryRunner run = new QueryRunner() {
652 * protected ResultSet wrap(ResultSet rs) {
653 * return StringTrimmedResultSet.wrap(rs);
654 * }
655 * };
656 * </pre>
657 *
658 * @param rs
659 * The {@code ResultSet} to decorate; never
660 * {@code null}.
661 * @return The {@code ResultSet} wrapped in some decorator.
662 */
663 protected ResultSet wrap(final ResultSet rs) {
664 return rs;
665 }
666
667 }