SpringBoot+Beetl实现动态数据库DDL的示例代码_Java

序言

最近公司里有一个新的需求，需要导出数据库元数据表中存储的表的 ddl 语句，而在元数据表中数据源的类型庞大，少则十几种多则达到几十种，各种数据库类型都有，比如常见就有 mysql postgresql oracle sql server等等，而不同的数据库其数据类型和语法都存在一些差异，如果在代码中去处理工作量就太大了。所以我想到使用模版引擎来处理，现在市面上出现了一个新的模版引擎 beetl。本篇文章就从这个模版引擎来入手介绍如何实现功能。代码已经提交到了我的 github 仓库

beetl

beetl 的使用方式和 thymeleaf、freemarker 差不多，而且其语法简单，性能也优于这两者，目前在国内已经有一定的用户群体。话不多说，具体的使用方式非常简单，大家看一下官网就会了，下面来说功能怎么实现

代码

使用前在springboot中使用的时候需要先导入依赖，我测试的 springboot 的版本为 3.2.1

<dependency>
    <groupid>com.ibeetl</groupid>
    <artifactid>beetl-spring-boot-starter-classic</artifactid>
    <version>3.19.1.release</version>
</dependency>

创建一下库表，因为模版文件是存储在数据库中，方便后期维护

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;

表创建完成后，我事先往这个表中添加了三条数据，分别代表 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}';
<%
    }
}
%>

准备工作完成后，接下来开发代码，具体代码如下：

@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 表示从字符串中来加载模板，也有其他的 resourceloader，比如 fileresourceloader 表示从文件中加载模版，具体的看官网就可以了

/**
 * 单张表的ddl语句创建
 *
 * @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, "数据库类型不支持");

    switch (dbenum) {
        case mysql:
            generatemysqlddl(version, dbtype, buildmysqldata());
            break;
        case oracle:
            generateoracleddl(version, dbtype, buildoracledata());
            break;
        case postgresql:
            generatepostgresqlddl(version, dbtype, buildpostgresqldata());
            break;
    }
}

这里支持的数据库类型还比较少，大家可以后面扩充

/**
 * 这里只是模拟一下，实际情况应该是从数据库中查询的对应的表及字段
 *
 * @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("创建时间").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("创建时间").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("创建时间").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语句成功");
        fileutil.writeutf8string(render, "d:/ddl.sql");
    } catch (exception e) {
        throw new runtimeexception("生成ddl语句失败", 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语句成功");
        fileutil.writeutf8string(render, "d:/ddl.sql");
    } catch (exception e) {
        throw new runtimeexception("生成ddl语句失败", 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语句成功");
    fileutil.writeutf8string(render, "d:/ddl.sql");
}

我这里运行后会分别针对不同的数据库类型来生成对应的表 ddl 语句，效果如下：

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 '创建时间'
,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 '创建时间';

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 '创建时间';

这三个 ddl 语句在对应的数据库里面是可以运行