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