| /* |
| * 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 */ |
| } |
| } |
| } |
| } |
| } |