Running SQL files scripts from a Java program

Question

I have a set of SQL files that transform my original dataset. Currently, I open each file and execute it. How can I execute each file inside a Java program? The goal is to make this process a lot more automatic.

I would like to do something like SqlScript.execute("myScript.sql");

NOTE these SQL scripts act on one database. I assume I would have to pass some kind of connection string. I am using MySQL.

What objects, libraries, packages, etc... do I need to perform this inside Java?

checkout java.sql API http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/package-summary.html — sasankad, Jan 21 '13 at 02:34
Duplicate for http://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc — Jaime Hablutzel, Aug 17 '14 at 05:28

score 13 · Accepted Answer · edited Jun 12 '15 at 11:48

13

Ibatis provides a ScriptRunner that will help you. Simple code snippets you can refer:

Connection conn=getConnection();//some method to get a Connection
ScriptRunner runner=new ScriptRunner(conn, false, false);
InputStreamReader reader = new InputStreamReader(new FileInputStream("foo.sql"));
runner.runScript(reader);
reader.close();
conn.close();

edited Jun 12 '15 at 11:48

gawi

2,843
4
29
44

answered Jan 21 '13 at 03:58

Gavin Xiong

957
6
9

Where can I get the jar for this? – CodeKingPlusPlus Jan 21 '13 at 04:44
@CodeKingPlusPlus http://code.google.com/p/mybatis/downloads/list?can=1 or google 'ibatis', there are sufficient resources you need. – Gavin Xiong Jan 21 '13 at 05:03
Any suggestions about the resource parameter? Netbeans is telling me it cannot find the resource. I am putting files where I usually do. – CodeKingPlusPlus Jan 21 '13 at 05:32
@CodeKingPlusPlus The runScript method accepts a java.io.Reader object as a argument and I modified the code a little. Check out the Java Doc: http://ibatis.apache.org/docs/java/dev/com/ibatis/common/jdbc/ScriptRunner.html – Gavin Xiong Jan 21 '13 at 07:33

score 4 · Answer 2 · answered Oct 12 '13 at 02:28

It'll be easier using iBatics.

http://repo1.maven.org/maven2/org/mybatis/mybatis/3.2.3/mybatis-3.2.3.jar

Additionally you need MySQL java driver:com.mysql.jdbc.Driver which can be found in mysql site.

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.DriverManager;

import org.apache.ibatis.jdbc.ScriptRunner;

public class Main {
    public static void main(String[] args) {

        String script = "scriptname.sql";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            new ScriptRunner(DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/mysql", "root", "root`"))
                    .runScript(new BufferedReader(new FileReader(script)));
        } catch (Exception e) {
            System.err.println(e);
        }
    }
}

Thank you for pointing this out. It is very important to use MyBatis because iBATIS is retired at the Apache Software foundation. I tried it with MyBatis 3.2.6 and it worked. :-) — Benny Code, Mar 30 '14 at 12:45

score 3 · Answer 3 · edited Jun 21 '16 at 19:10

You can try something like the following: http://www.tonyspencer.com/2005/01/20/execute-mysql-script-from-java/

public static String executeScript (String dbname, String dbuser,
        String dbpassword, String scriptpath, boolean verbose) {
    String output = null;
    try {
        String[] cmd = new String[]{"mysql",
            dbname,
            "--user=" + dbuser,
            "--password=" + dbpassword,
            "-e",
            "\"source " + scriptpath + "\""
            };
        System.err.println(cmd[0] + " " + cmd[1] + " " +
        cmd[2] + " " + cmd[3] + " " +
        cmd[4] + " " + cmd[5]);
        Process proc = Runtime.getRuntime().exec(cmd);
        if (verbose) {
            InputStream inputstream = proc.getInputStream();
            InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
            BufferedReader bufferedreader = new BufferedReader(inputstreamreader);

            // read the output
            String line;
            while ((line = bufferedreader.readLine()) != null) {
                System.out.println(line);
            }

            // check for failure
            try {
                if (proc.waitFor() != 0) {
                    System.err.println("exit value = " +
                    proc.exitValue());
                }
            }
            catch (InterruptedException e) {
                System.err.println(e);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return output;
}

I'm try with this and works fine in windows, but in linux always finish with exit code 1, it's compatible with linux? — Aarón Zúñiga, Jan 14 '18 at 06:31

Running SQL files scripts from a Java program

3 Answers3

Linked