Geek Answers Handbook

Running SQL file scripts from a Java program

I have a set of SQL files that convert my original dataset. I am currently opening every file and executing it. How can I execute every file inside a Java program? The goal is to make this process much more automatic.

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

NOTE These SQL scripts operate in the same database. I assume that I will have to pass some kind of connection string. I am using MySQL.

  • What objects, libraries, packages, etc. do i need to execute inside java?
+4
source share
3 answers

Ibatis provides a ScriptRunner to help you. Simple code snippets you can find:

 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();
+9
source

Using iBatics will be easier.

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

Additionally, you need the MySQL java driver: com.mysql.jdbc.Driver , which can be found on the mysql website.

 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); } } }
+4
source

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; }
+3
source

More articles:


All Articles