Database agnostic jdbc table import/export to files?

Question

Is it at all possible to do database-agnostic table dumps/hydrates? I don't have any complicated constraints. I would also settle for db-specific ways, but the more pure jdbc it is the better (I don't want to resort to impdp/expdp).

Answer 1

Have a look at DBUnit. DBUnit support exporting to a xml file, and importing from a xml file. Their faq has some advice on how to make the export / imports fast, be sure to check that out.

Answer 2

DBUnit looks good, however you probably are not going to beat the vendor tools for import/export. If you are going to be importing or exporting 100,000+ rows it's probably best to use impdp/expdp.

I've also done strange things like building an insert statement from a sql query and then using sqlplus to process. [select 'insert into table values ( ...'||column||' ...) ] But i was being lazy and didn't want to create a sqlldr or jdbc insert program.

I've used perl the same way for when i needed larger imports.

Answer 3

If it's a small volume of data, some programs (like Aqua Data Studio) can export data as a sequence of SQL 'INSERT' statements.