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.
Is it possible to obtain a complete copy of the updated class mentioned here? “March 15th, 2006 at 4:04 pm Here’s an updated version that copes ..”
The following example also can be useful:
Copy table from one database to another
text of the comment no.9 is cut out on page the page preview, so there is not possible to get compleate code of DBCopy version2. have you done it for purpouse, or can you please update the DBCopy.jar url with newest version. Thank you and best regards.
Nice Thing, I got some requirement and about to start code, i found yours.
I could able to see only part of your code. How can i get complete one.
Thank you
Raj
Here’s an updated version that copes with multiple tables, uses prepared statements and batches results for speed.
Note that you need to pecify a db/properties.properties file with the programs defaults.
package db;
/*
* Copy the contents of a table from one database to another.
*
* Created 2nd of December 2004 by Zeger W. Hendrikse
*
* Modifed 15th of March 2006 by Karl Wyer
*
* Version 2.0
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
/**
* Copy the contents of a table from one database to another.
*/
public class DBCopy {
private static String toURL;
private static String toUser;
private static String toPasswd;
private static String fromURL;
private static String fromUser;
private static String fromPasswd;
private static String fromDriver;
private static String toDriver;
private static Connection fromDB;
private static Connection toDB;
private static String[] tablesToCopy;
public static void main(String args[]) {
readProperties();
createConnection();
for (String table : tablesToCopy) {
System.out.println(“Deleting data from ” + table);
clearTable(table);
System.out.println(“Copying table ” + table);
copyTable(table);
try {
if (toDB != null) {
toDB.commit();
}
if (fromDB != null) {
fromDB.commit();
}
} catch (SQLException e) {
System.out.println(“Commits failed: ” + e);
}
}
closeConnections();
}
/**
* Read all the properties for this run
*
*/
private static void readProperties() {
ResourceBundle rb = ResourceBundle.getBundle(“db.properties”);
toURL = rb.getString(“TO_URL”);
toUser = rb.getString(“TO_USER”);
toPasswd = rb.getString(“TO_PASSWD”);
toDriver = rb.getString(“TO_DRIVER”);
fromURL = rb.getString(“FROM_URL”);
fromUser = rb.getString(“FROM_USER”);
fromPasswd = rb.getString(“FROM_PASSWD”);
fromDriver = rb.getString(“FROM_DRIVER”);
String tablesString = rb.getString(“TABLES”);
tablesToCopy = tablesString.split(“,”);
}
/**
* Close the database connections before we exit
*
*/
private static void closeConnections() {
try {
if (toDB != null) {
toDB.close();
}
if (fromDB != null) {
fromDB.close();
}
} catch (SQLException e) {
System.out.println(“Closing failed: ” + e);
}
}
/**
* Delete the contents of the TO table.
*
* @param tableName
*/
private static void clearTable(String tableName) {
try {
Statement deleteStatement = toDB.createStatement();
deleteStatement.execute(“delete from ” + tableName);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* Copy the contents of a table from source to dest database.
*/
private static void copyTable(String tableName) {
Statement fromStatement = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
fromStatement = fromDB.createStatement();
resultSet = fromStatement.executeQuery(“select * from ” + tableName);
// Find out the number of columns
int columnCount = resultSet.getMetaData().getColumnCount();
StringBuilder psSQL = new StringBuilder(“INSERT INTO “);
psSQL.append(tableName);
psSQL.append(” VALUES (“);
for (int j = 1; j
One obvious flaw is that one has to check the type by means of equalsIgnoreCase(“DATE”)
and so forth. On the Oracle installation I’m working against at the moment, there was no hit
in the tests in the original code. But when I changed it from “equals(..)” to
“equalsIgnoreCase(..)”, there was response.
Nice work anyway.
You can make this program go alot faster if you set a fetch size on the result set, and
update in batches. This is helpful when copying alot of data.
This case concerned a JDBC accessible SQL server, but you may be right that SQL server supports replication too. However, I didn’t have the tools/knowledge/access to my avail to go for “direct replication”.
Isn’t that called replication, a feature most databases have? What if your program is interrupted due to network outage–can it pick up where it left off, even if the copies are not done in key order?
Of course, now that you mention it! What a pity that I didn’t think of this tool myself, because I do know about DBUnit. Apparently, somehow this option wasn’t triggered in my memory… well, let’s say it was useful to refresh my JDBC skills 😉
Many thanks!
DBUnit is excellent for this! Handles referential integrity too. Just export a schema to XML and import it to the target schema.
You’re absolutely right. I allowed myself (too) lazy programming and should have cleaned it up before publishing. I’ll upload an updated & revised version first thing tomorrow!
(Update has taken place)
Allthough it is just a “run once” program, an easy improvement would be to close all your statements, resultsets and connections in a finally block. Even in case of an exception you’ll leave everything nicely behind …