Create a database / execute a bunch of mysql statements from Java

Question

I have a library that needs to create a schema in MySQL from Java. Currently, I have a dump of the schema that I just pipe into the mysql command. This works okay, but it is not ideal because:

It's brittle: the mysql command needs to be on the path: usually doesn't work on OSX or Windows without additional configuration.
Also brittle because the schema is stored as statements, not descriptively
Java already can access the mysql database, so it seems silly to depend on an external program to do this.

Does anyone know of a better way to do this? Perhaps...

I can read the statements in from the file and execute them directly from Java? Is there a way to do this that doesn't involve parsing semicolons and dividing up the statements manually?
I can store the schema in some other way - either as a config file or directly in Java, not as statements (in the style of rails' db:schema or database.yml) and there is a library that will create the schema from this description?

Here is a snippet of the existing code, which works (when mysql is on the command line):

if( db == null ) throw new Exception ("Need database name!");
String userStr = user == null ? "" : String.format("-u %s ", user);
String hostStr = host == null ? "" : String.format("-h %s ", host);
String pwStr = pw == null ? "" : String.format("-p%s ", pw);

String cmd = String.format("mysql %s %s %s %s", hostStr, userStr, pwStr, db);

System.out.println(cmd + " < schema.sql");      

final Process pr = Runtime.getRuntime().exec(cmd);

new Thread() {
    public void run() {              
        try (OutputStream stdin = pr.getOutputStream()) {
            Files.copy(f, stdin);
        } 
        catch (IOException e) { e.printStackTrace(); }                          
    }
}.start();

new Thread() {
    public void run() {             
        try (InputStream stdout = pr.getInputStream() ) {
            ByteStreams.copy(stdout, System.out);
        } 
        catch (IOException e) { e.printStackTrace(); }
    }
}.start();              

int exitVal = pr.waitFor();
if( exitVal == 0 )
    System.out.println("Create db succeeded!");
else    
    System.out.println("Exited with error code " + exitVal);

Have you tried using ant and sql task, all it needs is the database driver (jar file) to execute SQL portably. (http://ant.apache.org/manual/Tasks/sql.html) You can also try DBDeploy java library which integrates to ant and allows you to incrementaly deploy SQL (http://dbdeploy.com/) — Alex Chacha, Mar 18 '13 at 17:39

score 1 · Answer 1 · edited May 23 '17 at 12:04

The short answer (as far as i know) is no. You will have to do some parsing of the file into separate statements.

I have faced the same situation and you can find many questions on this topic here on SO. some like here will show a parser. others can direct to tools Like this post from apache that can convert the schema to an xml format and then can read it back.

My main intention when writing this answer is to tell that I chose to use the command line in the end.

extra configuration: maybe it is an additional work but you can do it by config or at runtime based on the system you are running inside. you do the effort one time and you are done
depending on external tool: it is not as bad as it seems. you have some benefits too.

1- you don't need to write extra code or introduce additional libraries just for parsing the schema commands.

2- the tool is provided by the vendor. it is probably more debugged and tested than any other code that will do the parsing.

3- it is safer on the long run. any additions or changes in the format of dump that "might" break the parser will most probably be supported with the tool that comes with the database release. you won't need to do any change in your code.

4- the nature of the action where you are going to use the tool (creating schema) does not suggest frequent usage, minimizing the risk of it becoming a performance bottle neck.

I hope you can find the best solution for your needs.

+1 Thanks for sharing your experience. Arguments in favor of using the commandline seem very reasonable. I just thought it was a bit of a hack when I implemented it. — Andrew Mao, Mar 21 '13 at 15:32
I agree. It looks like a hack, as every code that needs to access command line tools :) — , Mar 21 '13 at 15:49

score 0 · Answer 2 · answered Mar 18 '13 at 17:38

0

Check out Yank, and more specifically the code examples linked to on that page. It's a light-weight persistence layer build on top of DBUtils, and hides all the nitty-gritty details of handling connections and result sets. You can also easily load a config file like you mentioned. You can also store and load SQL statements from a properties file and/or hard code the SQL statements in your code.

answered Mar 18 '13 at 17:38

herrtim

2,697
1
26
36

I used to use `DBUtils`, but switched to `QueryDSL` as it was more appropriate for my uses. Are you saying that I should use that library just for the ability to execute multiple statements? It seems like I'd still have to parse them myself from the file. – Andrew Mao Mar 18 '13 at 17:40
If you have your statements in a .properties file, Yank can read them in and you can reference the statement with the appropriate key in the properties file. This may or may not work for you depending on your needs. As far as executing "create database" statements I never tried that with Yank. Apologies, if that was the main point of your question. – herrtim Mar 18 '13 at 17:51
There are a lot of statements. I wouldn't want to paste them all into a `.properties` file manually. The schema can change as well, which is easily managed with `mysqldump` right now, but wouldn't be easy if I had to go and edit the `.properties` file after each change. – Andrew Mao Mar 18 '13 at 17:52

Create a database / execute a bunch of mysql statements from Java

2 Answers2