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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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.

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