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

心已赠人 2024-02-05 16:54 29阅读 0赞

## 初始化数据库 ##

> 更新时间：2023-12-01 15:38:30
> 
> 文档链接：[https://open.hand-china.com/document-center/doc/product/10067/10032?doc\_id=6229&doc\_code=6229][https_open.hand-china.com_document-center_doc_product_10067_10032_doc_id_6229_doc_code_6229]

## 介绍 ##

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

### 创建用户 ###

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

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_.xls]
    
    ![img][]
    
    ![img][img 1]
 *  设计完成后，将相应数据库的脚本拷贝到 `todo_service` 库下执行，创建表。
    
    ![img][img 2]

### 导出Groovy脚本 ###

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

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

*  使用代码生成器工具生成 Groovy 脚本。
    
    ![img][img 3]
 *  **todo\_user.groovy**
    
        package script.db
        databaseChangeLog(logicalFilePath: 'script/db/todo_user.groovy') {
              
            changeSet(author: "your.email@email.com", 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: "your.email@email.com", 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` 启动安装工具
    
    ![img][img 4]
 *  访问安装工具页面，选择 todo\_service 安装
    
    ![img][img 5]
 *  生成 groovy 之后，如果表结构有变更，首先更新 Excel 表设计，再向 groovy 脚本中添加 changeSet 来添加变更记录，再使用安装工具来同步其它环境。

### 验证表结构 ###

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

### 项目数据库配置 ###

在`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

[https_open.hand-china.com_document-center_doc_product_10067_10032_doc_id_6229_doc_code_6229]: https://open.hand-china.com/document-center/doc/product/10067/10032?doc_id=6229&doc_code=6229
[TODO_.xls]: https://file.open.hand-china.com/hsop-hsop/hsop/video/3/25ca224b804a4453a6f9896593b1b2e9/TODO%E8%A1%A8%E8%AE%BE%E8%AE%A1.xls
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8dd3caecc60942029474279156b16519.png
[img 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c10d40747bac4b44b53ec6d30100c3bc.png
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d5cf0737263d4c358d1f28092a10eaef.png
[Liquibase]: http://www.liquibase.org/
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/2ab5f77268e14784a90aae9832af4328.png
[img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bd1b790ef2ac4bf8abcca2eee54d34c4.png
[img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/080cc1658a8a491d86db1aec33b62ce7.png