Executing a .sql file through Java

I have a sql script file, i need to execute commands present in that through java. I searched in internet regarding the same, i got some code defining parsers to split the SQL statements and executing that. But none of them worked for my sql script file.Because my script file contains both create statements and alter statements without semicolon at the end[Instead it has GO]Can anybody suggest a solution to execute the script fi开发者_如何转开发le? Thanks, Mahesh

For simple scripts I generally use this class from ibatis - ScriptRunner. Alternative you can spawn a new db client process from Java and feed in the script you wan't execute. This will work for all scripts, as simple solutions like ScriptRunner don't work well when the delimiters in the sql files get changed for instance.

Here's an example how to feed the sql as a string to a spawed db client process:

private void runSql(String pSql) {
        String tCommand = "mysql -u " + username + (password != null ? " -p" + password : "") + " " + dbName;
        System.out.println(tCommand);

        try {
            Process tProcess = Runtime.getRuntime().exec(tCommand);
            OutputStream tOutputStream = tProcess.getOutputStream();
            Writer w = new OutputStreamWriter(tOutputStream);
            System.out.println(pSql);
            w.write(pSql);
            w.flush();

            Scanner in = new Scanner(tProcess.getErrorStream());

            String errorMessage = "";

            while (in.hasNext()) {
                errorMessage += in.next() + " ";
            }

            if (errorMessage.length() > 0) {
                System.out.println(errorMessage);
                throw new ClientSqlExecutionException(errorMessage);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

Have a look at Mybatis Migrations code, it does something like the one you need:

http://code.google.com/p/mybatis/wiki/Migration

You need to change the parser so it produces executable statements. But I'm not sure I understand what you mean when you say "execute through Java".

Java won't execute those SQL statements - the database you connect to will. Java can connect to a database using JDBC and send the SQL statements from the file.

I don't see why you have to parse the SQL, unless you want Java to validate them before sending them on to the database server. The server will parse and validate them again, so it feels like you're doing extra work for nothing.

The simplest solution I can present to you is this, presuming I understand your question.

1) Read text file into a string or array via Java IO. 2) Pass string or array to MySQL via JDBC.

Read from file example,

import java.io.*;
class FileRead 
{
   public static void main(String args[])
  {
      try{
    // Open the file that is the first 
    // command line parameter
    FileInputStream fstream = new FileInputStream("textfile.txt");
    // Get the object of DataInputStream
    DataInputStream in = new DataInputStream(fstream);
        BufferedReader br = new BufferedReader(new InputStreamReader(in));
    String strLine;
    //Read File Line By Line
    while ((strLine = br.readLine()) != null)   {
      // Print the content on the console
      System.out.println (strLine);
    }
    //Close the input stream
    in.close();
    }catch (Exception e){//Catch exception if any
      System.err.println("Error: " + e.getMessage());
    }
  }
}

acquired from, http://www.roseindia.net/java/beginners/java-read-file-line-by-line.shtml

The simplest way is to simply get the statements and check if they need they semi-column at the end: (this is an example and only works if it is a statement by line:

 public void executeScript(String script) {
   BufferedReader in  = new BufferedReader(new FileReader(script));
   while (in.read() > 0) {
     String statement = in.readLine();
     statement = statement.trim().toLowerCase();
     String command = statement.split("[ ]+")[0]; // split the statement.

     if (command.equals("insert") || command.equals("update") /* || any other */) {
       statement = statement + ";";
     } 

     // execute statement using jdbc
   }
}

If you do not know how to use jdbc, just ask :-)

Use this slightly modified version of the com.ibatis.common.jdbc.ScriptRunner class which is fully self contained, i.e. you do not need to have any third party jar dependencies.

It is possible to change the delimiter from ; to GO. I think that should do the trick.

Here is an example:

        Reader reader = new BufferedReader(*** Your FileReader instance ***);
        try
        {
            ScriptRunner runner = new ScriptRunner(connection, false, true);
            runner.setDelimiter("GO", true);
            runner.runScript(reader);
        }
        finally
        {
            reader.close();
        }

Apache Ant SQL Task worked for me. https://ant.apache.org/manual/Tasks/sql.html