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

Spring Framework's ResourceDatabasePopulator may help. As you said you're using MySQL and JDBC, let's assume you have a MySQL-backed DataSource instance ready. Further, let's assume your MySQL script files are classpath-locatable. Let's assume you are using WAR layout and the script files are located in a directory src/main/webapp/resources/mysql-scripts/... or src/test/resources/mysql-scripts/.... Then you can use ResourceDatabasePopulator to execute SQL scripts like this:

import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import javax.sql.DataSource;

DataSource dataSource = getYourMySQLDriverBackedDataSource();

ResourceDatabasePopulator rdp = new ResourceDatabasePopulator();    
rdp.addScript(new ClassPathResource(
                        "mysql-scripts/firstScript.sql"));
rdp.addScript(new ClassPathResource(
                        "mysql-scripts/secondScript.sql"));

try {
        Connection connection = dataSource.getConnection();
        rdp.populate(connection); // this starts the script execution, in the order as added
    } catch (SQLException e) {
        e.printStackTrace();
    }

Answer 4

For simple sql script splitted by ';' you can use this simple function. It remove comments and run statements one by one

  static void executeScript(Connection conn, InputStream in)
    throws SQLException
  {
    Scanner s = new Scanner(in);
    s.useDelimiter("/\\*[\\s\\S]*?\\*/|--[^\\r\\n]*|;");

    Statement st = null;

    try
    {
      st = conn.createStatement();

      while (s.hasNext())
      {
        String line = s.next().trim();

        if (!line.isEmpty())
          st.execute(line);
      }
    }
    finally
    {
      if (st != null)
        st.close();
    }
  }

Answer 5

@Pantelis Sopasakis

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

Its easier to track modifications there.

Answer 6

Another interesting option would be to use Jisql to run the scripts. Since the source code is available, it should be possible to embed it into an application.

---

Edit: took a careful look at it; embedding it inside something else would require some modification to its source code.

Answer 7

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

Answer 8

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 9

Write code to:

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

Answer 10

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 11

For Oracle PL/SQL, the Oracle JDBC-driver indeed supports executing entire SQL-scripts including stored procedures and anonymous blocks (PL/SQL specific notation), see

Can the JDBC Drivers access PL/SQL Stored Procedures?

The Oracle JDBC driver FAQ has more info:

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle PL/SQL block syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

// SQL92 syntax
CallableStatement cs1 = conn.prepareCall
                       ( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
                       ( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
                       ( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
                       ( "begin ? := func(?,?); end;" ) ; // stored func

It should be possible to read in a file and feed the content to the prepareCall()-method.

Answer 12

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.

Answer 13

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