importing sql file to mysql programmatically from a CloudBees deployed Java app

Question

What is the best way to import a full .sql file (with DDL and DML sentences) to a mysql database from a Java application deployed in CloudBees?

Should I try to get the Runtime process and see if something like this works

  Runtime rt = Runtime.getRuntime();
  Process pr = rt.exec("mysql -p -h ServerName DbName < dump.sql");

(recommended solution in a previous question for a self-hosted environment, not sure if in a CloudBees hosted application I can execute process that access mysql)?

Is there a better solution? (again, to execute it from within the application, the .sql file to import will be provided by the user as part of his interaction with the web application).

I´d really like to avoid parsing the .sql file and sending the sentences one by one through jdbc.

To provide more info, so far all my attempts to get the runtime context and use it to execute mysql (or mysqldump for the export part) have failed (though they work just fine in my local installation) — Jordi Cabot
– Jordi Cabot, Commented Nov 15, 2012 at 17:13

swashbuck1r · Accepted Answer · 2012-11-15 18:49:12Z

I haven't tried this myself, but Flyway seems like it would let you import your SQL file during initialization of your app on CloudBees.

Flyway is an attempt to bring the popular Ruby concept of database migrations to Java. Flyway will let you place your .sql files inside the classpath of your app, and you can then use some Java code to update your database as needed.

Based on their migration docs, you should be able to place your .sql file as a file named V1__Initial_version.sql into a /db/migration/ directory on your classpath. You would then use something like the following code to trigger the migration when the app starts:

import com.googlecode.flyway.core.Flyway;
...

Flyway flyway = new Flyway();
flyway.setDataSource(...);
flyway.migrate();

I noticed that the Flyway FAQ explains that the database is locked during migrations, so this approach should work even if you scale out your application on CloudBees to use more than one instance (very nice!!).

Ivan Meredith · Accepted Answer · 2012-11-15 09:30:19Z

0

Give it a try I guess. mysql cmd tools may or may not be on host. If they aren't we can probably add them.

The other option would be using a Jenkins job to do it. You could expose an api that jenkins calls and loads the database.

answered Nov 15, 2012 at 9:30

Ivan Meredith

2,22214 silver badges13 bronze badges

1 Comment

Jordi Cabot Over a year ago

It looks like it´s not possible to call mysql (or mysqldump for the export part) from the runtime context. I manage to write the file I want to import in the temp folder of the app but when I try to execute mysql I get a Cannot run program "mysql" exception

Collectives™ on Stack Overflow

importing sql file to mysql programmatically from a CloudBees deployed Java app

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related