Database Migration simplified….

August 3, 2006

Here is my actual code for migration.. dont get confused with ConnectionString object, its from proprietory API.  Instead, you can use the Connection from java.sql.

This program has the following spec:

. This program must read all the tables from the source database and create them in the target database. After creation, data from each table in the source must be copied into the corresponding table in the target.

  • Source and Target databases are accessible and the drivers are in the classpath.
  • The input parameters will be a source com.rli.util.ConnectionString object and a target com.rli.util.ConnectionString object.  (See javadoc on how to use the ConnectionString class)
  • The connection information like driver, url, ursername and password must be extracted from this object to make the connection to the respective databases.
  • All constraints must be recreated.

The actual Code goes here:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.rli.util.ConnectionString;

public class Test {

 public static void main(String[] args) throws Exception {

  com.rli.util.ConnectionString sourceCn = new ConnectionString(
    “org.hsqldb.jdbcDriver”, “jdbc:hsqldb:hsql://localhost:9001”,
    “sa”, “”);
  com.rli.util.ConnectionString destnCn = new ConnectionString(
    “org.apache.derby.jdbc.ClientDriver”,
    “jdbc:derby://localhost:1527/Test8”, “app”, “app”);
  migratesourceDest(sourceCn, destnCn);
 }

 private static Connection sourceConn() {
  Connection conn = null;
  com.rli.util.ConnectionString sourceCn = new ConnectionString(
    “org.hsqldb.jdbcDriver”, “jdbc:hsqldb:hsql://localhost:9001″,”sa”, “”);
  try {
   Class.forName(sourceCn.getDriverName());
   conn = DriverManager.getConnection(sourceCn.getURL(), sourceCn
     .getUser(), sourceCn.getPassword());
  } catch (Exception e) {
   e.printStackTrace();
  }

  return conn;
 }

 private static Connection destnConn() {
  Connection conn = null;
  com.rli.util.ConnectionString destnCn = new ConnectionString(
    “org.apache.derby.jdbc.ClientDriver”,
    “jdbc:derby://localhost:1527/Test8”, “app”, “app”);
  try {
   Class.forName(destnCn.getDriverName());
   conn = DriverManager.getConnection(destnCn.getURL(), destnCn
     .getUser(), destnCn.getPassword());
  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 }
 

 private static void migratesourceDest(ConnectionString source,
   ConnectionString destn) throws Exception {

  try {
   Class.forName(source.getDriverName());
   Connection conn = DriverManager.getConnection(source.getURL(),
     source.getUser(), source.getPassword());
   Statement sql_stmt = conn.createStatement();
   String[] strarr = new String[5];

   try {
    DatabaseMetaData dbmd = conn.getMetaData();
   
    String pkField = null;

    String[] types = new String[]{“TABLE”};
    ResultSet resultSet = dbmd.getTables(null, null, “%”, types);
    
   

    while (resultSet.next()) {
     String tableType = resultSet.getString(“TABLE_TYPE”);
     System.out.println(“tableType: “+tableType);
     if(tableType.equals(“TABLE”)){
     
     String sourceTableName = resultSet.getString(“TABLE_NAME”);
     ResultSet results = dbmd.getPrimaryKeys(null, null,
       sourceTableName);
     boolean first = true;
     StringBuffer result = new StringBuffer();

     while (results.next()) {

      if (first) {
       first = false;
       result.append(‘,’);
       result.append(“PRIMARY KEY(“);
      } else
       result.append(“,”);
      result.append(results.getString(“COLUMN_NAME”));
      pkField = results.getString(“COLUMN_NAME”);
      System.out.println(“Primary Key : ” + pkField
        + “for table:” + sourceTableName);

      for (int i = 0; i < strarr.length; i++) {

       strarr[i] = results.getString(“COLUMN_NAME”);

      }

     }

     if (!first)
      result.append(‘)’);
     System.out.println(“result: ” + result);
     String tableDesc = getTableDesc(sourceTableName, result,
       pkField, conn);
     System.out.println(tableDesc);

     createDestTable(destn, sourceTableName, tableDesc);
     copyData(sourceTableName);
    }}
   } catch (SQLException e) {
    e.printStackTrace();
   }
   sql_stmt.close();
   conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }

 }

 private static void createDestTable(ConnectionString destn,
   String sourceTableName, String tableDesc) throws Exception {
  try {
   int exUpdate;
   Class.forName(destn.getDriverName());
   Connection connection =

   DriverManager.getConnection(destn.getURL(), destn.getUser(), destn
     .getPassword());
   Statement stmt = connection.createStatement();
   String sql = “CREATE TABLE ” + sourceTableName + “(” + tableDesc
     + “)”;
   System.out.println(“sql:” + sql);
   exUpdate = stmt.executeUpdate(sql);
   System.out.println(“UpdateCount” + exUpdate);
   connection.close();
  } catch (SQLException e) {
   // e.printStackTrace();
  }
 }

 private static String getTableDesc(String tableName, StringBuffer strarr,
   String pColName, Connection conn) throws Exception {

  String tableDesc = null;

  String typeName = null;
  final String sqlBase = “select * from “;
  String sql = sqlBase + tableName + ” where 1 = 2″;
  Statement stmt = null;
  ResultSet rslt = null;
  
  try {

   
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(sql);
   ResultSetMetaData meta = rslt.getMetaData();
   int numCols = meta.getColumnCount();
   StringBuffer strBuf = new StringBuffer();
   for (int i = 1; i <= numCols; i++) {
    int precision = meta.getPrecision(i);
    String colName = meta.getColumnName(i);

    if ((meta.getColumnTypeName(i).equals(“Counter”)) ||

    (meta.getColumnTypeName(i).equals(“counter”)) ||

    (meta.getColumnTypeName(i).equals(“COUNTER”))) {
     typeName = “INTEGER”;
    }

    else if

    (meta.getColumnTypeName(i).equals(“DATETIME”)) {
     typeName = “Date”;

    } else if

    (meta.getColumnTypeName(i).equals(“LONGCHAR”)) {
     typeName = “VARCHAR”;
    }

    else if

    (meta.getColumnTypeName(i).equals(“CURRENCY”)) {
     typeName = “NUMERIC”;
    }

    else if ((meta.getColumnTypeName(i).equals(“BIT”))) {
     typeName = “VARCHAR”;
    }
    
    /*
     else if ((meta.getColumnTypeName(i).equals(“LONGVARBINARY”))) {
     typeName = “VARCHAR(1) FOR BIT DATA”;
    }*/
    
    
   
    
    
    else if

    ((meta.getColumnTypeName(i).equals(“LONGBINARY”))|| (meta.getColumnTypeName(i).equals(“LONGVARBINARY”))) {
     
    typeName = “BLOB”;
    }

    else if (meta.getColumnTypeName(i).equals(“VARCHAR_IGNORECASE”)) {
     typeName = “VARCHAR”;
    } else if (meta.getColumnTypeName(i).equals(“LONGVARCHAR”)) {
     typeName = “VARCHAR”;
    }

    else {
     typeName = meta.getColumnTypeName(i);
    }

    if (!(colName.equals(pColName))) {

     if (colName.indexOf(‘ ‘) >= 0) {
      
      strBuf.append(“\””);
      strBuf.append(colName);
      strBuf.append(“\””);
      strBuf.append(” “);
      strBuf.append(typeName);

     } else {

      strBuf.append(colName);
      strBuf.append(” “);
      strBuf.append(typeName);
     }

    }

    else if ((colName.equals(pColName))
      && (!(typeName.equals(“VARCHAR”)))) {

     if (colName.indexOf(‘ ‘) >= 0) {
      
      strBuf.append(“\””);
      strBuf.append(colName);
      strBuf.append(“\” “);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      

     } else {

      strBuf.append(colName);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      
     }

    }

    else if ((colName.equals(pColName))
      && (typeName.equals(“VARCHAR”))) {

     if (colName.indexOf(‘ ‘) >= 0) {
      
      strBuf.append(colName);
      strBuf.append(” \” “);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(‘(‘);
      strBuf.append(“500″);
      strBuf.append(‘)’);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      

     } else {

      strBuf.append(colName);
      strBuf.append(”  “);
      strBuf.append(typeName);
      strBuf.append(‘(‘);
      strBuf.append(“500″);
      strBuf.append(‘)’);
      strBuf.append(”  “);
      strBuf.append(“NOT NULL”);
      
     }

    }

    

    if ((!(colName.equals(pColName)))
      && (typeName.equals(“VARCHAR”))) {
     if (precision > 255) {
      strBuf.append(‘(‘);
      strBuf.append(“500”);
      strBuf.append(‘)’);

     } else {
      strBuf.append(‘(‘);
      strBuf.append(“500”);
      strBuf.append(‘)’);
     }
    }
    if (typeName.equals(“INTEGER”)) {
     if (precision > 0) {
      strBuf.append(“”);
     }
    }
    if (typeName.equals(“NUMERIC”)) {
     if (precision > 0) {
      strBuf.append(“”);
     }
    }

    if (precision > 0) {
     // strBuf.append(‘(‘);
     // strBuf.append(precision);
     // strBuf.append(‘)’);
    }

    if ((colName.equals(pColName))) {

     strBuf.append(strarr);

    }

    if (i < numCols) {
     strBuf.append(‘,’);
     }
   }

   tableDesc = strBuf.toString();

  } catch (SQLException e) {

  } finally {
   if (stmt != null) {
    try {
     stmt.close();
    } catch (SQLException ignore) {
    }
   }
   if (rslt != null) {
    try {
     rslt.close();
    } catch (SQLException ignore) {
    }
   }
  }
  System.out.println(tableDesc);

  return tableDesc;

 }

 private static void copyData(String tableName) throws Exception {

  final String sqlBase = “select * from “;
  String sql = sqlBase + tableName + ” where 1 = 2″;
  Statement stmt = null;
  ResultSet rslt = null;
  String colName = null;
  PreparedStatement statement = null;
  PreparedStatement pst2 = null;
  ResultSet rs = null;

  StringBuffer selectSQL = new StringBuffer();
  StringBuffer insertSQL = new StringBuffer();
  StringBuffer values = new StringBuffer();

  System.out.println(“Begin copy: ” + tableName);

  selectSQL.append(“SELECT “);
  insertSQL.append(“INSERT INTO “);
  insertSQL.append(tableName);
  insertSQL.append(“(“);

  try {

   Connection conn = sourceConn();
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(sql);
   ResultSetMetaData meta = rslt.getMetaData();
   int numCols = meta.getColumnCount();
   StringBuffer strBuf = new StringBuffer();

   for (int i = 1; i <= numCols; i++) {
    boolean first = true;
    colName = meta.getColumnName(i);
    strBuf.append(colName);
    strBuf.append(”  “);
    if (!first) {
     selectSQL.append(“,”);
     insertSQL.append(“,”);
     values.append(“,”);
    } else
     first = false;

    selectSQL.append(colName);

    insertSQL.append(colName);

    values.append(“?”);

    if (i < numCols) {
     selectSQL.append(“,”);
     insertSQL.append(“,”);
     values.append(“,”);

    }
   }
   selectSQL.append(” FROM “);
   selectSQL.append(tableName);
   insertSQL.append(“) VALUES (“);
   insertSQL.append(values);
   insertSQL.append(“)”);

   Connection sConn = sourceConn();
   Connection dConn = destnConn();
  
   pst2 = sConn.prepareStatement(selectSQL.toString());
   statement = dConn.prepareStatement(insertSQL.toString());

   rs = pst2.executeQuery();

   int rows = 0;

   while (rs.next()) {
    rows++;
    for (int i = 1; i <= numCols; i++) {
     statement.setString(i, rs.getString(i));
     }
    statement.execute();
   }

   System.out.println(“Transferred ” + rows + ” rows.”);
   

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {

   if (rs != null) {
    try {
     rs.close();
    } catch (SQLException sqle) {
     sqle.printStackTrace();
    }
   }
   if (stmt != null) {
    try {
     stmt.close();
    } catch (SQLException sqle) {
     // sqle.printStackTrace();
    }
   }
   if (rslt != null) {
    try {
     rslt.close();
    } catch (SQLException sqle) {
     // sqle.printStackTrace();
    }
   }
  }
 }
}

Advertisements

One Response to “Database Migration simplified….”

  1. Joel Ark said

    Good job tauri.which database are u migrating to where is this the postgres thing?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: