How To Copy Database Data Using JDBC

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.

Accessing Table and Catalog Information
To copy the database you must obtain the catalog information about the database. Specifically, you’ll need a list of the tables in the database and a list of all columns within each table. That information lets the data mover create the SQL statements necessary to copy the data.

The Database class provides a method named listTables that returns a list of all tables in the database. There are several ways to list the tables in JDBC. Most databases provide a stored procedure or system table that will give you a list of the tables. However because the name of the stored procedure or system table varies from one database to another, the most compatible way to list the tables is to use the Java class DatabaseMetaData. This class provides information about the database, such as the names of the tables.

The listTables method first creates a collection to hold the table list.

   Collection result = new       ArrayList();

Next, it creates a ResultSet to hold the list of tables, and obtains a DatabaseMetaData object.

   ResultSet rs = null;   try   {      DatabaseMetaData dbm =          connection.getMetaData();

Although the DatabaseMetaData class has many properties and methods, for this example you only want to obtain the tables, which you can do using the getTables method. The method takes three arguments: a catalog string and a schemaPattern string (both null in the example), and a string array of the table types to be retrieved. The example below creates the last array argument with the single element of TABLE, and calls the getTables method.

   String types[] = { "TABLE" };   rs = dbm.getTables(null, null, "", types);

The getTables method returns a ResultSet that contains a list of all of the tables. Now it’s just a matter of iterating over the results and adding each table name to the result collection.

     while (rs.next())     {       String str = rs.getString("TABLE_NAME");       result.add(str);     }   } catch (SQLException e)   {     throw (new DatabaseException(e));   }

A finally block ensures that the ResultSet is properly closed.

   finally   {     if( rs!=null )     {       try       {         rs.close();       } catch (SQLException e)       {       }     }   }

Finally, the method returns the list of tables.

   return result;

After obtaining the list of tables, you process them to get a list of the columns in that table. Doing that lets you create a proper CREATE TABLE statement, as well as INSERT and SELECT statements to match the current table. To get the list of columns for a table, the Database class contains a listColumns method. The process is similar to getting the table list, so I won’t explain the code; however Listing 1 shows the complete listColumns method.

After you have the database catalog information (tables and columns) you can generate the required SQL statements.

Generating a CREATE TABLE Statement
Being able to construct a CREATE TABLE statement from the database catalog information is a handy routine to use for many different purposes. The generateCreate method in the Database class begins by creating a StringBuffer to hold the CREATE TABLE statement.

       StringBuffer result = new StringBuffer();

Next create and execute a SELECT * FROM [table] to query the table structure so you can examine the results.

   try   {   StringBuffer sql = new StringBuffer();   sql.append("SELECT * FROM ");   sql.append(table);   ResultSet rs = executeQuery(sql.toString());   ResultSetMetaData md = rs.getMetaData();

Create the CREATE TABLE part of the statement first; you can fill in the columns later.

   result.append("CREATE TABLE ");   result.append(table);   result.append(" ( ");

A loop over all of the columns adds their names to the CREATE TABLE statement.

   for (int i = 1; i <= md.getColumnCount(); i++)   {

The columns in a CREATE TABLE statement are comma separated, so except for the first column, you'll need to add a comma after each column name.

     if (i != 1)       result.append(',');     result.append(md.getColumnName(i));     result.append(' ');

Retrieve the column type, and append it to the statement.

     String type = processType(md.getColumnTypeName(i),        md.getPrecision(i));     result.append(type);

You should specify the precision after the type. If the precision is above 65535, then it is a Binary Large Object (BLOB) or Text type and does not need a precision. Otherwise specify the precision and scale.

     if (md.getPrecision(i) < 65535)     {       result.append('(');       result.append(md.getPrecision(i));       if (md.getScale(i) > 0)       {         result.append(',');         result.append(md.getScale(i));       }       result.append(") ");     } else         result.append(' ');

If the type is numeric, then you should specify an UNSIGNED clause if the type is unsigned.

     if (this.isNumeric(md.getColumnType(i)))     {       if (!md.isSigned(i))         result.append("UNSIGNED ");     }

Also specify whether the data type does or does not accept NULL values.

     if (md.isNullable(i) ==        ResultSetMetaData.columnNoNulls)       result.append("NOT NULL ");     else       result.append("NULL ");     if (md.isAutoIncrement(i))       result.append(" auto_increment");     }

Finally, you must specify the primary key, which you can obtain using the DatabaseMetaData class. Any columns that are primary keys will be noted as such.

     DatabaseMetaData dbm = connection.getMetaData();     ResultSet primary = dbm.getPrimaryKeys(       null, null, table);     boolean first = true;     while (primary.next())     {       if (first)       {         first = false;         result.append(',');         result.append("PRIMARY KEY(");       } else           result.append(",");           result.append(primary.getString            ("COLUMN_NAME"));     }         if (!first)       result.append(')');

Complete the CREATE TABLE statement by adding its final parenthesis.

       result.append(" ); ");     } 

If any errors occur, the data mover throws a DatabaseException.

   catch (SQLException e)   {     throw (new DatabaseException(e));   }

Finally, the generateCreate method returns the completed CREATE TABLE statement as a String.

       return result.toString();
Author's Note: You'll need to drop any existing target database tables before executing the CREATE TABLE statement, or the generateCreate method will throw an error. The Database class contains a generateDrop method that accepts a table name and generates a DROP TABLE statement that you can execute.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a