Geek Answers Handbook

Database migration

I am working on a database migration tool in java. The tool copies the database tables with their data to the target database. But I want it to work in different databases. Copying from mysql and creating in derby etc. With JDBC, we can gather enough information about the table and its columns. But I'm going to ask about this if I can recreate tables in Java using sql free. I mean, different databases have different data types, and sometimes they differ in sql syntax. So can JDBC or any other library (maybe open source) do this work in a simple and global way?

PS: The first question is in SO, I read the posts, but the first time I help the site, so hello everyone.

+4
source share
4 answers

Apache DdlUtils is what I need. When I search about crossdb, I find it, and it is very useful, but powerful. It can generate a database from scratch, just with parameters. Or it can capture existing database table definitions as well as index definitions. You can use the delimiter if you want (it is very important for me to use Apache Derby). You can simply print these definitions or apply them directly to the source database (I have not tried the second yet). It converts definitions for the selected database. But one big problem is that there is no good tutorial on how to start using it. I looked through the packages to find a good place to start. Here is what I have achieved, the sample code to create a complete database table creates 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);
+2
source

I do not know that JDBC has a common mechanism for this. You probably need to create a utility library that generates SQL to create the table.

Start with the one that makes ANSI SQL and tests it on as many platforms as you want to support. Remember that Java still writes once, debugging everywhere, so you will need to test it on any platform on which you are going to support the system. A subclass of the generator if you need to make dialectical variations of the create statement for any of your platforms.

+1
source

Try SchemaCrawler, which is also open source and written in Java. It provides programmatic access to database metadata, and also allows you to create scripts in JavaScript. http://schemacrawler.sourceforge.net/

+1
source

try to see what HIBERNATE provides for migration. I know that H can generate model objects from database schemas and schemas from model objects. Therefore, perhaps you can reuse some parts from HIBERNATE. They have a DIALECT concept, which does exactly what you say: determining the specifics of the db implementation.

0
source

More articles:


All Articles