Java > Open Source Codes > org > apache > tools > ant > taskdefs > SQLExec


1 /*
2  * Licensed to the Apache Software Foundation (ASF) under one or more
3  * contributor license agreements. See the NOTICE file distributed with
4  * this work for additional information regarding copyright ownership.
5  * The ASF licenses this file to You under the Apache License, Version 2.0
6  * (the "License"); you may not use this file except in compliance with
7  * the License. You may obtain a copy of the License at
8  *
9  * http://www.apache.org/licenses/LICENSE-2.0
10  *
11  * Unless required by applicable law or agreed to in writing, software
12  * distributed under the License is distributed on an "AS IS" BASIS,
13  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14  * See the License for the specific language governing permissions and
15  * limitations under the License.
16  *
17  */
18
19 package org.apache.tools.ant.taskdefs;
20
21 import org.apache.tools.ant.BuildException;
22 import org.apache.tools.ant.Project;
23 import org.apache.tools.ant.util.FileUtils;
24 import org.apache.tools.ant.util.StringUtils;
25 import org.apache.tools.ant.types.EnumeratedAttribute;
26 import org.apache.tools.ant.types.FileSet;
27 import org.apache.tools.ant.types.Resource;
28 import org.apache.tools.ant.types.ResourceCollection;
29 import org.apache.tools.ant.types.resources.FileResource;
30 import org.apache.tools.ant.types.resources.Union;
31
32 import java.io.File ;
33 import java.io.PrintStream ;
34 import java.io.BufferedOutputStream ;
35 import java.io.FileOutputStream ;
36 import java.io.IOException ;
37 import java.io.Reader ;
38 import java.io.BufferedReader ;
39 import java.io.StringReader ;
40 import java.io.InputStream ;
41 import java.io.InputStreamReader ;
42 import java.util.Enumeration ;
43 import java.util.Iterator ;
44 import java.util.StringTokenizer ;
45 import java.util.Vector ;
46
47 import java.sql.Connection ;
48 import java.sql.Statement ;
49 import java.sql.SQLException ;
50 import java.sql.SQLWarning ;
51 import java.sql.ResultSet ;
52 import java.sql.ResultSetMetaData ;
53
54 /**
55  * Executes a series of SQL statements on a database using JDBC.
56  *
57  * <p>Statements can
58  * either be read in from a text file using the <i>src</i> attribute or from
59  * between the enclosing SQL tags.</p>
60  *
61  * <p>Multiple statements can be provided, separated by semicolons (or the
62  * defined <i>delimiter</i>). Individual lines within the statements can be
63  * commented using either --, // or REM at the start of the line.</p>
64  *
65  * <p>The <i>autocommit</i> attribute specifies whether auto-commit should be
66  * turned on or off whilst executing the statements. If auto-commit is turned
67  * on each statement will be executed and committed. If it is turned off the
68  * statements will all be executed as one transaction.</p>
69  *
70  * <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
71  * during the execution of one of the statements.
72  * The possible values are: <b>continue</b> execution, only show the error;
73  * <b>stop</b> execution and commit transaction;
74  * and <b>abort</b> execution and transaction and fail task.</p>
75  *
76  * @since Ant 1.2
77  *
78  * @ant.task name="sql" category="database"
79  */
80 public class SQLExec extends JDBCTask {
81
82     /**
83      * delimiters we support, "normal" and "row"
84      */
85     public static class DelimiterType extends EnumeratedAttribute {
86         /** The enumerated strings */
87         public static final String NORMAL = "normal", ROW = "row";
88         /** @return the enumerated strings */
89         public String [] getValues() {
90             return new String [] {NORMAL, ROW};
91         }
92     }
93
94     private int goodSql = 0;
95
96     private int totalSql = 0;
97
98     /**
99      * Database connection
100      */
101     private Connection conn = null;
102
103     /**
104      * files to load
105      */
106     private Union resources = new Union();
107
108     /**
109      * SQL statement
110      */
111     private Statement statement = null;
112
113     /**
114      * SQL input file
115      */
116     private File srcFile = null;
117
118     /**
119      * SQL input command
120      */
121     private String sqlCommand = "";
122
123     /**
124      * SQL transactions to perform
125      */
126     private Vector transactions = new Vector ();
127
128     /**
129      * SQL Statement delimiter
130      */
131     private String delimiter = ";";
132
133     /**
134      * The delimiter type indicating whether the delimiter will
135      * only be recognized on a line by itself
136      */
137     private String delimiterType = DelimiterType.NORMAL;
138
139     /**
140      * Print SQL results.
141      */
142     private boolean print = false;
143
144     /**
145      * Print header columns.
146      */
147     private boolean showheaders = true;
148
149     /**
150      * Print SQL stats (rows affected)
151      */
152     private boolean showtrailers = true;
153
154     /**
155      * Results Output file.
156      */
157     private File output = null;
158
159
160     /**
161      * Action to perform if an error is found
162      **/
163     private String onError = "abort";
164
165     /**
166      * Encoding to use when reading SQL statements from a file
167      */
168     private String encoding = null;
169
170     /**
171      * Append to an existing file or overwrite it?
172      */
173     private boolean append = false;
174
175     /**
176      * Keep the format of a sql block?
177      */
178     private boolean keepformat = false;
179
180     /**
181      * Argument to Statement.setEscapeProcessing
182      *
183      * @since Ant 1.6
184      */
185     private boolean escapeProcessing = true;
186
187     /**
188      * should properties be expanded in text?
189      * false for backwards compatibility
190      *
191      * @since Ant 1.7
192      */
193     private boolean expandProperties = false;
194
195     /**
196      * Set the name of the SQL file to be run.
197      * Required unless statements are enclosed in the build file
198      * @param srcFile the file containing the SQL command.
199      */
200     public void setSrc(File srcFile) {
201         this.srcFile = srcFile;
202     }
203
204     /**
205      * Enable property expansion inside nested text
206      *
207      * @param expandProperties if true expand properties.
208      * @since Ant 1.7
209      */
210     public void setExpandProperties(boolean expandProperties) {
211         this.expandProperties = expandProperties;
212     }
213
214     /**
215      * is property expansion inside inline text enabled?
216      *
217      * @return true if properties are to be expanded.
218      * @since Ant 1.7
219      */
220     public boolean getExpandProperties() {
221         return expandProperties;
222     }
223
224     /**
225      * Set an inline SQL command to execute.
226      * NB: Properties are not expanded in this text unless {@link #expandProperties}
227      * is set.
228      * @param sql an inline string containing the SQL command.
229      */
230     public void addText(String sql) {
231         //there is no need to expand properties here as that happens when Transaction.addText is
232 //called; to do so here would be an error.
233 this.sqlCommand += sql;
234     }
235
236     /**
237      * Adds a set of files (nested fileset attribute).
238      * @param set a set of files contains SQL commands, each File is run in
239      * a separate transaction.
240      */
241     public void addFileset(FileSet set) {
242         add(set);
243     }
244
245     /**
246      * Adds a collection of resources (nested element).
247      * @param rc a collection of resources containing SQL commands,
248      * each resource is run in a separate transaction.
249      * @since Ant 1.7
250      */
251     public void add(ResourceCollection rc) {
252         resources.add(rc);
253     }
254
255     /**
256      * Add a SQL transaction to execute
257      * @return a Transaction to be configured.
258      */
259     public Transaction createTransaction() {
260         Transaction t = new Transaction();
261         transactions.addElement(t);
262         return t;
263     }
264
265     /**
266      * Set the file encoding to use on the SQL files read in
267      *
268      * @param encoding the encoding to use on the files
269      */
270     public void setEncoding(String encoding) {
271         this.encoding = encoding;
272     }
273
274     /**
275      * Set the delimiter that separates SQL statements. Defaults to &quot;;&quot;;
276      * optional
277      *
278      * <p>For example, set this to "go" and delimitertype to "ROW" for
279      * Sybase ASE or MS SQL Server.</p>
280      * @param delimiter the separator.
281      */
282     public void setDelimiter(String delimiter) {
283         this.delimiter = delimiter;
284     }
285
286     /**
287      * Set the delimiter type: "normal" or "row" (default "normal").
288      *
289      * <p>The delimiter type takes two values - normal and row. Normal
290      * means that any occurrence of the delimiter terminate the SQL
291      * command whereas with row, only a line containing just the
292      * delimiter is recognized as the end of the command.</p>
293      * @param delimiterType the type of delimiter - "normal" or "row".
294      */
295     public void setDelimiterType(DelimiterType delimiterType) {
296         this.delimiterType = delimiterType.getValue();
297     }
298
299     /**
300      * Print result sets from the statements;
301      * optional, default false
302      * @param print if true print result sets.
303      */
304     public void setPrint(boolean print) {
305         this.print = print;
306     }
307
308     /**
309      * Print headers for result sets from the
310      * statements; optional, default true.
311      * @param showheaders if true print headers of result sets.
312      */
313     public void setShowheaders(boolean showheaders) {
314         this.showheaders = showheaders;
315     }
316
317     /**
318      * Print trailing info (rows affected) for the SQL
319      * Addresses Bug/Request #27446
320      * @param showtrailers if true prints the SQL rows affected
321      * @since Ant 1.7
322      */
323     public void setShowtrailers(boolean showtrailers) {
324         this.showtrailers = showtrailers;
325     }
326
327     /**
328      * Set the output file;
329      * optional, defaults to the Ant log.
330      * @param output the output file to use for logging messages.
331      */
332     public void setOutput(File output) {
333         this.output = output;
334     }
335
336     /**
337      * whether output should be appended to or overwrite
338      * an existing file. Defaults to false.
339      *
340      * @since Ant 1.5
341      * @param append if true append to an existing file.
342      */
343     public void setAppend(boolean append) {
344         this.append = append;
345     }
346
347
348     /**
349      * Action to perform when statement fails: continue, stop, or abort
350      * optional; default &quot;abort&quot;
351      * @param action the action to perform on statement failure.
352      */
353     public void setOnerror(OnError action) {
354         this.onError = action.getValue();
355     }
356
357     /**
358      * whether or not format should be preserved.
359      * Defaults to false.
360      *
361      * @param keepformat The keepformat to set
362      */
363     public void setKeepformat(boolean keepformat) {
364         this.keepformat = keepformat;
365     }
366
367     /**
368      * Set escape processing for statements.
369      * @param enable if true enable escape processing, default is true.
370      * @since Ant 1.6
371      */
372     public void setEscapeProcessing(boolean enable) {
373         escapeProcessing = enable;
374     }
375
376     /**
377      * Load the sql file and then execute it
378      * @throws BuildException on error.
379      */
380     public void execute() throws BuildException {
381         Vector savedTransaction = (Vector ) transactions.clone();
382         String savedSqlCommand = sqlCommand;
383
384         sqlCommand = sqlCommand.trim();
385
386         try {
387             if (srcFile == null && sqlCommand.length() == 0
388                 && resources.size() == 0) {
389                 if (transactions.size() == 0) {
390                     throw new BuildException("Source file or resource "
391                                              + "collection, "
392                                              + "transactions or sql statement "
393                                              + "must be set!", getLocation());
394                 }
395             }
396
397             if (srcFile != null && !srcFile.exists()) {
398                 throw new BuildException("Source file does not exist!", getLocation());
399             }
400
401             // deal with the resources
402 Iterator iter = resources.iterator();
403             while (iter.hasNext()) {
404                 Resource r = (Resource) iter.next();
405                 // Make a transaction for each resource
406 Transaction t = createTransaction();
407                 t.setSrcResource(r);
408             }
409
410             // Make a transaction group for the outer command
411 Transaction t = createTransaction();
412             t.setSrc(srcFile);
413             t.addText(sqlCommand);
414             conn = getConnection();
415             if (!isValidRdbms(conn)) {
416                 return;
417             }
418             try {
419                 statement = conn.createStatement();
420                 statement.setEscapeProcessing(escapeProcessing);
421
422                 PrintStream out = System.out;
423                 try {
424                     if (output != null) {
425                         log("Opening PrintStream to output file " + output,
426                             Project.MSG_VERBOSE);
427                         out = new PrintStream (
428                                   new BufferedOutputStream (
429                                       new FileOutputStream (output
430                                                            .getAbsolutePath(),
431                                                            append)));
432                     }
433
434                     // Process all transactions
435 for (Enumeration e = transactions.elements();
436                          e.hasMoreElements();) {
437
438                         ((Transaction) e.nextElement()).runTransaction(out);
439                         if (!isAutocommit()) {
440                             log("Committing transaction", Project.MSG_VERBOSE);
441                             conn.commit();
442                         }
443                     }
444                 } finally {
445                     if (out != null && out != System.out) {
446                         out.close();
447                     }
448                 }
449             } catch (IOException e) {
450                 closeQuietly();
451                 throw new BuildException(e, getLocation());
452             } catch (SQLException e) {
453                 closeQuietly();
454                 throw new BuildException(e, getLocation());
455             } finally {
456                 try {
457                     if (statement != null) {
458                         statement.close();
459                     }
460                     if (conn != null) {
461                         conn.close();
462                     }
463                 } catch (SQLException ex) {
464                     // ignore
465 }
466             }
467
468             log(goodSql + " of " + totalSql
469                 + " SQL statements executed successfully");
470         } finally {
471             transactions = savedTransaction;
472             sqlCommand = savedSqlCommand;
473         }
474     }
475
476     /**
477      * read in lines and execute them
478      * @param reader the reader contains sql lines.
479      * @param out the place to output results.
480      * @throws SQLException on sql problems
481      * @throws IOException on io problems
482      */
483     protected void runStatements(Reader reader, PrintStream out)
484         throws SQLException , IOException {
485         StringBuffer sql = new StringBuffer ();
486         String line;
487
488         BufferedReader in = new BufferedReader (reader);
489
490         while ((line = in.readLine()) != null) {
491             if (!keepformat) {
492                 line = line.trim();
493             }
494             line = getProject().replaceProperties(line);
495             if (!keepformat) {
496                 if (line.startsWith("//")) {
497                     continue;
498                 }
499                 if (line.startsWith("--")) {
500                     continue;
501                 }
502                 StringTokenizer st = new StringTokenizer (line);
503                 if (st.hasMoreTokens()) {
504                     String token = st.nextToken();
505                     if ("REM".equalsIgnoreCase(token)) {
506                         continue;
507                     }
508                 }
509             }
510
511             if (!keepformat) {
512                 sql.append(" ");
513                 sql.append(line);
514             } else {
515                 sql.append("\n");
516                 sql.append(line);
517             }
518
519             // SQL defines "--" as a comment to EOL
520 // and in Oracle it may contain a hint
521 // so we cannot just remove it, instead we must end it
522 if (!keepformat) {
523                 if (line.indexOf("--") >= 0) {
524                     sql.append("\n");
525                 }
526             }
527             if ((delimiterType.equals(DelimiterType.NORMAL)
528                  && StringUtils.endsWith(sql, delimiter))
529                 ||
530                 (delimiterType.equals(DelimiterType.ROW)
531                  && line.equals(delimiter))) {
532                 execSQL(sql.substring(0, sql.length() - delimiter.length()),
533                         out);
534                 sql.replace(0, sql.length(), "");
535             }
536         }
537         // Catch any statements not followed by ;
538 if (sql.length() > 0) {
539             execSQL(sql.toString(), out);
540         }
541     }
542
543
544     /**
545      * Exec the sql statement.
546      * @param sql the SQL statement to execute
547      * @param out the place to put output
548      * @throws SQLException on SQL problems
549      */
550     protected void execSQL(String sql, PrintStream out) throws SQLException {
551         // Check and ignore empty statements
552 if ("".equals(sql.trim())) {
553             return;
554         }
555
556         ResultSet resultSet = null;
557         try {
558             totalSql++;
559             log("SQL: " + sql, Project.MSG_VERBOSE);
560
561             boolean ret;
562             int updateCount = 0, updateCountTotal = 0;
563
564             ret = statement.execute(sql);
565             updateCount = statement.getUpdateCount();
566             resultSet = statement.getResultSet();
567             do {
568                 if (!ret) {
569                     if (updateCount != -1) {
570                         updateCountTotal += updateCount;
571                     }
572                 } else {
573                     if (print) {
574                         printResults(resultSet, out);
575                     }
576                 }
577                 ret = statement.getMoreResults();
578                 if (ret) {
579                     updateCount = statement.getUpdateCount();
580                     resultSet = statement.getResultSet();
581                 }
582             } while (ret);
583
584             log(updateCountTotal + " rows affected",
585                 Project.MSG_VERBOSE);
586
587             if (print && showtrailers) {
588                 out.println(updateCountTotal + " rows affected");
589             }
590
591             SQLWarning warning = conn.getWarnings();
592             while (warning != null) {
593                 log(warning + " sql warning", Project.MSG_VERBOSE);
594                 warning = warning.getNextWarning();
595             }
596             conn.clearWarnings();
597             goodSql++;
598         } catch (SQLException e) {
599             log("Failed to execute: " + sql, Project.MSG_ERR);
600             if (!onError.equals("continue")) {
601                 throw e;
602             }
603             log(e.toString(), Project.MSG_ERR);
604         } finally {
605             if (resultSet != null) {
606                 resultSet.close();
607             }
608         }
609     }
610
611     /**
612      * print any results in the statement
613      * @deprecated since 1.6.x.
614      * Use {@link #printResults(java.sql.ResultSet, java.io.PrintStream)
615      * the two arg version} instead.
616      * @param out the place to print results
617      * @throws SQLException on SQL problems.
618      */
619     protected void printResults(PrintStream out) throws SQLException {
620         ResultSet rs = statement.getResultSet();
621         try {
622             printResults(rs, out);
623         } finally {
624             if (rs != null) {
625                 rs.close();
626             }
627         }
628     }
629
630     /**
631      * print any results in the result set.
632      * @param rs the resultset to print information about
633      * @param out the place to print results
634      * @throws SQLException on SQL problems.
635      * @since Ant 1.6.3
636      */
637     protected void printResults(ResultSet rs, PrintStream out)
638         throws SQLException {
639         if (rs != null) {
640             log("Processing new result set.", Project.MSG_VERBOSE);
641             ResultSetMetaData md = rs.getMetaData();
642             int columnCount = md.getColumnCount();
643             StringBuffer line = new StringBuffer ();
644             if (showheaders) {
645                 for (int col = 1; col < columnCount; col++) {
646                      line.append(md.getColumnName(col));
647                      line.append(",");
648                 }
649                 line.append(md.getColumnName(columnCount));
650                 out.println(line);
651                 line = new StringBuffer ();
652             }
653             while (rs.next()) {
654                 boolean first = true;
655                 for (int col = 1; col <= columnCount; col++) {
656                     String columnValue = rs.getString(col);
657                     if (columnValue != null) {
658                         columnValue = columnValue.trim();
659                     }
660
661                     if (first) {
662                         first = false;
663                     } else {
664                         line.append(",");
665                     }
666                     line.append(columnValue);
667                 }
668                 out.println(line);
669                 line = new StringBuffer ();
670             }
671         }
672         out.println();
673     }
674
675     /*
676      * Closes an unused connection after an error and doesn't rethrow
677      * a possible SQLException
678      * @since Ant 1.7
679      */
680     private void closeQuietly() {
681         if (!isAutocommit() && conn != null && onError.equals("abort")) {
682             try {
683                 conn.rollback();
684             } catch (SQLException ex) {
685                 // ignore
686 }
687         }
688     }
689
690     /**
691      * The action a task should perform on an error,
692      * one of "continue", "stop" and "abort"
693      */
694     public static class OnError extends EnumeratedAttribute {
695         /** @return the enumerated values */
696         public String [] getValues() {
697             return new String [] {"continue", "stop", "abort"};
698         }
699     }
700
701     /**
702      * Contains the definition of a new transaction element.
703      * Transactions allow several files or blocks of statements
704      * to be executed using the same JDBC connection and commit
705      * operation in between.
706      */
707     public class Transaction {
708         private Resource tSrcResource = null;
709         private String tSqlCommand = "";
710
711         /**
712          * Set the source file attribute.
713          * @param src the source file
714          */
715         public void setSrc(File src) {
716             //there are places (in this file, and perhaps elsewhere, where it is assumed
717 //that null is an acceptable parameter.
718 if (src != null) {
719                 setSrcResource(new FileResource(src));
720             }
721         }
722
723         /**
724          * Set the source resource attribute.
725          * @param src the source file
726          * @since Ant 1.7
727          */
728         public void setSrcResource(Resource src) {
729             if (tSrcResource != null) {
730                 throw new BuildException("only one resource per transaction");
731             }
732             tSrcResource = src;
733         }
734
735         /**
736          * Set inline text
737          * @param sql the inline text
738          */
739         public void addText(String sql) {
740             if (sql != null) {
741                 if (getExpandProperties()) {
742                     sql = getProject().replaceProperties(sql);
743                 }
744                 this.tSqlCommand += sql;
745             }
746         }
747
748         /**
749          * Set the source resource.
750          * @param a the source resource collection.
751          * @since Ant 1.7
752          */
753         public void addConfigured(ResourceCollection a) {
754             if (a.size() != 1) {
755                 throw new BuildException("only single argument resource "
756                                          + "collections are supported.");
757             }
758             setSrcResource((Resource) a.iterator().next());
759         }
760
761         /**
762          *
763          */
764         private void runTransaction(PrintStream out)
765             throws IOException , SQLException {
766             if (tSqlCommand.length() != 0) {
767                 log("Executing commands", Project.MSG_INFO);
768                 runStatements(new StringReader (tSqlCommand), out);
769             }
770
771             if (tSrcResource != null) {
772                 log("Executing resource: " + tSrcResource.toString(),
773                     Project.MSG_INFO);
774                 InputStream is = null;
775                 Reader reader = null;
776                 try {
777                     is = tSrcResource.getInputStream();
778                     reader =
779                         (encoding == null) ? new InputStreamReader (is)
780                         : new InputStreamReader (is, encoding);
781                     runStatements(reader, out);
782                 } finally {
783                     FileUtils.close(is);
784                     FileUtils.close(reader);
785                 }
786             }
787         }
788     }
789 }
790

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags