JDBC database copy of table contents
For one of our projects, we wanted to copy the contents of our production database to the test database. Both databases are accessible via JDBC. After an investigation of available tools, I had to revert to my own solution.
In our case, both the production and test databases are disclosed via the same SQL server. Integrity constraints are enforced between tables. We start with an empty test database, i.e. with empty tables and their constraints.
I investigated various existing solutions to achieve this:
- db2db: did not suffice, since it did not tolerate an already existing (empty) table structure at the destination.
- JDBC importer: the idea was to export the contents of the source database to a CSV file and import that file into the destination database. However, the tool choked on various columns. I did not find out why, the log only mentioned: “Could not export row, java.sql.SQLException: Could not retrieve column #1″
- Same procedure as outlined above with the import and export tools from Derby -> was too complicated for me
After a half a day of searching and trying out existing solutions, I figured it might be quicker to program the thing in Java/JDBC myself. I wrote a Java program that gets the table name from the command line, and copies the contents from source to dest. By traversing the database from parent to child tables, I was able to satisfy the integrity constraints.
My small Java program is contained in DBCopy.jar. Note that the passwords, usernames and DB-connection URLs are hard coded in (static) member variables. Of course, this can/should all be changed much more elegant and flexible, but for now it is simple and works. Go and get it if you got tired of searching, fill in your database specifics in the program, compile and run!
Note 1: When running my program, the JDBC driver must be on the classpath, of course.
Note 2: in case you still get integrity constraint violations, it is quite likely that somebody changed the contents of the production/source database just between your copy of the parent and child tables. This actually happened to me! After erasing the parent table at the destination and re-copying from source, the child table was copied without problems.
Note 3: Feel free to extend/distribute/modify this program where needed. It worked flawlessly for our system, but still might need some modifications.