Oracle向MySQL迁移表结构和数据

Oracle向MySQL迁移表结构和数据

实现思路

简单的实现了读取Oracle的表字段的字段名、类型、长度和小数位。根据Oracle的字段类型转换成MySQL的字段类型（对应在代码里面可以看到）并生成建表语句。代码比较原始,可以自行进行封装修改。

代码实现

ColumnData.java

package tm.entity;

/**
 * Oracle字段信息实体类
 * @author Cetrin Wang
 *
 */
public class ColumnData {
    private String columnName;//列名
    private String columnType;//类型
    private int columnSize;   //长度
    private int colScale;     //小数位

    public ColumnData(){
    }

    public ColumnData(String columnName,String columnType,int columnSize){
        this.columnName = columnName;
        this.columnType = columnType;
        this.columnSize = columnSize;
    }

    public ColumnData(String columnName,String columnType,int columnSize,int colScale){
        this.columnName = columnName;
        this.columnType = columnType;
        this.columnSize = columnSize;
        this.colScale = colScale;
    }


    public int getColScale() {
        return colScale;
    }

    public void setColScale(int colScale) {
        this.colScale = colScale;
    }
    public String getColumnName() {
        return columnName;
    }
    public void setColumnName(String columnName) {
        this.columnName = columnName;
    }
    public String getColumnType() {
        return columnType;
    }
    public void setColumnType(String columnType) {
        this.columnType = columnType;
    }
    public int getColumnSize() {
        return columnSize;
    }
    public void setColumnSize(int columnSize) {
        this.columnSize = columnSize;
    }

    @Override
    public String toString(){
        return "列名:"+getColumnName()+",字段类型:"+getColumnType()+",长度:"+getColumnSize()+",小数位:"+getColScale();

    }
}

CopyTypeToMySQL.java

package tm.utility;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import tm.entity.ColumnData;
import tm.pretreatment.MySQLHelper;
import tm.pretreatment.ODaos;

/**
 * 修改MySQL的blob字段为longtext字段
 * 
 * @author Cetrin Wang
 *
 */
public class CopyTypeToMySQL {

    public static void main(String args[]) {
        createTableMS("BD_ANALY_PA_INS_TOTAL");
    }

    /**
     * 将oracle的表复制到MySQL中
     */
    public static void createTableMS(String tableName) {

        Connection conn = MySQLHelper.getConntion();
        PreparedStatement ps = null;

        //查询全表列信息
        List<ColumnData> list = getColList(tableName);
        StringBuffer sb = new StringBuffer();

        sb.append("CREATE TABLE ").append(tableName.toUpperCase()).append("(");

        for (int i = 0; i < list.size(); i++) {
            ColumnData col = list.get(i);
            String type = getMySQLType(col);
            if("ID".equalsIgnoreCase(col.getColumnName())){
                sb.append(col.getColumnName()+" ").append(type+" not null PRIMARY KEY AUTO_INCREMENT,");
            }else{
                sb.append(col.getColumnName()+" ").append(type+",");
            }

        }
        sb = sb.deleteCharAt(sb.length() - 1);
        sb.append(") engine=innodb default charset=utf8 auto_increment=1 ");

        System.out.println(sb.toString());

        try {
            ps = conn.prepareStatement(sb.toString());
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            MySQLHelper.closeConnection(ps, null);
        }

    }

    /**
     * 把Oracle的Type转成Oracle的Type
     */
    private static String getMySQLType(ColumnData c){
        if(c == null){
            return null;
        }
        String type = c.getColumnType();
        if("BLOB".equalsIgnoreCase(type)){
            return "longtext";
        }else if("DATE".equalsIgnoreCase(type)){
            return "datetime";
        }else if("VARCHAR2".equalsIgnoreCase(type)){
            if(c.getColumnSize()>=2000){
                return "text";
            }else{
                return "varchar("+c.getColumnSize()+")";
            }
        }else if("NUMBER".equalsIgnoreCase(type)){
            if(c.getColScale() > 0 ){
                int n = c.getColumnSize() - c.getColScale();//整数位
                int s = c.getColScale();                    //小数位
                return "decimal("+n+","+s+")";
            }else{
                return "int";
            }
        }

        return c.getColumnType();
    }


    /**
     * 获取oracle列信息
     * 
     * @param tableName
     * @return
     */
    public static List<ColumnData> getColList(String tableName) {
        Connection conn = ODaos.getOracleConnection();
        PreparedStatement ps = null;
        ResultSet res = null;
        ArrayList<ColumnData> list = new ArrayList<ColumnData>();

        String sql = "SELECT * FROM " + tableName.toUpperCase()
                + " WHERE ROWNUM = 1";

        try {
            ps = conn.prepareStatement(sql);
            res = ps.executeQuery();
            ResultSetMetaData metaData = res.getMetaData();

            for (int i = 1, size = metaData.getColumnCount(); i <= size; i++) {
                ColumnData cd = new ColumnData(metaData.getColumnName(i),
                        metaData.getColumnTypeName(i),
                        metaData.getColumnDisplaySize(i),metaData.getScale(i));
                list.add(cd);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (res != null) {
                    res.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }

    /**
     * 获取oracle指定列信息
     * 
     * @param tableName
     * @return
     */
    public static List<ColumnData> getColList(String tableName,String colName) {
        Connection conn = ODaos.getOracleConnection();
        PreparedStatement ps = null;
        ResultSet res = null;
        ArrayList<ColumnData> list = new ArrayList<ColumnData>();

        String sql = "SELECT "+colName+" FROM " + tableName.toUpperCase()
                + " WHERE ROWNUM = 1";

        try {
            ps = conn.prepareStatement(sql);
            res = ps.executeQuery();
            ResultSetMetaData metaData = res.getMetaData();

            for (int i = 1, size = metaData.getColumnCount(); i <= size; i++) {
                ColumnData cd = new ColumnData(metaData.getColumnName(i),
                        metaData.getColumnTypeName(i),
                        metaData.getColumnDisplaySize(i),
                        metaData.getScale(i));
                list.add(cd);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (res != null) {
                    res.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }

}

CopyDataToMySQL.java

package tm.utility;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import tm.entity.ColumnData;
import tm.pretreatment.MySQLHelper;
import tm.pretreatment.ODaos;

/**
 * 将Oracle数据迁移到MySQL
 * 
 * @author Cetrin Wang
 *
 */
public class CopyDataToMySQL {

    public static void insertDataToMysql(String tableName) {
        List<ColumnData> list = CopyTypeToMySQL.getColList(tableName,"id,content");
        Connection conn = null;
        PreparedStatement pstm = null;

        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ").append(tableName.toUpperCase()).append("(");
        for (int i = 0; i < list.size(); i++) {
            ColumnData c = list.get(i);
            sb.append(c.getColumnName() + ",");
        }
        sb = sb.deleteCharAt(sb.length() - 1);
        sb.append(") values ( ").append(ODaos.copyString("?", list.size()))
                .append(")");

        //获取所有oracle列数据
        //ResultSet rs = getOracleData(tableName);
        //获取指定列数据
        ResultSet rs = getOracleData(tableName,"id,content");
        try {
            conn = MySQLHelper.getConntion();
            conn.setAutoCommit(false);
            pstm = conn.prepareStatement(sb.toString());
            int count = 0;
            while (rs.next()) {
                count ++;
                for (int i = 1; i <= list.size(); i++) {
                    ColumnData c = list.get(i-1);
                    String type = c.getColumnType();
                    String name = c.getColumnName();
                    if("NUMBER".equalsIgnoreCase(type)){
                        if(c.getColScale()>0){
                            pstm.setDouble(i, rs.getDouble(name));
                        }else{
                            pstm.setInt(i,rs.getInt(name));
                        }
                    }else if("DATE".equalsIgnoreCase(type)){
                        pstm.setDate(i, rs.getDate(name));
                    }else if("BLOB".equalsIgnoreCase(type)){
                        pstm.setString(i, ODaos.formatContent3(rs.getBlob(name)));
                    }else{
                        pstm.setString(i, rs.getString(name));
                    }
                }

                pstm.addBatch();
                if(count %  1000 == 0 || count ==150038){
                    pstm.executeBatch();
                    conn.commit();
                    System.out.println("第"+count+"条数据,1000条数据已更新!");    
                    pstm.clearBatch();
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            MySQLHelper.closeConnection(pstm, null);
        }
    }

    /**
     * 获取oracle表中的数据
     * 
     * @return 数据集
     */
    private static ResultSet getOracleData(String tableName) {

        String selectSql = "select * from " + tableName +" where content_clean is not null and rownum <= 100 ";

        // oracle 连接
        Connection oConn = ODaos.getOracleConnection();
        PreparedStatement pstm = null;

        try {
            pstm = oConn.prepareStatement(selectSql);
            return pstm.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 获取oracle表中的数据
     * 
     * @return 数据集
     */
    private static ResultSet getOracleData(String tableName,String colName) {

        String selectSql = "select "+colName+" from " + tableName;

        // oracle 连接
        Connection oConn = ODaos.getOracleConnection();
        PreparedStatement pstm = null;

        try {
            pstm = oConn.prepareStatement(selectSql);
            return pstm.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void main(String[] args) {
//      CopyTypeToMySQL.createTableMS("DICT_SPEWD");
        insertDataToMysql("BD_ANALY_PA_INS_TOTAL");
    }
}