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("gzh_survey", "gzh_survey", 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);

            //获取表主键
            ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, mysqlTable);
            String keyName = null;
            if (primaryKeys.next()) {
                keyName = primaryKeys.getString("column_name");
            }

            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);//字段注释
                int nullable = mysqlMeda.isNullable(i);//字段是否必填（0是，1否）

                createSql.append(columnName).append(" ");
                if (columnType.contains("char")){
                    createSql.append("varchar2("+columnDisplaySize+")");
                }else if ("bigint,int,tinyint,bit".contains(columnType)){
                    createSql.append("number("+columnDisplaySize+")");
                }else if (columnType.equals("datetime")){
                    createSql.append("date");
                }else if (columnType.contains("text")){
                    createSql.append("NCLOB");
                }else {
                    createSql.append(columnType);
                }

                if (columnName.equals("del_flag")){//默认值
                    createSql.append(" default 0 ");
                }

                if (StringUtils.isNotBlank(keyName) && keyName.equals(columnName)){
                    createSql.append(" primary key ");
                }else if (nullable==0){
                    createSql.append(" not null ");
                }
                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());
            }



            //获取表注释
            ResultSet resultSet = metaData.getTables(null, null, mysqlTable, null);
            while (resultSet.next()){
                String remarks = resultSet.getString("remarks");
                String rs = "comment on table " + oracleTable + " is '" + remarks + "';";
                if (!str.contains(rs)) {
                    str+= rs;
                }
            }

            //插入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;
    }