f you work with databases, you'll often need to copy data from one database to another. While that sounds like a straightforward task, it often involves more than just a straightforward copy. For example, you may need to move only part of the data, or to modify the data as it is copied. This article demonstrates a set of classes that perform basic database copies—moving tables from one database to another—using a simple application I've called a "data mover." While the example is only a starting point that shows how to copy an entire database, you'll see that you can modify the code to perform customized data copy and transformation operations, such as copying individual tables or specific table rows or columns based on your individual needs.

For novices, this article also serves as a good introduction to Java Database Connectivity (JDBC), by showing you how to perform the following JDBC tasks:

• Open a connection to a database
• Create and submit queries
• Process ResultSets returned by queries

In addition to demonstrating the basic use of JDBC, this article also shows how to query a database and gather information about the database's structure. This allows the data mover utility to gather enough information about the database to be able to copy it. Here's the four-step process that the data mover follows:

• Generate SQL CREATE TABLE statements to create target tables
• Generate SQL to drop existing tables
• Generate SQL to SELECT from the source tables
• Generate SQL to INSERT into the target tables

Before I show you exactly how to program the data mover application, I will first show you how to use it to copy a database.

Using the DataMover to Copy a Database
Included with the downloadable code you'll find a simple utility called DataMoverUtility. This utility reads a configuration file and copies data from the source database to the target database.

WARNING: As written, this utility overwrites existing data in the target database. In other words, as the utility copies tables to the target database, any tables with the same name will be deleted.

To run the utility, you must first create a configuration file. This file will contain four lines of data. For example, to test the data mover I used the following configuration file.

   sourceDriver=com.mysql.jdbc.Driver
   sourceURL=jdbc:mysql://127.0.0.1/test?user=root
   targetDriver=com.mysql.jdbc.Driver
   targetURL=jdbc:mysql://127.0.0.1/test2?user=root

The preceding configuration file tells the data mover utility to copy from one MySQL database to another MySQL database. It assumes that both of these databases are on your local computer (127.0.0.1). For every database in JDBC you must specify a driver and a connection URL. The sourceDriver and sourceURL specify the driver and URL of the source database. Likewise, the targetDriver and targetURL specify that information for the target database.

When you run the data mover utility you should specify the configuration file as the first parameter, for example:

   java DataMoverUtility c:\export.txt 

Using MySQL with Java
Before you can compile and use JDBC applications you must install the driver for the database you would like to use. Normally this is just a matter of adding the driver's JAR file to the classpath of your project.

For this article I will assume that you are using MySQL; however, the code presented here is very compatible with most databases. Anything that might be specific to MySQL has been confined to the MySQL.java class file. To use other databases simply extend the Database class and add any code that is custom to that database.

MySQL uses the Connector/J driver, which you can download from www.mysql.org. The package includes a JAR file, which you should add to your classpath. After doing that, you're ready to connect to a MySQL database.

Data Mover Structure
The data mover consists of two main packages. The package, named com.heatonresearch.datamover contains the DataMover and DataMoverUtility classes. The DataMover class contains several reusable functions that copy tables from one database to another or serve as the starting point for more advanced operations. The DataMoverUtility class is an example of how to use the classes provided by this article.

The package named com.heatonresearch.datamover.db contains all the lower-level database classes. The three classes provided in this article are DatabaseException, Database, and MySQL. The DatabaseException class defines an exception that the data mover throws whenever the it encounters a database problem. The Database class provides all the base functions for database access. The MySQL class inherits from the Database class and implements the MySQL-specific code. You can extend this class library to work with other databases by creating new database classes that descend from the Database class, similar to the MySQL class.

Connecting to MySQL
Before performing any operations you must connect to the database. To connect to any database through JDBC you must know two things: the JDBC driver name, and the database URL.

The JDBC driver name is just a class name. For MySQL, the class is provided by the MySQL JAR file that you installed earlier. The URL specifies the name of the database, where it is, and what user to use. To open a connection to the database, you should use the connect method of the Database class. The following lines of code are from that method and illustrate how to connect to the database.

   try
   {
     Class.forName(driver).newInstance();
     connection = DriverManager.getConnection(url);
   } catch (InstantiationException e)
   {
     throw new DatabaseException(e);
   } catch (IllegalAccessException e)
   {
     throw new DatabaseException(e);
   } catch (ClassNotFoundException e)
   {
     throw new DatabaseException(e);
   } catch (SQLException e)
   {
     throw new DatabaseException(e);
   }

The first two lines of code process the driver and the URL. The remaining lines of code handle the numerous exceptions that can occur when opening a database. If any exceptions do occur, they are packaged as a DatabaseException and thrown.