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 }