sqlexec.java

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

				}
			} catch (IOException e) {
				closeQuietly();
				throw new Exception(e);
			} catch (SQLException e) {
				closeQuietly();
				throw new Exception(e);
			} finally {
				try {
					if (statement != null) {
						statement.close();
					}
					if (conn != null && isclose) {
						conn.close();
					}
				} catch (SQLException ex) {
					// ignore
				}
			}
			log.info(goodSql + " of " + totalSql
					+ " SQL statements executed successfully");
		} finally {
			transactions = savedTransaction;
			sqlCommand = savedSqlCommand;
		}
	}

	/**
	 * read in lines and execute them
	 * 
	 * @param reader
	 *            the reader contains sql lines.
	 * @param out
	 *            the place to output results.
	 * @throws SQLException
	 *             on sql problems
	 * @throws IOException
	 *             on io problems
	 */
	protected void runStatements(Reader reader, PrintStream out)
			throws SQLException, IOException {
		StringBuffer sql = new StringBuffer();
		String line;

		BufferedReader in = new BufferedReader(reader);

		// 多行注释结束标志 true 结束 false 没结束
		boolean isend = true;
		while ((line = in.readLine()) != null) {
			System.out.println(line);
			if (!keepformat) {
				line = line.trim();
			}

			// 处理注释
			if (!keepformat) {

				// 过滤掉字符串 line中 /**/的所有内容
				String regEx = "/\\*[\\w\\s\\*@@-]*\\*/";
				Pattern p = Pattern.compile(regEx);
				Matcher m = p.matcher(line);
				line = m.replaceAll("");

				// 处理以--开头的单行注释
				if (line.startsWith("--")) {
					line = "";
					continue;
				} else {
					// 处理多行注释
					regEx = "\\*/[\\w\\s\\*@@-]*/\\*";
					int begin = line.indexOf("*/");
					int end = line.indexOf("/*");
					p = Pattern.compile(regEx);
					m = p.matcher(line);
					if (m.find()) {
						line = line.substring(begin + 2, end);
						isend = false;
						continue;
					} else if (line.indexOf("*/") > -1) {
						int index = line.indexOf("*/");
						line.substring(index + 1);
						isend = true;
						continue;
					} else if (line.indexOf("/*") > -1) {
						int index = line.indexOf("/*");
						line.substring(0, index);
						isend = false;
						continue;
					} else if (!isend) {
						line = "";
					}
					// 处理 /* 之后的单行注释
					if (line.indexOf("--") > -1) {
						int index = line.indexOf("--");
						line = line.substring(0, index - 1);
					}
					;
				}
				line = line.trim();

				StringTokenizer st = new StringTokenizer(line);
				if (st.hasMoreTokens()) {
					String token = st.nextToken();
					if ("REM".equalsIgnoreCase(token)) {
						continue;
					}
				}
			}

			if (!keepformat) {
				sql.append(" ");
				sql.append(line);
			} else {
				sql.append("\n");
				sql.append(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(DELIMITER_TYPE_NORMAL)
					&& sql.toString().endsWith(delimiter) && line.length() > 0)
					|| (delimiterType.equals(DELIMITER_TYPE_ROW) && line
							.equals(delimiter))) {
				execSQL(sql.substring(0, sql.length() - delimiter.length()),
						out);
				sql.replace(0, sql.length(), "");
			}

		}
		System.out.println("################" + sql);
		// Catch any statements not followed by ;
		if (sql.length() > 0) {
			execSQL(sql.toString(), out);
		}
	}

	/**
	 * Exec the sql statement.
	 * 
	 * @param sql
	 *            the SQL statement to execute
	 * @param out
	 *            the place to put output
	 * @throws SQLException
	 *             on SQL problems
	 */
	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.info("SQL: " + sql);

			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.info(updateCountTotal + " rows affected");

			if (print && showtrailers) {
				out.println(updateCountTotal + " rows affected");
			}

			SQLWarning warning = conn.getWarnings();
			while (warning != null) {
				log.warning(" sql warning " + warning.getLocalizedMessage());
				warning = warning.getNextWarning();
			}
			conn.clearWarnings();
			goodSql++;
		} catch (SQLException e) {
			log.severe("Failed to execute: " + sql);
			if (!onError.equals("continue")) {
				throw e;
			}
			log.severe(e.toString());
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
		}
	}

	/**
	 * print any results in the statement
	 * 
	 * @deprecated since 1.6.x. 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 = 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.
	 */
	protected void printResults(ResultSet rs, PrintStream out)
			throws SQLException {
		if (rs != null) {
			log.info("Processing new result set.");
			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();
	}

	/*
	 * Closes an unused connection after an error and doesn't rethrow a possible
	 * SQLException
	 */
	private void closeQuietly() {
		if (!isAutocommit() && conn != null && onError.equals("abort")) {
			try {
				conn.rollback();
			} catch (SQLException ex) {
				// ignore
			}
		}
	}

	/**
	 * 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 tSrcResource = null;
		private String tSqlCommand = "";

		/**
		 * Set the source file attribute.
		 * 
		 * @param src
		 *            the source file
		 */
		public void setSrc(File src) {
			// there are places (in this file, and perhaps elsewhere, where it
			// is assumed
			// that null is an acceptable parameter.
			if (src != null) {
				setSrcResource(src);
			}
		}

		/**
		 * Set the source resource attribute.
		 * 
		 * @param src
		 *            the source file
		 */
		public void setSrcResource(File src) {
			if (tSrcResource != null) {
				throw new IllegalArgumentException(
						"only one resource per transaction");
			}
			tSrcResource = src;
		}

		/**
		 * Set inline text
		 * 
		 * @param sql
		 *            the inline text
		 */
		public void addText(String sql) {
			if (sql != null) {
				this.tSqlCommand += sql;
			}
		}

		/**
		 * Set the source resource.
		 * 
		 * @param a
		 *            the source resource collection.
		 */
		public void addConfigured(Collection<File> files) {
			if (files.size() != 1) {
				throw new IllegalArgumentException(
						"only single argument resource "
								+ "collections are supported.");
			}
			setSrcResource((File) files.iterator().next());
		}

		/**
		 * 
		 */
		private void runTransaction(PrintStream out) throws IOException,
				SQLException {
			System.out.println("tSqlCommand.length():" + tSqlCommand.length());
			if (tSqlCommand.length() != 0) {
				log.info("Executing commands");
				runStatements(new StringReader(tSqlCommand), out);
			}

			if (tSrcResource != null) {
				log.info("Executing resource: " + tSrcResource.toString());
				InputStream is = null;
				Reader reader = null;
				try {
					is = new FileInputStream((File) tSrcResource);
					reader = (encoding == null) ? new InputStreamReader(is)
							: new InputStreamReader(is, encoding);
					runStatements(reader, out);
				} finally {
					is.close();
					reader.close();
				}
			}
		}
	}
}