sqlexec.java

DBExplorer 强烈推荐的一个JAVA项目 数据导出功能 支持的格式HTML PDF XLS等 支持的数据库有MYSQL ORACLE MSSQLSERVER等

package cn.com.qimingx.utils.sql;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Unmi Executes a series of SQL statements or Sql File on a database
 *         using JDBC.
 */
public class SQLExec extends JDBCTask {

	private static Logger log = Logger.getLogger(SQLExec.class.getName());
	static {
		log.setLevel(Level.WARNING);
	}

	// ���ָ�����
	public static final String DELIMITER_TYPE_NORMAL = "normal";
	public static final String DELIMITER_TYPE_ROW = "row";

	// ���ʱ������δ���
	public static final String ON_ERROR_ABORT = "abort";
	public static final String ON_ERROR_CONTINUE = "continue";
	public static final String ON_ERROR_STOP = "stop";

	private int goodSql = 0;

	private int totalSql = 0;

	/**
	 * Database connection
	 */
	private Connection conn = null;

	/**
	 * files to load
	 */
	private List<File> resources = new ArrayList<File>();

	/**
	 * 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<Transaction> transactions = new Vector<Transaction>();

	/**
	 * 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 = DELIMITER_TYPE_NORMAL;

	/**
	 * Print SQL results.
	 */
	private boolean print = false;

	/**
	 * Print header columns.
	 */
	private boolean showheaders = true;

	/**
	 * Print SQL stats (rows affected)
	 */
	private boolean showtrailers = 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
	 */
	private boolean escapeProcessing = true;

	/**
	 * should properties be expanded in text? false for backwards compatibility
	 */
	private boolean expandProperties = false;

	/**
	 * Set the name of the SQL file to be run. Required unless statements are
	 * enclosed in the build file
	 * 
	 * @param srcFile
	 *            the file containing the SQL command.
	 */
	public void setSrc(File srcFile) {
		this.srcFile = srcFile;
	}

	/**
	 * Set an inline SQL command to execute. NB: Properties are not expanded in
	 * this text unless {@link #expandProperties} is set.
	 * 
	 * @param sql
	 *            an inline string containing the SQL command.
	 */
	public void addText(String sql) {
		// there is no need to expand properties here as that happens when
		// Transaction.addText is
		// called; to do so here would be an error.
		this.sqlCommand += sql;
	}

	/**
	 * Adds a collection of resources (nested element).
	 * 
	 * @param rc
	 *            a collection of resources containing SQL commands, each
	 *            resource is run in a separate transaction.
	 */
	public void add(File srcFile) {
		resources.add(srcFile);
	}

	/**
	 * Add a SQL transaction to execute
	 * 
	 * @return a Transaction to be configured.
	 */
	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
	 * &quot;;&quot;; optional
	 * 
	 * <p>
	 * For example, set this to "go" and delimitertype to "ROW" for Sybase ASE
	 * or MS SQL Server.
	 * </p>
	 * 
	 * @param delimiter
	 *            the separator.
	 */
	public void setDelimiter(String delimiter) {
		this.delimiter = delimiter;
	}

	/**
	 * Set the delimiter type: "normal" or "row" (default "normal").
	 * 
	 * <p>
	 * 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.
	 * </p>
	 * 
	 * @param delimiterType
	 *            the type of delimiter - "normal" or "row".
	 */
	public void setDelimiterType(String delimiterType) {
		this.delimiterType = delimiterType;
	}

	/**
	 * Print result sets from the statements; optional, default false
	 * 
	 * @param print
	 *            if true print result sets.
	 */
	public void setPrint(boolean print) {
		this.print = print;
	}

	/**
	 * Print headers for result sets from the statements; optional, default
	 * true.
	 * 
	 * @param showheaders
	 *            if true print headers of result sets.
	 */
	public void setShowheaders(boolean showheaders) {
		this.showheaders = showheaders;
	}

	/**
	 * Print trailing info (rows affected) for the SQL Addresses Bug/Request
	 * #27446
	 * 
	 * @param showtrailers
	 *            if true prints the SQL rows affected
	 */
	public void setShowtrailers(boolean showtrailers) {
		this.showtrailers = showtrailers;
	}

	/**
	 * Set the output file; optional, defaults to the System.out log.
	 * 
	 * @param output
	 *            the output file to use for logging messages.
	 */
	public void setOutput(File output) {
		this.output = output;
	}

	/**
	 * whether output should be appended to or overwrite an existing file.
	 * Defaults to false.
	 * 
	 * @param append
	 *            if true append to an existing file.
	 */
	public void setAppend(boolean append) {
		this.append = append;
	}

	/**
	 * Action to perform when statement fails: continue, stop, or abort
	 * optional; default &quot;abort&quot;
	 * 
	 * @param action
	 *            the action to perform on statement failure.
	 */
	public void setOnerror(String onError) {
		this.onError = onError;
	}

	/**
	 * 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.
	 * 
	 * @param enable
	 *            if true enable escape processing, default is true.
	 */
	public void setEscapeProcessing(boolean enable) {
		escapeProcessing = enable;
	}

	/**
	 * Load the sql file and then execute it
	 * 
	 * @throws BuildException
	 *             on error.
	 */
	@SuppressWarnings("unchecked")
	public void execute(Connection cnn, boolean isclose) throws Exception {
		Vector<Transaction> savedTransaction = (Vector) transactions.clone();
		String savedSqlCommand = sqlCommand;

		sqlCommand = sqlCommand.trim();

		try {
			if (srcFile == null && sqlCommand.length() == 0
					&& resources.size() == 0) {
				if (transactions.size() == 0) {
					throw new Exception("Source file or resource "
							+ "collection, " + "transactions or sql statement "
							+ "must be set!");
				}
			}

			if (srcFile != null && !srcFile.exists()) {
				throw new Exception("Source file does not exist!");
			}

			// deal with the resources
			Iterator iter = resources.iterator();
			while (iter.hasNext()) {
				File file = (File) iter.next();
				// Make a transaction for each resource
				Transaction t = createTransaction();
				t.setSrcResource(file);
			}

			// Make a transaction group for the outer command
			Transaction t = createTransaction();
			t.setSrc(srcFile);
			t.addText(sqlCommand);
			conn = cnn;
			try {
				statement = conn.createStatement();
				statement.setEscapeProcessing(escapeProcessing);

				PrintStream out = System.out;
				try {
					if (output != null) {
						log
								.info("Opening PrintStream to output file "
										+ output);
						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.info("Committing transaction");
							conn.commit();
						}
					}
				} finally {
					if (out != null && out != System.out) {
						out.close();
					}