how to copy a schema in mysql using java

Question

in my application i need to copy a schema with its tables and store procedures from a base schemn to a new schema.

i am looking for a way to implement this. i looked into exacting the mysqldump using cmd however it is not a good solution because i have a client side application and this requires an instillation of the server on the client side. the other option is my own implantation using show query. the problem here is that i need t implement it all from scratch and the must problematic part is that i will need to arrange the order of the tables according to there foreign key (because if there is a foreign key in the table, the table i am pointing to needs to be created first).

i also thought of creating a store procedure to do this but store procedures in my SQL cant access the disk.

perhaps someone has an idea on how this can be implemented in another way?

Answer 1

You can try using the Apache ddlutils. There is a way to export the ddls from a database to an xml file and re-import it back.

The api usage page has examples on how to export schema to an xml file, read from xml file and apply it to a new database. I have reproduced those functions below along with a small snippet on how to use it to accomplish what you are asking for. You can use this as starting point and optimize it further.

DataSource sourceDb;
DataSource targetDb;

writeDatabaseToXML(readDatabase(sourceDb), "database-dump.xml");
changeDatabase(targetDb,readDatabaseFromXML("database-dump.xml"));



public Database readDatabase(DataSource dataSource)
{
   Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
   return platform.readModelFromDatabase("model");
}

public void writeDatabaseToXML(Database db, String fileName)
{
    new DatabaseIO().write(db, fileName);
}

public Database readDatabaseFromXML(String fileName)
{
    return new DatabaseIO().read(fileName);
}

public void changeDatabase(DataSource dataSource,
                           Database   targetModel)
{
    Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
    platform.createTables(targetModel, true, false);
}