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

Write code to:

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

Answer 2

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 3

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 4

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.