oracle数据迁移到 mysql

柔光的暖阳◎ 2022-08-27 00:29 318阅读 0赞

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;  
    \}  
  
\}