jisql/src/main/java/org/apache/util/sql/MySQLPLRunner.java - ranger - Git at Google

 /*
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you 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.util.sql;


 import java.io.FileReader;
 import java.io.IOException;
 import java.io.LineNumberReader;
 import java.io.PrintWriter;
 import java.io.Reader;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Properties;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;


 public class MySQLPLRunner {

     private static final String DEFAULT_DELIMITER = ";";
     private Connection connection;
     private boolean stopOnError;
     private boolean autoCommit;
     private PrintWriter logWriter = new PrintWriter(System.out);
     private PrintWriter errorLogWriter = new PrintWriter(System.err);
     private String delimiter = DEFAULT_DELIMITER;
     private boolean fullLineDelimiter = false;
     private static final String DELIMITER_LINE_REGEX = "(?i)delimiter.+";
     private static final String DELIMITER_LINE_SPLIT_REGEX = "(?i)delimiter";
     private boolean printDebug = false;
     /**
      * Default constructor
      */
     public MySQLPLRunner(Connection connection, boolean autoCommit,
             boolean stopOnError,boolean printDebug) {
         this.connection = connection;
         this.autoCommit = autoCommit;
         this.stopOnError = stopOnError;
         this.printDebug=printDebug;
     }

     public void setDelimiter(String delimiter, boolean fullLineDelimiter) {
         this.delimiter = delimiter;
         this.fullLineDelimiter = fullLineDelimiter;
     }

     /**
      * Setter for logWriter property
      *
      * @param logWriter
      *            - the new value of the logWriter property
      */
     public void setLogWriter(PrintWriter logWriter) {
         this.logWriter = logWriter;
     }

     /**
      * Setter for errorLogWriter property
      *
      * @param errorLogWriter
      *            - the new value of the errorLogWriter property
      */
     public void setErrorLogWriter(PrintWriter errorLogWriter) {
         this.errorLogWriter = errorLogWriter;
     }

     /**
      * Runs an SQL script (read in using the Reader parameter)
      *
      * @param reader
      *            - the source of the script
      */
     public void runScript(Reader reader) throws IOException, SQLException {
         try {
             boolean originalAutoCommit = connection.getAutoCommit();
             try {
                 if (originalAutoCommit != this.autoCommit) {
                     connection.setAutoCommit(this.autoCommit);
                 }
                 runScript(connection, reader);
             } finally {
                 connection.setAutoCommit(originalAutoCommit);
             }
         } catch (IOException e) {
             throw e;
         } catch (SQLException e) {
             throw e;
         } catch (Exception e) {
             throw new RuntimeException("Error running script.  Cause: " + e, e);
         }
     }

     /**
      * Runs an SQL script (read in using the Reader parameter) using the
      * connection passed in
      *
      * @param conn
      *            - the connection to use for the script
      * @param reader
      *            - the source of the script
      * @throws SQLException
      *             if any SQL errors occur
      * @throws IOException
      *             if there is an error reading from the Reader
      */
     private void runScript(Connection conn, Reader reader) throws IOException,
             SQLException {
         StringBuilder command = null;
         try {
             LineNumberReader lineReader = new LineNumberReader(reader);
             String line = null;
             while ((line = lineReader.readLine()) != null) {
                 if (command == null) {
                     command = new StringBuilder();
                 }
                 String trimmedLine = line.trim();

                 if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")
                     || trimmedLine.startsWith("//")) { //NOPMD
                     //println(trimmedLine);
                     // Do nothing
                 } else if (!fullLineDelimiter
                         && trimmedLine.endsWith(getDelimiter())
                         || fullLineDelimiter
                         && trimmedLine.equals(getDelimiter())) {


                     Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX);
                     Matcher matcher = pattern.matcher(trimmedLine);
                     if (matcher.matches()) {
                         setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter);
                         continue;
                         /*line = lineReader.readLine();
                         if (line == null) {
                             break;
                         }
                         trimmedLine = line.trim();*/
                     }

                     if(line!=null && line.endsWith(getDelimiter()) && !DEFAULT_DELIMITER.equalsIgnoreCase(getDelimiter())){
                     	 command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
                     }else{
                     	command.append(line);
                     }

                     command.append(" ");
                     Statement statement = conn.createStatement();
                     if(printDebug)
                     	println(command);
                     //System.out.println(getDelimiter());
                     boolean hasResults = false;
                     if (stopOnError) {
                         hasResults = statement.execute(command.toString());
                     } else {
                         try {
                             statement.execute(command.toString());
                         } catch (SQLException e) {
                             e.fillInStackTrace();
                             printlnError("Error executing: " + command);
                             printlnError(e);
                         }
                     }

                     if (autoCommit && !conn.getAutoCommit()) {
                         conn.commit();
                     }

                     ResultSet rs = statement.getResultSet();
                     if (hasResults && rs != null) {
                         ResultSetMetaData md = rs.getMetaData();
                         int cols = md.getColumnCount();
                         for (int i = 1; i <= cols; i++) {
                             String name = md.getColumnLabel(i);
                             print(name + "\t");
                         }
                         println("");
                         while (rs.next()) {
                             for (int i = 1; i <= cols; i++) {
                                 String value = rs.getString(i);
                                 print(value + "\t");
                             }
                             println("");
                         }
                     }

                     command = null;
                     try {
                     	if(rs!=null){
                         rs.close();
                     	}
                     } catch (Exception e) {
                         e.printStackTrace();
                     }
                     try {
                         statement.close();
                     } catch (Exception e) {
                         e.printStackTrace();
                         // Ignore to workaround a bug in Jakarta DBCP
                     }
                     Thread.yield();
                 } else {
                     Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX);
                     Matcher matcher = pattern.matcher(trimmedLine);
                     if (matcher.matches()) {
                         setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter);
                         continue;
                         /*line = lineReader.readLine();
                         if (line == null) {
                             break;
                         }
                         trimmedLine = line.trim();*/
                     }
                     command.append(line);
                     command.append(" ");
                 }
             }
             if (!autoCommit) {
                 conn.commit();
             }
         } catch (SQLException e) {
             e.fillInStackTrace();
             printlnError("Error executing: " + command);
             printlnError(e);
             throw e;
         } catch (IOException e) {
             e.fillInStackTrace();
             printlnError("Error executing: " + command);
             printlnError(e);
             throw e;
         } finally {
             conn.rollback();
             flush();
         }
     }

     private String getDelimiter() {
         return delimiter;
     }

     private void print(Object o) {
         if (logWriter != null) {
             System.out.print(o);
         }
     }

     private void println(Object o) {
         if (logWriter != null) {
             logWriter.println(o);
         }
     }

     private void printlnError(Object o) {
         if (errorLogWriter != null) {
             errorLogWriter.println(o);
         }
     }

     private void flush() {
         if (logWriter != null) {
             logWriter.flush();
         }
         if (errorLogWriter != null) {
             errorLogWriter.flush();
         }
     }

     public static void main(String args[]){
     	// Creating object of ScriptRunner class
     	  Connection con = null;
     	  String driverName = "com.mysql.jdbc.Driver";
     	  Properties props = null;
     	  try {
               Class.forName(driverName).newInstance();
               props = new Properties();

               props.put("user", "root");

               props.put("password", "root");
               String connectString = "jdbc:mysql://localhost:3306/ranger";
               con = DriverManager.getConnection(connectString, props);


     	MySQLPLRunner scriptRunner = new MySQLPLRunner(con, false, true,true);
      	String aSQLScriptFilePath = "/disk1/zero/jisql-2.0.11/xa_core_db.sql";


     	// Executing SQL Script
      	FileReader reader = new FileReader(aSQLScriptFilePath);

      	try {
      		scriptRunner.runScript(reader);
      	}
      	finally {
      		if (reader != null) {
      			try {
      			reader.close();
      			}
      			catch(IOException ioe) {
      				// Ignore IOException when reader is getting closed
      			}
      		}
      	}


     	  }
           catch (SQLException sqle) {
         	  sqle.printStackTrace();
           }
           catch (ClassNotFoundException cnfe) {
               System.err.println("Cannot find the driver class \"" + driverName + "\" in the current classpath.");
           }
           catch (InstantiationException ie) {
               System.err.println("Cannot instantiate the driver class \"" + driverName + "\"");
               ie.printStackTrace(System.err);
           }
           catch (IllegalAccessException iae) {
               System.err.println("Cannot instantiate the driver class \"" + driverName + "\" because of an IllegalAccessException");
               iae.printStackTrace(System.err);
           }catch (Exception sqle) {
         	  sqle.printStackTrace();
           }
           finally {
               if (con != null) {
                   try {
                 	  con.close();
                   }
                   catch (SQLException ignore) {
                       /* ignored */
                   }
               }
           }
     }
 }
	/*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you 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.util.sql;


	import java.io.FileReader;
	import java.io.IOException;
	import java.io.LineNumberReader;
	import java.io.PrintWriter;
	import java.io.Reader;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.Properties;
	import java.util.regex.Matcher;
	import java.util.regex.Pattern;


	public class MySQLPLRunner {

	private static final String DEFAULT_DELIMITER = ";";
	private Connection connection;
	private boolean stopOnError;
	private boolean autoCommit;
	private PrintWriter logWriter = new PrintWriter(System.out);
	private PrintWriter errorLogWriter = new PrintWriter(System.err);
	private String delimiter = DEFAULT_DELIMITER;
	private boolean fullLineDelimiter = false;
	private static final String DELIMITER_LINE_REGEX = "(?i)delimiter.+";
	private static final String DELIMITER_LINE_SPLIT_REGEX = "(?i)delimiter";
	private boolean printDebug = false;
	/**
	* Default constructor
	*/
	public MySQLPLRunner(Connection connection, boolean autoCommit,
	boolean stopOnError,boolean printDebug) {
	this.connection = connection;
	this.autoCommit = autoCommit;
	this.stopOnError = stopOnError;
	this.printDebug=printDebug;
	}

	public void setDelimiter(String delimiter, boolean fullLineDelimiter) {
	this.delimiter = delimiter;
	this.fullLineDelimiter = fullLineDelimiter;
	}

	/**
	* Setter for logWriter property
	*
	* @param logWriter
	* - the new value of the logWriter property
	*/
	public void setLogWriter(PrintWriter logWriter) {
	this.logWriter = logWriter;
	}

	/**
	* Setter for errorLogWriter property
	*
	* @param errorLogWriter
	* - the new value of the errorLogWriter property
	*/
	public void setErrorLogWriter(PrintWriter errorLogWriter) {
	this.errorLogWriter = errorLogWriter;
	}

	/**
	* Runs an SQL script (read in using the Reader parameter)
	*
	* @param reader
	* - the source of the script
	*/
	public void runScript(Reader reader) throws IOException, SQLException {
	try {
	boolean originalAutoCommit = connection.getAutoCommit();
	try {
	if (originalAutoCommit != this.autoCommit) {
	connection.setAutoCommit(this.autoCommit);
	}
	runScript(connection, reader);
	} finally {
	connection.setAutoCommit(originalAutoCommit);
	}
	} catch (IOException e) {
	throw e;
	} catch (SQLException e) {
	throw e;
	} catch (Exception e) {
	throw new RuntimeException("Error running script. Cause: " + e, e);
	}
	}

	/**
	* Runs an SQL script (read in using the Reader parameter) using the
	* connection passed in
	*
	* @param conn
	* - the connection to use for the script
	* @param reader
	* - the source of the script
	* @throws SQLException
	* if any SQL errors occur
	* @throws IOException
	* if there is an error reading from the Reader
	*/
	private void runScript(Connection conn, Reader reader) throws IOException,
	SQLException {
	StringBuilder command = null;
	try {
	LineNumberReader lineReader = new LineNumberReader(reader);
	String line = null;
	while ((line = lineReader.readLine()) != null) {
	if (command == null) {
	command = new StringBuilder();
	}
	String trimmedLine = line.trim();

	if (trimmedLine.length() < 1 \|\| trimmedLine.startsWith("--")
	\|\| trimmedLine.startsWith("//")) { //NOPMD
	//println(trimmedLine);
	// Do nothing
	} else if (!fullLineDelimiter
	&& trimmedLine.endsWith(getDelimiter())
	\|\| fullLineDelimiter
	&& trimmedLine.equals(getDelimiter())) {


	Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX);
	Matcher matcher = pattern.matcher(trimmedLine);
	if (matcher.matches()) {
	setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter);
	continue;
	/*line = lineReader.readLine();
	if (line == null) {
	break;
	}
	trimmedLine = line.trim();*/
	}

	if(line!=null && line.endsWith(getDelimiter()) && !DEFAULT_DELIMITER.equalsIgnoreCase(getDelimiter())){
	command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
	}else{
	command.append(line);
	}

	command.append(" ");
	Statement statement = conn.createStatement();
	if(printDebug)
	println(command);
	//System.out.println(getDelimiter());
	boolean hasResults = false;
	if (stopOnError) {
	hasResults = statement.execute(command.toString());
	} else {
	try {
	statement.execute(command.toString());
	} catch (SQLException e) {
	e.fillInStackTrace();
	printlnError("Error executing: " + command);
	printlnError(e);
	}
	}

	if (autoCommit && !conn.getAutoCommit()) {
	conn.commit();
	}

	ResultSet rs = statement.getResultSet();
	if (hasResults && rs != null) {
	ResultSetMetaData md = rs.getMetaData();
	int cols = md.getColumnCount();
	for (int i = 1; i <= cols; i++) {
	String name = md.getColumnLabel(i);
	print(name + "\t");
	}
	println("");
	while (rs.next()) {
	for (int i = 1; i <= cols; i++) {
	String value = rs.getString(i);
	print(value + "\t");
	}
	println("");
	}
	}

	command = null;
	try {
	if(rs!=null){
	rs.close();
	}
	} catch (Exception e) {
	e.printStackTrace();
	}
	try {
	statement.close();
	} catch (Exception e) {
	e.printStackTrace();
	// Ignore to workaround a bug in Jakarta DBCP
	}
	Thread.yield();
	} else {
	Pattern pattern = Pattern.compile(DELIMITER_LINE_REGEX);
	Matcher matcher = pattern.matcher(trimmedLine);
	if (matcher.matches()) {
	setDelimiter(trimmedLine.split(DELIMITER_LINE_SPLIT_REGEX)[1].trim(), fullLineDelimiter);
	continue;
	/*line = lineReader.readLine();
	if (line == null) {
	break;
	}
	trimmedLine = line.trim();*/
	}
	command.append(line);
	command.append(" ");
	}
	}
	if (!autoCommit) {
	conn.commit();
	}
	} catch (SQLException e) {
	e.fillInStackTrace();
	printlnError("Error executing: " + command);
	printlnError(e);
	throw e;
	} catch (IOException e) {
	e.fillInStackTrace();
	printlnError("Error executing: " + command);
	printlnError(e);
	throw e;
	} finally {
	conn.rollback();
	flush();
	}
	}

	private String getDelimiter() {
	return delimiter;
	}

	private void print(Object o) {
	if (logWriter != null) {
	System.out.print(o);
	}
	}

	private void println(Object o) {
	if (logWriter != null) {
	logWriter.println(o);
	}
	}

	private void printlnError(Object o) {
	if (errorLogWriter != null) {
	errorLogWriter.println(o);
	}
	}

	private void flush() {
	if (logWriter != null) {
	logWriter.flush();
	}
	if (errorLogWriter != null) {
	errorLogWriter.flush();
	}
	}

	public static void main(String args[]){
	// Creating object of ScriptRunner class
	Connection con = null;
	String driverName = "com.mysql.jdbc.Driver";
	Properties props = null;
	try {
	Class.forName(driverName).newInstance();
	props = new Properties();

	props.put("user", "root");

	props.put("password", "root");
	String connectString = "jdbc:mysql://localhost:3306/ranger";
	con = DriverManager.getConnection(connectString, props);


	MySQLPLRunner scriptRunner = new MySQLPLRunner(con, false, true,true);
	String aSQLScriptFilePath = "/disk1/zero/jisql-2.0.11/xa_core_db.sql";


	// Executing SQL Script
	FileReader reader = new FileReader(aSQLScriptFilePath);

	try {
	scriptRunner.runScript(reader);
	}
	finally {
	if (reader != null) {
	try {
	reader.close();
	}
	catch(IOException ioe) {
	// Ignore IOException when reader is getting closed
	}
	}
	}


	}
	catch (SQLException sqle) {
	sqle.printStackTrace();
	}
	catch (ClassNotFoundException cnfe) {
	System.err.println("Cannot find the driver class \"" + driverName + "\" in the current classpath.");
	}
	catch (InstantiationException ie) {
	System.err.println("Cannot instantiate the driver class \"" + driverName + "\"");
	ie.printStackTrace(System.err);
	}
	catch (IllegalAccessException iae) {
	System.err.println("Cannot instantiate the driver class \"" + driverName + "\" because of an IllegalAccessException");
	iae.printStackTrace(System.err);
	}catch (Exception sqle) {
	sqle.printStackTrace();
	}
	finally {
	if (con != null) {
	try {
	con.close();
	}
	catch (SQLException ignore) {
	/* ignored */
	}
	}
	}
	}
	}