Custom form development process and thought notes

Foreword:

Since there is a need for a custom form in the company’s project, but I have no similar development experience, I still haven’t found the best solution with the efforts of my friends and colleagues. Later, I learned from my own business understanding and others’ ideas. , And finally designed the logic of this custom form.

Project environment: king base V8(NPC gold warehouse database), Mybatis-Plus v3.4.2,spring-boot-2.2.5.RELEASE

In this paper, mainly on combined VUEJSdata structure of a dynamic table design interfaces and databases, before that, we need to look at the VUEJSdynamic table

main idea

The custom attribute and the data corresponding to the attribute need to be bound. In this article, the sorted field is used to bind the field and data

Combination inquiry page data using mybatisthe dynamic sqland the ${}query, with the proviso that the tip needs appropriate

1. VUEJSDynamic form

Data structure required for dynamic table

{    "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"}    ]}
By VUEJSunderstanding the dynamic form of data structure, then we will soon be able to design a database table structure

2. Design the database table

2.1 t_custom_table

Storage field belongs to which table, doing field in the table that uniquely identifies, 1 - N relations inside the main storage table name, here hold DDLstatement, and what belongs to this form of table_id id

2.2 Field table design (store user-defined fields submitted by users)

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);
Field description:
fields_type: Here is to standardize the storage type, but in fact the backend uses string storage, mainly for the convenience of users to watch directly, of course, the design of this field is also in demand
max_length: The maximum length of the field, if it is multi-line text or rich text, it is -1
show_table : Whether to display explicitly in the form, 0: display, 1: not display
data_src_type: A data source, such as (0: dictionary, 1: manual entry, 2 ....), the data source type of dictionary, the corresponding data_srcshould be acquired in the data dictionary table KEY
sorted: Note that this field is very important, this field has two effects: (1) to display the order field specification, (2) attributes sortedcan be found corresponding to the attribute data directly from a binding relationship, there may be temporarily It’s a bit fascinating, and it will look clearer later.
fields_entry_type : Data entry method: (0: single-line text 1: multi-line text 2: drop-down menu 3: time selection box)

2.3 Data storage table design (store data in custom forms)

It’s all here, and it’s finally determined that the data will be stored vertically

Problems to be overcome:

Data is stored vertically. How can so many pieces of data be regarded as horizontal pieces of data?

Data is vertical, so how to bind the field and field value?

Solution:

Longitudinal data storage, no unique identifier, can not form a plurality of rows of data into an identification data, before it is inserted in the plurality of pieces of data to generate a 32unique identifier bits as a data transfer vertical transverseflag

Generate a table in the data sortedfields, to bind the fields in a table of attributes, such as fields in the table sortedas 1the value userName, then the data table, stored as long sortedas 1the data 张三can, thus forming one Binding

The problem is solved, then I start to design the table, here I will directly put the table structure (the table structure is the NPC Jincang database, you mysqlneed to change it manually)

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. Interface design

3.1 New Correspondence Table for Attribute Fields t_inspector_db_fields(Submit 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 Get header and data paging interface (complex)

According to tableIdthe fields in the lookup table
problem appear:

How to page?

How to combine queries?

How to distinguish between exact match and fuzzy query?

Ideas:

The first thing to think about is how to perform paging queries, because the data in the database is stored vertically, not horizontally. Then I only need to page to find the unique identifier that matches the condition and de-duplicate, and then according to the page identifier again, the returned data can be converted into row data.

In combination inquiry need to sqlwork hard, the combined Mybatisdynamic sql, different conditions of sqluse UNION ALLto connect the query result, and finally to re-obtain a unique identification

Precise and fuzzy matches only need two sets to store the query set of conditions for a more flexible, I used here mybatisis ${}a placeholder for the query takes keyvalue

3.2.1 Combined query submission 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 Combined query dynamics sql

<!-- 先通过查询条件匹配到符合条件的唯一标识,然后通过拿到所有符合条件的唯一标识进行获取数据 --><select id="selectDistinctUniqueMarkPage" resultType="java.lang.String">    <!-- 精准匹配条件为空的情况查询的内容 -->    <if test="@[email protected](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="@[email protected](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 final print based query sqlstatement

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