Hzero教程：初始化数据库及同步表结构（基于liquibase + groovy）

初始化数据库

更新时间：2023-12-01 15:38:30

介绍

项目创建成功之后，需要初始化本地数据库。在开发之前，请确保本地项目已经创建成功，详见 创建项目

创建用户

确保数据库启动成功，并创建项目访问的用户。

CREATE USER 'hzero'@'%' IDENTIFIED BY "hzero";

创建数据库

用户创建成功之后，创建项目对应的数据库，并将新创建的数据库权限赋予用户。

CREATE DATABASE todo_service DEFAULT CHARACTER SET utf8;

GRANT ALL PRIVILEGES ON todo_service.* TO hzero@'%';

FLUSH PRIVILEGES;

设计表结构

• 先使用Excel设计 todo_user 及 todo_task 表结构。Excel 设计参考：
  TODO表设计.xls

• 设计完成后，将相应数据库的脚本拷贝到 todo_service 库下执行，创建表。

导出Groovy脚本

HZERO 采用Liquibase + groovy 的方式对数据库管理，便于后续同步各个环境数据库结构以及升级。

更多有关Liguibase的资料见 Liquibase 官网。

• 使用代码生成器工具生成 Groovy 脚本。
• todo_user.groovy

package script.db
databaseChangeLog(logicalFilePath: 'script/db/todo_user.groovy') {
    changeSet(author: "[email protected]", id: "2020-02-03-todo_user") {
        def weight = 1
        if(helper.isSqlServer()){
            weight = 2
        } else if(helper.isOracle()){
            weight = 3
        }
        if(helper.dbType().isSupportSequence()){
            createSequence(sequenceName: 'todo_user_s', startValue:"1")
        }
        createTable(tableName: "todo_user", remarks: "用户表") {
            column(name: "ID", type: "bigint(20)", autoIncrement: true ,   remarks: "表ID，主键，供其他表做外键")  {constraints(primaryKey: true)} 
            column(name: "EMPLOYEE_NAME", type: "varchar(" + 30 * weight + ")",  remarks: "员工名")  {constraints(nullable:"false")}  
            column(name: "EMPLOYEE_NUMBER", type: "varchar(" + 30 * weight + ")",  remarks: "员工编号")  {constraints(nullable:"false")}  
            column(name: "EMAIL", type: "varchar(" + 60 * weight + ")",  remarks: "邮箱")   
            column(name: "object_version_number", type: "bigint(20)",   defaultValue:"1",   remarks: "行版本号，用来处理锁")  {constraints(nullable:"false")}  
            column(name: "creation_date", type: "datetime",   defaultValueComputed:"CURRENT_TIMESTAMP",   remarks: "")  {constraints(nullable:"false")}  
            column(name: "created_by", type: "bigint(20)",   defaultValue:"-1",   remarks: "")  {constraints(nullable:"false")}  
            column(name: "last_updated_by", type: "bigint(20)",   defaultValue:"-1",   remarks: "")  {constraints(nullable:"false")}  
            column(name: "last_update_date", type: "datetime",   defaultValueComputed:"CURRENT_TIMESTAMP",   remarks: "")  {constraints(nullable:"false")}  
        }
        addUniqueConstraint(columnNames:"EMPLOYEE_NUMBER",tableName:"todo_user",constraintName: "TODO_USER_u1")
    }
}

• todo_task.groovy

package script.db
databaseChangeLog(logicalFilePath: 'script/db/todo_task.groovy') {
    changeSet(author: "[email protected]", id: "2020-02-03-todo_task") {
        def weight = 1
        if(helper.isSqlServer()){
            weight = 2
        } else if(helper.isOracle()){
            weight = 3
        }
        if(helper.dbType().isSupportSequence()){
            createSequence(sequenceName: 'todo_task_s', startValue:"1")
        }
        createTable(tableName: "todo_task", remarks: "任务表") {
            column(name: "ID", type: "bigint(20)", autoIncrement: true ,   remarks: "表ID，主键，供其他表做外键")  {constraints(primaryKey: true)} 
            column(name: "EMPLOYEE_ID", type: "bigint(20)",  remarks: "员工ID，TODO_USER.ID")  {constraints(nullable:"false")}  
            column(name: "STATE", type: "varchar(" + 30 * weight + ")",  remarks: "状态，值集：TODO.STATE")  {constraints(nullable:"false")}  
            column(name: "TASK_NUMBER", type: "varchar(" + 60 * weight + ")",  remarks: "任务编号")  {constraints(nullable:"false")}  
            column(name: "TASK_DESCRIPTION", type: "varchar(" + 240 * weight + ")",  remarks: "任务描述")   
            column(name: "TENANT_ID", type: "bigint(20)",  remarks: "租户ID")  {constraints(nullable:"false")}  
            column(name: "object_version_number", type: "bigint(20)",   defaultValue:"1",   remarks: "行版本号，用来处理锁")  {constraints(nullable:"false")}  
            column(name: "creation_date", type: "datetime",   defaultValueComputed:"CURRENT_TIMESTAMP",   remarks: "")  {constraints(nullable:"false")}  
            column(name: "created_by", type: "bigint(20)",   defaultValue:"-1",   remarks: "")  {constraints(nullable:"false")}  
            column(name: "last_updated_by", type: "bigint(20)",   defaultValue:"-1",   remarks: "")  {constraints(nullable:"false")}  
            column(name: "last_update_date", type: "datetime",   defaultValueComputed:"CURRENT_TIMESTAMP",   remarks: "")  {constraints(nullable:"false")}  
        }
        addUniqueConstraint(columnNames:"TASK_NUMBER,TENANT_ID",tableName:"todo_task",constraintName: "TODO_TASK_u1")
    }
}

同步表结构

将表结构同步到其它环境时，可使用数据安装工具来安装数据库，将 groovy 脚本导出后，放到本地 hzero-resource 项目的 groovy 目录下，然后启动工具安装数据库即可。

• 将导出的 groovy 脚本放到 ~/hzero-resource/groovy/todo_service 目录下
• 修改 ~/hzero-resource/docs/mapping/service-mapping.xml，加入 todo_service 的数据库信息

<service name="hzero-todo-service" filename="todo_service" schema="todo_service" description="TODO示例服务"/>

• 通过 database-init.sh 启动安装工具
• 访问安装工具页面，选择 todo_service 安装
• 生成 groovy 之后，如果表结构有变更，首先更新 Excel 表设计，再向 groovy 脚本中添加 changeSet 来添加变更记录，再使用安装工具来同步其它环境。

验证表结构

登录数据库，查询现有的表结构。

mysql> show tables;
+---------------------------------------+
| Tables_in_todo_service |
+---------------------------------------+
| DATABASECHANGELOG                     |
| DATABASECHANGELOGLOCK                 |
| TODO_TASK                             |
| TODO_USER                             |
+---------------------------------------+
5 rows in set (0.00 sec)

项目数据库配置

在pom.xml 文件中添加数据库操作相关依赖。

<dependency>
    <groupId>org.hzero.starter</groupId>
    <artifactId>hzero-starter-mybatis-mapper</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

在项目的 application.yml 文件中添加数据库连接信息：

spring:
  datasource:
    url: ${SPRING_DATASOURCE_URL:jdbc:mysql://db.hzero.com.cn:3306/todo_service?useUnicode=true&characterEncoding=utf-8&useSSL=false}
    username: ${SPRING_DATASOURCE_USERNAME:hzero}
    password: ${SPRING_DATASOURCE_PASSWORD:hzero}
# MyBatis Mapper 扫描
mybatis:
  mapperLocations: classpath*:/mapper/*.xml
  configuration:
    mapUnderscoreToCamelCase: true

项目根目录下执行命令。项目正常启动，则数据库连接配置正常。

$ mvn clean spring-boot:run