前言：

由于公司项目中有一个自定义表单的需求，而本人却无类似开发经验，在朋友和同事的努力下，依然没有找到最佳的解决方案，后来自己根据自己对业务的了解和别人的思路借鉴，最终设计出了现在这款自定义表单的逻辑。

项目环境：king base V8 (人大金仓数据库) ，Mybatis-Plus v3.4.2 ，spring-boot-2.2.5.RELEASE

在本文中，主要阐述结合 VUEJS 动态表格的数据结构进行设计接口和数据库，在此之前，需要了解一下 VUEJS 动态表格

核心思想

1. 自定义的属性与属性对应的数据要做绑定关系，本文中，使用了 sorted 字段对字段和数据进行绑定

2. 组合查询分页数据，利用 mybatis 的动态 sql 和 ${} 进行查询，前提是需要与前端配合恰当

1、 VUEJS 动态表格

动态表格所需数据结构

{
    "tableHead":[
        {"key":"nickName", "fieldsName":"昵称"},
        {"key":"account",  "fieldsName":"登录账户"},
        {"key":"password", "fieldsName":"登录密码"}
    ],
    "tableVal":[
        {"nickName":"雷电法王_杨永信", "account":"lei_dian_yyx", "password":"yangyongxin"},
        {"nickName":"潘嘎之交", "account":"gazige", "password":"gagagaga"},
        {"nickName":"精神小伙", "account":"jinshenxiaohuo", "password":"123456"}
    ]
}

通过对 VUEJS 动态表单的数据结构的了解，那么我们很快就能设计出数据库表结构

2、设计数据库表

2.1 t_custom_table

存储字段属于哪个表，做字段表唯一标识，1 - N 关系 里面主要存储 表名，这里就不放 DDL 语句了，一下的 table_id 属于本表 id

2.2 字段表设计 (存储用户提交的自定义字段)

CREATE TABLE "t_inspector_db_fields" (
    "id" INT8 NOT NULL, 
    "fields_name" VARCHAR(32 byte) NULL,                -- 字段名称
    "data_type" INT4 NULL,                              -- 字段数据类型 1：文字 2：数字
    "fields_entry_type" INT4 NULL,                      -- 字段录入方式,展示类型，0:单行文本 1:多行文本 2:下拉菜单 3:时间选择框
    "datasource_type" INT4 NULL,                        -- 数据源类型：1字典(datasource配置字典key) 2：枚举（枚举数据结构） 3 组织架构  4 人员架构  5 督查督办事项分类 6 文本输入
    "max_length" INT4 NULL,                             -- 最大字数
    "show_table" TINYINT NULL DEFAULT 0,                -- 是否列表展示 0：不展示 1：展示
    "audit_flag" TINYINT NULL DEFAULT 0,                -- 审核操作字段，在审核阶段需要填写 0不需要，1需要
    "selector_flag" TINYINT NULL DEFAULT '0'::TINYINT,  -- 是否参与查询 0：不参与 1：参与
    "table_id" INT8 NULL,                               -- 关联表ID
    "data_source" VARCHAR NULL,                         -- 配合datasource_type,字典配置字典KEY，枚举配置枚举数据结构
    "sorted" INT2 NULL,                                 -- 核心字段，排序标识，绑定标识
    "multi_line_text" INT4 NULL DEFAULT 0,              -- 是否可以多行扩展  0:不能，1:能
    "deleted" TINYINT NULL                              -- 软删除标识
)
WITH (
    OIDS=FALSE
);

字段描述：

fields_type: 这里是来规范存储类型，但是实际上后端都是用的字符串存储，主要是为了方便用户直观看，当然这个字段设计也在需求之中

max_length：字段最大长度，如果是多行文本或者是富文本，则为 -1

show_table : 是否在表单中进行显式展示，0：展示，1：不展示

data_src_type ：数据源，例如（0：字典，1：手动录入，2....），数据源类型为字典，之对应的 data_src 就应该是 字典表中 获取数据的 KEY

sorted : 注意，此字段非常重要，该字段有两个作用：(1) 来对字段展示的顺序进行规范 ，(2) 通过属性的 sorted 可以直接找到 属性对应的 数据，起一个绑定关系，这里可能暂时有点迷，后续就会看起来更加清晰。

fields_entry_type : 数据录入方式：（0:单行文本 1:多行文本 2:下拉菜单 3:时间选择框）

2.3 数据存储表设计 (存储自定义表单的数据)

都到这一步了，最终确定下来，数据通过纵向存储的方式

需要攻克的问题：

1. 数据纵向存储，那么多条数据，怎么被视为横向的一条数据呢？

2. 数据纵向，那么如何将字段和字段值进行绑定？

解决办法:

1. 纵向对数据存储，没有唯一标识，无法组建一个多行数据转为一条数据的标识，那么就在插入多条数据之前，生成一个 32 位的唯一标识，来作为一条数据纵转横的 flag

2. 在数据表中也生成一个 sorted 字段，来与字段表中的属性进行绑定，例如 字段 表中 sorted 为 1 的值是 userName， 那么 数据表中，只要存储 sorted 为 1 的数据为 张三 即可，这样就形成了一对一的绑定

问题解决了，那么就开始设计表，这里我就直接放表结构了（表结构是 人大金仓数据库的，mysql 的需要手动改改）

CREATE TABLE "t_inspector_fields_data" (
    "id" VARCHAR(32 byte) NOT NULL,
    "fields_id" VARCHAR(32 byte) NOT NULL,      -- 被关联的字段的Id
    "unique_mark" VARCHAR(32 byte) NOT NULL,    -- 唯一标识，32位
    "sorted" INT4 NOT NULL,                     -- 核心字段，排序标识，绑定标识，与t_inspector_db_fields表保持一致
    "content" TEXT NULL,                        -- 真实的内容，这里考虑到字段长度无法确定，只有用 text 来规定
    "deleted" INT4 NULL DEFAULT 0,              -- 软删除标识
    "classify_id" INT8 NULL,                    -- 关联表ID
    "year" INT4 NULL,                           -- 冗余字段，用于方便查询
    "frequency" INT2 NULL,                      -- 冗余字段，用于方便查询
    "month" INT2 NULL,                          -- 冗余字段，用于方便查询
    "week" INT4 NULL,                           -- 冗余字段，用于方便查询
    "fill" INT2 NULL DEFAULT '0'::SMALLINT,     -- 业务字段
    "audit" INT2 NULL DEFAULT '0'::SMALLINT     -- 业务字段
)
WITH (
    OIDS=FALSE
);

3、接口设计

3.1 属性字段新增 对应表 t_inspector_db_fields（提交 JSON）

{
    "fieldsName":"任务分类",  // 字段名称   
    "dataType":"1",         // 字段数据类型 1：文字 2：数字
    "fieldsEntryType":3,    // 0:单行文本 1:多行文本 2:下拉菜单 3:时间选择框
    "datasourceType":6,     // 1字典(datasource配置字典key) 2：枚举（枚举数据结构） 3 组织架构  4 人员架构  5 督查督办事项分类 6 文本输入
    "dataSource":"",        //datasourceType=0，dataSource对应字典中的key。datasourceType=1，存储下拉菜单的数据，可以自定义数据结构，方便解析就行，可自定义
    "maxLength":-1,         // 该字段无需校验内容长度
    "showTable":1,          // 显示在 table 中
    "auditFlag":0,          // 不参与审核
    "selectorFlag":0,       // 不参与头部查询
    "multiLineText":0,      // 不参与行扩展
    "tableId":1355          // 关联的表
}

3.2 获取表头和数据分页接口（复杂）

根据 tableId 查询表下的字段

出现问题：

1. 如何分页？

2. 如何组合查询？

3. 如何区分精准匹配和模糊查询？

思路：

1. 首先要思考如何进行分页查询，因为数据库里面的数据是纵向存储的，非横向存储。那么我只需要分页找到条件匹配的并且去重后的唯一标识即可，然后再次根据分页标识，查到返回的数据转为行数据即可。

1. 组合查询就需要在 sql 上下功夫了，结合 Mybatis 动态 sql，不同条件的 sql 使用 UNION ALL 进行连接查询结果，最后去重得到唯一标识

2. 精准匹配和模糊匹配只需要两个集合来存储查询条件集合，为了更加的灵活，我这里使用了mybatis 的 ${} 占位符取查询的 key 值

3.2.1 组合查询提交 json

{
    "tableId":1355,
    "query":[ // 模糊查询
        {"sorted":"8","content":"测试"},     // 查询位置为8的字段类容包含 测试 字样的数据
        {"sorted":"13","content":"测试单位"}  // 查询位置为13的字段类容包含 测试单位 字样的数据
    ],
    "page": {"current":1, "size":10},       // 分页信息
    "nonQuery":[// 精准匹配
        {
            "keyName":"week",   // 根据周字段精准匹配
            "keyVal":"12"
        },
        {
            "keyName":"year",   // 根据年字段精准匹配
            "keyVal":"2021"
        }
    ]
}

3.2.2 组合查询动态 sql

<!-- 先通过查询条件匹配到符合条件的唯一标识，然后通过拿到所有符合条件的唯一标识进行获取数据 -->
<select id="selectDistinctUniqueMarkPage" resultType="java.lang.String">
    <!-- 精准匹配条件为空的情况查询的内容 -->
    <if test="@org.apache.commons.collections4.CollectionUtils@isEmpty(dto.query)">
        select
            distinct "unique_mark"
        from
            "t_inspector_fields_data"
        where
            deleted = 0
            and table_id = #{dto.tableId}
        <foreach collection="dto.nonQuery" index="j" item="non" open=" " separator=" " close=" ">
            <!-- {"keyName":"year", "keyVal":"2021" } -->
            and ${non.keyName} = #{non.keyVal}
        </foreach>
    </if>
    
    <!-- 精准匹配条件不为空的情况查询的内容 -->
    <if test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(dto.query)">
        select distinct a."unique_mark" from
        (
            <foreach collection="dto.query" index="i" item="val" open=" " separator=" UNION ALL " close=" ">
                select
                    distinct "unique_mark"
                from
                    "t_inspector_fields_data"
                where
                    deleted = 0
                    and table_id = #{dto.tableId}
                    AND "sorted" = #{val.sorted}
                    AND content like concat('%', #{val.content}, '%')
                    <foreach collection="dto.nonQuery" index="j" item="non" open=" " separator=" " close=" ">
                        <!-- {"keyName":"year", "keyVal":"2021" } -->
                        and ${non.keyName} = #{non.keyVal}
                    </foreach>
            </foreach>
        ) a
    </if>
</select>

3.2.3 最终根据查询条件打印的 sql 语句

select
    distinct a."unique_mark"
from
    (
    select
        distinct "unique_mark"
    from
        "t_inspector_fields_data"
    where
        deleted = 0
        and table_id = ?
        and "sorted" = ?
        and content like concat('%', ?, '%')
        and week = ?
        and year = ?
union all
    select
        distinct "unique_mark"
    from
        "t_inspector_fields_data"
    where
        deleted = 0
        and table_id = ?
        and "sorted" = ?
        and content like concat('%', ?, '%')
        and week = ?
        and year = ?
    ) a