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?

Pratfall

#8206685 – Havener

@Havener thanks but this doesn't help because it doesn't take into account the order the tables should be created. – Pratfall

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

Photoneutron

Answer 2

You can use information_schema to fetch the foreign key information and build a dependency tree. Here is an example.

But I think you are trying to solve something that has been solved many times before. I'm not familiar with Java, but there are ORM tools (for Python at least) that can inspect your current database and create a complementing model in Java (or Python). Then you can deploy that model into another database.

Thema

i yes i am familiar with some tools that do this like hibernate but it will take to long to ingrate them into my application. so i need something that does this using the regular JDBC. – Pratfall