Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


How To Copy Database Data Using JDBC : Page 4

Copying database data is a basic, yet endlessly complex task, because not all data copy operations are alike. But the sample database-copy classes you'll find here provide a basic template for nearly every type of copy you need to perform.




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

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.

Jeff Heaton is an author, college instructor, and consultant. Jeff is the author of four books and over two dozen journal and magazine articles. Jeff maintains a personal website where he publishes information about artificial intelligence, spider/bot programming, and other topics.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date