How To Copy Database Data Using JDBC : Page 4

Copying the Data
After creating a table in the target database, you need to copy data to it. To do that you must create both SELECT and INSERT statements. The SELECT will read the table data from the source database. The INSERT statement will write the data to the target database.

First create three StringBuffer instances to hold the SELECT and INSERT statements as well as one to hold the VALUES clause of the INSERT statement.

       StringBuffer selectSQL = new StringBuffer();
       StringBuffer insertSQL = new StringBuffer();
       StringBuffer values = new StringBuffer();

The data mover retrieves the columns, displaying the status for each.

       Collection<String> columns = 
         source.getColumns(table);
   
       System.out.println("Begin copy: " + table);

Next it constructs the beginnings of the SELECT and INSERT statements.

       selectSQL.append("SELECT ");
       insertSQL.append("INSERT INTO ");
       insertSQL.append(table);
       insertSQL.append("(");

Then it loops over all the columns to create the SELECT and INSERT statements, inserting commas after all but the first column name

       boolean first = true;
       for (String column : columns)
       {
   
         if (!first)
         {
           selectSQL.append(",");
           insertSQL.append(",");
           values.append(",");
         } else
           first = false;

It adds the column name to each statement, and a question mark character (?) to the VALUES clause. The question mark is necessary because you're using parameterized SQL; you'll replace that with the actual value later. Creating the statement this way lets the database pre-compile the INSERT statement to save time.

         selectSQL.append(column);
         insertSQL.append(column);
         values.append("?");
       }

Next append the FROM and VALUES clauses.

       selectSQL.append(" FROM ");
       selectSQL.append(table);
   
       insertSQL.append(") VALUES (");
       insertSQL.append(values);
       insertSQL.append(")");

At this point you have completed the SELECT and INSERT statements, so you can execute the SELECT statement and get a list of records.

       // now copy
       PreparedStatement statement = null;
       ResultSet rs = null;
       
       try
       {
         statement = target.prepareStatement(
           insertSQL.toString());
         rs = source.executeQuery(selectSQL.toString());

The sample keeps a count of rows for status reporting.

         int rows = 0;

You can now loop over all the records to complete the INSERT statements, setting the values as you go.

         while (rs.next())
         {
           rows++;

For each INSERT statement copy in the individual column data.

           for (int i = 1; i <= columns.size(); i++)
           {
             statement.setString(i, rs.getString(i));
           }

Finally, execute the INSERT.

           statement.execute();
         }

The sample code completes by displaying status information.

     System.out.println("Copied " + rows + " rows.");
     System.out.println("");
   } 

As described here, this example is a simple utility to copy data from one database to the other, but I often use it as a foundation for larger database utilities. You'll find that it contains many of the functions that you'll need to create more complex data-copying applications.

Previous Page