Running a .sql script using MySQL with JDBC

user_119895 0
Viewed 69330

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?

jdbc mysql sql java
13 Answers

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!

user_1224280
user_27392110

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.

user_6130160

Write code to:

  1. Read in a file containing a number of SQL statements.
  2. Run each SQL statement.
user_42490

There isn't really a way to do this.

You could either run the mysql command line client via Runtime.exec(String[]) and read this article when you decide for this option

Or try using the ScriptRunner (com.ibatis.common.jdbc.ScriptRunner) from ibatis. But it's a bit stupid to include a whole library just to run a script.

user_1224280

Here's a quick and dirty solution that worked for me.

public void executeScript(File scriptFile) {
  Connection connection = null;
  try {
    connection = DriverManager.getConnection(url, user, password);
    if(scriptFile.exists()) {
      var buffer = new StringBuilder();
      var scanner = new Scanner(scriptFile);
      while(scanner.hasNextLine()) {
        var line = scanner.nextLine();
        buffer.append(line);
        // If we encounter a semicolon, then that's a complete statement, so run it.
        if(line.endsWith(";")) {
          String command = buffer.toString();
          connection.createStatement().execute(command);
          buffer = new StringBuilder();
        } else { // Otherwise, just append a newline and keep scanning the file.
          buffer.append("\n");
        }
     }
    }
    else System.err.println("File not found.");
  } catch (SQLException e) {
    e.printStackTrace();
} finally {
  if(connection != null) connection.close();
}
user_12349900
Related