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