Importing a (mysql) database dump programmatically through Java

Question

How can I import a mysql database dump file (contains insert and create table statements) programmatically through a java program. I need this as the setup phase of a unit test.

Unfortunately this doesn't work:

Connection conn = dbConnectionSource.getConnection();
Statement stmt = conn.createStatement();
stmt.execute(FileUtils.readFileToString(new File("./some-sql-file")));
conn.close();

Thanks, -A

PS - In Rails, I used fixtures for filling a test database. I made rails rails create the underlying tables through setting the environment to test, anything similar in Java.

"pragmatically" should be "programmatically"

Jason S
– Jason S

2009-12-23 21:01:42 +00:00
Commented Dec 23, 2009 at 21:01 — Jason S
– Jason S, Commented Dec 23, 2009 at 21:01

jvilalta · Accepted Answer · 2009-12-23 21:43:04Z

15

You could start a new process from java and execute this command if you have access to the mysql executable wherever you are running the import. Something like this:

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

answered Dec 23, 2009 at 21:43

jvilalta

6,8191 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MarkR Over a year ago

This is the right way to do this. Any other way may fail to work in subtle edge-cases. Mysql dumps are written to be imported with the mysql client, not any other tool.

Ali Salehi Over a year ago

Just a fix, we cant use redirection like that in java: Process pr = rt.exec(new String[]{"/bin/bash","-c","mysql -p -h ServerName DbName < dump.sql"}); Thanks for the answer :)

Travis Webb Over a year ago

any ideas on how to do this in Windows?

Mehdi · Accepted Answer · 2012-08-31 15:20:05Z

Backup:

/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;

/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;
executeCmd = “mysqldump -u “+dbUser+” -p”+dbPass+” “+dbName+” -r backup.sql”;
}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){

out.println(“Backup taken successfully”);

} else {

out.println(“Could not take mysql backup”);

}

Restore:

/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;

/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;

executeCmd = new String[]{“/bin/sh”, “-c”, “mysql -u” + dbUser+ ” -p”+dbPass+” ” + dbName+ ” < backup.sql” };

}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){

out.println(“success”);

} else {

out.println(“restore failure”);

}

Roland Bouman · Accepted Answer · 2009-12-23 21:05:46Z

4

Personally I would disrecommend loading a regular SQL dump in this way, because you would need non-trivial code to parse or at least tokenize SQL.

I would recommend using CSV data dumps, you can load these with a the LOAD DATA INFILE syntax. See: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Of course, you would still need to ensure the target tables exist, but if you know you only have to parse table creation DDL stattemnts, that will drastically simplify your java code.

Note that you can use mysqldump to extract CSV data from your database, see: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tab

answered Dec 23, 2009 at 21:05

Roland Bouman

32.1k8 gold badges69 silver badges69 bronze badges

1 Comment

gumuruh Over a year ago

don't they have the library for import / exporting mysql in java?

Community · Accepted Answer · 2017-05-23 12:33:12Z

0

Effective solution can be found here:

https://stackoverflow.com/a/1044837

This explains how to run any sql script over jdbc.

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Feb 22, 2013 at 11:12

shandor

532 silver badges6 bronze badges

Comments

Abderaouf Boucenna · Accepted Answer · 2022-11-12 21:36:31Z

I know this question is a bit old, but I think people are still looking for an answer like me.

Basically, I wanted to make two buttons in my GUI (one to import, and the other to export) and generate an SQL file, I tested the chosen solution to start a process from java and execute it with Runtime but it didn't work, I had an Access Denied error eventhought I am the only user in my computer. After some researches, I found this library (mysql-backup4j) and did this code :

EXPORT FUNCTION :

Properties properties = new Properties();
properties.setProperty(MysqlExportService.DB_NAME, "DATABASE_NAME");
properties.setProperty(MysqlExportService.DB_USERNAME, "DATABASE_USERNAME");
properties.setProperty(MysqlExportService.DB_PASSWORD, "DATABASE_PWD");

properties.setProperty(MysqlExportService.TEMP_DIR, new File(System.getProperty("user.dir") + "\\database_dump").getAbsolutePath());
properties.setProperty(MysqlExportService.PRESERVE_GENERATED_ZIP, "true");
MysqlExportService mysqlExportService = new MysqlExportService(properties); mysqlExportService.export();

IMPORT FUNCTION (the user chooses the SQL file) :

FileChooser fc = new FileChooser();

List<String> sqlExtensions = new ArrayList<>(List.of("*.sql", "*.SQL"));
fc.getExtensionFilters().add(new FileChooser.ExtensionFilter("Fichier SQL", sqlExtensions));

File f = fc.showOpenDialog(null);
if (f != null) {
        System.out.println("database path : " + f.getAbsolutePath());
        String sql = new String(Files.readAllBytes(Paths.get(f.getAbsolutePath())));
        boolean res = MysqlImportService.builder()
                    .setDatabase("DATABASE_NAME").setSqlString(sql)
                    .setUsername("DATABASE_USERNAME").setPassword("DATABASE_PWD").setDeleteExisting(true)
                    .setDropExisting(true)
                    .importDatabase();
}

P.S.: I am using JavaFX for the GUI with JDK11.

Reference : How to backup your MySQL database programmatically using mysql-backup4j

Collectives™ on Stack Overflow

Importing a (mysql) database dump programmatically through Java

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related