My name is Philipp C. Heckel and I write about nerdy things.

Snippet 0x04: Run .sql-scripts from Java (on HSQLDB, Derby, MySQL, etc.)


Code Snippets, Programming

Snippet 0x04: Run .sql-scripts from Java (on HSQLDB, Derby, MySQL, etc.)


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:

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:

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:

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.

4 Comments

  1. Pesse

    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);


  2. Pesse

    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());



  3. Pesse

    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.