How to execute sql script file using JDBC

Question

Possible Duplicate    Running a  sql script using MySQL with JDBC       I have an SQL script file which contains 40-50 SQL statements  Is it possible to run this script file using JDBC

User · Answer

I use this bit of code to import sql statements created by mysqldump   public static void importSQL Connection conn  InputStream in  throws SQLException       Scanner s   new Scanner in       s useDelimiter      r   n   -- n         Statement st   null      try               st   conn createStatement            while  s hasNext                          String line   s next                if  line startsWith         amp  amp  line endsWith                                      int i   line indexOf                       line   line substring i   1  line length   -       length                                if  line trim   length    gt  0                                st execute line                                     finally               if  st    null  st close

User · Answer

I had the same problem trying to execute an SQL script that creates an SQL database  Googling here and there I found a Java class initially written by Clinton Begin which supports comments  see http   pastebin com P14HsYAG   I modified slightly the file to cater for triggers where one has to change the default DELIMITER to something different  I ve used that version ScriptRunner  see http   pastebin com sb4bMbVv   Since an  open source and free  SQLScriptRunner class is an absolutely necessary utility  it would be good to have some more input from developers and hopefully we ll have soon a more stable version of it

User · Answer

This link might help you out  http   pastebin com f10584951   Pasted below for posterity         Slightly modified version of the com ibatis common jdbc ScriptRunner class    from the iBATIS Apache project  Only removed dependency on Resource class    and a constructor            Copyright 2004 Clinton Begin        Licensed 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       import java io IOException  import java io LineNumberReader  import java io PrintWriter  import java io Reader  import java sql            Tool to run database scripts     public class ScriptRunner        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                  Default constructor             public ScriptRunner Connection connection  boolean autoCommit              boolean stopOnError            this connection   connection          this autoCommit   autoCommit          this stopOnError   stopOnError             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           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                           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   0  i  lt  cols  i                                      String value   rs getString i                                   print value     t                                                              println                                                                           command   null                      try                           statement close                          catch  Exception e                               Ignore to workaround a bug in Jakarta DBCP                                           Thread yield                      else                       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

User · Answer

You should be able to parse the SQL file into statements  And run a single statement a time  If you know that your file consists of simple insert update delete statements you can use a semicolon as statement delimiter  In common case you have a task to create your specific SQL-dialect parser

User · Answer

Another option  this DOESN T support comments  very useful with AmaterasERD DDL export for Apache Derby   public void executeSqlScript Connection conn  File inputFile            Delimiter     String delimiter                Create scanner     Scanner scanner      try           scanner   new Scanner inputFile  useDelimiter delimiter         catch  FileNotFoundException e1            e1 printStackTrace            return                Loop through the SQL file statements      Statement currentStatement   null      while scanner hasNext                  Get statement          String rawStatement   scanner next     delimiter          try                  Execute statement             currentStatement   conn createStatement                currentStatement execute rawStatement             catch  SQLException e                e printStackTrace              finally                  Release resources             if  currentStatement    null                    try                       currentStatement close                      catch  SQLException e                        e printStackTrace                                                currentStatement   null                  scanner close

User · Answer

Just read it and then use the preparedstatement with the full sql-file in it    If I remember good   ADD  You can also read and split on     and than execute them all in a loop  Do not forget the comments and add again the

User · Answer

You can read the script line per line with a BufferedReader and append every line to a StringBuilder so that the script becomes one large string   Then you can create a Statement object using JDBC and call statement execute stringBuilder toString

[java] How to execute .sql script file using JDBC

Examples related to java

Examples related to jdbc