org.apache.tools.ant.taskdefs.SQLExec Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of testatoo-container-jetty-full Show documentation
Show all versions of testatoo-container-jetty-full Show documentation
Testatoo Jetty Container with JSP support
/*
* Copyright 2000-2005 The Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
*/
package org.apache.tools.ant.taskdefs;
import org.apache.tools.ant.BuildException;
import org.apache.tools.ant.DirectoryScanner;
import org.apache.tools.ant.Project;
import org.apache.tools.ant.types.EnumeratedAttribute;
import org.apache.tools.ant.types.FileSet;
import java.io.File;
import java.io.PrintStream;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.io.BufferedReader;
import java.io.StringReader;
import java.io.FileReader;
import java.io.InputStreamReader;
import java.io.FileInputStream;
import java.util.Enumeration;
import java.util.StringTokenizer;
import java.util.Vector;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/**
* Executes a series of SQL statements on a database using JDBC.
*
* Statements can
* either be read in from a text file using the src attribute or from
* between the enclosing SQL tags.
*
* Multiple statements can be provided, separated by semicolons (or the
* defined delimiter). Individual lines within the statements can be
* commented using either --, // or REM at the start of the line.
*
* The autocommit attribute specifies whether auto-commit should be
* turned on or off whilst executing the statements. If auto-commit is turned
* on each statement will be executed and committed. If it is turned off the
* statements will all be executed as one transaction.
*
* The onerror attribute specifies how to proceed when an error occurs
* during the execution of one of the statements.
* The possible values are: continue execution, only show the error;
* stop execution and commit transaction;
* and abort execution and transaction and fail task.
*
* @since Ant 1.2
*
* @ant.task name="sql" category="database"
*/
public class SQLExec extends JDBCTask {
/**
* delimiters we support, "normal" and "row"
*/
public static class DelimiterType extends EnumeratedAttribute {
public static final String NORMAL = "normal";
public static final String ROW = "row";
public String[] getValues() {
return new String[] {NORMAL, ROW};
}
}
private int goodSql = 0;
private int totalSql = 0;
/**
* Database connection
*/
private Connection conn = null;
/**
* files to load
*/
private Vector filesets = new Vector();
/**
* SQL statement
*/
private Statement statement = null;
/**
* SQL input file
*/
private File srcFile = null;
/**
* SQL input command
*/
private String sqlCommand = "";
/**
* SQL transactions to perform
*/
private Vector transactions = new Vector();
/**
* SQL Statement delimiter
*/
private String delimiter = ";";
/**
* The delimiter type indicating whether the delimiter will
* only be recognized on a line by itself
*/
private String delimiterType = DelimiterType.NORMAL;
/**
* Print SQL results.
*/
private boolean print = false;
/**
* Print header columns.
*/
private boolean showheaders = true;
/**
* Results Output file.
*/
private File output = null;
/**
* Action to perform if an error is found
**/
private String onError = "abort";
/**
* Encoding to use when reading SQL statements from a file
*/
private String encoding = null;
/**
* Append to an existing file or overwrite it?
*/
private boolean append = false;
/**
* Keep the format of a sql block?
*/
private boolean keepformat = false;
/**
* Argument to Statement.setEscapeProcessing
*
* @since Ant 1.6
*/
private boolean escapeProcessing = true;
/**
* Set the name of the SQL file to be run.
* Required unless statements are enclosed in the build file
*/
public void setSrc(File srcFile) {
this.srcFile = srcFile;
}
/**
* Set an inline SQL command to execute.
* NB: Properties are not expanded in this text.
*/
public void addText(String sql) {
this.sqlCommand += sql;
}
/**
* Adds a set of files (nested fileset attribute).
*/
public void addFileset(FileSet set) {
filesets.addElement(set);
}
/**
* Add a SQL transaction to execute
*/
public Transaction createTransaction() {
Transaction t = new Transaction();
transactions.addElement(t);
return t;
}
/**
* Set the file encoding to use on the SQL files read in
*
* @param encoding the encoding to use on the files
*/
public void setEncoding(String encoding) {
this.encoding = encoding;
}
/**
* Set the delimiter that separates SQL statements. Defaults to ";";
* optional
*
* For example, set this to "go" and delimitertype to "ROW" for
* Sybase ASE or MS SQL Server.
*/
public void setDelimiter(String delimiter) {
this.delimiter = delimiter;
}
/**
* Set the delimiter type: "normal" or "row" (default "normal").
*
* The delimiter type takes two values - normal and row. Normal
* means that any occurrence of the delimiter terminate the SQL
* command whereas with row, only a line containing just the
* delimiter is recognized as the end of the command.
*/
public void setDelimiterType(DelimiterType delimiterType) {
this.delimiterType = delimiterType.getValue();
}
/**
* Print result sets from the statements;
* optional, default false
*/
public void setPrint(boolean print) {
this.print = print;
}
/**
* Print headers for result sets from the
* statements; optional, default true.
*/
public void setShowheaders(boolean showheaders) {
this.showheaders = showheaders;
}
/**
* Set the output file;
* optional, defaults to the Ant log.
*/
public void setOutput(File output) {
this.output = output;
}
/**
* whether output should be appended to or overwrite
* an existing file. Defaults to false.
*
* @since Ant 1.5
*/
public void setAppend(boolean append) {
this.append = append;
}
/**
* Action to perform when statement fails: continue, stop, or abort
* optional; default "abort"
*/
public void setOnerror(OnError action) {
this.onError = action.getValue();
}
/**
* whether or not format should be preserved.
* Defaults to false.
*
* @param keepformat The keepformat to set
*/
public void setKeepformat(boolean keepformat) {
this.keepformat = keepformat;
}
/**
* Set escape processing for statements.
*
* @since Ant 1.6
*/
public void setEscapeProcessing(boolean enable) {
escapeProcessing = enable;
}
/**
* Load the sql file and then execute it
*/
public void execute() throws BuildException {
Vector savedTransaction = (Vector) transactions.clone();
String savedSqlCommand = sqlCommand;
sqlCommand = sqlCommand.trim();
try {
if (srcFile == null && sqlCommand.length() == 0
&& filesets.isEmpty()) {
if (transactions.size() == 0) {
throw new BuildException("Source file or fileset, "
+ "transactions or sql statement "
+ "must be set!", getLocation());
}
}
if (srcFile != null && !srcFile.exists()) {
throw new BuildException("Source file does not exist!", getLocation());
}
// deal with the filesets
for (int i = 0; i < filesets.size(); i++) {
FileSet fs = (FileSet) filesets.elementAt(i);
DirectoryScanner ds = fs.getDirectoryScanner(getProject());
File srcDir = fs.getDir(getProject());
String[] srcFiles = ds.getIncludedFiles();
// Make a transaction for each file
for (int j = 0; j < srcFiles.length; j++) {
Transaction t = createTransaction();
t.setSrc(new File(srcDir, srcFiles[j]));
}
}
// Make a transaction group for the outer command
Transaction t = createTransaction();
t.setSrc(srcFile);
t.addText(sqlCommand);
conn = getConnection();
if (!isValidRdbms(conn)) {
return;
}
try {
statement = conn.createStatement();
statement.setEscapeProcessing(escapeProcessing);
PrintStream out = System.out;
try {
if (output != null) {
log("Opening PrintStream to output file " + output,
Project.MSG_VERBOSE);
out = new PrintStream(
new BufferedOutputStream(
new FileOutputStream(output
.getAbsolutePath(),
append)));
}
// Process all transactions
for (Enumeration e = transactions.elements();
e.hasMoreElements();) {
((Transaction) e.nextElement()).runTransaction(out);
if (!isAutocommit()) {
log("Committing transaction", Project.MSG_VERBOSE);
conn.commit();
}
}
} finally {
if (out != null && out != System.out) {
out.close();
}
}
} catch (IOException e) {
if (!isAutocommit() && conn != null && onError.equals("abort")) {
try {
conn.rollback();
} catch (SQLException ex) {
// ignore
}
}
throw new BuildException(e, getLocation());
} catch (SQLException e) {
if (!isAutocommit() && conn != null && onError.equals("abort")) {
try {
conn.rollback();
} catch (SQLException ex) {
// ignore
}
}
throw new BuildException(e, getLocation());
} finally {
try {
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
// ignore
}
}
log(goodSql + " of " + totalSql
+ " SQL statements executed successfully");
} finally {
transactions = savedTransaction;
sqlCommand = savedSqlCommand;
}
}
/**
* read in lines and execute them
*/
protected void runStatements(Reader reader, PrintStream out)
throws SQLException, IOException {
StringBuffer sql = new StringBuffer();
String line = "";
BufferedReader in = new BufferedReader(reader);
while ((line = in.readLine()) != null) {
if (!keepformat) {
line = line.trim();
}
line = getProject().replaceProperties(line);
if (!keepformat) {
if (line.startsWith("//")) {
continue;
}
if (line.startsWith("--")) {
continue;
}
StringTokenizer st = new StringTokenizer(line);
if (st.hasMoreTokens()) {
String token = st.nextToken();
if ("REM".equalsIgnoreCase(token)) {
continue;
}
}
}
if (!keepformat) {
sql.append(" " + line);
} else {
sql.append("\n" + line);
}
// SQL defines "--" as a comment to EOL
// and in Oracle it may contain a hint
// so we cannot just remove it, instead we must end it
if (!keepformat) {
if (line.indexOf("--") >= 0) {
sql.append("\n");
}
}
if ((delimiterType.equals(DelimiterType.NORMAL)
&& sql.toString().endsWith(delimiter))
||
(delimiterType.equals(DelimiterType.ROW)
&& line.equals(delimiter))) {
execSQL(sql.substring(0, sql.length() - delimiter.length()),
out);
sql.replace(0, sql.length(), "");
}
}
// Catch any statements not followed by ;
if (!sql.equals("")) {
execSQL(sql.toString(), out);
}
}
/**
* Exec the sql statement.
*/
protected void execSQL(String sql, PrintStream out) throws SQLException {
// Check and ignore empty statements
if ("".equals(sql.trim())) {
return;
}
ResultSet resultSet = null;
try {
totalSql++;
log("SQL: " + sql, Project.MSG_VERBOSE);
boolean ret;
int updateCount = 0, updateCountTotal = 0;
ret = statement.execute(sql);
updateCount = statement.getUpdateCount();
resultSet = statement.getResultSet();
do {
if (!ret) {
if (updateCount != -1) {
updateCountTotal += updateCount;
}
} else {
if (print) {
printResults(resultSet, out);
}
}
ret = statement.getMoreResults();
if (ret) {
updateCount = statement.getUpdateCount();
resultSet = statement.getResultSet();
}
} while (ret);
log(updateCountTotal + " rows affected",
Project.MSG_VERBOSE);
if (print) {
StringBuffer line = new StringBuffer();
line.append(updateCountTotal + " rows affected");
out.println(line);
}
SQLWarning warning = conn.getWarnings();
while (warning != null) {
log(warning + " sql warning", Project.MSG_VERBOSE);
warning = warning.getNextWarning();
}
conn.clearWarnings();
goodSql++;
} catch (SQLException e) {
log("Failed to execute: " + sql, Project.MSG_ERR);
if (!onError.equals("continue")) {
throw e;
}
log(e.toString(), Project.MSG_ERR);
} finally {
if (resultSet != null) {
resultSet.close();
}
}
}
/**
* print any results in the statement
* @deprecated use {@link #printResults(java.sql.ResultSet, java.io.PrintStream)
* the two arg version} instead.
* @param out the place to print results
* @throws SQLException on SQL problems.
*/
protected void printResults(PrintStream out) throws SQLException {
ResultSet rs = null;
rs = statement.getResultSet();
try {
printResults(rs, out);
} finally {
if (rs != null) {
rs.close();
}
}
}
/**
* print any results in the result set.
* @param rs the resultset to print information about
* @param out the place to print results
* @throws SQLException on SQL problems.
* @since Ant 1.6.3
*/
protected void printResults(ResultSet rs, PrintStream out) throws SQLException {
if (rs != null) {
log("Processing new result set.", Project.MSG_VERBOSE);
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
StringBuffer line = new StringBuffer();
if (showheaders) {
for (int col = 1; col < columnCount; col++) {
line.append(md.getColumnName(col));
line.append(",");
}
line.append(md.getColumnName(columnCount));
out.println(line);
line = new StringBuffer();
}
while (rs.next()) {
boolean first = true;
for (int col = 1; col <= columnCount; col++) {
String columnValue = rs.getString(col);
if (columnValue != null) {
columnValue = columnValue.trim();
}
if (first) {
first = false;
} else {
line.append(",");
}
line.append(columnValue);
}
out.println(line);
line = new StringBuffer();
}
}
out.println();
}
/**
* The action a task should perform on an error,
* one of "continue", "stop" and "abort"
*/
public static class OnError extends EnumeratedAttribute {
public String[] getValues() {
return new String[] {"continue", "stop", "abort"};
}
}
/**
* Contains the definition of a new transaction element.
* Transactions allow several files or blocks of statements
* to be executed using the same JDBC connection and commit
* operation in between.
*/
public class Transaction {
private File tSrcFile = null;
private String tSqlCommand = "";
/**
*
*/
public void setSrc(File src) {
this.tSrcFile = src;
}
/**
*
*/
public void addText(String sql) {
this.tSqlCommand += sql;
}
/**
*
*/
private void runTransaction(PrintStream out)
throws IOException, SQLException {
if (tSqlCommand.length() != 0) {
log("Executing commands", Project.MSG_INFO);
runStatements(new StringReader(tSqlCommand), out);
}
if (tSrcFile != null) {
log("Executing file: " + tSrcFile.getAbsolutePath(),
Project.MSG_INFO);
Reader reader =
(encoding == null) ? new FileReader(tSrcFile)
: new InputStreamReader(
new FileInputStream(tSrcFile),
encoding);
try {
runStatements(reader, out);
} finally {
reader.close();
}
}
}
}
}