SpringBoot+Beetl實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)庫(kù)DDL的示例代碼

更新時(shí)間：2025年06月22日 08:37:51 作者：月生_

最近公司里有一個(gè)新的需求,需要導(dǎo)出數(shù)據(jù)庫(kù)元數(shù)據(jù)表中存儲(chǔ)的表的 DDL 語(yǔ)句,而在元數(shù)據(jù)表中數(shù)據(jù)源的類型龐大,少則十幾種多則達(dá)到幾十種,各種數(shù)據(jù)庫(kù)類型都有,本篇文章就從這個(gè)模版引擎來(lái)入手介紹如何實(shí)現(xiàn)功能,需要的朋友可以參考下

序言

最近公司里有一個(gè)新的需求，需要導(dǎo)出數(shù)據(jù)庫(kù)元數(shù)據(jù)表中存儲(chǔ)的表的 DDL 語(yǔ)句，而在元數(shù)據(jù)表中數(shù)據(jù)源的類型龐大，少則十幾種多則達(dá)到幾十種，各種數(shù)據(jù)庫(kù)類型都有，比如常見(jiàn)就有 MySQL PostgreSQL Oracle SQL Server等等，而不同的數(shù)據(jù)庫(kù)其數(shù)據(jù)類型和語(yǔ)法都存在一些差異，如果在代碼中去處理工作量就太大了。所以我想到使用模版引擎來(lái)處理，現(xiàn)在市面上出現(xiàn)了一個(gè)新的模版引擎 Beetl。本篇文章就從這個(gè)模版引擎來(lái)入手介紹如何實(shí)現(xiàn)功能。代碼已經(jīng)提交到了我的 GitHub 倉(cāng)庫(kù)

Beetl

Beetl 的使用方式和 Thymeleaf、Freemarker 差不多，而且其語(yǔ)法簡(jiǎn)單，性能也優(yōu)于這兩者，目前在國(guó)內(nèi)已經(jīng)有一定的用戶群體。話不多說(shuō)，具體的使用方式非常簡(jiǎn)單，大家看一下官網(wǎng)就會(huì)了，下面來(lái)說(shuō)功能怎么實(shí)現(xiàn)

代碼

使用前在SpringBoot中使用的時(shí)候需要先導(dǎo)入依賴，我測(cè)試的 SpringBoot 的版本為 3.2.1

<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetl-spring-boot-starter-classic</artifactId>
    <version>3.19.1.RELEASE</version>
</dependency>

創(chuàng)建一下庫(kù)表，因?yàn)槟０嫖募谴鎯?chǔ)在數(shù)據(jù)庫(kù)中，方便后期維護(hù)

create table ddl_template
(
    id          int auto_increment
        primary key,
    name        varchar(255) not null,
    description varchar(255) null,
    created_at  timestamp    not null,
    updated_at  timestamp    null,
    created_by  varchar(255) null,
    updated_by  varchar(255) null,
    version     float        not null,
    db_type     varchar(255) not null,
    template    text         not null
)
    comment 'ddl模板表' charset = utf8mb4;

表創(chuàng)建完成后，我事先往這個(gè)表中添加了三條數(shù)據(jù)，分別代表 MySQL、Oracle、PgSQL 的DDL 模版

MySQL:

drop table if exists `${table.tableCode}`;
create table ${table.tableCode}(
    <%
    for(field in fieldList){
    var code = field.code;
    var comment = field.comment;
    var type = field.type;
    var len = field.len;
    var scale = field.scale;
    var notNull = field.notNull;
    var autoIncrement = field.autoIncrement;
    var defaultValue = field.defaultValue;

    var last =  fieldLP.last;
    if (len != null) {
        if (len > 0) {
            var temp = "(" + len;
            if (scale != null) {
                if (scale > 0) {
                    temp = temp + "," + scale;
                }
            }
            temp = temp + ")";
            type = type + temp;
        }
    }
    %>
    `${field.code}` ${type}${autoIncrement ? " auto_increment":""}${notNull ? " not null":" null"}${defaultValue != null ? " default '" + defaultValue + "'" : "" }
     ${comment != null ? " comment '" + comment + "'":""}${!last ? ",":""}
    <%
    }
     %>
    <%
    if (primaryKey != null){
    %>
,primary key (${primaryKey})
    <%
    }
    %>
) comment = '${table.tableComment}';

Oracle:

create table "${table.tableCode}"(
    <%
    for(field in fieldList){
    var code = field.code;
    var comment = field.comment;
    var type = field.type;
    var len = field.len;
    var scale = field.scale;
    var primaryKey = field.primaryKey;
    var notNull = field.notNull;
    var defaultValue = field.defaultValue;

    var last =  fieldLP.last;
    if (len != null && type != 'date') {
        if (len > 0) {
            var temp = "(" + len;
            if (scale != null) {
                if (scale > 0) {
                    temp = temp + "," + scale;
                }
            }
            temp = temp + ")";
            type = type + temp;
        }
    }
    if (type == 'date') {
            defaultValue = null;
            notNull = false;
    }
    %>
    "${field.code}" ${type}${defaultValue != null ? " default '" + defaultValue + "'" : "" }${notNull ? " not null":" null"}${!last ? ",":""}
    <%
    }
     %>
     <%
     if (primaryKey != null){
     %>
,primary key ("${primaryKey}")
     <%
     }
     %>
);

comment on table "${table.tableCode}" is '${table.tableComment}';

<%
    for(field in fieldList){
    var code = field.code;
    var comment = field.comment;
    var last =  fieldLP.last;
    %>
comment on column "${table.tableCode}"."$[code]" is '${comment}';

    <%
    }
     %>

PgSQL:

drop table if exists "${table.tableCode}";

create table "${table.tableCode}"(
    <%
    for(field in fieldList){
    var code = field.code;
    var comment = field.comment;
    var type = field.type;
    var len = field.len;
    var scale = field.scale;
    var notNull = field.notNull;
    var autoIncrement = field.autoIncrement;
    var defaultValue = field.defaultValue;

    var last =  fieldLP.last;

    if (len != null) {
        if (len > 0) {
            var temp = "(" + len;
            if (scale != null) {
                if (scale > 0) {
                    temp = temp + "," + scale;
                }
            }
            temp = temp + ")";
            type = type + temp;
        }
    }

    %>
    "$[code]" ${type}${autoIncrement ? " generated always as identity" : ""}${notNull ? " not null" : ""}${defaultValue != null ? " default '" + defaultValue + "'" : "" }
    ${!last ? "," : ""}
    <%
    }
    %>
    <%
    if (primaryKey != null){
    %>
    ,primary key (${primaryKey})
    <%
    }
    %>
);

comment on table "${table.tableCode}" is '${table.tableComment}';

<%
for(field in fieldList){
    var code = field.code;
    var comment = field.comment;
    if (comment != null) {
%>
comment on column "${table.tableCode}"."$[code]" is '${comment}';
<%
    }
}
%>

準(zhǔn)備工作完成后，接下來(lái)開(kāi)發(fā)代碼，具體代碼如下：

@Bean
public GroupTemplate groupTemplate() throws Exception {
    StringTemplateResourceLoader loader = new StringTemplateResourceLoader();
    org.beetl.core.Configuration cfg = org.beetl.core.Configuration.defaultConfiguration();
    GroupTemplate gt = new GroupTemplate(loader, cfg);
    return gt;
}

這里使用的 StringTemplateResourceLoader 表示從字符串中來(lái)加載模板，也有其他的 ResourceLoader，比如 FileResourceLoader 表示從文件中加載模版，具體的看官網(wǎng)就可以了

/**
 * 單張表的ddl語(yǔ)句創(chuàng)建
 *
 * @return
 */
@PostMapping("/createDdl")
public void createDdl(@RequestParam("version") String version,@RequestParam("dbType") String dbType) {
    ddlTemplateService.createDdl(version, dbType);
}

@Override
public void createDdl(String version, String dbType) {
    DbEnum dbEnum = DbEnum.fromString(dbType);
    Assert.notNull(dbType, "數(shù)據(jù)庫(kù)類型不支持");

    switch (dbEnum) {
        case MYSQL:
            generateMysqlDdl(version, dbType, buildMySQLData());
            break;
        case ORACLE:
            generateOracleDdl(version, dbType, buildOracleData());
            break;
        case POSTGRESQL:
            generatePostgresqlDdl(version, dbType, buildPostgresqlData());
            break;
    }
}

這里支持的數(shù)據(jù)庫(kù)類型還比較少，大家可以后面擴(kuò)充

/**
 * 這里只是模擬一下，實(shí)際情況應(yīng)該是從數(shù)據(jù)庫(kù)中查詢的對(duì)應(yīng)的表及字段
 *
 * @return
 */
private Map<String, Object> buildMySQLData() {
    MySQLTable tableVO = MySQLTable.builder().tableCode("t_user").tableComment("用戶表").build();
    List<MySQLField> fieldVOList = List.of(
            MySQLField.builder().code("id").comment("主鍵").type("int").len(11).scale(0.0).autoIncrement(true).notNull(true).build(),
            MySQLField.builder().code("name").comment("姓名").type("varchar").len(50).scale(0.0).autoIncrement(false).defaultValue("").notNull(true).build(),
            MySQLField.builder().code("age").comment("年齡").type("int").len(11).scale(0.0).autoIncrement(false).notNull(true).build(),
            MySQLField.builder().code("email").comment("郵箱").type("varchar").len(50).scale(0.0).autoIncrement(false).defaultValue("").notNull(true).build(),
            MySQLField.builder().code("create_time").comment("創(chuàng)建時(shí)間").type("date").len(0).scale(0.0).autoIncrement(false).notNull(true).notNull(true).build()
    );
    return Map.of("table", tableVO, "fieldList", fieldVOList, "primaryKey", "id");
}

private Map<String, Object> buildOracleData() {
    OracleTable tableVO = OracleTable.builder().tableCode("t_user").tableComment("用戶表").build();
    List<OracleField> fieldVOList = List.of(
            OracleField.builder().code("id").comment("主鍵").type("NUMBER").len(11).scale(0.0).autoIncrement(true).notNull(true).build(),
            OracleField.builder().code("name").comment("姓名").type("varchar").len(50).scale(0.0).autoIncrement(false).defaultValue("").notNull(true).build(),
            OracleField.builder().code("age").comment("年齡").type("NUMBER").len(11).scale(0.0).autoIncrement(false).notNull(true).build(),
            OracleField.builder().code("email").comment("郵箱").type("varchar").len(50).scale(0.0).autoIncrement(false).defaultValue("").notNull(true).build(),
            OracleField.builder().code("create_time").comment("創(chuàng)建時(shí)間").type("date").len(0).scale(0.0).autoIncrement(false).notNull(true).notNull(true).build()
    );
    return Map.of("table", tableVO, "fieldList", fieldVOList, "primaryKey", "id");
}

private Map<String, Object> buildPostgresqlData() {
    PostgresqlTable tableVO = PostgresqlTable.builder().tableCode("t_user").tableComment("用戶表").build();
    List<PostgresqlField> fieldVOList = List.of(
            PostgresqlField.builder().code("id").comment("主鍵").type("integer").autoIncrement(true).notNull(true).build(),
            PostgresqlField.builder().code("name").comment("姓名").type("varchar").len(50).scale(0.0).autoIncrement(false).defaultValue("").notNull(true).build(),
            PostgresqlField.builder().code("age").comment("年齡").type("int").autoIncrement(false).notNull(true).build(),
            PostgresqlField.builder().code("email").comment("郵箱").type("varchar").len(50).scale(0.0).autoIncrement(false).defaultValue("").notNull(true).build(),
            PostgresqlField.builder().code("create_time").comment("創(chuàng)建時(shí)間").type("timestamp").len(0).scale(0.0).autoIncrement(false).notNull(true).notNull(true).build()
    );
    return Map.of("table", tableVO, "fieldList", fieldVOList, "primaryKey", "id");
}


private void generateMysqlDdl(String version, String dbType, Map<String, Object> data) {
    try {
        // 可以考慮將模版文件緩存
        Template template = groupTemplate.getTemplate(queryByUnique(version, dbType).getTemplate());
        template.binding(data);
        String render = template.render();
        log.info("生成MysqlDDL語(yǔ)句成功");
        FileUtil.writeUtf8String(render, "D:/ddl.sql");
    } catch (Exception e) {
        throw new RuntimeException("生成DDL語(yǔ)句失敗", e);
    }
}

private void generateOracleDdl(String version, String dbType, Map<String, Object> data) {
    try {
        // 可以考慮將模版文件緩存
        Template template = groupTemplate.getTemplate(queryByUnique(version, dbType).getTemplate());
        template.binding(data);
        String render = template.render();
        log.info("生成OracleDDL語(yǔ)句成功");
        FileUtil.writeUtf8String(render, "D:/ddl.sql");
    } catch (Exception e) {
        throw new RuntimeException("生成DDL語(yǔ)句失敗", e);
    }
}

private void generatePostgresqlDdl(String version, String dbType, Map<String, Object> data) {
    // 可以考慮將模版文件緩存
    Template template = groupTemplate.getTemplate(queryByUnique(version, dbType).getTemplate());
    template.binding(data);
    String render = template.render();
    log.info("生成PgSQLDDL語(yǔ)句成功");
    FileUtil.writeUtf8String(render, "D:/ddl.sql");
}

我這里運(yùn)行后會(huì)分別針對(duì)不同的數(shù)據(jù)庫(kù)類型來(lái)生成對(duì)應(yīng)的表 DDL 語(yǔ)句，效果如下：

MySQL

drop table if exists `t_user`;
create table t_user(
    `id` int(11) auto_increment not null
      comment '主鍵',
    `name` varchar(50) not null default ''
      comment '姓名',
    `age` int(11) not null
      comment '年齡',
    `email` varchar(50) not null default ''
      comment '郵箱',
    `create_time` date not null
      comment '創(chuàng)建時(shí)間'
,primary key (id)
) comment = '用戶表';

Oracle

create table "t_user"(
    "id" NUMBER(11) not null,
    "name" varchar(50) default '' not null,
    "age" NUMBER(11) not null,
    "email" varchar(50) default '' not null,
    "create_time" date null
,primary key ("id")
);

comment on table "t_user" is '用戶表';

comment on column "t_user"."id" is '主鍵';

comment on column "t_user"."name" is '姓名';

comment on column "t_user"."age" is '年齡';

comment on column "t_user"."email" is '郵箱';

comment on column "t_user"."create_time" is '創(chuàng)建時(shí)間';

PgSQL

drop table if exists "t_user";

create table "t_user"(
    "id" integer generated always as identity not null
    ,
    "name" varchar(50) not null default ''
    ,
    "age" int not null
    ,
    "email" varchar(50) not null default ''
    ,
    "create_time" timestamp not null
    
    ,primary key (id)
);

comment on table "t_user" is '用戶表';

comment on column "t_user"."id" is '主鍵';
comment on column "t_user"."name" is '姓名';
comment on column "t_user"."age" is '年齡';
comment on column "t_user"."email" is '郵箱';
comment on column "t_user"."create_time" is '創(chuàng)建時(shí)間';

這三個(gè) DDL 語(yǔ)句在對(duì)應(yīng)的數(shù)據(jù)庫(kù)里面是可以運(yùn)行

總結(jié)

這里采用數(shù)據(jù)庫(kù)存儲(chǔ)模版的方式比在代碼中硬編碼維護(hù)性要好，不然模版有改動(dòng)的話，還需要去修改代碼重新打包，總體下來(lái)并不是很難。

以上就是SpringBoot+Beetl實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)庫(kù)DDL的示例代碼的詳細(xì)內(nèi)容，更多關(guān)于SpringBoot Beetl動(dòng)態(tài)數(shù)據(jù)庫(kù)DDL的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: