Posts Tagged ‘Apache’
Executing a MySQL Script through JDBC July 30, 2013 | 03:26 pm

There’s no way to execute a MySQL script through JDBC, and most of the tools (including SimpleJdbcTestUtils) have weird requirements for the script and/or a bunch of overhead to do what you want to do. The big trick is that DELIMITER isn’t actually a SQL command: it’s a pre-processing instruction (at least conceptually). And that makes feeding the script into MySQL a pain.

Groovy and Commons-Lang StringUtils to the rescue. Here’s the short script that will break up a SQL script into a Collection of String objects, which can then be fed to your favorite SQL execution tool.

	static Collection<String> breakUpSqlScript(String loadSql) {
		List<String> statements = []
 
		String delimiter = ";"
		String currentStatement = ""
		loadSql.eachLine { String line ->
			line = line.trim()
			if(StringUtils.startsWithIgnoreCase(line, "--")) {
				// Comment: IGNORE
			} else if(line.endsWith(delimiter)) {
				line = StringUtils.removeEnd(line, delimiter)
				currentStatement = "$currentStatement \n $line".trim()
				statements << currentStatement
				currentStatement = ""
			} else if(StringUtils.startsWithIgnoreCase(line, "DELIMITER")) {
				delimiter = StringUtils.removeStartIgnoreCase(line, "DELIMITER").trim()
			} else {
				currentStatement = "$currentStatement \n $line"
			}
		}
		if(!StringUtils.isBlank(currentStatement)) statements << currentStatement
 
		return statements
	}

PS: If you want to get the contents of a file as a string, use File#getText().