Java is sometimes harder than it should be. Oftentimes, very easy things are made hard for no apparent reason. Running SQL scripts (.sql) from inside Java is one of those cases. I really expected this to be easy. And it should be, right? Simply take your JDBC connection and do connection.executeScript() or something. After all, a .sql-script is just a file with many statements.
Turns out there is no easy way to do it without writing some code yourself — at least not to my knowledge. Please enlighten me if there is. In the meantime, checkout my SqlRunner class — a class that reads SQL script files and executes the statements.
Content
1. Full code
As always, the full working code (embedded in a my open source file sync software Syncany is available on GitHub. Feel free to use it, or to leave feedback in the comments.
2. The SqlRunner reads .sql-scripts and runs all statements
The basic idea is simple: Read the .sql-file from an input stream line by line. If the line does not end with a delimiter (by default a semi-colon ;), append the line to the statement-buffer. If the line ends with a delimiter, append it to the statement buffer, execute the statement and reset the buffer.
2.1. SqlRunner main loop
Pretty simple right? Yes! There is no magic to it. Here’s what I just said in code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
while ((line = lineReader.readLine()) != null) { if (command == null) { command = new StringBuffer(); } String trimmedLine = line.trim(); Matcher delimiterMatcher = NEW_DELIMITER_PATTERN.matcher(trimmedLine); Matcher commentMatcher = COMMENT_PATTERN.matcher(trimmedLine); if (delimiterMatcher.find()) { delimiter = delimiterMatcher.group(1); } else if (commentMatcher.find()) { // Ignore comments! } else { command.append(trimmedLine); command.append(" "); if (trimmedLine.endsWith(delimiter)) { Statement statement = connection.createStatement(); statement.execute(command.toString()); statement.close(); command = null; } } } |
As you can see the code is pretty straight forward. A BufferedReader is used to read a line, the line is trimmed and matched to two regular expressions. One to detect comments (lines starting with //, # or --) and one to detect delimiter changes.
Delimiter changes, you ask? Why that? Well, SQL functions and procedures (create function .., etc.) consist of other statements and most likely contain semi-colons at the line end. To handle this in the SqlRunner, the delimiter can be changed using --!DELIMITER=.. (see below for an example).
2.2. Usage of the class
Using the class is pretty easy. The SqlRunner has just one static method: runScript(Connection connection InputStream inputStream) takes two arguments, one database Connection and one InputStream:
|
1 2 3 4 5 6 7 |
Connection connection = DriverManager.getConnection("jdbc:hsqldb:file:/some/file;user=sa;..."); InputStream scriptInputStream = YourClass.class.getResourceAsStream("/sql/create.all.sql"); // Alternatively: // InputStream scriptInputStream = new FileInputStream(new File("/some/file.sql")); SqlRunner.runScript(connection, inputStream); |
The input stream is used to read the script (examples see below) from a resource, a file or a network resource. The connection must be an active database connection, e.g. created by a DriverManager.
3. Example .sql scripts
For Syncany, I use this exact class to create the initial database — using the create.all.sql-script. Here is a small excerpt:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE CACHED TABLE known_databases ( id int NOT NULL IDENTITY, database_name varchar(255) NOT NULL, UNIQUE (database_name) ); -- Functions --!DELIMITER=end; create function substr_count(haystack varchar(255), needle varchar(255)) returns integer begin atomic declare strCount integer; declare lastIndex integer; set strCount = 0; set lastIndex = 1; while lastIndex <> 0 do set lastIndex = locate(needle, haystack, lastIndex); if lastIndex <> 0 then set strCount = strCount + 1; set lastIndex = lastIndex + length(needle); end if; end while; return strCount; end; |
As you can see, the first statement ends with a ; in line 5, and the function ends with end; in line 29. The delimiter is changed in line 8 by --!DELIMITER=end;.
A. About this post
I’m trying a new section for my blog. I call it Code Snippets. It’ll be very short, code-focused posts of things I recently discovered or find fascinating or helpful. I hope this helps.
Hey, great post, thank you!
Although it might be necessary to remove the delimiter at the end of the command before executing:
+line 24:
command.delete(command.length()-delimiter.length()-2, command.length()-1);
Narf – see what happens if you don’t test it before posting:
Of course it should be like this:
+line 24:
command.delete(command.length()-delimiter.length()-1, command.length());
Thanks for sharing. For HSQLDB it works like this. :)
Never used HSQLDB so far but I am very sure that Oracle has serious problems with a delimiter at the end (or in this case it is treated as unknown character) and most likely the same for MySQL.
I can btw confirm that the code works greatly and it is no problem anymore to run scripts which contain triggers and procedures etc.