Load and execute SQL from multiple line file?

[Mark Hissink Muller]

Kindly I would like to ask what is the preferred way of loading a file containing multiple sql-statements (CREATEs and INSERTs) into a (MySQL) database?

In the setUp()-method of a JUnit-test, I would like to freshen up the test-database by dropping all tables and loading a new default state. Since some of the INSERT-statements contain quotes (") or other special characters, I cannot just load everything into a single Statement.

Is there a convenient way to encode the data so it can be used in a simple statement.executeQuery(sql)?

Thanks for your time,
Mark

[Jeanne Boyarsky]

Mark,
You could parse the file (by statement termination character) and executeUpdate() for each statement. Alternatively, you could do a batch update and save on trips to the database.

[Mark Hissink Muller]

Thanks for your thoughs Jeanne. I solved it like this:

  
public void testLoadDatabase() throws Exception
{
Connection connection = getConnection();
Statement statement = connection.createStatement();

String sql = readSqlFromFile("file.sql");
StringTokenizer semiColonTokenizer = new StringTokenizer(sql, ";");
int counter = 0;
while (semiColonTokenizer.hasMoreTokens())
{
String nextToken = (String)semiColonTokenizer.nextToken();
// prevent bogus queries from being added
if (nextToken.length() > 10)
{
statement.addBatch(nextToken+";");
counter++;
}
}

System.out.println("counter = "+counter);

int[] result = statement.executeBatch();

statement.close();
connection.close();

}