Mysql转数据到Oracle中

前期写了个功能实现Mysql的table schema到Oracle的装换，并没有数据的转移，现在完善了一下，实现转移数据，转移数据有点慢，是一条一条数据进行插入的，可能在插入的时候会报异常，特别是table中的not null和date类型的，要特别注意，有时间在完善了，先给出代码。

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.Connection;
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 java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;

public class TestMysql {
 public static Connection conn;
 public static Statement statement;
 public static Connection OracleConn;
 public static Statement OracleStatement;
 public static PreparedStatement PreOracleStatement;
 public Hashtable<String, Vector<Vector<String>>> hashtable = new Hashtable<String, Vector<Vector<String>>>();
 public static final String filepathCreateTable = "D://CreateTable.txt";
 public static final String filepathCreateSequence = "D://CreateSequence.txt";
 public static final String filepathCreateTrigger = "D://CreateTrigger.txt";
 public static final String filepathCreatePrimarykey = "D://CreatePrimarykey.txt";
 public static final String filepathCreateIndex = "D://CreateIndex.txt";
 
 public static final String DBdriver = "com.mysql.jdbc.Driver";
 public static final String DBURL = "jdbc:mysql://ip地址:3306/数据库?user=用户名&password=密码";
 public static final String DBSCHEMA = "idqr"; // 
  
 public static final String OracleDBdriver = "oracle.jdbc.driver.OracleDriver";
 public static final String OracleDBURL = "jdbc:oracle:thin:@ip地址:1521:数据库";
 public static final String OracleDBSCHEMA = "数据库";//我这边用户名和密码和数据库名字是一样的，有需要的要先修改
 
 
 public static final String ORACLEKEY=";COMMENT;LEVEL;TRIGGER;DATE;RESOURCE;SESSION;SIZE;USER;ROWID";//我在转移数据的时候发现的关键字

 public static Hashtable columnType=new Hashtable();
 
 public static void main(String[] args) {
  new TestMysql();
 }

 public TestMysql() {
  
  //刪除文件
  deleteFile();
  
  if (!connectionMethod()) {
   System.out.println("鏈接錯誤");
   return;
  }
  
  if (!connectionOracleMethod()) {
   System.out.println("鏈接錯誤");
   return;
  }
  
  System.out.println("操作正在進行中,請耐心等待......");

  Vector<String> table = queryAllTable(DBSCHEMA);
  if (table.size() == 0) {
   System.out.println("沒有找到表");
   return;
  }

  for (int i = 0; i < table.size(); i++) { //得到tables規格的原始數據
   hashtable.put(table.get(i), handle_table(table.get(i)));
  }
       
  generatorString(hashtable); //產生table字符串

 

//下面的方法在开始获得建表语句的时候要注释掉，当得到tablescript，在oracle中建好表后在去掉注释一起运行就ok了。

//  generatorData(hashtable,table);//轉移數據
//  check(table);   //檢驗數據是否相等
  
  
  close();//關閉連接
  System.out.println("finish");
 }

 
 public void generatorData(Hashtable hashtable,Vector<String> table){
  try {
   for(int i=0;i<table.size();i++){
    String sql="select * from "+table.get(i);
    ResultSet rst = statement.executeQuery(sql);
    ResultSetMetaData rsData=rst.getMetaData();
    
       String sqlinsert="insert into "+(ORACLEKEY.indexOf(table.get(i).toUpperCase())>0?table.get(i)+"s":table.get(i))+"(";
       String flag="";
          for(int j = 1; j<=rsData.getColumnCount() ;j++){
       sqlinsert=sqlinsert+(ORACLEKEY.indexOf(rsData.getColumnLabel(j).toUpperCase())>0?rsData.getColumnLabel(j)+"s":rsData.getColumnLabel(j))+",";
       flag=flag+"?,";
    }
         
          Hashtable tablemap= (Hashtable)columnType.get(table.get(i));
         
          sqlinsert=sqlinsert.substring(0, sqlinsert.length()-1);
          flag=flag.substring(0, flag.length()-1);
          sqlinsert=sqlinsert+")values("+flag+")";
          PreOracleStatement = OracleConn.prepareStatement(sqlinsert);
    System.out.println(sqlinsert);
          int count=0;
          while(rst.next())
       {
           count++;
     for(int j = 1; j<=rsData.getColumnCount() ;j++){
      int num=(Integer)tablemap.get(rsData.getColumnLabel(j));
      if(num==1){
       PreOracleStatement.setInt(j, rst.getInt(rsData.getColumnLabel(j)));
      }else if(num==2){
       PreOracleStatement.setString(j, rst.getString(rsData.getColumnLabel(j)));
      }else if(num==3){
       PreOracleStatement.setDate(j, rst.getDate(rsData.getColumnLabel(j)));
      }
        }
     PreOracleStatement.executeUpdate();
       }
    rst.close();
    System.out.println(table.get(i)+"有"+count+"條數據，操作完成");
   }
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
 }
 
 public void check(Vector<String> table){
  int mysql=0;
  int oracle=0;
  for(int i=0;i<table.size();i++){
   String sql="select count(1) a from "+table.get(i);
   try {
    ResultSet rst = statement.executeQuery(sql);
    if(rst.next()){
     mysql=rst.getInt(1);
    }
    rst.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   
   sql="select count(1) a from "+(ORACLEKEY.indexOf(table.get(i).toUpperCase())>0?table.get(i)+"s":table.get(i));
   try {
    ResultSet rst = OracleStatement.executeQuery(sql);
    if(rst.next()){
     oracle=rst.getInt(1);
    }
    rst.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   if(mysql!=oracle){
    System.out.println(table.get(i)+"  Oracle:"+oracle+" Mysql:"+mysql);
   }
  }
 }
 
 public void generatorString(Hashtable hashtable) {
  Iterator iter = hashtable.keySet().iterator();
  while (iter.hasNext()) {
   String tablescript = ""; // 創表語句
   String tablesequence = ""; // 建立sequence
   String tabletrigger = ""; // 建立trigger
   String tableprimarykey = "";// 建立主鍵
   String tableindex = "";// 建立索引
   String primarkeyColumn = "";
   String indexColumn = "";

   int primarykey = 0;
   int index = 0;

   String tablename = (String) iter.next();
   Vector valall = (Vector) hashtable.get(tablename);
   tablescript = "create table " + (ORACLEKEY.indexOf(tablename.toUpperCase())>0?tablename+"s":tablename) + "(";
   Hashtable<String, Integer> columnall=new Hashtable();
   
   for (int i = 0; i < valall.size(); i++) {
    Vector<String> val = (Vector) valall.get(i);
    String column_name = val.get(0).toString();// 列名
    String is_nullable = val.get(1).toString();// 是否為空，如果不允許NO,允許為YES
    String data_type = val.get(2).toString();// int,varchar,text,timestamp,date
    String character_maximun_length = val.get(3).toString();// 長度大小
    String column_key = val.get(4).toString();// 是否主鍵 是的話為PRI
               // MUL(index)
               // 有兩個PRI說明是複合index
    String extra = val.get(5).toString(); // 是否自動增長列 是的話
              // auto_increment
    String column_default = val.get(6).toString();// 是否有默認值

    if (data_type.equals("varchar") || data_type.equals("char")) { // 驗證是否有中文字符
     if (judge_china(tablename, column_name)) {
      character_maximun_length = Integer
        .parseInt(character_maximun_length)
        * 3 + "";
     }
    }
   
    tablescript = tablescript + (ORACLEKEY.indexOf(column_name.toUpperCase())>0?column_name+"s":column_name) + " ";
    
    if(ORACLEKEY.indexOf(column_name.toUpperCase())>0){
     System.out.println(tablename+" 的 "+column_name);
    }
    
    
    if (data_type.equals("int")) {
     tablescript = tablescript + "NUMBER" + " ";
     columnall.put(column_name, 1);
    } else if (data_type.equals("mediumint")) {
     tablescript = tablescript + "NUMBER" + " ";
     columnall.put(column_name, 1);
    } else if (data_type.equals("char")) {
     tablescript = tablescript + "varchar2("
       + character_maximun_length + ")" + " ";
     columnall.put(column_name, 2);
    } else if (data_type.equals("varchar")) {
     tablescript = tablescript + "varchar2("
       + character_maximun_length + ")" + " ";
     columnall.put(column_name, 2);
    } else if (data_type.equals("text")) {
     tablescript = tablescript + "varchar2(4000) ";
     columnall.put(column_name, 2);
    } else if (data_type.equals("timestamp")) {
     tablescript = tablescript + "date" + " ";
     columnall.put(column_name, 3);
    } else if (data_type.equals("date")) {
     tablescript = tablescript + "date" + " ";
     columnall.put(column_name, 3);
    } else if (data_type.equals("float")) {
     tablescript = tablescript + "NUMBER" + " ";
     columnall.put(column_name, 1);
    } else if (data_type.equals("longtext")) {
     tablescript = tablescript + "varchar2(4000) ";
     columnall.put(column_name, 2);
    } else if (data_type.equals("smallint")) {
     tablescript = tablescript + "NUMBER" + " ";
     columnall.put(column_name, 1);
    } else if (data_type.equals("double")) {
     tablescript = tablescript + "NUMBER" + " ";
     columnall.put(column_name, 1);
    } else if (data_type.equals("datetime")) {
     tablescript = tablescript + "date" + " ";
     columnall.put(column_name, 3);
    }

    if (column_default.length() > 0) { // 是否有默認值
     if (column_default.equals("CURRENT_TIMESTAMP")) {
      tablescript = tablescript + "default sysdate" + " ";
     } else {
      tablescript = tablescript + "default " + column_default
        + " ";
     }
    }

    if (is_nullable.equals("NO")) { // 是否為空值
     tablescript = tablescript + "not null,";
    } else {
     tablescript = tablescript + ",";
    }

    if (extra.equals("auto_increment")) { // 是否自動增長列
     int maxid = get_maxId(tablename, column_name);
     tablesequence = "create sequence sq_" + tablename + " "
       + "minvalue " + maxid + " "
       + "maxvalue 9999999999999999 " + "increment by 1 "
       + "start with " + maxid + " " + "cache 20;";
     tabletrigger = "EXECUTE IMMEDIATE  'create trigger tr_"
       + tablename + " " + "before " + "insert on "
       + tablename + " for each row " + "begin "
       + "select sq_" + tablename + ".nextval into:new."
       + column_name + " from dual; " + "end;';";
    }

    if (column_key.length() > 0) {
     if (column_key.equals("PRI")) {
      primarykey++;
      primarkeyColumn = primarkeyColumn + column_name + ",";
     } else if (column_key.equals("MUL")) {
      index++;
      indexColumn = indexColumn + column_name + ",";
     }
    }

   }
   
   columnType.put(tablename, columnall);

   if (primarykey == 1) {
    primarkeyColumn = primarkeyColumn.substring(0, primarkeyColumn
      .length() - 1);
    String key = "pr_" + tablename + "_" + primarkeyColumn;
    if (key.length() > 30) {
     key = "pr_" + primarkeyColumn;
    }
    tableprimarykey = "alter table " + tablename
      + "  add constraint " + key + " primary key ("
      + primarkeyColumn + ");";
   } else {
    primarkeyColumn = primarkeyColumn.substring(0, primarkeyColumn
      .length() - 1);
    String indextemp = tablename + "_index";
    if (indextemp.length() > 30)
     indextemp = primarkeyColumn.replace(',', '_') + "_index";
    tableindex = "create index " + indextemp + " on " + tablename
      + " (" + primarkeyColumn + ");";
   }

   if (index > 0) {
    indexColumn = indexColumn
      .substring(0, indexColumn.length() - 1);
    String indextemp = tablename + "_index";
    if (indextemp.length() > 30)
     indextemp = indexColumn.replace(',', '_') + "_index";
    tableindex = "create index " + indextemp + " on " + tablename
      + " (" + indexColumn + ");";
   }

   tablescript = tablescript.substring(0, tablescript.length() - 1);
   tablescript = tablescript + ");";

   if (tablescript.length() > 0)
    write(filepathCreateTable, tablescript);
   if (tablesequence.length() > 0)
    write(filepathCreateSequence, tablesequence);
   if (tabletrigger.length() > 0)
    write(filepathCreateTrigger, tabletrigger);
   if (tableprimarykey.length() > 0)
    write(filepathCreatePrimarykey, tableprimarykey);
   if (tableindex.length() > 0)
    write(filepathCreateIndex, tableindex);

  }

 }

 public void close() {
  try {
   statement.close();
   conn.close();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }

 public Vector<Vector<String>> handle_table(String tablename) {
  Vector<Vector<String>> arg = new Vector<Vector<String>>();
  try {
   String queryDetail = "SELECT * "
     + "FROM `information_schema`.`COLUMNS` "
     + "where `TABLE_SCHEMA`='" + DBSCHEMA + "' "
     + "and `TABLE_NAME`='" + tablename + "' "
     + "order by `ORDINAL_POSITION`";
   // System.out.println("sql= "+queryDetail);
   ResultSet rst = statement.executeQuery(queryDetail);

   while (rst.next()) {
    Vector<String> vec = new Vector<String>();
    String column_name = NulltoSpace(rst.getString("COLUMN_NAME"));// 列名
    String is_nullable = NulltoSpace(rst.getString("IS_NULLABLE"));// 是否為空，如果不允許NO,允許為YES
    String data_type = NulltoSpace(rst.getString("DATA_TYPE"));// int,varchar,text,timestamp,date
    String character_maximun_length = NulltoSpace(rst
      .getString("CHARACTER_MAXIMUM_LENGTH"));// 長度大小
    String column_key = NulltoSpace(rst.getString("COLUMN_KEY"));// 是否主鍵
                    // 是的話為PRI
                    // MUL(index)
                    // 有兩個PRI說明是複合index
    String extra = NulltoSpace(rst.getString("EXTRA")); // 是否自動增長列
                 // 是的話
                 // auto_increment
    String column_default = NulltoSpace(rst
      .getString("COLUMN_DEFAULT"));// 是否有默認值
    vec.add(column_name);
    vec.add(is_nullable);
    vec.add(data_type);
    vec.add(character_maximun_length);
    vec.add(column_key);
    vec.add(extra);
    vec.add(column_default);
    arg.add(vec);
   }
   rst.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }

  return arg;
 }

 public boolean judge_china(String tablename, String columnname) {
  try {
   String querysql = "select count(1) row from " + tablename
     + " where length(" + columnname + ")!=char_length("
     + columnname + ")";
   // System.out.println("sql= "+querysql);
   ResultSet rst = statement.executeQuery(querysql);
   if (rst.next()) {
    if (NulltoSpace(rst.getString("row")).equals("0")) {
     return false;
    } else {
     return true;
    }
   }
    rst.close();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
  }
  return true;
 }

 public int get_maxId(String tablename, String columnname) {
  String maxValue = "0";
  try {
   String querysql = "select max(" + columnname + ") maxid from "
     + tablename;
   // System.out.println("sql= "+querysql);
   ResultSet rst = statement.executeQuery(querysql);
   if (rst.next()) {
    maxValue = NulltoSpace(rst.getString("maxid"));
   }
    rst.close();
  } catch (SQLException e) {
  }
  if(maxValue.equals("")){
   maxValue="0";
  }
  return Integer.parseInt(maxValue)+1;
 }

 public Vector<String> queryAllTable(String table_schema) {
  Vector<String> tableName = new Vector<String>();
  try {
   String queryTable = "SELECT `TABLES`.`TABLE_NAME` "
     + "FROM `information_schema`.`TABLES` "
     + "WHERE `TABLES`.`TABLE_TYPE` = 'base table'"
     + "and `TABLES`.`TABLE_SCHEMA`  ='" + table_schema + "'";
//    System.out.println("sql= "+queryTable);  and `TABLES`.`TABLE_NAME`='timer'
   ResultSet rst = statement.executeQuery(queryTable);
   while (rst.next()) {
    tableName.add(NulltoSpace(rst.getString("TABLE_NAME")));
//    System.out.println("delete from "+rst.getString("TABLE_NAME")+";");
    if(ORACLEKEY.indexOf(NulltoSpace(rst.getString("TABLE_NAME")).toUpperCase())>0){
     System.out.println(NulltoSpace(rst.getString("TABLE_NAME"))+"修改");
    }
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
  }
  return tableName;
 }

 public boolean connectionOracleMethod() {
  try {
   Class.forName(OracleDBdriver).newInstance();
   OracleConn = DriverManager.getConnection(OracleDBURL,OracleDBSCHEMA,OracleDBSCHEMA);
   OracleStatement = OracleConn.createStatement();
   return true;
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   return false;
  }
 }
 
 public boolean connectionMethod() {
  try {
   Class.forName(DBdriver).newInstance();
   conn = DriverManager.getConnection(DBURL);
   statement = conn.createStatement();
   return true;
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
   return false;
  }
 }

 public static String NulltoSpace(Object o) {
  if (o == null)
   return "";
  else if (o.equals("null")) {
   return "";
  } else {
   return o.toString().trim();
  }
 }

 public static void deleteFile(){
  File f;
  f= new File(filepathCreateTable);
  if(f.exists()) f.delete();
  f= new File(filepathCreatePrimarykey);
  if(f.exists()) f.delete();
  f= new File(filepathCreateSequence);
  if(f.exists()) f.delete();
  f= new File(filepathCreateTrigger);
  if(f.exists()) f.delete();
  f= new File(filepathCreateIndex);
  if(f.exists()) f.delete();
 }
 
 public static void write(String path, String content) {
  String s = new String();
  String s1 = new String();
  try {
   File f = new File(path);
   if (f.exists()) {
   } else {
       f.createNewFile();
   }
   BufferedReader input = new BufferedReader(new FileReader(f));

   while ((s = input.readLine()) != null) {
    s1 += s + "\r\n";
   }
   input.close();
   s1 += content;

   BufferedWriter output = new BufferedWriter(new FileWriter(f));
   output.write(s1);
   output.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

}