oracle数据迁移到 mysql

来源：互联网发布：汽车保养提醒软件编辑：程序博客网时间：2024/04/26 23:26

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class TestSql
{

    public static void main(String[] args) throws Exception{
//        String paraSql="provider_id in(select p.provider_id from t_provider p where p.attribution_id = 1)";
//        String bmsTables="t_provider@attribution_id=1,t_provider_contact@"+paraSql+",t_operator@"+paraSql+",t_content_provider@"+paraSql+","+
//        "t_service_provider@"+paraSql+",T_PROVIDER_BUSINESS@"+paraSql+","+
//        "T_PROSPEC_R,T_PROD_RESOURCE_R,t_productoffering_r,"+
//        "t_price_policy,t_prodoffering_price_r,t_promotion_policy,t_prodoffering_promotion_r";
//        String ip="172.20.14.6";
//        String user="bmsb036gd_0716";



      String iepgmTables="T_COLUMN@parent_id!=0,T_IEPG_ASSET,T_IEPG_ASSET_FILE,T_IEPG_CHANNEL_TYPE,T_IEPG_CHANNEL,T_IEPG_CHANNEL_TYPE_RELATION,T_IEPG_GOODS" +
             ",T_IEPG_PROGRAM_GUIDE,T_IEPG_PROVIDER,T_NPVR_RECORD,T_PRODOFFERING,T_PO_RESOURCE,T_REGION_FREQ,T_RESOURCE_POSTER,T_RES_COLUMN_MAP,T_USERINFO,T_USER_RECOMMAND";
      String ip="172.20.100.25";
      String user="sdp_xxx_b052";

        printSql(iepgmTables,ip,user,"xxxxxx");
    }

    private static void printSql(String tables,String ip,String user,String pass) throws Exception{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = null;
        try{
            con = DriverManager.getConnection("jdbc:oracle:thin:@"+ip+":1521:orcl",user,pass);
            Map<String ,Object> tempMap = new HashMap<String ,Object>();
            for(String table : tables.split(",")){
                if(tempMap.containsKey(table)){
                    continue;
                }else{
                    tempMap.put(table, table);
                }
                String[] tw = table.split("@");
                table = tw[0];
                String where =null;
                if(tw.length==2){
                    where = tw[1];
                }
                String gen = getSql(con, table,where,user);
                System.out.println(gen);
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            if(con!=null) con.close();
        }
    }

    private static String getSql(Connection con,String table,String where,String owner)throws Exception{
        String sql="select column_name,data_type from all_tab_columns where table_name='"+table.toUpperCase()+"' AND owner='"+owner.toUpperCase()+"'";
        ResultSet rs = con.createStatement().executeQuery(sql);
        String gen="select 'insert into "+table.toLowerCase()+"(`";
        List<String> cols = new ArrayList<String>();
        List<String> values = new ArrayList<String>();
        while(rs.next()){
            String col = rs.getString("COLUMN_NAME");
            cols.add(col);
            String type= rs.getString("DATA_TYPE");
            if(type.contains("DATE")||type.contains("TIME")){
                values.add("'||decode("+col+",null,'null',''''||to_char("+col+",'yyyy-mm-dd hh24:mi:ss')||'''')||'");
            }else if(type.contains("CHAR")){
                values.add("'||decode("+col+",null,'null',''''||"+col+"||'''')||'");
            }else if(type.contains("NUMBER")){
                values.add("'||decode("+col+",null,'null',"+col+")||'");
            }
        }
        gen += StringUtils.join(cols.toArray(),"`,`")+"`) values (";
        gen += StringUtils.join(values.toArray(),",")+");' from "+table+(where==null?"":" where "+where)+";";
        return gen;
    }

}

0 0