title: SCALA: executing complete .sql file
body: <p>I have a .sql script files and need to execute complete .sql file at once, I have found many related answers, where programmer reads file and execute query one by one. </p>
  
  <p>I require to run .sql file all at-once, like picking up the file and executing in within SCALA.</p>
  
  <p>Currently I am doing it with following code, copied it from copied it from:<a href="https://gist.github.com/joe776/831762" rel="nofollow noreferrer">https://gist.github.com/joe776/831762</a>, my Lead ask me to do it in simple way, instead of executing script line by line, it should get executed as .SQL file</p>
  
  <pre><code>import java.io.IOException;
  import java.io.LineNumberReader;
  import java.io.PrintWriter;
  import java.io.Reader;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.regex.Matcher;
  import java.util.regex.Pattern;
  
  
  public class ScriptRunner {
  
      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 PrintWriter logWriter = new PrintWriter(System.out);
      private PrintWriter errorLogWriter = new PrintWriter(System.err);
      private String delimiter = DEFAULT_DELIMITER;
      private boolean fullLineDelimiter = false;
  
      /**
       * Default constructor.
       * 
       * @param connection
       * @param autoCommit
       * @param stopOnError
       */
      public ScriptRunner(Connection connection, boolean autoCommit, boolean stopOnError) {
          this.connection = connection;
          this.autoCommit = autoCommit;
          this.stopOnError = stopOnError;
      }
  
      /**
       * @param delimiter
       * @param fullLineDelimiter
       */
      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
       * @throws SQLException
       *         if any SQL errors occur
       * @throws IOException
       *         if there is an error reading from the Reader
       */
      public void runScript(Reader reader) throws IOException, SQLException {
          try {
              boolean originalAutoCommit = connection.getAutoCommit();
              try {
                  if (originalAutoCommit != autoCommit) {
                      connection.setAutoCommit(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 {
          StringBuffer command = null;
          try {
              LineNumberReader lineReader = new LineNumberReader(reader);
              String line = null;
              while ((line = lineReader.readLine()) != null) {
                  if (command == null) {
                      command = new StringBuffer();
                  }
                  String trimmedLine = line.trim();
                  if (trimmedLine.startsWith("--")) {
                      println(trimmedLine);
                  } else if (trimmedLine.length() &lt; 1 || trimmedLine.startsWith("//")) {
                      // Do nothing
                  } else if (trimmedLine.length() &lt; 1 || trimmedLine.startsWith("--")) {
                      // Do nothing
                  } else if (!fullLineDelimiter &amp;&amp; trimmedLine.endsWith(getDelimiter())
                          || fullLineDelimiter &amp;&amp; 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);
                          line = lineReader.readLine();
                          if (line == null) {
                              break;
                          }
                          trimmedLine = line.trim();
                      }
  
                      command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
                      command.append(" ");
                      Statement statement = conn.createStatement();
  
                      println(command);
  
                      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 &amp;&amp; !conn.getAutoCommit()) {
                          conn.commit();
                      }
  
                      ResultSet rs = statement.getResultSet();
                      if (hasResults &amp;&amp; rs != null) {
                          ResultSetMetaData md = rs.getMetaData();
                          int cols = md.getColumnCount();
                          for (int i = 0; i &lt; cols; i++) {
                              String name = md.getColumnLabel(i);
                              print(name + "\t");
                          }
                          println("");
                          while (rs.next()) {
                              for (int i = 1; i &lt;= 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 {
                          if (statement != null) {
                              statement.close();
                          }
                      } catch (Exception e) {
                          e.printStackTrace();
                          // Ignore to workaround a bug in Jakarta DBCP
                      }
                  } 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);
                          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) {
              logWriter.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();
          }
      }
  }
  </code></pre>