I am working on database migration tool in java. The tool is copying database tables with their data's to the destination database. But I want it to work on different databases. Copy from mysql and create in derby etc. With JDBC, we can gather enough information about the table and its columns. But I am going to ask this, if I can recreate tables on java with sql free. I mean different databases have different data types and some times they differs at sql syntax. So can JDBC or any other library (can be open source) do this job at an easy and global way?
Answer
Apache's DdlUtils is done what I need. When I am searching about crossdb found it, and it is very useful yet powerful. It can generate a database from scratch, just with the parameters. Or it can grab existing database table definitions, also with index definitions. You can use delimiter if you want (it is a deadly important option for me to use Apache Derby). You can just print out these definitions or apply them directly to source database (I haven't tried the second one yet). It translates definitions for the selected database. But one big problem is there is no good tutorial about how to start using it. I searched through the packages to find a good place to start. Here is what i have achieved, a sample code to generate full database table create sql.
DerbyPlatform dp = new DerbyPlatform();
dp.setDelimitedIdentifierModeOn(true);
Database dbs = new Database();
DerbyModelReader dmr = new DerbyModelReader(dp);
Database test = dmr.getDatabase(conn, "MyDBTest");
DerbyBuilder db = new DerbyBuilder(dp);
String testSqlDerby = dp.getCreateTablesSql(test, true, true);
System.out.println(testSqlDerby);
System.out.println("\n\n\n\n");
MySql50Platform mp = new MySql50Platform();
mp.setDelimitedIdentifierModeOn(true);
MySqlBuilder mb = new MySqlBuilder(mp);
String testSqlMysql = mp.getCreateTablesSql(test, true, true);
System.out.println(testSqlMysql);