1 公用类
import lombok.Data;
@Data
public class DdlChangeInfo {
/**
* 表名
*/
private String tableName;
/**
* 来源数据库DDL
*/
private String sourceDdl;
/**
* 目标数据库DDL
*/
private String targetDdl;
/**
* 来源数据库类型
*/
private String sourceSqlType;
/**
* 目标数据库类型
*/
private String targetSqlType;
}
import java.util.List;
public interface IDdlChangeStartegy {
void change(List<DdlChangeInfo> ddlChangeInfoList);
}
2 转换类
2.1 MySQL->PostgreSQL
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
/**
* Mysql转Postgresql DDL策略
*/
@Slf4j
public class Mysql2PosgresqlDdlChangeStartgy implements IDdlChangeStartegy {
@Override
public void change(List<DdlChangeInfo> ddlChangeInfoList) {
for (DdlChangeInfo ddlChangeInfo : ddlChangeInfoList) {
String ddl = ddlChangeInfo.getSourceDdl().replaceAll("current_timestamp\\(\\) ON UPDATE current_timestamp\\(\\)", "current_timestamp")
.replaceAll("current_timestamp\\(\\)", "current_timestamp")
.replaceAll("current_timestamp\\(\\)", "current_timestamp")
.replaceAll("int\\([0-9]*\\)", "INT")
.replaceAll("tinyint\\([0-9]*\\)", "INT")
.replaceAll(" tinyINT ", "INT")
.replaceAll("double\\([0-9]*,[0-9]*\\)", "float8")
.replaceAll(" double ", " float8 ")
.replaceAll(" datetime ", " timestamp(6) ")
.replaceAll("DEFAULT 00000000000", "DEFAULT 0")
.replaceAll("unsigned zerofill", "")
.replaceAll("bigint\\([0-9]*\\) NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
.replaceAll("bigint\\([0-9]*\\)", "INT8");
try {
Statements statements = CCJSqlParserUtil.parseStatements(ddl);
statements.getStatements()
.stream()
.map(statement -> (CreateTable) statement).forEach(ct -> {
Table table = ct.getTable();
List<ColumnDefinition> columnDefinitions = ct.getColumnDefinitions();
List<String> comments = new ArrayList<>();
List<ColumnDefinition> collect = columnDefinitions.stream()
.peek(columnDefinition -> {
List<String> columnSpecStrings = columnDefinition.getColumnSpecStrings();
int commentIndex = getCommentIndex(columnSpecStrings);
if (commentIndex != -1) {
int commentStringIndex = commentIndex + 1;
String commentString = columnSpecStrings.get(commentStringIndex);
String commentSql = genCommentSql(table.toString(), columnDefinition.getColumnName(), commentString);
comments.add(commentSql);
columnSpecStrings.remove(commentStringIndex);
columnSpecStrings.remove(commentIndex);
}
columnDefinition.setColumnSpecStrings(columnSpecStrings);
}).collect(Collectors.toList());
ct.setColumnDefinitions(collect);
String createSQL = ct.toString()
.replaceAll("`", "\"")
.replaceAll("BIGINT UNIQUE NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
.replaceAll("BIGINT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
.replaceAll("BIGINT NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
.replaceAll("INT NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
.replaceAll("INT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
.replaceAll("IF NOT EXISTS", "")
.replaceAll("TINYINT", "SMALLINT")
.replaceAll("DATETIME", "TIMESTAMP")
.replaceAll(", PRIMARY KEY \\(\"id\"\\)", "");
// 如果存在表注释
if (createSQL.contains("COMMENT")) {
createSQL = createSQL.substring(0, createSQL.indexOf("COMMENT"));
}
StringBuilder postgresqlDdl = new StringBuilder();
postgresqlDdl.append(createSQL + ";");
comments.forEach(t -> postgresqlDdl.append(t.replaceAll("`", "\"") + ";"));
ddlChangeInfo.setTargetDdl(postgresqlDdl.toString());
});
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
}
/**
* 获得注释的下标
*
* @param columnSpecStrings columnSpecStrings
* @return 下标
*/
private static int getCommentIndex(List<String> columnSpecStrings) {
for (int i = 0; i < columnSpecStrings.size(); i++) {
if ("COMMENT".equalsIgnoreCase(columnSpecStrings.get(i))) {
return i;
}
}
return -1;
}
/**
* 生成COMMENT语句
*
* @param table 表名
* @param column 字段名
* @param commentValue 描述文字
* @return COMMENT语句
*/
private static String genCommentSql(String table, String column, String commentValue) {
return String.format("COMMENT ON COLUMN %s.%s IS %s", table, column, commentValue);
}
}待补充
3 测试类
3.1 MySQL->PostgreSQL
import net.sf.jsqlparser.JSQLParserException;
import java.util.ArrayList;
import java.util.List;
public class MysqlDdl2PgDdlTestMain {
public static void main(String[] args) throws JSQLParserException {
List<DdlChangeInfo> ddlChangeInfoList = new ArrayList<>();
DdlChangeInfo ddlChangeInfo0 = new DdlChangeInfo();
ddlChangeInfo0.setSourceDdl("CREATE TABLE `t_order` (" +
" `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'," +
" `goods_id` int(11) DEFAULT NULL COMMENT '商品id'," +
" `goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称'," +
" `dealer_id` int(11) DEFAULT NULL COMMENT '经销商id'," +
" `dealer_name` varchar(255) DEFAULT NULL COMMENT '经销商名称'," +
" `goods_num` int(11) DEFAULT NULL COMMENT '商品数量'," +
" `amount` decimal(10,2) DEFAULT NULL COMMENT '折前金额'," +
" `discount_amount` decimal(10,2) DEFAULT NULL COMMENT '折后金额'," +
" `discount` double DEFAULT NULL COMMENT '折扣'," +
" `order_time` datetime DEFAULT NULL COMMENT '订单时间'," +
" `status` int(11) DEFAULT NULL COMMENT '订单状态，0：未支付 1：已支付'," +
" `create_time` datetime DEFAULT NULL COMMENT '创建时间'," +
" `update_time` datetime DEFAULT NULL COMMENT '更新时间'," +
" PRIMARY KEY (`id`)" +
");");
ddlChangeInfo0.setTableName("t_order");
ddlChangeInfoList.add(ddlChangeInfo0);
DdlChangeInfo ddlChangeInfo1 = new DdlChangeInfo();
ddlChangeInfo1.setSourceDdl("CREATE TABLE `t_order_1` (" +
" `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'," +
" `goods_id` int(11) DEFAULT NULL COMMENT '商品id'," +
" `goods_name` varchar(255) DEFAULT NULL COMMENT '商品名称'," +
" `discount` double DEFAULT NULL COMMENT '折扣'," +
" `order_time` datetime DEFAULT NULL COMMENT '订单时间'," +
" `status` int(11) DEFAULT NULL COMMENT '订单状态，0：未支付 1：已支付'," +
" `create_time` datetime DEFAULT NULL COMMENT '创建时间'," +
" `update_time` datetime DEFAULT NULL COMMENT '更新时间'," +
" PRIMARY KEY (`id`)" +
");");
ddlChangeInfo1.setTableName("t_order_1");
ddlChangeInfoList.add(ddlChangeInfo1);
System.out.println("init:" + ddlChangeInfoList);
Mysql2PosgresqlDdlChangeStartgy mysql2PosgresqlDdlChangeStartgy = new Mysql2PosgresqlDdlChangeStartgy();
mysql2PosgresqlDdlChangeStartgy.change(ddlChangeInfoList);
System.out.println("changed:" + ddlChangeInfoList);
}
}作者：Jeebiz 创建时间：2022-10-16 01:17
更新时间：2024-07-10 22:56
更新时间：2024-07-10 22:56
