Running a .sql script using MySQL with JDBC

Question

I am starting to use MySQL with JDBC.

Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///x", "x", "x");
stmt = conn.createStatement();
stmt.execute( "CREATE TABLE amigos" +
            "("+
            "id          int AUTO_INCREMENT          not null,"+
            "nombre      char(20)                    not null,"+
            "primary key(id)" +
            ")");

I have 3-4 tables to create and this doesn't look good.

Is there a way to run a .sql script from MySQL JDBC?

Answer 1

Ok. You can use this class here (posted on pastebin because of file length) in your project. But remember to keep the apache license info.

JDBC ScriptRunner

It's ripoff of the iBatis ScriptRunner with dependencies removed.

You can use it like this

Connection con = ....
ScriptRunner runner = new ScriptRunner(con, [booleanAutoCommit], [booleanStopOnerror]);
runner.runScript(new BufferedReader(new FileReader("test.sql")));

That's it!

Answer 2

I did a lot of research on this and found a good util from spring. I think using SimpleJdbcTestUtils.executeSqlScript(...) is actually the best solution, as it is more maintained and tested.

Edit: SimpleJdbcTestUtils is deprecated. You should use JdbcTestUtils. Updated the link.

Answer 3

Maven SQL Plugin Use this plugin to execute SQL statements a file or list of files through

1. sqlCommand
2. srcFiles 3.fileset configurations

Answer 4

Regarding SQL script runner (which I'm also using), I noticed the following piece of code:

for (int i = 0; i < cols; i++) {
  String value = rs.getString(i);
  print(value + "\t");
}

However, in the API documentation for the method getString(int) it's mentioned that indexes start with 1, so this should become:

for (int i = 1; i <= cols; i++) {
  String value = rs.getString(i);
  print(value + "\t");
}

Second, this implementation of ScriptRunner does not provide support for DELIMITER statements in the SQL script which are important if you need to compile TRIGGERS or PROCEDURES. So I have created this modified version of ScriptRunner: http://pastebin.com/ZrUcDjSx which I hope you'll find useful.

Answer 5

@Pantelis Sopasakis

Slightly modified version on GitHub: https://gist.github.com/831762/

Its easier to track modifications there.

Answer 6

Can you use this:

public static void executeSQL(File f, Connection c) throws Exception {
    BufferedReader br = new BufferedReader(new FileReader(f));
    String sql = "", line;
    while ((line = br.readLine()) != null) sql += (line+"\n");
    c.prepareCall(sql).execute(sql);
}

This function gets SQL file and DB connection. Then it reads the file line-by-line using BufferedReader from java.io.
And, finally, executes the read statements.

Java 8+ version:

public static void executeSQL(Path p, Connection c) throws Exception {
    List<String> lines = Files.readAllLines(p);
    String s = String.join("\n", lines.toArray(new String[0]));
    c.prepareCall(s).execute(s);
}