查Oracle表转MySQL建表语句

application.yml

spring:
  application:
    name: ora
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@192.168.0.0:30/db
    username: username
    password: password
    hikari:
      maximum-pool-size: 30
      minimum-idle: 1
      connection-timeout: 20000
      data-source-properties: {useUnicode: true,characterEncoding: utf8}
    continue-on-error: true
server:
  port: 8080
  tomcat:
    uri-encoding: utf-8
  servlet:
    session:
      tracking-modes: cookie

templates/oracle2mysql.ftl

create table ${tableName}
(
<#list columns as column>
    ${column.name} ${column.type}<#if column.length??>(${column.length})</#if> ${column.nullable!''}
    comment '${column.comments!''}',
</#list>
<#if pkVal??>primary key(${pkVal})</#if>
) comment '${tableComment!''}';

Oracle2Mysql.java

import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;

import javax.annotation.PostConstruct;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

@SpringBootApplication
public class Oracle2Mysql {
    private final Logger logger = LoggerFactory.getLogger(getClass());

    public static void main(String[] args) {
        SpringApplication.run(Oracle2Mysql.class, args);
    }

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @PostConstruct
    public void contextLoads() {
        String owner = "";
        String tableName = "";
        tableName = tableName.toUpperCase();

        String tableCommentSql = "select * from all_tab_comments where owner=? and table_name=?";
        Map<String, Object> tableMap = jdbcTemplate.queryForMap(tableCommentSql, owner, tableName);
        String tableComment = null == tableMap.get("TABLE_COMMENT") ? null : String.valueOf(tableMap.get("TABLE_COMMENT")).toLowerCase();

        String keySql = "select distinct col.column_name from user_cons_columns col inner join user_constraints con on con.table_name = col.table_name and con.constraint_name = col.constraint_name " +
                " where con.owner = ? and col.table_name = ?";
        List<Map<String, Object>> keys = jdbcTemplate.queryForList(keySql, owner, tableName);
        int keysSize = keys.size();
        String pkVal = "";
        for (int i = 0; i < keysSize; i++) {
            Map<String, Object> key = keys.get(i);
            Object col = key.get("COLUMN_NAME");
            if (!StringUtils.isEmpty(col)) {
                pkVal += col.toString().toLowerCase();
            }
            if (i < keysSize - 1) {
                pkVal += ",";
            }
        }

        String columnsSql = "select com.comments, col.* from all_tab_columns col inner join user_col_comments com on com.table_name = col.table_name and com.column_name = col.column_name\n" +
                "where col.owner=? and col.table_name= ?";
        List<Map<String, Object>> columns = jdbcTemplate.queryForList(columnsSql, owner, tableName);
        for (Map<String, Object> column : columns) {
            String columnName = String.valueOf(column.get("COLUMN_NAME"));
            column.put("name", columnName.toLowerCase());

            String nulllabel = String.valueOf(column.get("NULLABLE"));
            column.put("nullable", "N".equals(nulllabel) ? "not null" : "");

            column.put("comments", column.get("COMMENTS"));

            column.put("length", null == column.get("DATA_LENGTH") ? null : column.get("DATA_LENGTH").toString());
            String dataType = String.valueOf(column.get("DATA_TYPE"));
            if (dataType.equals("VARCHAR") || dataType.equals("VARCHAR2")) {
                column.put("type", "varchar");
            } else if (dataType.equals("DATETIME") || dataType.equals("DATE")) {
                column.put("type", "date");
                column.put("length", null);
            } else if (dataType.equals("TIME")) {
                column.put("type", "time");
                column.put("length", null);
            } else if (dataType.equals("NUMBER")) {
                column.put("type", "integer");
                column.put("length", null);
            } else if (dataType.equals("CLOB")) {
                column.put("type", "text");
            } else {
                throw new RuntimeException("未识别的类型:" + dataType);
            }
        }

        String ftl = this.getFileContent("templates/oracle2mysql.ftl");
        FreeMarkerHelper helper = new FreeMarkerHelper();
        helper.setVariable("tableName", tableName.toLowerCase());
        helper.setVariable("tableComment", tableComment);
        helper.setVariable("columns", columns);
        helper.setVariable("pkVal", pkVal);
        String expression = helper.parseExpression(ftl);
        System.out.println();
        System.out.println(expression);
        throw new RuntimeException("终止程序执行");
    }

    public String getFileContent(String fileName) {
        ClassPathResource templateResource = new ClassPathResource(fileName);
        InputStream inputStream = null;

        String context;
        try {
            inputStream = templateResource.getInputStream();
            context = IOUtils.toString(inputStream, "UTF-8");
        } catch (IOException e) {
            throw new RuntimeException("IO异常", e);
        } finally {
            this.close(inputStream);
        }
        return context;
    }

    public void close(AutoCloseable s) {
        if (s != null) {
            try {
                s.close();
                s = null;
            } catch (Exception var2) {
                logger.error("关闭失败", var2);
            }
        }

    }
}

FreeMarkerHelper.java

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

public class FreeMarkerHelper {
    protected Map<String, Object> messageMap = new HashMap();

    public FreeMarkerHelper() {
    }

    public String parseExpression(String config) {
        StringReader stringReader = null;
        StringWriter writer = null;

        String var5;
        try {
            stringReader = new StringReader(config);
            writer = new StringWriter();
            Template template = new Template((String)null, stringReader, (Configuration)null);
            template.process(this.messageMap, writer);
            var5 = writer.toString();
        } catch (IOException var10) {
            throw new RuntimeException("FreeMarker输入输出异常:" + var10.getMessage(), var10);
        } catch (TemplateException var11) {
            throw new RuntimeException("FreeMarker模板异常:" + var11.getMessage(), var11);
        } finally {
            if (writer != null) {
                try {
                    writer.close();
                    writer = null;
                } catch (Exception e) {
                    logger.error("关闭失败", e);
                }
            }   
            if (stringReader!= null) {
                try {
                    stringReader.close();
                    stringReader= null;
                } catch (Exception e) {
                    logger.error("关闭失败", e);
                }
            }  
        }

        return var5;
    }

    public void setVariable(String key, Object value) {
        this.messageMap.put(key, value);
    }

    public Object parseExpressionExtend(String config) {
        if (config != null && config.startsWith("Timestamp(")) {
            try {
                String[] timestampValues = config.split(",");
                String timestamp = timestampValues[0].replace("Timestamp(", "");
                String dateFormat = timestampValues[1].replace(")", "");
                SimpleDateFormat format = new SimpleDateFormat(dateFormat);
                return new Timestamp(format.parse(timestamp).getTime());
            } catch (Exception var6) {
                throw new RuntimeException("日期转换失败，请使用Timestamp(日期时间值,日期时间格式)标识", var6);
            }
        } else {
            return config;
        }
    }

    public void setMap(Map map) {
        this.messageMap.putAll(map);
    }
}