java实现 mysql表转化为Oracle表

1.MysqlToOracle.java

package com.jiayou.wechatofficialhis.biz.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

@Slf4j
public class MysqlToOracle {
    public static void main(String[] args) {
        String str = mysqlToOracle("sys_user_detail", "sys_user_detail", 1);
        System.out.println(str);
    }

    /**
     *
     * @param mysqlTable
     * @param oracleTable
     * @param type (1.只获取创建Oracle表和插入的sql语句)
     */
    public static String mysqlToOracle(String mysqlTable,String oracleTable,Integer type){
        Connection mysqlConn=null;
        Connection oracleConn=null;
        Statement mysqlStmt=null;
        Statement oracleStmt=null;
        ResultSet mysqlRs=null;
        String str="";

        //mysql 数据库连接 com.mysql.cj.jdbc.Driver || com.mysql.jdbc.Driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            mysqlConn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai","root","root");
            mysqlStmt=mysqlConn.createStatement();
            mysqlRs=mysqlStmt.executeQuery("select * from "+ mysqlTable);

            //oracle 数据库连接
            if (type!=1) {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                oracleConn= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521;orcl","root","root");
                oracleStmt=oracleConn.createStatement();
            }

            ResultSetMetaData mysqlMeda=mysqlRs.getMetaData();
            int columnCount=mysqlMeda.getColumnCount();
            StringBuilder createSql= new StringBuilder("create table " + oracleTable + "(");

            //获取字段注释map
            DatabaseMetaData metaData = mysqlConn.getMetaData();
            Map<String, String> columnComments = getColumnComments(metaData, mysqlTable);

            StringBuilder cmmSql=new StringBuilder();
            for (int i = 1; i <= columnCount; i++) {
                String columnName=mysqlMeda.getColumnName(i);//字段名
                String columnType=mysqlMeda.getColumnTypeName(i).toLowerCase();//字段类型
                int columnDisplaySize = mysqlMeda.getColumnDisplaySize(i);//字段大小
                String columnComment = columnComments.get(columnName);//字段注释

                createSql.append(columnName).append(" ");
                if (columnType.equalsIgnoreCase("varchar")){
                    createSql.append("varchar2("+columnDisplaySize+")");
                }else if ("bigint,int".contains(columnType)){
                    createSql.append("number("+columnDisplaySize+")");
                }else if (columnType.equals("datetime")){
                    createSql.append("date");
                }else {
                    createSql.append(columnType);
                }
                if (i<columnCount){
                    createSql.append(",");
                }

                String comment="COMMENT ON COLUMN "+oracleTable+"."+columnName+" IS '"+columnComment+"';";
                cmmSql.append(comment);
            }
            createSql.append(");");
            str=createSql.toString()+cmmSql;

            if (type!=1) {
                oracleStmt.executeUpdate(createSql.toString());
            }

            //插入Oracle数据
            while (mysqlRs.next()){
                StringBuilder insertSql=new StringBuilder("insert into " + oracleTable + "values(");
                for (int i = 1; i <= columnCount; i++) {
                    String columnValue=mysqlRs.getString(i);
                    if (StringUtils.isBlank(columnValue)){
                        insertSql.append("null");
                    }else {
                        insertSql.append("'").append(columnValue).append("'");
                    }
                    if (i<columnCount){
                        insertSql.append(", ");
                    }
                    insertSql.append(")");

                    if (type!=1) {
                        oracleStmt.executeUpdate(insertSql.toString());
                    }
                }
                str+=insertSql.toString();
            }

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (mysqlConn!=null){
                    mysqlConn.close();
                }
                if (oracleConn!=null){
                    oracleConn.close();
                }
                if (mysqlStmt!=null){
                    mysqlStmt.close();
                }
                if (oracleStmt!=null){
                    oracleStmt.close();
                }
                if (mysqlRs!=null){
                    mysqlRs.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
        return str;
    }

    /**获取字段注解*/
    private static Map<String, String> getColumnComments(DatabaseMetaData metaData, String tableName) throws SQLException {
        Map<String, String> columnComments = new HashMap<>();
        try (ResultSet columns = metaData.getColumns(null, null, tableName, null)) {
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String columnComment = columns.getString("REMARKS");
                columnComments.put(columnName, columnComment);
            }
        }
        return columnComments;
    }
}