根据mysql表生成pgsql建表语句

目标

写个小工具，传入jdbcurl mysql 信息，读取mysql表名，

生成的表和字段要添加"

id是固定写法

每个字段要么NOT NULL，要么NULL

create_time是固定写法，

要生成注释

示例sql

CREATE TABLE "extra_backlog" (
  "id" bigint GENERATED BY DEFAULT AS IDENTITY,
  "content" varchar(200)  NOT NULL,
  "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "create_user_id" bigint NOT NULL,
  "update_user_id" bigint NOT NULL,
  CONSTRAINT "extra_backlog_pkey" PRIMARY KEY ("id")
);
COMMENT ON TABLE "extra_backlog" IS '开发记录';
COMMENT ON COLUMN "extra_backlog"."id" IS 'id';
COMMENT ON COLUMN "extra_backlog"."content" IS '内容';
COMMENT ON COLUMN "extra_backlog"."create_time" IS '创建时间';
COMMENT ON COLUMN "extra_backlog"."update_time" IS '修改日期';
COMMENT ON COLUMN "extra_backlog"."create_user_id" IS '创建者id';
COMMENT ON COLUMN "extra_backlog"."update_user_id" IS '更新者id';

实现

使用mybatis-generator。

            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <configuration>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                    <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>me.nobody</groupId>
                        <artifactId>common</artifactId>
                        <version>${project.version}</version>
                    </dependency>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.21</version>
                    </dependency>
                    <dependency>
                        <groupId>com.itfsw</groupId>
                        <artifactId>mybatis-generator-plugin</artifactId>
                        <version>1.3.5</version>
                    </dependency>
                </dependencies>
            </plugin>

generatorConfig.xml

        <!-- 自定义插件 -->
        <plugin type="me.nobody.mbg.common.extend.PgsqlCreateTablePlugin">
            <property name="outputDir" value="src/main/resources/pgsql" />
            <property name="fileName" value="all_tables.sql" />
        </plugin>

package me.nobody.mbg.common.extend;

import org.apache.commons.lang3.StringUtils;
import org.mybatis.generator.api.GeneratedJavaFile;
import org.mybatis.generator.api.IntrospectedColumn;
import org.mybatis.generator.api.IntrospectedTable;
import org.mybatis.generator.api.PluginAdapter;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * MyBatis Generator 插件
 * 一次性生成所有表的 PostgreSQL 建表语句到一个文件
 */
public class PgsqlCreateTablePlugin extends PluginAdapter {

    private String outputDir;
    private String fileName;
    private static final StringBuilder allTablesSql = new StringBuilder();

    @Override
    public boolean validate(List<String> warnings) {
        outputDir = properties.getProperty("outputDir", "src/main/resources/sql");
        fileName = properties.getProperty("fileName", "all_tables.sql");
        return true;
    }

    @Override
    public List<GeneratedJavaFile> contextGenerateAdditionalJavaFiles(IntrospectedTable introspectedTable) {
        List<GeneratedJavaFile> files = new ArrayList<>();

        String tableName = introspectedTable.getFullyQualifiedTableNameAtRuntime();
        List<IntrospectedColumn> columns = introspectedTable.getAllColumns();

        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE \"").append(tableName).append("\" (\n");

        for (int i = 0; i < columns.size(); i++) {
            IntrospectedColumn col = columns.get(i);
            final String actualColumnName = col.getActualColumnName();
            String pgsqlType = convertToPgsqlType(col);
            sb.append("  ")
              .append(actualColumnName)
              .append(" ")
              .append(pgsqlType);

            if (col.isIdentity() || actualColumnName.equals("id")) {
                if (StringUtils.equalsAnyIgnoreCase("BIGINT", "INTEGER", "INT8")) {
                    sb.append(" GENERATED BY DEFAULT AS IDENTITY");
                }
            }

            if (!col.isNullable()) {
                sb.append(" NOT NULL");
            } else {
                sb.append(" NULL");
            }
            if (StringUtils.equalsAny(actualColumnName, "create_time", "update_time")) {
                sb.append(" DEFAULT CURRENT_TIMESTAMP");
            //} else if (StringUtils.equalsAny(actualColumnName, "create_user_id", "update_user_id")) {
            }

            if (i < columns.size() - 1) {
                sb.append(",");
            }
            sb.append("\n");
        }

        // 主键
        if (!introspectedTable.getPrimaryKeyColumns().isEmpty()) {
            sb.append(",  CONSTRAINT \""+tableName+"_pkey\" PRIMARY KEY(");
            for (int i = 0; i < introspectedTable.getPrimaryKeyColumns().size(); i++) {
                sb.append(introspectedTable.getPrimaryKeyColumns().get(i).getActualColumnName());
                if (i < introspectedTable.getPrimaryKeyColumns().size() - 1) {
                    sb.append(", ");
                }
            }
            sb.append(")\n");
        }

        sb.append(");\n\n");

        // 表注释
        if (introspectedTable.getRemarks() != null && !introspectedTable.getRemarks().isEmpty()) {
            sb.append("COMMENT ON TABLE ").append(tableName)
                .append(" IS '").append(introspectedTable.getRemarks().replace("'", "")).append("';\n");
        }

        // 字段注释
        for (IntrospectedColumn col : columns) {
            if (col.getRemarks() != null && !col.getRemarks().isEmpty()) {
                sb.append("COMMENT ON COLUMN ").append(tableName).append(".")
                    .append(col.getActualColumnName())
                    .append(" IS '").append(col.getRemarks().replace("'", "")).append("';\n");
            }
        }

        sb.append("\n");

        // 累加 SQL
        allTablesSql.append(sb);

        return files;
    }

    @Override
    public List<GeneratedJavaFile>  contextGenerateAdditionalJavaFiles() {
        // 所有表处理完之后，写入到一个文件
        writeSqlToFile(allTablesSql.toString());
        return new ArrayList<>();
    }

    private String convertToPgsqlType(IntrospectedColumn column) {
        int jdbcType = column.getJdbcType();
        int length = column.getLength();

        switch (jdbcType) {
            case java.sql.Types.BIGINT:
                return "BIGINT";
            case java.sql.Types.INTEGER:
            case java.sql.Types.TINYINT:
            case java.sql.Types.SMALLINT:
                return "INTEGER";
            case java.sql.Types.VARCHAR:
                return "VARCHAR(" + length + ")";
            case java.sql.Types.CHAR:
                return "CHAR(" + length + ")";
            case java.sql.Types.TIMESTAMP:
                return "TIMESTAMP";
            case java.sql.Types.DATE:
                return "DATE";
            case java.sql.Types.DECIMAL:
            case java.sql.Types.NUMERIC:
                return "NUMERIC(" + column.getLength() + "," + column.getScale() + ")";
            case java.sql.Types.DOUBLE:
                return "DOUBLE PRECISION";
            case java.sql.Types.FLOAT:
                return "REAL";
            case java.sql.Types.BOOLEAN:
            case java.sql.Types.BIT:
                return "BOOLEAN";
            default:
                return "TEXT";
        }
    }

    private void writeSqlToFile(String sql) {
        try {
            File dir = new File(outputDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            File file = new File(dir, fileName);
            try (FileWriter writer = new FileWriter(file)) {
                writer.write(sql);
            }
        } catch (IOException e) {
            throw new RuntimeException("写入SQL文件失败: " + e.getMessage(), e);
        }
    }
}