Browse DevX
Sign up for e-mail newsletters from DevX


How To Copy Database Data Using JDBC : Page 3

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

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.

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