mysql同步表到oracle

mysql同步表到oracle

背景：使用sqldeveloper工具同步失败

实现：通过mysql元数据生成oracle建表语句

package com.concom.yunzq.impl;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SmallCatTest3 {

    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://ip:3306/dbName?useUnicode=true&characterEncoding=utf8"; 
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    
    public static void main(String[] args) throws Exception {
    	String DateNow = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")); // 当前日期和时间 
		System.out.println("-- 开始时间：" + DateNow);
    	
    	// 加载驱动到JVM
    	Class.forName(DRIVER);
    	// 获取连接
    	Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    	// 数据库的所有数据
    	DatabaseMetaData metaData = connection.getMetaData();
    	
    	Map<String, List<TableInfo>> tableMap = getTableMap(metaData);
    	
    	getOpDbSql(tableMap);
    	//创建后的文件在  E:\\oracle\\table\\  下
   
    	String DateEnd = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")); // 当前日期和时间 
		System.out.println("-- 结束时间：" + DateEnd);
    	System.err.println("--------执行结束--------");
	}

    /**
     * .根据元数据生成对应的oracle
     * 1.创建表语句
     * 2.创建主键
     * 3.创建表字段注释
     * 4.创建序列和触发器
     * @param tableMap
     * @throws IOException
     */
	private static void getOpDbSql(Map<String, List<TableInfo>> tableMap) throws IOException {
		String newLine = "\r\n";//换行符
    	StringBuffer createTableText = new StringBuffer();//生成oracle建表脚本
    	StringBuffer createPrimaryText = new StringBuffer();//生成表主键脚本
    	StringBuffer createColumnText = new StringBuffer();//生成表注释脚本
    	StringBuffer createTriggerText = new StringBuffer();//生成序列，触发器脚本
    	
    	for (Map.Entry<String, List<TableInfo>> item : tableMap.entrySet()) {
    		//清空数据 
			
			  createTableText.setLength(0); 
			  createPrimaryText.setLength(0);
			  createColumnText.setLength(0); 
			  createTriggerText.setLength(0);
			 
    		//删除表 -- begin
    		createTableText.append("CALL PROC_DROPIFEXISTS('" + item.getKey() + "');"+newLine);
    		//删除表 --end
    		
    		createTableText.append("create table " + item.getKey() + "(" + newLine);
    		int index = 0;
    		List<String> primaryList = new ArrayList<String>();
    		for (TableInfo table : item.getValue()) {
    			//拼接列名脚本
    			createTableText.append("   \"" + table.getFiled().toUpperCase() + "\" " + formateFiledType(table) + " " +formateDefaultValue(table.getDefaultValue()) + " " + formateIsNull(table.isNull()));
    			createTableText.append( (index + 1 != item.getValue().size()) ?  ", " + newLine : newLine);
    			index ++;
    			
    			
    			if (table.isPrimaryKey()) {
    				primaryList.add(table.getFiled());
    			}
    			
    			//拼接注释脚本
    			if (table.getRemark() != null && table.getRemark().length() > 0) {
    				createColumnText.append("comment on column " +item.getKey()+  "."  + formateFiledKey(table.getFiled()) + " is '" + table.getRemark() + "';" + newLine);
				}
			}
    		createTableText.append(");" + newLine);
    		
    		//拼接主键脚本
    		if (primaryList != null && primaryList.size() > 0) {
    			createPrimaryText.append("ALTER TABLE " + item.getKey() + " ADD PRIMARY KEY ("+String.join(",", primaryList)+");" + newLine);
			}
    		
    		//拼接序列脚本
    		createTriggerText.append(
    			"CALL PROC_DROPSEQUENCES_IFEXISTS('yunzq_metro_id_" + item.getKey() + "');"+ newLine +
				"CREATE SEQUENCE yunzq_metro_id_" + item.getKey() + newLine +
				"  INCREMENT BY 1"+ newLine + 
				"  START WITH 1"+ newLine + 
				"  MAXVALUE 1.0E20"+ newLine + 
				"  MINVALUE 1"+ newLine + 
				"  NOCYCLE"+ newLine + 
				"  CACHE 20"+ newLine + 
				"  NOORDER ;"+ newLine + 
				"  -- 触发器：  " + newLine + 
				
				"CALL PROC_DROPTRIGGER_IFEXISTS('trigger_id_add_" + item.getKey() + "');"+ newLine + 
				"create or replace trigger trigger_id_add_" + item.getKey() + newLine + 
				"before insert on " + item.getKey() + newLine + 
				"for each row"+ newLine + 
				"begin"+ newLine + 
				"\tselect yunzq_metro_id_" + item.getKey() + ".nextval into :new.id from dual;"+ newLine + 
				"end;" + newLine
			);

    		
			/*
			 * System.out.println(createTableText.toString());
			 * System.out.println(createPrimaryText.toString());
			 * System.out.println(createColumnText.toString());
			 * System.out.println(createTriggerText.toString());
			 */
    		
    		//根据表名生成文件
    		String path = "E:\\oracle\\table\\"+item.getKey()+".txt";
    	    File file = new File(path);
    	    if(!file.exists()){
    	        file.getParentFile().mkdirs();          
	        }else {
				file.delete();
	        }
    	    file.createNewFile();
    	 
    	    // write
    	    FileWriter fw = new FileWriter(file, true);
    	    fw.write("-- 创建表：" + item.getKey() + newLine + newLine); 
    	    fw.write(createTableText.toString());
    	    fw.write(newLine + "-- 创建主键："  + newLine);
    	    fw.write(createPrimaryText.toString());
    	    fw.write(newLine + "-- 创建注释："  + newLine);
    	    fw.write(createColumnText.toString());
    	    fw.write(newLine + "-- 创建序列和触发器："  + newLine);
    	    fw.write(createTriggerText.toString());
    	    fw.flush();
    	    fw.close();
    		 

		}
	}
    
    /**
     * 1.格式化关键字
     * @return
     */
    private static String formateFiledKey(String filed) {
    	String returnString = "";
    	switch (filed.toUpperCase()) {
		case "LEVER":
			returnString = "\"LEVEL\"";
			break;
		case "NUMBER":
			returnString = "\"NUMBER\"";
			break;
		case "DESC":
			returnString = "\"DESC\"";
			break;
		case "RESOURCE":
			returnString = "\"RESOURCE\"";
			break;

		default:
			returnString = filed;
		}
    	return returnString;
    }
    
    /**
     * .根据mysql的表字段类型  映射 oracle的字段类型
     * @param table
     * @return
     */
    private static String formateFiledType(TableInfo table) {
    	String returnType = "";
    	switch (table.getFiledType()) {
		case "INT":
			returnType = "NUMBER(" +  table.getFiledLenth() + ")";
			break;
		case "DATETIME":
			returnType = "DATE";
			break;
		case "DATE":
			returnType = "DATE";
			break;
		case "TIMESTAMP":
			returnType = "DATE";
			break;
		case "TEXT":
			returnType = "CLOB";
			break;
		case "LONGTEXT":
			returnType = "CLOB";
			break;
		case "TINYINT":
			returnType = "NUMBER(3)";
			break;
		case "BIGINT":
			returnType = "NUMBER(20)";
			break;
		case "SMALLINT":
			returnType = "NUMBER(5)";
			break;
		case "INT UNSIGNED":
			returnType = "NUMBER(" +  (table.getFiledLenth()>20 ? 20 : table.getFiledLenth()) + ")";
			break;
		case "DOUBLE":
			returnType = "DECIMAL(" +  (table.getFiledLenth()>20 ? 20 : table.getFiledLenth()) + "," + (table.getDecimalDigits() != null && table.getDecimalDigits().length() > 0  ? table.getDecimalDigits():"4") + ")";
			break;
		case "VARCHAR":
			if (table.getFiledLenth() > 4000) {
				returnType = "CLOB";
			}else {
				returnType = "VARCHAR2(" +  table.getFiledLenth() + ")";
			}
			break;
		case "FLOAT":
			returnType = "DECIMAL(" +  table.getFiledLenth() + ",10)";
			System.err.println("---- 表名：" + table.getTableName() + "  列名：" + table.getFiled() +"  类型：" + table.getFiledType() + "  小数："+table.getDecimalDigits() + "     出现异常！");
			break;
		case "CHAR":
			returnType = "VARCHAR2(" +  table.getFiledLenth() + ")";
			break;
		case "BIT":
			returnType = "NUMBER(1)";
			break;
		case "DECIMAL":
			returnType = "DECIMAL(" + (table.getFiledLenth()>20 ? 20 : table.getFiledLenth()) + "," + (table.getDecimalDigits() != null && table.getDecimalDigits().length() > 0  ? table.getDecimalDigits():"4") + ")";
			break;
		default:
			System.err.println("---- 表名：" + table.getTableName() + "  列名：" + table.getFiled() +"  类型：" + table.getFiledType() + "     出现异常！");
			returnType = "VARCHAR2(" +  table.getFiledLenth() + ")";
			break;
		}
    	return returnType;
    }
    
    /**
     * .格式化一些默认值
     * @param defaultValue
     * @return
     */
    private static String formateDefaultValue(String defaultValue) {
    	String returnValue = "";
    	if (defaultValue != null && defaultValue.length() > 0) {
			if (defaultValue.equals("0000-00-00 00:00:00") || defaultValue.equals("CURRENT_TIMESTAMP")) {
				returnValue = "DEFAULT sysdate" ;
			}else {
				returnValue = "DEFAULT '" + defaultValue  + "'";
			}
		}
    	return  returnValue;
    }
    
    /**
     * .格式化空
     * @param isNull
     * @return
     */
    private static String formateIsNull(boolean isNull) {
    	return isNull ? "NULL" : "NOT NULL";
    }

    /**
     * .获取表信息
     * @param metaData
     * @return
     * @throws SQLException
     */
	private static Map<String, List<TableInfo>> getTableMap(DatabaseMetaData metaData) throws SQLException {
		/**
    	 * 1.获取表名
    	 */
    	List<String> tableNames = new ArrayList<>();
        ResultSet rs = null;
        //获取数据库的元数据
        //从元数据中获取到所有的表名
        rs = metaData.getTables(null, null, null, new String[] { "TABLE" });
        while(rs.next()) {
            tableNames.add(rs.getString(3));
        }
        /**
         * 2.遍历表名
         * 	①.遍历表字段信息
         * 	②.遍历主键
         */
        Map<String, List<TableInfo>> tableMap = new HashMap<String, List<TableInfo>>(); 
        ArrayList<TableInfo> tableList = null;
    	for (String thisTableName : tableNames) {
    		tableList = new ArrayList<TableInfo>();
    		
    		// 获取表的相对应的列的名字
    		ResultSet tableInfo = metaData.getColumns(null,"%", thisTableName, "%");
    		while (tableInfo.next()){
    			tableList.add(
    					new TableInfo(
    	    					tableInfo.getString("TABLE_NAME"),
    	    					tableInfo.getString("COLUMN_NAME"),
    	    					tableInfo.getString("TYPE_NAME"), 
    	    					tableInfo.getInt("COLUMN_SIZE"),
    	    					tableInfo.getString("DECIMAL_DIGITS"),
    	    					tableInfo.getString("COLUMN_DEF"), 
    	    					tableInfo.getBoolean("IS_NULLABLE"), 
    	    					tableInfo.getBoolean("IS_AUTOINCREMENT"),
    	    					false, 
    	    					tableInfo.getString("REMARKS")
    	    			)
    			);
    		}
    		
    		// 获取表的主键名字
    		ResultSet pkInfo = metaData.getPrimaryKeys(null, "%", thisTableName);
    		while (pkInfo.next()){
    			for (TableInfo table : tableList) {
					if (table.getFiled().equals(pkInfo.getString("COLUMN_NAME"))) {
						table.setisPrimaryKey(true);
					}
				}
    		}
    		tableMap.put(thisTableName, tableList);
		}
		return tableMap;
	}
	
}



class TableInfo{
	private String tableName;//表名
	private String filed;//列名
	private String filedType;//字段类型
	private Integer filedLenth;//字段长度 
	private String decimalDigits;//小数长度 
	private String defaultValue;//默认值
	private boolean isNull;//是否为空
	private boolean isAutoAdd;//是否自增
	private boolean isPrimaryKey;//主键
	private String remark;//字段说明
	
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public String getFiled() {
		return filed;
	}
	public void setFiled(String filed) {
		this.filed = filed;
	}
	public String getFiledType() {
		return filedType;
	}
	public void setFiledType(String filedType) {
		this.filedType = filedType;
	}
	public Integer getFiledLenth() {
		return filedLenth;
	}
	public void setFiledLenth(Integer filedLenth) {
		this.filedLenth = filedLenth;
	}
	public String getDefaultValue() {
		return defaultValue;
	}
	public void setDefaultValue(String defaultValue) {
		this.defaultValue = defaultValue;
	}
	public boolean isNull() {
		return isNull;
	}
	public void setIsNull(boolean isNull) {
		this.isNull = isNull;
	}
	public boolean isAutoAdd() {
		return isAutoAdd;
	}
	public void setIsAutoAdd(boolean isAutoAdd) {
		this.isAutoAdd = isAutoAdd;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	public boolean isPrimaryKey() {
		return isPrimaryKey;
	}
	public void setisPrimaryKey(boolean isPrimaryKey) {
		this.isPrimaryKey = isPrimaryKey;
	}
	
	public String getDecimalDigits() {
		return decimalDigits;
	}
	public void setDecimalDigits(String decimalDigits) {
		this.decimalDigits = decimalDigits;
	}
	/**
	 * 
	 * @param tableName 表名
	 * @param filed 字段名
	 * @param filedType 字段类型
	 * @param filedLenth 字段长度
	 * @param decimalDigits 小数长度
	 * @param defaultValue 默认值
	 * @param isNull 是否允许为null
	 * @param isAutoAdd 是否自增
	 * @param isPrimaryKey 是否为主键
	 * @param remark 字段注释
	 */
	public TableInfo(String tableName, String filed, String filedType, Integer filedLenth,String decimalDigits,
			String defaultValue, boolean isNull,
			boolean isAutoAdd, boolean isPrimaryKey,String remark) {
		super();
		this.tableName = tableName;
		this.filed = filed;
		this.filedType = filedType;
		this.filedLenth = filedLenth;
		this.decimalDigits = decimalDigits;
		this.defaultValue = defaultValue;
		this.isNull = isNull;
		this.isAutoAdd = isAutoAdd;
		this.isPrimaryKey = isPrimaryKey;
		this.remark = remark;
	}
	
}