Sometimes it is nice to programmatically run .sql scripts on a MySQL database using Java.  This is easily accomplished using the allowMultiQueries configuration property for the MySQL Connector/J driver.  When set to true it allows the use of ‘;’ to delimit multiple queries.

In the below code snippet line 1 ensures the MySQL driver is loaded. Line 2 creates a connection that allows multiple queries per statement. The root login is used for brevity, but in a production system the user account should have limited access. Notice the default catalog (or database) was not specified on the connection URL.

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/? \
   user=root&password=rootpassword&allowMultiQueries=true");

The code snippet below creates a database and sets the connections catalog to the newly created database.

PreparedStatement stmt = conn.prepareStatement("CREATE DATABASE IF NOT \
   EXISTS MultilineMySqlTest");
stmt.execute();
stmt.close();

conn.setCatalog("MultilineMySqlTest");

Now we want to run the follow test.sql script against the newly created database.

CREATE TABLE IF NOT EXISTS Product (
	ID int(11) NOT NULL auto_increment,
	Data varchar(512) NOT NULL,
	PRIMARY KEY (ID)
);

INSERT INTO Product (Data) VALUES ('data test 1');

INSERT INTO Product (Data) VALUES ('data test 2');

INSERT INTO Product (Data) VALUES ('data test 3');

The code below loads the entire script using the Apache common IOUtils into a string variable then executes the script.

String query = IOUtils.toString(new FileReader("./scripts/test.sql"));

stmt = conn.prepareStatement(query);
stmt.execute();
stmt.close();

conn.close();

CONCLUSION

This makes running .sql scripts from Java a breeze.  In the real world I’ve used this technique to run some pretty complicated scripts.  There are a few exceptions with running scripts in this manner, mainly, the DELIMITER keyword isn’t recognized or understood by the Connector/J driver.  If you want to run a more complicated script in the SQL Browser you would need to include DELIMITER  at the end.  I’ve also noticed sql comments tend to confuse the parser and are best avoided.