001/** 002 * Licensed to the Apache Software Foundation (ASF) under one 003 * or more contributor license agreements. See the NOTICE file 004 * distributed with this work for additional information 005 * regarding copyright ownership. The ASF licenses this file 006 * to you under the Apache License, Version 2.0 (the 007 * "License"); you may not use this file except in compliance 008 * with the License. You may obtain a copy of the License at 009 * 010 * http://www.apache.org/licenses/LICENSE-2.0 011 * 012 * Unless required by applicable law or agreed to in writing, software 013 * distributed under the License is distributed on an "AS IS" BASIS, 014 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 015 * See the License for the specific language governing permissions and 016 * limitations under the License. 017 */ 018 019package org.apache.oozie.util.db; 020 021import java.io.BufferedInputStream; 022import java.io.BufferedOutputStream; 023import java.io.ByteArrayOutputStream; 024import java.io.IOException; 025import java.sql.Blob; 026import java.sql.Connection; 027import java.sql.PreparedStatement; 028import java.sql.ResultSet; 029import java.sql.SQLException; 030import java.sql.Timestamp; 031import java.util.ArrayList; 032import java.util.HashSet; 033import java.util.List; 034import java.util.Map; 035import java.util.Set; 036 037import org.apache.commons.logging.LogFactory; 038import org.apache.oozie.util.XLog; 039import org.apache.oozie.util.db.Schema.Column; 040import org.apache.oozie.util.db.Schema.Table; 041 042/** 043 * The <code>SqlStatement</code> is used to generate SQL Statements. Prepare the generated Statements and also to parse 044 * the resultSets 045 */ 046public abstract class SqlStatement { 047 048 private static XLog log = new XLog(LogFactory.getLog(SqlStatement.class)); 049 protected boolean forUpdate = false; 050 051 /** 052 * <code>ResultSetReader</code> is used to parse the result set and gives methods for getting appropriate type of 053 * data given the column name 054 */ 055 public static class ResultSetReader { 056 final ResultSet rSet; 057 058 private ResultSetReader(ResultSet rSet) { 059 this.rSet = rSet; 060 } 061 062 /** 063 * Move the Result Set to next record 064 * 065 * @return true if there is a next record 066 * @throws SQLException 067 */ 068 public boolean next() throws SQLException { 069 return rSet.next(); 070 } 071 072 /** 073 * Close the Result Set 074 * 075 * @throws SQLException 076 */ 077 public void close() throws SQLException { 078 rSet.close(); 079 } 080 081 /** 082 * Get the Column data given its type and name 083 * 084 * @param <T> Type of the column 085 * @param clazz Class of the Type 086 * @param col Column name 087 * @return Column data 088 * @throws SQLException 089 */ 090 @SuppressWarnings("unchecked") 091 public <T> T get(Class<T> clazz, Column col) throws SQLException { 092 if (clazz.isAssignableFrom(col.getType())) { 093 return (T) rSet.getObject(col.asLabel()); 094 } 095 else { 096 if (String.class.equals(clazz)) { 097 return (T) ("" + rSet.getObject(col.asLabel())); 098 } 099 else { 100 throw new RuntimeException("Column Error : Actual Type [" + col.getType() + "]," + " Requested Type [" 101 + clazz + "] !!"); 102 } 103 } 104 } 105 106 /** 107 * Get the data for columns with blob type 108 * 109 * @param col Column name 110 * @return Column data 111 * @throws SQLException 112 */ 113 public byte[] getByteArray(Column col) throws SQLException { 114 byte[] bArray = null; 115 if (Blob.class.equals(col.getType())) { 116 BufferedInputStream bStream = new BufferedInputStream(rSet.getBinaryStream(col.asLabel())); 117 byte[] temp = new byte[1024]; 118 int num = 0; 119 ByteArrayOutputStream baos = new ByteArrayOutputStream(); 120 BufferedOutputStream bOut = new BufferedOutputStream(baos); 121 try { 122 while ((num = bStream.read(temp)) != -1) { 123 bOut.write(temp, 0, num); 124 } 125 bOut.flush(); 126 bOut.close(); 127 } 128 catch (IOException e) { 129 throw new SQLException(e); 130 } 131 bArray = baos.toByteArray(); 132 } 133 else { 134 throw new RuntimeException("Column Error : Actual Type [" + col.getType() + "]," + " Requested Type [" 135 + Blob.class + "] !!"); 136 } 137 return bArray; 138 } 139 140 /** 141 * Get a String Column 142 * 143 * @param col Column Name 144 * @return Column data 145 * @throws SQLException 146 */ 147 public String getString(Column col) throws SQLException { 148 return get(String.class, col); 149 } 150 151 /** 152 * Get the Timestamp Column 153 * 154 * @param col Column name 155 * @return Column data 156 * @throws SQLException 157 */ 158 public Timestamp getTimestamp(Column col) throws SQLException { 159 return get(Timestamp.class, col); 160 } 161 162 /** 163 * Get the Boolean Column 164 * 165 * @param col Column name 166 * @return Column data 167 * @throws SQLException 168 */ 169 public Boolean getBoolean(Column col) throws SQLException { 170 return get(Boolean.class, col); 171 } 172 173 /** 174 * Get the Numeric data 175 * 176 * @param col Column name 177 * @return Column data 178 * @throws SQLException 179 */ 180 public Long getLong(Column col) throws SQLException { 181 return get(Long.class, col); 182 } 183 184 } 185 186 /** 187 * Construct the condition statement that will be used in the where clause 188 */ 189 public static class Condition { 190 191 final Column column; 192 protected StringBuilder sb = new StringBuilder("( "); 193 protected List<Object> values = new ArrayList<Object>(); 194 195 private Condition(Column column) { 196 this.column = column; 197 if (column != null) { 198 sb.append(column); 199 } 200 } 201 202 /** 203 * Return the Condition string 204 */ 205 public String toString() { 206 return sb.toString(); 207 } 208 } 209 210 /** 211 * NULL/NOT NULL Condition Generator 212 */ 213 static class Null extends Condition { 214 Null(boolean isInvert, Column column) { 215 super(column); 216 sb.append(" IS"); 217 sb.append(isInvert ? " NOT" : ""); 218 sb.append(" NULL "); 219 sb.append(" )"); 220 } 221 } 222 223 /** 224 * Generate condition statement for IS NULL 225 * 226 * @param column column name 227 * @return IS NULL condition statement 228 */ 229 public static Condition isNull(Column column) { 230 return new Null(false, column); 231 } 232 233 /** 234 * Generate condition statement for IS NOT NULL 235 * 236 * @param column column name 237 * @return IS NOT NULL condition statement 238 */ 239 public static Condition isNotNull(Column column) { 240 return new Null(true, column); 241 } 242 243 /** 244 * LIKE/NOT LIKE Condition Generator 245 */ 246 static class Like extends Condition { 247 Like(boolean isInvert, Column column, String value) { 248 super(column); 249 sb.append(isInvert ? " NOT" : "").append(" LIKE ").append("?").append(" )"); 250 values.add(value); 251 } 252 } 253 254 /** 255 * Generate condition statement for IS LIKE 256 * 257 * @param column column name 258 * @param value value to be checked 259 * @return IS LIKE condition statement 260 */ 261 public static Condition isLike(Column column, String value) { 262 return new Like(false, column, value); 263 } 264 265 /** 266 * Generates condition statement for IS NOT LIKE 267 * 268 * @param column column name 269 * @param value value to be checked 270 * @return IS NOT LIKE condition statement 271 */ 272 public static Condition isNotLike(Column column, String value) { 273 return new Like(true, column, value); 274 } 275 276 /** 277 * Comparison condition statement generator 278 */ 279 static class Compare extends Condition { 280 Compare(String oper, Column column, Object value) { 281 super(column); 282 if (value instanceof Column) { 283 sb.append(oper).append(value).append(" )"); 284 } 285 else { 286 sb.append(oper).append("?").append(" )"); 287 values.add(value); 288 } 289 } 290 } 291 292 /** 293 * Generate Condition statement for equality check 294 * 295 * @param column 296 * @param value 297 * @return Equality Condition statement 298 */ 299 public static Condition isEqual(Column column, Object value) { 300 return new Compare(" = ", column, value); 301 } 302 303 /** 304 * Generate InEquality Condition statement 305 * 306 * @param column 307 * @param value 308 * @return Inequality Condition statement 309 */ 310 public static Condition isNotEqual(Column column, Object value) { 311 return new Compare(" <> ", column, value); 312 } 313 314 /** 315 * Generate Condition statement for LESS THAN condition checking 316 * 317 * @param column 318 * @param value 319 * @return less than condition statement 320 */ 321 public static Condition lessThan(Column column, Object value) { 322 return new Compare(" < ", column, value); 323 } 324 325 /** 326 * Generate Condition statement for GREATER THAN condition checking 327 * 328 * @param column 329 * @param value 330 * @return greater than condition statement 331 */ 332 public static Condition greaterThan(Column column, Object value) { 333 return new Compare(" > ", column, value); 334 } 335 336 /** 337 * Generate Condition statement for LESS THAN OR EQUAL condition checking 338 * 339 * @param column 340 * @param value 341 * @return less than or equal condition statement 342 */ 343 public static Condition lessThanOrEqual(Column column, Object value) { 344 return new Compare(" <= ", column, value); 345 } 346 347 /** 348 * Generate Condition statement for GREATER THAN OR EQUAL condition checking 349 * 350 * @param column 351 * @param value 352 * @return greater than or equal condition statement 353 */ 354 public static Condition greaterThanOrEqual(Column column, Object value) { 355 return new Compare(" >= ", column, value); 356 } 357 358 /** 359 * IN/NOT IN condition statement generator for checking multiple values and for sub queries 360 */ 361 static class In extends Condition { 362 In(boolean isInvert, Column column, Object... values) { 363 super(column); 364 sb.append(isInvert ? " NOT" : "").append(" IN ("); 365 for (Object value : values) { 366 sb.append(" ? ").append(","); 367 this.values.add(value); 368 } 369 // remove last comma 370 sb.setLength(sb.length() - 1); 371 sb.append(") )"); 372 } 373 374 In(boolean isInvert, Column column, Select select) { 375 super(column); 376 sb.append(isInvert ? " NOT" : "").append(" IN ("); 377 for (Object value : select.values) { 378 this.values.add(value); 379 } 380 sb.append(select.toString()); 381 sb.append(") )"); 382 } 383 } 384 385 /** 386 * IN Condition for checking multiple values 387 * 388 * @param column 389 * @param values 390 * @return In condition statement 391 */ 392 public static Condition in(Column column, Object... values) { 393 return new In(false, column, values); 394 } 395 396 /** 397 * NOT IN Condition for checking multiple values 398 * 399 * @param column 400 * @param values 401 * @return not in condition statement 402 */ 403 public static Condition notIn(Column column, Object... values) { 404 return new In(true, column, values); 405 } 406 407 /** 408 * Sub query with IN condition 409 * 410 * @param column 411 * @param select 412 * @return Sub query using in 413 */ 414 public static Condition in(Column column, Select select) { 415 return new In(false, column, select); 416 } 417 418 /** 419 * Sub query with NOT IN condition 420 * 421 * @param column 422 * @param select 423 * @return sub query using not in 424 */ 425 public static Condition notIn(Column column, Select select) { 426 return new In(true, column, select); 427 } 428 429 /** 430 * Generate the Range checking statements 431 */ 432 static class Between extends Condition { 433 Between(boolean isInvert, Column column, Object lVal, Object rVal) { 434 super(column); 435 sb.append(isInvert ? " NOT" : "").append(" BETWEEN "); 436 sb.append(" ? "); 437 values.add(lVal); 438 sb.append(" AND "); 439 sb.append(" ? ").append(" )"); 440 values.add(rVal); 441 } 442 } 443 444 /** 445 * BETWEEN range checking statement 446 * 447 * @param column 448 * @param lVal min value for range checking 449 * @param rVal max value for range checking 450 * @return between condition statement 451 */ 452 public static Condition between(Column column, Object lVal, Object rVal) { 453 return new Between(false, column, lVal, rVal); 454 } 455 456 /** 457 * NOT BETWEEN range checking statement 458 * 459 * @param column 460 * @param lVal min value for range checking 461 * @param rVal max value for range checking 462 * @return not between condition statement 463 */ 464 public static Condition notBetween(Column column, Object lVal, Object rVal) { 465 return new Between(true, column, lVal, rVal); 466 } 467 468 /** 469 * Logical AND condition Generator 470 * 471 * @param conds list of conditions for AND 472 * @return AND statement 473 */ 474 public static Condition and(Condition... conds) { 475 Condition retVal = new Condition(null); 476 if (conds.length >= 2) { 477 for (int i = 0; i < conds.length; i++) { 478 if (i > 0) { 479 retVal.sb.append(" AND "); 480 } 481 retVal.sb.append(conds[i].sb); 482 retVal.values.addAll(conds[i].values); 483 } 484 } 485 else { 486 if (conds.length == 1) { 487 return conds[0]; 488 } 489 } 490 retVal.sb.append(" )"); 491 return retVal; 492 } 493 494 /** 495 * Logical OR condition generator 496 * 497 * @param conds list of conditions for OR 498 * @return OR statement 499 */ 500 public static Condition or(Condition... conds) { 501 Condition retVal = new Condition(null); 502 if (conds.length >= 2) { 503 for (int i = 0; i < conds.length; i++) { 504 if (i > 0) { 505 retVal.sb.append(" OR "); 506 } 507 retVal.sb.append(conds[i].sb); 508 retVal.values.addAll(conds[i].values); 509 } 510 } 511 else { 512 if (conds.length == 1) { 513 return conds[0]; 514 } 515 } 516 retVal.sb.append(" )"); 517 return retVal; 518 } 519 520 protected StringBuilder sb = new StringBuilder(""); 521 protected List<Object> values = new ArrayList<Object>(); 522 523 /** 524 * Select Statement generator. Generate the SQL Statement for select statements. Provide methods to add WHERE 525 * clause, ORDER BY clause, FOR UPDATE clause. 526 */ 527 public static class Select extends SqlStatement { 528 private Condition condition; 529 private boolean isOdered = false; 530 private Column[] orderby = null; 531 private boolean[] isAscending = null; 532 private boolean isLimitSet = false; 533 534 private Select(Select other) { 535 this.condition = other.condition; 536 this.sb.append(other.sb); 537 this.values.addAll(other.values); 538 this.isOdered = other.isOdered; 539 this.isLimitSet = other.isLimitSet; 540 this.orderby = other.orderby; 541 this.isAscending = other.isAscending; 542 this.forUpdate = other.forUpdate; 543 } 544 545 Select(boolean count, Table... tables) { 546 String temp = count ? "COUNT(*)" : "*"; 547 this.sb.append("SELECT " + temp + " FROM"); 548 if ((tables != null) && (tables.length > 0)) { 549 for (Table table : tables) { 550 this.sb.append(" " + table + " ,"); 551 } 552 // remove comma 553 this.sb.setLength(sb.length() - 1); 554 } 555 else { 556 throw new RuntimeException("Need atleast 1 Table !!"); 557 } 558 } 559 560 Select(Column... columns) { 561 this.sb.append("SELECT"); 562 if ((columns != null) && (columns.length > 0)) { 563 Set<Table> tables = new HashSet<Table>(); 564 for (Column column : columns) { 565 tables.add(column.table()); 566 this.sb.append(" " + column + " AS " + column.asLabel() + " ,"); 567 } 568 // remove comma 569 this.sb.setLength(sb.length() - 1); 570 this.sb.append(" FROM"); 571 for (Table table : tables) { 572 this.sb.append(" " + table + " ,"); 573 } 574 // remove comma 575 this.sb.setLength(sb.length() - 1); 576 } 577 else { 578 throw new RuntimeException("Need atleast 1 Column !!"); 579 } 580 } 581 582 /** 583 * Set the condition for where clause 584 * 585 * @param condition condition for where clause 586 * @return <code>Select</code> for cascading 587 */ 588 public Select where(Condition condition) { 589 Select retVal = new Select(this); 590 retVal.condition = condition; 591 retVal.values.addAll(condition.values); 592 return retVal; 593 } 594 595 /** 596 * Sets the column to sort and the order of sort 597 * 598 * @param column column to sort 599 * @param order true = ascending 600 * @return <code>Select</code> for cascading 601 */ 602 public Select orderBy(Column column, boolean order) { 603 if (!isOdered) { 604 Select retVal = new Select(this); 605 retVal.orderby = new Column[]{column}; 606 retVal.isAscending = new boolean[]{order}; 607 retVal.isOdered = true; 608 return retVal; 609 } 610 return this; 611 } 612 613 /** 614 * To sort 2 columns 615 * 616 * @param column0 First column to be sorted 617 * @param order0 true = ascending 618 * @param column1 Second column to be sorted 619 * @param order1 true = ascending 620 * @return <code>Select</code> for cascading 621 */ 622 public Select orderBy(Column column0, boolean order0, Column column1, boolean order1) { 623 if (!isOdered) { 624 Select retVal = new Select(this); 625 retVal.orderby = new Column[]{column0, column1}; 626 retVal.isAscending = new boolean[]{order0, order1}; 627 retVal.isOdered = true; 628 return retVal; 629 } 630 return this; 631 } 632 633 /** 634 * Setting the offset and limit for LIMIT clause 635 * 636 * @param offset 637 * @param limit 638 * @return <code>Select</code> for cascading 639 */ 640 public Select limit(int offset, int limit) { 641 if (isOdered) { 642 Select retVal = new Select(this); 643 retVal.values.add(limit); 644 retVal.values.add(offset); 645 retVal.isLimitSet = true; 646 return retVal; 647 } 648 return this; 649 } 650 651 /** 652 * Set the "for update" flag to lock the rows for updating 653 * 654 * @return <code>Select</code> for cascading 655 */ 656 // TODO Not working for hsql 657 public Select forUpdate() { 658 Select retVal = new Select(this); 659 retVal.forUpdate = true; 660 return retVal; 661 } 662 663 /** 664 * Generate the SQL Select Statement with conditions and other clauses that were set 665 */ 666 public String toString() { 667 String oBy = ""; 668 if ((orderby != null) && (isAscending != null) && (orderby.length == isAscending.length)) { 669 StringBuffer osb = new StringBuffer(" ORDER BY "); 670 int i = 0; 671 for (Column column : orderby) { 672 osb.append(column.asLabel()).append(isAscending[i] ? " ASC ," : " DESC ,"); 673 } 674 osb.setLength(osb.length() - 1); 675 if (isLimitSet) { 676 osb.append("LIMIT ").append("?").append(" OFFSET ").append("?").append(" "); 677 } 678 oBy = osb.toString(); 679 } 680 return sb.toString() + ((condition != null) ? "WHERE " + condition.toString() : "") + oBy; 681 } 682 } 683 684 /** 685 * SQL Statement generator for DELETE Statements 686 */ 687 public static class Delete extends SqlStatement { 688 Condition condition; 689 final Table table; 690 691 Delete(Table table) { 692 this.table = table; 693 this.sb.append("DELETE FROM " + table + " "); 694 } 695 696 private Delete(Delete other) { 697 this.table = other.table; 698 this.condition = other.condition; 699 this.sb.append(other.sb); 700 this.values.addAll(other.values); 701 } 702 703 /** 704 * Set the where clause for DELETE 705 * 706 * @param condition condition for where clause 707 * @return <code>Delete</code> for cascading 708 */ 709 public Delete where(Condition condition) { 710 Delete retVal = new Delete(this); 711 retVal.condition = condition; 712 retVal.values.addAll(condition.values); 713 return retVal; 714 } 715 716 /** 717 * Return the DELETE Statement 718 */ 719 public String toString() { 720 return sb.toString() + ((condition != null) ? "WHERE " + condition.toString() : ""); 721 } 722 } 723 724 /** 725 * UPDATE SQL Statement generator 726 */ 727 public static class Update extends SqlStatement { 728 Condition condition; 729 final Table table; 730 731 Update(Table table) { 732 this.table = table; 733 this.sb.append("UPDATE " + table + " SET "); 734 } 735 736 private Update(Update other) { 737 this.table = other.table; 738 this.condition = other.condition; 739 this.sb.append(other.sb); 740 this.values.addAll(other.values); 741 } 742 743 /** 744 * SET clause for update statement 745 * 746 * @param column column name 747 * @param value A temporary place holder which can be replaced while preparing 748 * @return <code>Update</code> for cascading 749 */ 750 public Update set(Column column, Object value) { 751 Update retVal = new Update(this); 752 retVal.sb.append((values.isEmpty() ? "" : ", ") + column + " = ? "); 753 retVal.values.add(value); 754 return retVal; 755 } 756 757 /** 758 * Set condition for updating 759 * 760 * @param condition condition for where clause 761 * @return <code>Update</code> for cascading 762 */ 763 public Update where(Condition condition) { 764 Update retVal = new Update(this); 765 retVal.condition = condition; 766 retVal.values.addAll(condition.values); 767 return retVal; 768 } 769 770 /** 771 * Return the UPDATE statement 772 */ 773 public String toString() { 774 return sb.toString() + ((condition != null) ? " WHERE " + condition.toString() : ""); 775 } 776 } 777 778 /** 779 * INSERT Statement generator 780 */ 781 public static class Insert extends SqlStatement { 782 StringBuilder sbCol = new StringBuilder(""); 783 StringBuilder sbVal = new StringBuilder(""); 784 boolean isFirst = true; 785 final Table table; 786 787 Insert(Table table) { 788 this.table = table; 789 this.sbCol.append("INSERT INTO " + table + " ( )"); 790 this.sbVal.append("VALUES ( )"); 791 } 792 793 private Insert(Insert other) { 794 this.table = other.table; 795 this.sbCol.append(other.sbCol); 796 this.sbVal.append(other.sbVal); 797 this.values.addAll(other.values); 798 if (other.isFirst) { 799 this.isFirst = false; 800 } 801 } 802 803 /** 804 * Set the VALUES that are to be inserted 805 * 806 * @param column 807 * @param value A temporary place holder which will be replaced while preparing 808 * @return 809 */ 810 public Insert value(Column column, Object value) { 811 Insert retVal = new Insert(this); 812 retVal.sbCol.setLength(retVal.sbCol.length() - 1); 813 retVal.sbVal.setLength(retVal.sbVal.length() - 1); 814 retVal.values.add(value); 815 retVal.sbCol.append(((isFirst) ? "" : ", ") + column + " )"); 816 retVal.sbVal.append(((isFirst) ? "" : ", ") + "? )"); 817 retVal.isFirst = false; 818 return retVal; 819 } 820 821 /** 822 * Return the INSERT Statement 823 */ 824 public String toString() { 825 return sbCol.toString() + " " + sbVal.toString(); 826 } 827 } 828 829 /** 830 * Prepare the SQL Statement that is generated and assign the values to prepared statement. setValues should be 831 * called to set the Real Values for place holders 832 * 833 * @param conn Connection 834 * @return Prepared SQL Statement 835 * @throws SQLException 836 */ 837 public PreparedStatement prepareAndSetValues(Connection conn) throws SQLException { 838 String stmt = toString(); 839 if (forUpdate && !Schema.isHsqlConnection(conn)) { 840 stmt += " FOR UPDATE"; 841 } 842 PreparedStatement pStmt = conn.prepareStatement(stmt); 843 int i = 1; 844 for (Object value : this.values) { 845 pStmt.setObject(i, value); 846 i++; 847 } 848 log.trace(XLog.Info.get().createPrefix() + " Preparing : " + stmt); 849 log.trace(XLog.Info.get().createPrefix() + " Values : " + values); 850 return pStmt; 851 } 852 853 /** 854 * Assign the values to Prepared Statement. setValues should be called to set the Real Values for place holders 855 * 856 * @param pStmt Prepared Statement 857 * @return PreparedStatement with values set 858 * @throws SQLException 859 */ 860 public PreparedStatement prepare(PreparedStatement pStmt) throws SQLException { 861 int i = 1; 862 pStmt.clearParameters(); 863 for (Object value : this.values) { 864 pStmt.setObject(i, value); 865 i++; 866 } 867 return pStmt; 868 } 869 870 /** 871 * Prepare the SQL Statement. Doesn't set the values. 872 * 873 * @param conn Connection 874 * @return PreparedStatement 875 * @throws SQLException 876 */ 877 public PreparedStatement prepare(Connection conn) throws SQLException { 878 String stmt = toString(); 879 if (forUpdate && !Schema.isHsqlConnection(conn)) { 880 stmt += " FOR UPDATE"; 881 } 882 return conn.prepareStatement(stmt); 883 } 884 885 /** 886 * Preparing Multiple statements for batch execution. 887 * 888 * @param conn Connection 889 * @param values A list of maps that contains the actual values 890 * @return Prepared Statement 891 * @throws SQLException 892 */ 893 public PreparedStatement prepareForBatch(Connection conn, List<? extends Map<Object, Object>> values, 894 PreparedStatement pStmt) throws SQLException { 895 String stmt = toString(); 896 if (forUpdate && !Schema.isHsqlConnection(conn)) { 897 stmt += " FOR UPDATE"; 898 } 899 // PreparedStatement pStmt = conn.prepareStatement(stmt); 900 for (Map<Object, Object> map : values) { 901 getNewStatementWithValues(map).prepare(pStmt); 902 pStmt.addBatch(); 903 } 904 return pStmt; 905 } 906 907 /** 908 * Replace the place holders with actual values in the sql statement 909 * 910 * @param oldVal Place holder 911 * @param newVal Actual Value 912 * @return SQL Statement with oldVal(place holder) replaced with newVal(actual value) 913 */ 914 public SqlStatement setValue(Object oldVal, Object newVal) { 915 ArrayList<Object> temp = new ArrayList<Object>(values); 916 if (values.contains(oldVal)) { 917 int i = temp.indexOf(oldVal); 918 temp.set(i, newVal); 919 } 920 SqlStatement retVal = create(temp); 921 return retVal; 922 } 923 924 /** 925 * Replace the keys(newValues) which are place holders in the sql statements with the corresponding new values. And 926 * Gives back a new SQL Statement so that the actual statement can be re-used 927 * 928 * @param newValues 929 * @return A New SQL Statement object with actual values set in its member 930 */ 931 public SqlStatement getNewStatementWithValues(Map<Object, Object> newValues) { 932 ArrayList<Object> temp = new ArrayList<Object>(); 933 for (Object value : values) { 934 if (newValues.containsKey(value)) { 935 temp.add(newValues.get(value)); 936 } 937 else { 938 temp.add(value); 939 } 940 } 941 SqlStatement retVal = create(temp); 942 return retVal; 943 } 944 945 /** 946 * Create the Appropriate SQL Statement with the given values 947 * 948 * @param temp 949 * @return 950 */ 951 private SqlStatement create(ArrayList<Object> temp) { 952 SqlStatement retVal = null; 953 if (this instanceof Select) { 954 retVal = new Select((Select) this); 955 } 956 else { 957 if (this instanceof Insert) { 958 retVal = new Insert((Insert) this); 959 } 960 else { 961 if (this instanceof Update) { 962 retVal = new Update((Update) this); 963 } 964 else { 965 if (this instanceof Delete) { 966 retVal = new Delete((Delete) this); 967 } 968 } 969 } 970 } 971 retVal.values.clear(); 972 retVal.values.addAll(temp); 973 return retVal; 974 } 975 976 /** 977 * Create the <code>ResultSetReader</code> object that has the methods to access the data from the result set 978 * 979 * @param rSet Result Set 980 * @return ResultSet Reader 981 */ 982 public static ResultSetReader parse(ResultSet rSet) { 983 return new ResultSetReader(rSet); 984 } 985 986 /** 987 * Return a new Insert Statement 988 * 989 * @param table 990 * @return Insert statement 991 */ 992 public static Insert insertInto(Table table) { 993 return new Insert(table); 994 } 995 996 /** 997 * Return a new Update Statement 998 * 999 * @param table 1000 * @return Update statement 1001 */ 1002 public static Update update(Table table) { 1003 return new Update(table); 1004 } 1005 1006 /** 1007 * Return a new Delete Statement 1008 * 1009 * @param table 1010 * @return Delete Statement 1011 */ 1012 public static Delete deleteFrom(Table table) { 1013 return new Delete(table); 1014 } 1015 1016 /** 1017 * Return a Select All Statement 1018 * 1019 * @param tables 1020 * @return Select * statement 1021 */ 1022 public static Select selectAllFrom(Table... tables) { 1023 return new Select(false, tables); 1024 } 1025 1026 /** 1027 * Return a Select Statement 1028 * 1029 * @param columns columns to select 1030 * @return select statement 1031 */ 1032 public static Select selectColumns(Column... columns) { 1033 return new Select(columns); 1034 } 1035 1036 /** 1037 * Select count(*) Statement generator. 1038 * 1039 * @param tables 1040 * @return "select count(*) from tables" statement 1041 */ 1042 public static Select getCount(Table... tables) { 1043 return new Select(true, tables); 1044 } 1045}