Exporting and importing relational data

Design and implement a Java application which allows for:

  • Exporting relational data to XML using JDBC.

  • Insert XML data into a relational database.

Your application shall make no assumptions whatsoever regarding a relational database's state. Records from all tables shall be dumped into one or more XML files. In addition the relational schema shall be transformed into a corresponding XSD schema containing the following meta information:

  • Table names among with their corresponding column names and data types.

  • Nullability of attributes

  • Primary / unique key definitions

  • Foreign key definitions

Testing your application may be effected by:

  • Starting from an arbitrary database state containing records in a given set of tables. As your application matures choose a database of sufficient complexity including ~5 tables with one to many and many to many relationships.

  • Export your database to XML (e.g. dump.xml) and XSD (e.g. schema.xsd) based on the JDBC connection URI , user name and password parameters being provided by a properties file.

  • Drop all database tables.

  • Import from dump.xml and the implicitly referenced schema.xsd required for recreating the relational schema and subsequently insert all corresponding records.

If your application works correctly you should end up with the initial database state.

Tip

  1. You may limit your work to Mysql using Innodb tables.

  2. Reading a database's schema requires JDBC meta data support e.g. ResultSetMetaData and possibly DatabaseMetaData.

  3. You may want to create database tables beforehand supplying foreign key constraints later by issuing appropriate ALTER TABLE Xyz ADD CONSTRAINT ...after inserting your data records. This way you avoid topological sorting of foreign key constraint induced table creation dependency problems.

  4. You may start by exporting a schema.xsd file. Then implement relational schema recreation. This schoud work not yet bothering about actual data.