1 /* 2 * Copyright 2001,2004 The Apache Software Foundation. 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package org.apache.commons.scaffold.sql; 18 19 20 import java.sql.Connection; 21 import java.sql.SQLException; 22 import java.sql.PreparedStatement; 23 import java.sql.Statement; 24 import java.sql.ResultSet; 25 26 import java.util.Collection; 27 28 29 /** 30 * General purpose SQL Statements. 31 * 32 * @author Ted Husted 33 * @version $Revision: 155464 $ $Date: 2005-02-26 13:26:54 +0000 (Sat, 26 Feb 2005) $ 34 */ 35 public final class StatementUtils { 36 37 /** 38 * The delimiter used by a SQL "LIKE" expression. 39 */ 40 private static final String LIKE_DELIM = "%"; 41 42 43 /** 44 * Prepare the parameter for use in a SQL "LIKE" expression. 45 */ 46 public static final String like(String parameter) { 47 48 return LIKE_DELIM + parameter + LIKE_DELIM; 49 50 } 51 52 53 /** 54 * Create a new database table in an existing database, 55 * by sending "CREATE TABLE " and the parameters to 56 * the DBMS configured with the ConnectionPool. 57 * <p> 58 * For safety, does <b>not</b> drop table first. 59 * <p> 60 * Returns false if a SQL exception is thrown; exception 61 * is written to the servlet log. 62 * 63 * @param resource The database resource key or null for default 64 * @param tableName The name of the table to create 65 * @param tableCreate The SQL command defining the 66 * fields and indices 67 * @return Result of statement.execute() 68 * @exception SQL Exception if SQL error occurs 69 */ 70 public static final int createTable( 71 String resource, 72 String tableName, 73 String tableCreate) 74 throws SQLException { 75 76 return executeUpdate(resource,"CREATE TABLE " + 77 tableName + " " + tableCreate); 78 79 } // end createTable 80 81 82 83 /** 84 * Returns next sequential key for given table. 85 * <p> 86 * This ensures compatibility for DBMS products that do not 87 * support auto-incrementing a key field. 88 * <p> 89 * Intended to generate primary keys, but could be used to 90 * create other serial numbers based on an unsigned int. 91 * <p> 92 * Allocating the key involves reading the current key, and 93 * then incrementing the key for the next user. The method 94 * is synchronized so that two threads do not read the 95 * same key before it is incremented. 96 * <p> 97 * This routine is "unrolled" for efficiency, but the same 98 * effect can be accomplished using <code>getColumn()</code> 99 * to fetch the next key, 100 * and <code>executeUpdate</code> to increment the key. 101 * 102 * @param resource The database resource key or null for default 103 * @param column The column to return from the result set. 104 * @param query The SQL statement to fetch the next key. 105 * @param key The table name or other replaceable parameter. 106 * @param query The SQL statement to increment the next key. 107 * @exception SQLException if SQL error occurs 108 */ 109 public static final synchronized Object createKey( 110 String resource, 111 int column, 112 String query, 113 Object key, 114 String update) 115 throws SQLException { 116 117 Connection connection = null; 118 ResultSet resultSet = null; 119 PreparedStatement preparedStatement = null; 120 Object result = null; 121 int resultCode = 0; 122 123 try { 124 connection = 125 ConnectionAdaptor.getPool().getConnection(resource); 126 127 // fetch the next key 128 // - Object next = getColumn(resource,1,query,key); 129 preparedStatement = connection.prepareStatement(query); 130 preparedStatement.setObject(1,key); 131 resultSet = preparedStatement.executeQuery(); 132 if (resultSet.next()) { 133 result = resultSet.getObject(column); 134 } 135 136 // increment key (SQL command handles increment) 137 // - int result = executeUpdate(resource,update,key); 138 if (preparedStatement!=null) preparedStatement.close(); 139 preparedStatement = connection.prepareStatement(update); 140 preparedStatement.setObject(1,key); 141 resultCode = preparedStatement.executeUpdate(); 142 } 143 144 finally { 145 try { 146 if (preparedStatement!=null) preparedStatement.close(); 147 if (resultSet!=null) resultSet.close(); 148 if (connection!=null) connection.close(); 149 } 150 catch (SQLException sqle) { 151 // do nothing 152 } 153 } 154 155 return result; 156 157 } // end createKey 158 159 160 // ------------------------------------------------------ executeUpdate 161 162 163 /** 164 * Used to select correct executeUpdate signature. 165 */ 166 private static final Object[] nullParams = null; 167 168 169 /** 170 * Prepares statement using SQL statement and executes 171 * with DBMS configured with the ConnectionPool. 172 * <p> 173 * Command may be an INSERT, UPDATE, or DELETE statement 174 * or anything that returns nothing, such as a DDL 175 * statement. 176 * 177 * @param connection The database connection or null to acquire 178 * @param resource The database resource key or null for default 179 * @param command The SQL statement to execute. 180 * @param parameters An array of parameter objects 181 * @exception SQLException if SQL error occurs 182 */ 183 public static final int executeUpdate( 184 Connection connection, 185 String resource, 186 String command, 187 Object[] parameters) 188 throws SQLException { 189 190 boolean acqConnection = (connection==null); 191 Statement statement = null; 192 PreparedStatement preparedStatement = null; 193 int result = 0; 194 195 try { 196 197 if (acqConnection) { 198 connection = 199 ConnectionAdaptor.getPool().getConnection(resource); 200 } 201 202 if ((parameters==null) || (parameters.length==0)) { 203 statement = connection.createStatement(); 204 result = statement.executeUpdate(command); 205 } 206 else { 207 preparedStatement = connection.prepareStatement(command); 208 for (int i=0; i<parameters.length; i++) { 209 preparedStatement.setObject(i+1, parameters[i]); 210 } 211 result = preparedStatement.executeUpdate(); 212 } 213 214 } // end try 215 216 finally { 217 try { 218 if (preparedStatement != null) preparedStatement.close(); 219 if (statement != null) statement.close(); 220 if ((acqConnection) && (connection!= null)) connection.close(); 221 } 222 catch (SQLException sqle) { 223 // do nothing 224 } 225 } 226 227 return result; 228 229 } // end executeUpdate 230 231 232 /** 233 * Convenience method for calling 234 * <code>executeUpdate(String,String,Object[]);</code> 235 * with a single Object parameter. 236 * 237 * @param resource The database resource key or null for default 238 * @param command The SQL statement to execute. 239 * @param parameter A single parameter to use with command 240 * @exception SQLException if SQL error occurs 241 */ 242 public static final int executeUpdate( 243 String resource, 244 String command, 245 Object parameter) 246 throws SQLException { 247 248 Object[] parameters = new Object[1]; 249 parameters[0] = parameter; 250 251 return executeUpdate(null,resource,command,parameters); 252 253 } // end executeUpdate 254 255 256 /** 257 * Convenience method for calling 258 * <code>executeUpdate(String,String,Object[]);</code> 259 * without a parameter list. 260 * 261 * @param resource The database resource key or null for default 262 * @param command The SQL statement to execute. 263 * @exception SQLException if SQL error occurs 264 */ 265 public static final int executeUpdate( 266 String resource, 267 String command) 268 throws SQLException { 269 270 return executeUpdate(null,resource,command,nullParams); 271 272 } // end executeUpdate 273 274 275 /** 276 * Prepares statement using SQL statement and executes 277 * with DBMS configured with the ConnectionPool. 278 * <p> 279 * Command may be an INSERT, UPDATE, or DELETE statement 280 * or anything that returns nothing, such as a DDL 281 * statement. 282 * 283 * @param resource The database resource key or null for default 284 * @param command The SQL statement to execute. 285 * @param parameters An array of parameter objects 286 * @exception SQLException if SQL error occurs 287 */ 288 public static final int executeUpdate( 289 String resource, 290 String command, 291 Object[] parameters) 292 throws SQLException { 293 294 return executeUpdate(null,resource,command,parameters); 295 296 } // end executeUpdate 297 298 299 /** 300 * Convenience method for calling 301 * <code>executeUpdate(String,String,Object[]);</code> 302 * without a parameter list. 303 * 304 * @param resource The database resource key or null for default 305 * @param command The SQL statement to execute. 306 * @exception SQLException if SQL error occurs 307 */ 308 public static final int executeUpdate( 309 String command) 310 throws SQLException { 311 312 return executeUpdate(null,null,command,nullParams); 313 314 } // end executeUpdate 315 316 317 318 // ------------------------------------------------------- executeQuery 319 320 /** 321 * Merges a SQL command with an array of parameters. 322 * Any number or no parameters may be passed. 323 * The parameters parameter may also be null. 324 * <p> 325 * The Statement or PreparedStatement used internally 326 * are closed, but the Connection is left open so that the 327 * ResultSet remains valid. 328 * The caller should close the Connection and ResultSet 329 * as soon as possible, especially if they are pooled. 330 * 331 * @param resource The database resource key or null for default 332 * @param target The JavaBean object to return in the collection. 333 * @param command The SQL statement to prepare and execute. 334 * @param parameters The replaceable parameters to use with command. 335 * @exception SQLException if SQL error occurs 336 */ 337 public static final ResultSet executeQuery( 338 Connection connection, 339 String command, 340 Object[] parameters) 341 throws SQLException { 342 343 Statement statement = null; 344 PreparedStatement preparedStatement = null; 345 ResultSet resultSet = null; 346 347 try { 348 349 if ((parameters==null) || (parameters.length==0)) { 350 statement = connection.createStatement(); 351 resultSet = statement.executeQuery(command); 352 } 353 else { 354 preparedStatement = connection.prepareStatement(command); 355 for (int i=0; i<parameters.length; i++) { 356 preparedStatement.setObject(i+1, parameters[i]); 357 } 358 resultSet = preparedStatement.executeQuery(); 359 } 360 361 } 362 363 finally { 364 try { 365 if (statement != null) statement.close(); 366 if (preparedStatement != null) preparedStatement.close(); 367 } 368 catch (SQLException sqle) {} 369 } 370 371 return resultSet; 372 373 } // end executeQuery 374 375 376 // ---------------------------------------------------------- getColumn 377 378 /** 379 * Merges a SQL command with an array of parameters 380 * and returns a column from the first record of the result set as 381 * an Object. 382 * If an empty set results, null is returned. 383 * Any number or no parameters may be passed. 384 * The parameters parameter may also be null. 385 * The SQL Statement/ResultSet are handled by 386 * <code>ExecuteQuery()</code>. 387 * The ResultSet is converted to a collection using 388 * <code>ResultSetUtils.getCollection(Object,ResultSet)</code>. 389 * The ResultSet is released, and the Collection returned. 390 * 391 * @param resource The database resource key or null for default 392 * @param column The column to return from the result set 393 * @param command The SQL statement to prepare and execute. 394 * @param parameters The replaceable parameters to use with command. 395 * @exception SQLException if SQL error occurs 396 */ 397 public static final Object getColumn( 398 String resource, 399 int column, 400 String command, 401 Object[] parameters) 402 throws SQLException { 403 404 Object result = null; 405 Connection connection = null; 406 ResultSet resultSet = null; 407 408 try { 409 410 connection = 411 ConnectionAdaptor.getPool().getConnection(resource); 412 resultSet = executeQuery(connection,command,parameters); 413 if (resultSet.next()) { 414 result = resultSet.getObject(column); 415 } 416 417 } 418 419 finally { 420 try { 421 422 if (resultSet!=null) resultSet.close(); 423 if (connection!=null) connection.close(); 424 } 425 catch (SQLException sqle) { 426 // do nothing 427 } 428 } 429 430 return result; 431 432 } // end getColumn 433 434 435 /** 436 * Convenience method for calling 437 * <code>getColumn(String,int,String,Object[]);</code> 438 * with a single object parameter. 439 * 440 * @param resource The database resource key or null for default 441 * @param column The column to return from the result set. 442 * @param command The SQL statement to prepare and execute. 443 * @param key The replaceable parameter 444 * @exception SQLException if SQL error occurs 445 */ 446 public static final Object getColumn( 447 String resource, 448 int column, 449 String command, 450 Object key) 451 throws SQLException { 452 453 Object[] parameters = new Object[1]; 454 parameters[0] = key; 455 return getColumn(resource,column,command,parameters); 456 457 } // end getColumn 458 459 460 /** 461 * Convenience method for calling 462 * <code>getColumn(String,Object,String,Object[]);</code> 463 * 464 * with a single int parameter. 465 * @param resource The database resource key or null for default 466 * @param column The column to return from the result set. 467 * @param command The SQL statement to prepare and execute. 468 * @param key The replaceable parameter to use, if any. 469 * @exception SQLException if SQL error occurs 470 */ 471 public static final Object getColumn( 472 String resource, 473 int column, 474 String command, 475 int key) 476 throws SQLException { 477 478 Object[] parameters = new Object[1]; 479 parameters[0] = new Integer(key); 480 481 return getColumn(resource,column,command,parameters); 482 483 } // end getColumn 484 485 486 /** 487 * Convenience method for calling 488 * <code>getColumn(String,Object,String,Object[]);</code> 489 * without a parameter list. 490 * 491 * @param resource The database resource key or null for default 492 * @param column The column to return from the result set. 493 * @param command The SQL statement to prepare and execute. 494 * @param key The replaceable parameter. 495 * @exception SQLException if SQL error occurs 496 */ 497 public static final Object getColumn( 498 String resource, 499 int column, 500 String command) 501 throws SQLException { 502 503 return getColumn(resource,column,command,null); 504 505 } // end getColumn 506 507 508 // ------------------------------------------------------ getElement 509 510 /** 511 * Merges a SQL command with an array of parameters. 512 * Any number or no parameters may be passed. 513 * The parameters parameter may also be null. 514 * The SQL Statement/ResultSet are handled by 515 * <code>ExecuteQuery()</code>. 516 * The first record of the ResultSet is used to populate 517 * <code>ResultSetUtils.getElement(Object,ResultSet)</code>. 518 * The ResultSet is released, and the bean returned. 519 * 520 * @param resource The database resource key or null for default 521 * @param target The JavaBean object to populate. 522 * @param command The SQL statement to prepare and execute. 523 * @param parameters The replaceable parameters to use with 524 * command. 525 * @exception SQLException if SQL error occurs 526 * @return True if element is found 527 */ 528 public static final boolean getElement( 529 String resource, 530 Object target, 531 String command, 532 Object[] parameters) 533 throws SQLException { 534 535 Object collection = null; 536 Connection connection = null; 537 ResultSet resultSet = null; 538 boolean found = false; 539 540 try { 541 542 connection = 543 ConnectionAdaptor.getPool().getConnection(resource); 544 resultSet = executeQuery(connection,command,parameters); 545 found = ResultSetUtils.getElement(target,resultSet); 546 } 547 548 finally { 549 try { 550 if (resultSet!=null) resultSet.close(); 551 if (connection!=null) connection.close(); 552 } 553 catch (SQLException sqle) { 554 // do nothing 555 } 556 } 557 558 return found; 559 560 } // end getElement 561 562 563 /** 564 * Convenience method for calling 565 * <code>getElement(String,Object,String,Object[]);</code> 566 * with a single object parameter. 567 * 568 * @param resource The database resource key or null for default 569 * @param target The JavaBean object to populate. 570 * @param command The SQL statement to prepare and execute. 571 * @param key The replaceable parameter to use with LIKE. 572 * @exception SQLException if SQL error occurs 573 * @return True if element is found 574 */ 575 public static final boolean getElement( 576 String resource, 577 Object target, 578 String command, 579 Object key) 580 throws SQLException { 581 582 Object[] parameters = new Object[1]; 583 parameters[0] = key; 584 return getElement(resource,target,command,parameters); 585 586 } // end getElement 587 588 589 /** 590 * Convenience method for calling 591 * <code>getElement(String,Object,String,Object[]);</code> 592 * with a single int parameter. 593 * 594 * @param resource The database resource key or null for default 595 * @param target The JavaBean object to populate. 596 * @param command The SQL statement to prepare and execute. 597 * @param key The replaceable parameter to use with LIKE. 598 * @exception SQLException if SQL error occurs 599 * @return True if element is found 600 */ 601 public static final boolean getElement( 602 String resource, 603 Object target, 604 String command, 605 int key) 606 throws SQLException { 607 608 Object[] parameters = new Object[1]; 609 parameters[0] = new Integer(key); 610 return getElement(resource,target,command,parameters); 611 612 } // end getElement 613 614 615 /** 616 * Convenience method for calling 617 * <code>getElement(String,Object,String,Object[]);</code> 618 * without a parameter list. 619 * 620 * @param resource The database resource key or null for default 621 * @param target The JavaBean object to populate. 622 * @param command The SQL statement to prepare and execute. 623 * @param key The replaceable parameter to use with LIKE. 624 * @exception SQLException if SQL error occurs 625 * @return True if element is found 626 */ 627 public static final boolean getElement( 628 String resource, 629 Object target, 630 String command) 631 throws SQLException { 632 633 return getElement(resource,target,command,null); 634 635 } // end getElement 636 637 638 // ------------------------------------------------------ getCollection 639 640 /** 641 * Merges a SQL command with an array of parameters. 642 * Any number or no parameters may be passed. 643 * The parameters parameter may also be null. 644 * The SQL Statement/ResultSet are handled by 645 * <code>ExecuteQuery()</code>. 646 * The ResultSet is converted to a collection using 647 * <code>ResultSetUtils.getCollection(Object,ResultSet)</code>. 648 * The ResultSet is released, and the Collection returned. 649 * 650 * @param resource The database resource key or null for default 651 * @param target The JavaBean object to return in the collection. 652 * @param command The SQL statement to prepare and execute. 653 * @param parameters The replaceable parameters to use with command. 654 * @exception SQLException if SQL error occurs 655 */ 656 public static final Collection getCollection( 657 String resource, 658 Object target, 659 String command, 660 Object[] parameters) 661 throws SQLException { 662 663 Object collection = null; 664 Connection connection = null; 665 ResultSet resultSet = null; 666 667 try { 668 669 connection = 670 ConnectionAdaptor.getPool().getConnection(resource); 671 resultSet = executeQuery(connection,command,parameters); 672 collection = ResultSetUtils.getCollection(target,resultSet); 673 } 674 675 finally { 676 try { 677 if (resultSet!=null) resultSet.close(); 678 if (connection!=null) connection.close(); 679 } 680 catch (SQLException sqle) { 681 // do nothing 682 } 683 } 684 685 return (Collection) collection; 686 687 } // end getCollection 688 689 690 /** 691 * Convenience method for calling 692 * <code>getCollection(String,Object,String,Object[]);</code> 693 * with a single object parameter. 694 * 695 * @param resource The database resource key or null for default 696 * @param target The JavaBean object to return in the collection. 697 * @param command The SQL statement to prepare and execute. 698 * @param key The replaceable parameter to use with LIKE. 699 * @exception SQLException if SQL error occurs 700 */ 701 public static final Collection getCollection( 702 String resource, 703 Object target, 704 String command, 705 Object key) 706 throws SQLException { 707 708 Object[] parameters = new Object[1]; 709 parameters[0] = key; 710 return getCollection(resource,target,command,parameters); 711 712 } // end getCollection 713 714 715 /** 716 * Convenience method for calling 717 * <code>getCollection(String,Object,String,Object[]);</code> 718 * with a single int parameter. 719 * 720 * @param resource The database resource key or null for default 721 * @param target The JavaBean object to return in the collection. 722 * @param command The SQL statement to prepare and execute. 723 * @param key The replaceable parameter to use with LIKE. 724 * @exception SQLException if SQL error occurs 725 */ 726 public static final Collection getCollection( 727 String resource, 728 Object target, 729 String command, 730 int key) 731 throws SQLException { 732 733 Object[] parameters = new Object[1]; 734 parameters[0] = new Integer(key); 735 return getCollection(resource,target,command,parameters); 736 737 } // end getCollection 738 739 740 /** 741 * Convenience method for calling 742 * <code>getCollection(String,Object,String,Object[]);</code> 743 * without a parameter list. 744 * 745 * @param resource The database resource key or null for default 746 * @param target The JavaBean object to return in the collection. 747 * @param command The SQL statement to prepare and execute. 748 * @param key The replaceable parameter to use with LIKE. 749 * @exception SQLException if SQL error occurs 750 */ 751 public static final Collection getCollection( 752 String resource, 753 Object target, 754 String command) 755 throws SQLException { 756 757 return getCollection(resource,target,command,null); 758 759 } // end getCollection 760 761 762 } // end StatementUtils 763