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 static final String DELIMITER_LINE_REGEX       = "(?i)delimiter.+";
     private static final String DELIMITER_LINE_SPLIT_REGEX = "(?i)delimiter";

     private final Connection connection;
     private final boolean    stopOnError;
     private final boolean    autoCommit;
     private final boolean    printDebug;

     private PrintWriter logWriter      = new PrintWriter(System.out);
     private PrintWriter errorLogWriter = new PrintWriter(System.err);
     private String      delimiter      = DEFAULT_DELIMITER;
     private boolean     fullLineDelimiter;

     /**
      * 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 static void main(String[] args) {
         // Creating object of ScriptRunner class
         Connection con        = null;
         String     driverName = "com.mysql.jdbc.Driver";

         try {
             Class.forName(driverName).newInstance();

             Properties 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

             try (FileReader reader = new FileReader(aSQLScriptFilePath)) {
                 scriptRunner.runScript(reader);
             }
         } 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 */
                 }
             }
         }
     }

     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 | 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;

             while ((line = lineReader.readLine()) != null) {
                 if (command == null) {
                     command = new StringBuilder();
                 }

                 String trimmedLine = line.trim();

                 if (trimmedLine.isEmpty() || trimmedLine.startsWith("--") || trimmedLine.startsWith("//")) { //NOPMD
                     // 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;
                     }

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

                     command.append(" ");

                     try (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;
                     }

                     command.append(line);
                     command.append(" ");
                 }
             }

             if (!autoCommit) {
                 conn.commit();
             }
         } catch (SQLException | 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();
         }
     }
 }
	/*
	* 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 static final String DELIMITER_LINE_REGEX = "(?i)delimiter.+";
	private static final String DELIMITER_LINE_SPLIT_REGEX = "(?i)delimiter";

	private final Connection connection;
	private final boolean stopOnError;
	private final boolean autoCommit;
	private final boolean printDebug;

	private PrintWriter logWriter = new PrintWriter(System.out);
	private PrintWriter errorLogWriter = new PrintWriter(System.err);
	private String delimiter = DEFAULT_DELIMITER;
	private boolean fullLineDelimiter;

	/**
	* 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 static void main(String[] args) {
	// Creating object of ScriptRunner class
	Connection con = null;
	String driverName = "com.mysql.jdbc.Driver";

	try {
	Class.forName(driverName).newInstance();

	Properties 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

	try (FileReader reader = new FileReader(aSQLScriptFilePath)) {
	scriptRunner.runScript(reader);
	}
	} 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 */
	}
	}
	}
	}

	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 \| 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;

	while ((line = lineReader.readLine()) != null) {
	if (command == null) {
	command = new StringBuilder();
	}

	String trimmedLine = line.trim();

	if (trimmedLine.isEmpty() \|\| trimmedLine.startsWith("--") \|\| trimmedLine.startsWith("//")) { //NOPMD
	// 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;
	}

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

	command.append(" ");

	try (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;
	}

	command.append(line);
	command.append(" ");
	}
	}

	if (!autoCommit) {
	conn.commit();
	}
	} catch (SQLException \| 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();
	}
	}
	}