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);
}
}