switching sql db’s data using dbunit

recently i search for data migration from mysql to h2. two dbs have different sql syntax
and at first i thought it’ll become a very hard job. but using dbunit, i can switch database
so easily and perfectly. this is very useful for any kind of sql db migration, not only mysql to h2.
oracle or postgresql or mssql or any db to any db else!

and the job is very simple and fast

just type this command
mvn dbunit:export
and you can see xml file with all data from db in target/dbunit/export.xml

next, change db setting in maven’s pom.xml or set db explicitly and load these data
mvn -Ph2 dbunit:operation

and that’s all, game over! and sorry, really it was not that easy..
there’s some problems in dbunit. dbunit doesn’t support foreign key.
so if there are two tables, address, zipcode and address refer zipcode,
you can’t input address first, you have to move it below zipcode.
and dbunit get data from alphabetical order, and put data sequentially
you have to care about this characteristic.

2nd one was more difficult to solve. dbunit gets data as an utf-8 text format
including non-compatible character, 0×00, i don’t know why. it’s obviously weird.
if there’s 0×00 you cannot insert data before you remove that character.
i know one editor that remove it automatically. it’s EmEditor! cool!
and you have nothing to do, just open and save as make things work!
maybe tr or something do the samething but i didn’t test  another  way.

3rd one was not caused by dbunit, it caused by db’s sql syntax, and hibernate.
varchar’s length or text type is different  little.  and mysql skips some unexplicit
annotation setting and other db doesn’t. e.g) you can set length=6
in hibernate annotation and save 7 char string in mysql, but it generate an error in h2.

and finally i succeeded to migrate! thanx god! i probably died if i had to change mysql sql syntax to h2’s..

dbunit is very cool stuff, but has some bugs.. i wish it’s next version will be solved these bugs..
but too slow to update.. i may participate dbunit’s improvement.. can i?.. um.. um..