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.

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

Answer 1

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();

Answer 2

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);
        }
    }
}