How To Copy Database Data Using JDBC : Page 3

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.

---

Previous Page

Next Page