Browse DevX
Sign up for e-mail newsletters from DevX


How To Copy Database Data Using JDBC : Page 2

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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<String> result = new ArrayList<String>();

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.

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