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    }