1.字典项
字典主要是方便维护常用的数据。比如常用的变量使用枚举进行维护,枚举还要编译有点麻烦,可以通过数据库字典和字典项进行维护。表如下:
DROP TABLE IF EXISTS `sys_dict`; CREATE TABLE `sys_dict` ( `ID` varchar(40) NOT NULL COMMENT '记录ID', `DICT_NAME` varchar(40) DEFAULT NULL COMMENT '字典名称', `DICT_CODE` varchar(40) DEFAULT NULL COMMENT '字典编码', `TYPE` varchar(4) DEFAULT NULL COMMENT '内外部字典(1-内部 2-外部)', `STATUS` varchar(4) DEFAULT NULL COMMENT '字典状态(1-正常 2-停用)', `DICT_SORT` decimal(10,0) DEFAULT NULL COMMENT '排序', `SYS_FLAG` varchar(4) DEFAULT NULL COMMENT '有效标志', `SYS_DEPT` varchar(40) DEFAULT NULL COMMENT '部门', `SYS_CREATE_USER` varchar(40) DEFAULT NULL COMMENT '新增用户', `SYS_UPDATE_USER` varchar(40) DEFAULT NULL COMMENT '更新用户', `SYS_CREATE_TIME` datetime DEFAULT NULL COMMENT '新增时间', `SYS_UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间', `SYS_REMARK` varchar(40) DEFAULT NULL COMMENT '备注/描述信息', PRIMARY KEY (`ID`) ) COMMENT='数据字典类型'; DROP TABLE IF EXISTS `sys_dict_item`; CREATE TABLE `sys_dict_item` ( `ID` varchar(40) NOT NULL COMMENT '记录ID', `DICT_CODE` varchar(40) DEFAULT NULL COMMENT '字典类别编码', `ITEM_NAME` varchar(40) DEFAULT NULL COMMENT '字典项名称', `ITEM_CODE` varchar(40) DEFAULT NULL COMMENT '字典项编码', `ITEM_SORT` decimal(10,0) DEFAULT NULL COMMENT '排序', `STATUS` varchar(4) DEFAULT NULL COMMENT '状态(1启用 0不启用)', `SYS_FLAG` varchar(4) DEFAULT NULL COMMENT '有效标志', `SYS_DEPT` varchar(40) DEFAULT NULL COMMENT '部门', `SYS_CREATE_USER` varchar(40) DEFAULT NULL COMMENT '新增用户', `SYS_UPDATE_USER` varchar(40) DEFAULT NULL COMMENT '更新用户', `SYS_CREATE_TIME` datetime DEFAULT NULL COMMENT '新增时间', `SYS_UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间', `SYS_REMARK` varchar(40) DEFAULT NULL COMMENT '备注/描述信息', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据字典项';
这个场景常用来控制下拉框、常量池子等。
2.业务表设计
我们内部的场景是这样的,用户可以输入表字段、类型等。也可以通过关系聚合这些表,这里我们有两个表需要维护
一个是业务表、一个是关系表(说是对象表也可以)。
通过可视化的形式组织这些关系,然后生成表单。也就是html或者未来的前瞻性框架vue、bootstrap之类的。我们叫做设计表,或者关系定义表、或者设计表,叫法不同,用法类似
页面的交互中可能会使用到对话框之类的,我们也可以建立一个对话框表。主要维护数据的来源,不如http或者数据源。不说了 上表ddl
DROP TABLE IF EXISTS `PANGU_FORM_BUSINESS_MODEL`; CREATE TABLE `PANGU_FORM_BUSINESS_MODEL` ( `ID` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '主键id', `DESCRIPTION` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '描述', `MODEL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '实体名', `TABLE_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '表名', `GROUP_ID` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '分类id', `GROUP_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '分类名称', `TYPE` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '类型', `DS_KEY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据源Id', `DS_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据源名称', `CREATE_BY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人', `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `UPDATE_BY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '更新人', `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', `IS_CREATED_TABLE` bit(1) DEFAULT NULL COMMENT '是否创建表', `IS_EXTERNAL` bit(1) DEFAULT NULL COMMENT '外部', `IS_DELETE` tinyint(255) DEFAULT NULL, PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='业务对象表'; -- ---------------------------- -- Records of PANGU_FORM_BUSINESS_MODEL -- ---------------------------- BEGIN; INSERT INTO `PANGU_FORM_BUSINESS_MODEL` VALUES ( '请假流程', 'leave1', 'leave1', '20041', '请假流程', 'inner', '1', '本地数据源', 'super', '17:28:24', NULL, '2016-04-15 17:28:24', b'1', b'0', 0); INSERT INTO `PANGU_FORM_BUSINESS_MODEL` VALUES ( '发文流程', 'fawen', 'fawen', '66681', '发文流程', 'inner', '1', '本地数据源', 'super', '2016-04-15 17:28:24', NULL, '2016-04-15 17:28:24', b'1', b'0', 0); COMMIT;
-- ---------------------------- -- Table structure for PANGU_FORM_BUSINESS_MODEL_COLUMN -- ---------------------------- DROP TABLE IF EXISTS `PANGU_FORM_BUSINESS_MODEL_COLUMN`; CREATE TABLE `PANGU_FORM_BUSINESS_MODEL_COLUMN` ( `ID` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '主键id', `DESCRIPTION` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '描述', `COLUMN_KEY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '属性名', `NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '数据库字段名', `FORMAT` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '日期格式化', `TABLE_ID` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '对应的表id', `DEFAULT_VALUE` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '默认值', `DATA_TYPE` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '字段类型', `ATTR_LENGTH` int(11) DEFAULT NULL COMMENT '属性长度', `DECIMAL_LENGTH` int(11) DEFAULT NULL COMMENT '小数长度', `IS_REQUIRED` bit(1) DEFAULT NULL COMMENT '必填', `IS_PRIMARY` bit(1) DEFAULT NULL COMMENT '是否主键', `IS_DELETE` bit(1) DEFAULT NULL COMMENT '是否删除', `CREATE_BY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人', `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `UPDATE_BY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '更新人', `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='业务对象列明细表'; -- ---------------------------- -- Table structure for PANGU_FORM_BUS_DEFINITION -- ---------------------------- DROP TABLE IF EXISTS `PANGU_FORM_BUS_DEFINITION`; CREATE TABLE `PANGU_FORM_BUS_DEFINITION` ( `ID` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '表单元数据定义ID', `NAME` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表单名称', `FORM_KEY` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表单key', `FORM_BUSINESS_ID` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表单key', `FORM_DESC` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '描述', `FORM_HTML` longblob COMMENT '表单定义HTML', `STATUS` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '状态 draft=草稿;deploy=发布', `FORM_TYPE` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '表单类型 分为 pc,mobile', `GROUP_ID` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '分类id', `GROUP_NAME` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '分类名称', `IS_MAIN` tinyint(4) DEFAULT NULL COMMENT '是否主版本', `HIDE_MAIN` tinyint(4) DEFAULT NULL COMMENT '是否隐含主表(1:是,0:否)', `VERSION` int(11) DEFAULT NULL COMMENT '版本', `DEPLOYMENT_ID` varchar(255) DEFAULT NULL COMMENT '部署ID', `DEF_ID` varchar(255) DEFAULT NULL COMMENT '定义ID', `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `CREATE_BY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人', `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', `UPDATE_BY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '更新人', `FORM_TAB_TITLE` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '表单tab标题', `CLASSIFY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '表单类型(combine:组合,single:单表)', `PAGE_FORMS` varchar(2000) COLLATE utf8_bin DEFAULT NULL COMMENT '页签表单keys', `PAGE_FORM_JSON` longblob COMMENT '页签表单json', `SCOPE_TYPE` varchar(255) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='业务表单定义'; -- ---------------------------- -- Records of PANGU_FORM_BUS_DEFINITION -- ---------------------------- BEGIN; INSERT INTO `PANGU_FORM_BUS_DEFINITION` VALUES ( '请假流程', 'leave_', '719227522360803328', '请假流程', NULL, 'draft', 'pc', '20041', '请假流程', 0, 0, 0, NULL, NULL, '2016-04-15 17:28:24 04:37:22', 'super', '2016-04-15 17:28:24 04:37:29', 'super', NULL, NULL, NULL, 0x7B226C697374223A5B7B226E616D65223A22E5BC80E5A78BE697B6E997B4222C22636F6E74726F6C54797065223A6E756C6C2C2269636F6E223A2269636F6E2D64617465222C2274797065223A2264617465222C226B6579223A2273746172745F222C226D6F64656C223A2273746172745F222C226F7074696F6E73223A7B227370616E223A31322C2264697361626C6564223A66616C73652C2273686F77223A747275652C2273686F775469746C65223A747275652C22637573746F6D436C617373223A6E756C6C2C22616E6E6F746174696F6E5469746C65223A22E6B7BBE58AA0E689B9E6B3A8222C22616E6E6F746174696F6E436F6E74657874223A22222C226461746154797065223A22222C2273686F77416E6E6F746174696F6E223A66616C73652C2264656661756C7456616C7565223A22222C22706C616365686F6C646572223A6E756C6C2C227374617274506C616365686F6C646572223A6E756C6C2C22656E64506C616365686F6C646572223A6E756C6C2C227769647468223A6E756C6C2C226461746556616C696452756C65223A6E756C6C2C22636B65636B65644461746576616C696454797065223A6E756C6C2C22666F726D6174223A22797979792D4D4D2D64642048483A6D6D3A7373222C22726561646F6E6C79223A66616C73652C226564697461626C65223A66616C73652C22636C65617261626C65223A66616C73652C2273686F77526564223A66616C73652C227265717569726564223A66616C73652C2274797065223A226461746574696D65222C2274696D657374616D70223A747275657D2C2272756C6573223A6E756C6C2C227461626C65436F6C756D6E73223A6E756C6C2C22746162436F6C756D6E73223A6E756C6C2C22636F6C756D6E73223A6E756C6C7D2C7B226E616D65223A22E7BB93E69D9FE697B6E997B4222C22636F6E74726F6C54797065223A6E756C6C2C2269636F6E223A2269636F6E2D64617465222C2274797065223A2264617465222C226B6579223A22656E645F222C226D6F64656C223A22656E645F222C226F7074696F6E73223A7B227370616E223A31322C2264697361626C6564223A66616C73652C2273686F77223A747275652C2273686F775469746C65223A747275652C22637573746F6D436C617373223A6E756C6C2C22616E6E6F746174696F6E5469746C65223A22E6B7BBE58AA0E689B9E6B3A8222C22616E6E6F746174696F6E436F6E74657874223A22222C226461746154797065223A22222C2273686F77416E6E6F746174696F6E223A66616C73652C2264656661756C7456616C7565223A22222C22706C616365686F6C646572223A6E756C6C2C227374617274506C616365686F6C646572223A6E756C6C2C22656E64506C616365686F6C646572223A6E756C6C2C227769647468223A6E756C6C2C226461746556616C696452756C65223A6E756C6C2C22636B65636B65644461746576616C696454797065223A6E756C6C2C22666F726D6174223A22797979792D4D4D2D64642048483A6D6D3A7373222C22726561646F6E6C79223A66616C73652C226564697461626C65223A66616C73652C22636C65617261626C65223A66616C73652C2273686F77526564223A66616C73652C227265717569726564223A66616C73652C2274797065223A226461746574696D65222C2274696D657374616D70223A747275657D2C2272756C6573223A6E756C6C2C227461626C65436F6C756D6E73223A6E756C6C2C22746162436F6C756D6E73223A6E756C6C2C22636F6C756D6E73223A6E756C6C7D2C7B226E616D65223A22E8AFB7E58187E7B1BBE59E8B222C22636F6E74726F6C54797065223A6E756C6C2C2269636F6E223A2269636F6E2D696E707574222C2274797065223A22696E707574222C226B6579223A22747970655F222C226D6F64656C223A22747970655F222C226F7074696F6E73223A7B227370616E223A31322C2264697361626C6564223A66616C73652C2273686F77223A747275652C2273686F775469746C65223A747275652C22637573746F6D436C617373223A6E756C6C2C22616E6E6F746174696F6E5469746C65223A22E6B7BBE58AA0E689B9E6B3A8222C22616E6E6F746174696F6E436F6E74657874223A22222C226461746154797065223A6E756C6C2C2273686F77416E6E6F746174696F6E223A66616C73652C226461746543616C63756C6174696F6E223A66616C73652C226461746543616C63756C6174696F6E5374617274223A6E756C6C2C226461746543616C63756C6174696F6E456E64223A6E756C6C2C226461746543616C634469666654797065223A6E756C6C2C2264656661756C7456616C7565223A22222C22706C616365686F6C646572223A22222C2273686F77526564223A6E756C6C2C22626F6C64223A66616C73652C22666F6E7453697A65223A302C22636F6C6F72223A6E756C6C2C22746970223A6E756C6C2C2272656D6F746546756E63223A6E756C6C2C22756E6974223A6E756C6C2C2273686F7750617373776F7264223A66616C73652C22637573746F6D5374796C65223A6E756C6C7D2C2272756C6573223A6E756C6C2C227461626C65436F6C756D6E73223A6E756C6C2C22746162436F6C756D6E73223A6E756C6C2C22636F6C756D6E73223A6E756C6C7D2C7B226E616D65223A22E8AFB7E58187E5A4A9E695B0222C22636F6E74726F6C54797065223A6E756C6C2C2269636F6E223A2269636F6E2D696E707574222C2274797065223A22696E707574222C226B6579223A226461795F222C226D6F64656C223A226461795F222C226F7074696F6E73223A7B227370616E223A31322C2264697361626C6564223A66616C73652C2273686F77223A747275652C2273686F775469746C65223A747275652C22637573746F6D436C617373223A6E756C6C2C22616E6E6F746174696F6E5469746C65223A22E6B7BBE58AA0E689B9E6B3A8222C22616E6E6F746174696F6E436F6E74657874223A22222C226461746154797065223A6E756C6C2C2273686F77416E6E6F746174696F6E223A66616C73652C226461746543616C63756C6174696F6E223A66616C73652C226461746543616C63756C6174696F6E5374617274223A6E756C6C2C226461746543616C63756C6174696F6E456E64223A6E756C6C2C226461746543616C634469666654797065223A6E756C6C2C2264656661756C7456616C7565223A22222C22706C616365686F6C646572223A22222C2273686F77526564223A6E756C6C2C22626F6C64223A66616C73652C22666F6E7453697A65223A302C22636F6C6F72223A6E756C6C2C22746970223A6E756C6C2C2272656D6F746546756E63223A6E756C6C2C22756E6974223A6E756C6C2C2273686F7750617373776F7264223A66616C73652C22637573746F6D5374796C65223A6E756C6C7D2C2272756C6573223A6E756C6C2C227461626C65436F6C756D6E73223A6E756C6C2C22746162436F6C756D6E73223A6E756C6C2C22636F6C756D6E73223A6E756C6C7D2C7B226E616D65223A22E4BA8BE794B1222C22636F6E74726F6C54797065223A6E756C6C2C2269636F6E223A2269636F6E2D696E707574222C2274797065223A22696E707574222C226B6579223A22726561736F6E222C226D6F64656C223A22726561736F6E222C226F7074696F6E73223A7B227370616E223A32342C2264697361626C6564223A66616C73652C2273686F77223A747275652C2273686F775469746C65223A747275652C22637573746F6D436C617373223A6E756C6C2C22616E6E6F746174696F6E5469746C65223A22E6B7BBE58AA0E689B9E6B3A8222C22616E6E6F746174696F6E436F6E74657874223A22222C226461746154797065223A6E756C6C2C2273686F77416E6E6F746174696F6E223A66616C73652C226461746543616C63756C6174696F6E223A66616C73652C226461746543616C63756C6174696F6E5374617274223A6E756C6C2C226461746543616C63756C6174696F6E456E64223A6E756C6C2C226461746543616C634469666654797065223A6E756C6C2C2264656661756C7456616C7565223A22222C22706C616365686F6C646572223A22222C2273686F77526564223A6E756C6C2C22626F6C64223A66616C73652C22666F6E7453697A65223A302C22636F6C6F72223A6E756C6C2C22746970223A6E756C6C2C2272656D6F746546756E63223A6E756C6C2C22756E6974223A6E756C6C2C2273686F7750617373776F7264223A66616C73652C22637573746F6D5374796C65223A6E756C6C7D2C2272756C6573223A6E756C6C2C227461626C65436F6C756D6E73223A6E756C6C2C22746162436F6C756D6E73223A6E756C6C2C22636F6C756D6E73223A6E756C6C7D5D2C22636F6E666967223A7B226C6162656C5769647468223A3130302C226C6162656C506F736974696F6E223A227269676874222C2273697A65223A22736D616C6C222C22637573746F6D436C617373223A6E756C6C2C22666F726D4E616D65223A22E8AFB7E58187E6B581E7A88B222C22666F726D4E756D626572223A6E756C6C2C22637573746F6D5374796C65223A6E756C6C2C22637573746F6D4A73223A6E756C6C2C226A73457870616E64223A6E756C6C2C2264697361626C65223A66616C73652C2273686F77416E6E6F746174696F6E223A66616C73657D7D, NULL);
-- ---------------------------- -- Table structure for PANGU_FORM_DEFINITION_HISTORY -- ---------------------------- DROP TABLE IF EXISTS `PANGU_FORM_DEFINITION_HISTORY`; CREATE TABLE `PANGU_FORM_DEFINITION_HISTORY` ( `ID` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '主键', `FORM_ID` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '对应表单ID', `NAME` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表单名称', `FORM_DESC` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '描述', `FORM_HTML` longblob COMMENT '表单定义HTML', `CREATE_USER_ID` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人ID', `CREATE_USER_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人名称', `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', `FORM_KEY` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表单key', `VERSION` int(11) DEFAULT NULL COMMENT '版本', `CLASSIFY` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '表单类型(combine:组合,single:单表)', `PAGE_FORMS` varchar(2000) COLLATE utf8_bin DEFAULT NULL COMMENT '页签表单keys', `PAGE_FORM_JSON` longblob COMMENT '页签表单json', `HIDE_MAIN` tinyint(4) DEFAULT NULL COMMENT '是否隐含主表(1:是,0:否)', PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='流程表单设计历史明细';
DROP TABLE IF EXISTS `PANGU_FORM_DEFINITION`; CREATE TABLE `PANGU_FORM_DEFINITION` ( `ID_` varchar(255) COLLATE utf8_bin NOT NULL, `NAME_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `VERSION_` int(11) DEFAULT NULL, `KEY_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `CATEGORY_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `DEPLOYMENT_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `TENANT_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `RESOURCE_NAME_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `DESCRIPTION_` varchar(255) COLLATE utf8_bin DEFAULT NULL, `SCOPE_TYPE` varchar(255) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID_`) USING BTREE, UNIQUE KEY `ACT_IDX_FORM_DEF_UNIQ` (`KEY_`,`VERSION_`,`TENANT_ID_`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS `pangu_form_custom`; CREATE TABLE `pangu_form_custom` ( `id_` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '主键', `name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '名称', `alias_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '别名', `need_page_` tinyint(1) DEFAULT NULL COMMENT '是否分页', `page_Size` int(11) DEFAULT NULL, `condition_field` blob, `result_field` blob, `sort_field` blob, `ds_alias` varchar(255) COLLATE utf8_bin DEFAULT NULL, `is_Table` tinyint(1) DEFAULT NULL, `sql_Build_Type` int(255) DEFAULT NULL, `ds_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `url` varchar(255) COLLATE utf8_bin DEFAULT NULL, `request_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Size_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `total_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `list_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `IS_DELETED` tinyint(1) DEFAULT '0', PRIMARY KEY (`id_`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of pangu_form_custom -- ---------------------------- BEGIN; INSERT INTO `pangu_form_custom` VALUES ( '测试', 'test', 1, 10, 0x5B7B224C41595F5441424C455F494E444558223A302C226465736372697074696F6E223A226B6579222C2264656661756C7456616C7565223A2220E588ABE5908D222C224C41595F434845434B4544223A747275657D2C7B224C41595F5441424C455F494E444558223A312C226465736372697074696F6E223A2276616C7565222C22636F6C756D6E4B6579223A22222C2264656661756C7456616C7565223A2220E580BC222C224C41595F434845434B4544223A747275657D2C7B224C41595F5441424C455F494E444558223A322C226465736372697074696F6E223A223131222C22636F6C756D6E4B6579223A22222C2264656661756C7456616C7565223A22203232227D5D, 0x5B7B226465736372697074696F6E223A226B6579222C2264656661756C7456616C7565223A2220E588ABE5908D222C224C41595F5441424C455F494E444558223A307D2C7B226465736372697074696F6E223A2276616C7565222C22636F6C756D6E4B6579223A22222C2264656661756C7456616C7565223A2220E580BC222C224C41595F5441424C455F494E444558223A317D5D, NULL, NULL, NULL, NULL, 'restful', 'a1bpmn/api/btn/test', 'GET', 'page', 'pageSize', 'total', 'data', 0);
DROP TABLE IF EXISTS `pangu_form_custom_Dialog`; CREATE TABLE `pangu_form_custom_Dialog` ( `id_` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '主键', `name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '名称', `alias_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '别名', `need_page_` tinyint(1) DEFAULT NULL COMMENT '是否分页', `page_Size` int(11) DEFAULT NULL, `condition_field` blob, `result_field` blob, `sort_field` blob, `ds_alias` varchar(255) COLLATE utf8_bin DEFAULT NULL, `is_Table` tinyint(1) DEFAULT NULL, `sql_Build_Type` int(255) DEFAULT NULL, `ds_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `url` varchar(255) COLLATE utf8_bin DEFAULT NULL, `request_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Size_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `total_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `list_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `IS_DELETED` tinyint(1) DEFAULT '0', `obj_Name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表名', `display_field` blob COMMENT '显示字段', `width_` int(11) DEFAULT NULL, `height_` int(11) DEFAULT NULL, `style_` varchar(10) COLLATE utf8_bin DEFAULT '2' COMMENT '风格,1树,2table', `select_Num` tinyint(1) DEFAULT NULL COMMENT '单选多选:', `system_` tinyint(1) DEFAULT '0', PRIMARY KEY (`id_`) USING BTREE, UNIQUE KEY `pangu_form_custom_Dialog_UNIQ_alias_` (`alias_`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS `pangu_form_custom_Dialog`; CREATE TABLE `pangu_form_custom_Dialog` ( `id_` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '主键', `name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '名称', `alias_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '别名', `need_page_` tinyint(1) DEFAULT NULL COMMENT '是否分页', `page_Size` int(11) DEFAULT NULL, `condition_field` blob, `result_field` blob, `sort_field` blob, `ds_alias` varchar(255) COLLATE utf8_bin DEFAULT NULL, `is_Table` tinyint(1) DEFAULT NULL, `sql_Build_Type` int(255) DEFAULT NULL, `ds_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `url` varchar(255) COLLATE utf8_bin DEFAULT NULL, `request_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Size_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `total_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `list_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `IS_DELETED` tinyint(1) DEFAULT '0', `obj_Name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表名', `display_field` blob COMMENT '显示字段', `width_` int(11) DEFAULT NULL, `height_` int(11) DEFAULT NULL, `style_` varchar(10) COLLATE utf8_bin DEFAULT '2' COMMENT '风格,1树,2table', `select_Num` tinyint(1) DEFAULT NULL COMMENT '单选多选:', `system_` tinyint(1) DEFAULT '0', PRIMARY KEY (`id_`) USING BTREE, UNIQUE KEY `pangu_form_custom_Dialog_UNIQ_alias_` (`alias_`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS `PANGU_FORM_BUSINESS_MODEL`; CREATE TABLE `PANGU_FORM_BUSINESS_MODEL` ( `ID` varchar(64) NOT NULL DEFAULT '' COMMENT '主键id', `DESCRIPTION` varchar(64) COMMENT '描述', `MODEL_NAME` varchar(64) COMMENT '实体名', `TABLE_NAME` varchar(64) COMMENT '表名', `GROUP_ID` varchar(64) COMMENT '分类id', `GROUP_NAME` varchar(64) COMMENT '分类名称', `TYPE` varchar(64) COMMENT '类型', `DS_KEY` varchar(64) COMMENT '数据源Id', `DS_NAME` varchar(64) COMMENT '数据源名称', `CREATE_BY` varchar(64) COMMENT '创建人', `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `UPDATE_BY` varchar(64) COMMENT '更新人', `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', `IS_CREATED_TABLE` bit(1) COMMENT '是否创建表', `IS_EXTERNAL` bit(1) COMMENT '外部', `IS_DELETE` tinyint(255) , PRIMARY KEY (`ID`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='业务对象表';
DROP TABLE IF EXISTS `pangu_form_custom_Dialog`; CREATE TABLE `pangu_form_custom_Dialog` ( `id_` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '主键', `name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '名称', `alias_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '别名', `need_page_` tinyint(1) DEFAULT NULL COMMENT '是否分页', `page_Size` int(11) DEFAULT NULL, `condition_field` blob, `result_field` blob, `sort_field` blob, `ds_alias` varchar(255) COLLATE utf8_bin DEFAULT NULL, `is_Table` tinyint(1) DEFAULT NULL, `sql_Build_Type` int(255) DEFAULT NULL, `ds_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `url` varchar(255) COLLATE utf8_bin DEFAULT NULL, `request_Type` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `page_Size_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `total_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `list_Key` varchar(255) COLLATE utf8_bin DEFAULT NULL, `IS_DELETED` tinyint(1) DEFAULT '0', `obj_Name_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '表名', `display_field` blob COMMENT '显示字段', `width_` int(11) DEFAULT NULL, `height_` int(11) DEFAULT NULL, `style_` varchar(10) COLLATE utf8_bin DEFAULT '2' COMMENT '风格,1树,2table', `select_Num` tinyint(1) DEFAULT NULL COMMENT '单选多选:', `system_` tinyint(1) DEFAULT '0', PRIMARY KEY (`id_`) USING BTREE, UNIQUE KEY `pangu_form_custom_Dialog_UNIQ_alias_` (`alias_`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
3、数据库可能会生成一系列的表主键操作。这里以activiti的主键生成策略为例手写一个自己的主键生成器。
3.1表建立
DROP TABLE IF EXISTS `sys_serialno`;
CREATE TABLE `sys_serialno` (
`ID_` varchar(255) COLLATE utf8_bin NOT NULL COMMENT '主键',
`NAME_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
`ALIAS_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '别名',
`regulation_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '流水号规则',
`gen_type_` decimal(10,0) DEFAULT NULL COMMENT '生成类型',
`no_length_` decimal(10,0) DEFAULT NULL COMMENT '流水号长度',
`cur_date_` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '执行实例id',
`init_value_` decimal(10,0) DEFAULT NULL COMMENT '初始值',
`cur_value_` decimal(10,0) DEFAULT NULL COMMENT '是否成功',
`step_` decimal(10,0) DEFAULT NULL COMMENT '步长',
`DELETED` tinyint(1) DEFAULT NULL COMMENT '是否直接删除',
PRIMARY KEY (`ID_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='流水号';
3.1初始化数据
-- ----------------------------
-- Records of sys_serialno
-- ----------------------------
BEGIN;
INSERT INTO `sys_serialno` VALUES ('1', '每天使用一组流水号', 'dayNo', '{yyyy}{MM}{DD}{NO}', 1, 5, NULL, 1, 0, 2, 0);
INSERT INTO `sys_serialno` VALUES ( 'ccc', 'c', 'c', 1, 1, NULL, 1, 0, 1,
COMMIT;
每天使用一组流水号的策略参考activiti框架。
1、
流水号生成管理说明
/**
* 流水号生成管理
*/
@RestController
@RequestMapping("/sys/serialNo/")
public class SysSerialNoController extends AbstractController {
@Resource
SerialNoManager serialNoManager;
@Value("${pangu.delete}")
private Boolean panguDelete;
@PostMapping(value = "/listJson")
@SysLog("流水号生成列表(分页条件查询)数据")
@ResponseBody
public R listJson(@ApiIgnore @RequestParam Map<String, Object> params, SerialNoQuery serialNoQuery) {
return serialNoManager.listJson(params, serialNoQuery);
}
@PostMapping(value = "/save")
@ResponseBody
@SysLog("保存流水号")
public R save(@RequestBody SerialNo serialNo) {
boolean exist = serialNoManager.isAliasExisted(serialNo.getId(), serialNo.getAlias());
if (!exist) {
if (StringUtils.isNotEmpty(serialNo.getId())) {
serialNo.setCreateTime(DateUtils.getCurrentDate());
serialNo.setUpdateTime(DateUtils.getCurrentDate());
serialNoManager.update(serialNo);
} else {
serialNo.setUpdateTime(DateUtils.getCurrentDate());
serialNo.setId(SnowflakeIdWorkerUtils.getNextId());
serialNoManager.save(serialNo);
}
} else {
return R.error("添加流水号失败,别名【" + serialNo.getAlias() + "】在系统中已存在,不能重复");
}
return R.ok();
}
@SysLog("删除流水号")
@PostMapping("/remove")
@ResponseBody
public R delete(@RequestBody String[] ids) {
if (org.springframework.util.StringUtils.isEmpty(ids)) {
return R.error("没有选中数据");
}
if (panguDelete) {
serialNoManager.batchRemove(Arrays.asList(ids));
return R.ok();
} else {
return R.error("演示模式下无法执行该操作");
}
}
/**
* V2.6.2 版本新增
*
* @param jsonParam
* @return
*/
@PostMapping(value = "/v2/remove")
@SysLog("批量删除")
@ResponseBody
public R removeByIds(@RequestBody JSONObject jsonParam) {
List list = jsonParam.getJSONArray("ids");
if (list.isEmpty()) {
return R.error("没有选中数据");
}
if (panguDelete) {
serialNoManager.batchRemove(list);
return R.ok();
} else {
return R.error("演示模式下无法执行该操作");
}
}
@GetMapping(value = "/get/{id}")
@SysLog("查询流水号")
@ResponseBody
public R get(@PathVariable("id") String id) {
return serialNoManager.getCurById(id);
}
@GetMapping(value = "/previewIden/{alias}")
@SysLog("执行流水号")
@ResponseBody
public R previewIden(@PathVariable("alias") String alias) {
List<SerialNo> previewIden = serialNoManager.getPreviewIden(alias);
return R.ok().put("data", previewIden);
}
}
2、流水号接口以及实现类
public interface SerialNoManager {
/**
* 判读流水号别名是否已经存在
*
* @param id
* id为null 表明是新增的流水号,否则为更新流水号
* @param alias
* @return
*/
boolean isAliasExisted(String id, String alias);
/**
* 根据别名获取当前流水号
*
* @param alias
* @return
*/
public String getCurIdByAlias(String alias);
/**
* 根据别名获取下一个流水号
*
* @param alias
* @return
*/
public String nextId(String alias);
/**
* 根据别名预览前十条流水号
*
* @param alias
* @return
*/
public List<SerialNo> getPreviewIden(String alias);
R listJson(Map<String, Object> params, SerialNoQuery serialNoQuery);
void save(SerialNo serialNo);
void batchRemove(List<String> list);
R getCurById(String id);
void update(SerialNo serialNo);
}
@Service("serialNoManager")
public class SerialNoManagerImpl implements SerialNoManager {
@Autowired
SerialNoDao serialNoDao;
@Override
public boolean isAliasExisted(String id, String alias) {
return serialNoDao.isAliasExisted(id, alias)>0;
}
/**
* 根据流程规则别名获取得当前流水号。
*
* @param alias 流水号规则别名。
* @return
*/
@Override
public String getCurIdByAlias(String alias) {
SerialNo serialNoDaoByAlias = this.serialNoDao.getByAlias(alias);
Integer curValue = serialNoDaoByAlias.getCurValue();
if (curValue == null) {
curValue = serialNoDaoByAlias.getInitValue();
}
return getByRule(serialNoDaoByAlias.getRegulation(), serialNoDaoByAlias.getNoLength(), curValue);
}
/**
* 根据规则返回需要显示的流水号。
*
* @param rule 流水号规则。
* @param length 流水号的长度。
* @param curValue 流水号的当前值。
* @return
*/
private String getByRule(String rule, int length, int curValue) {
Calendar now = Calendar.getInstance();
int month = now.get(Calendar.MONTH) + 1;
int day = now .get(Calendar.DAY_OF_MONTH);
StringBuilder serialNo = new StringBuilder();
int fillLength = length - String.valueOf(curValue).length();
for (int i = 0; i < fillLength; i++) {
serialNo.append("0");
}
serialNo.append(curValue);
return rule.replace("{yyyy}",String.valueOf(now.get(Calendar.YEAR)))
.replace("{MM}", String.valueOf((month < 10) ? "0" + month : "" + month))
.replace("{mm}", String.valueOf(month))
.replace("{DD}", String.valueOf((day < 10) ? "0" + day : "" + day))
.replace("{dd}", String.valueOf(day))
.replace("{NO}", serialNo.toString())
.replace("{no}", String.valueOf(curValue));
}
/**
* 根据流程规则别名获取得下一个流水号。
*
* @param alias 流水号规则别名。
* @return
*/
@Override
public synchronized String nextId(String alias) {
SerialNo serialNoDaoByAlias = serialNoDao.getByAlias(alias);
if (serialNoDaoByAlias == null) {
throw new BusinessException("流水号【" + alias + "】缺失!请联系系统管理员!");
}
Result result = genResult(serialNoDaoByAlias);
int tryTimes = 0;
while (result.getRtn() == 0) {
tryTimes++; // 防止在使用中修改步长,导致死循环
if (tryTimes > 100) {
throw new BusinessException("获取流水号失败! " + serialNoDaoByAlias.getAlias());
}
serialNoDaoByAlias.setCurValue(result.getCurValue());
result = genResult(serialNoDaoByAlias);
}
return result.getIdNo();
}
public Result genResult(SerialNo serialNo) {
String rule = serialNo.getRegulation();
int step = serialNo.getStep();
int genEveryDay = serialNo.getGenType();
//如果失败过一次、使用失败的当前值。没有失败
Integer curValue = serialNo.getCurValue();
if (curValue == 0) {
curValue = serialNo.getInitValue();
}
// 每天都生成
if (genEveryDay == 1) {
String curDate = getCurDate();
String oldDate = serialNo.getCurDate();
if (!curDate.equals(oldDate)) {
serialNo.setCurDate(curDate);
curValue = serialNo.getInitValue();
} else {
curValue = curValue + step;
}
} else {
curValue = curValue + step;
}
serialNo.setNewCurValue(curValue);
int i = 0;
i = serialNoDao.updByAlias(serialNo);
Result result = new Result(0, "", curValue);
if (i > 0) {
String rtn = getByRule(rule, serialNo.getNoLength(), curValue);
result.setIdNo(rtn);
result.setRtn(1);
}
return result;
}
/**
* 返回当前日期。格式为 年月日。
*
* @return
*/
public String getCurDate() {
Date date = new Date();
return DateUtil.format(date, "yyyyMMdd");
}
/**
* 预览时,获取前十个流水号
*
* @param alias
* @return
*/
@Override
public List<SerialNo> getPreviewIden(String alias) {
int genNum = 10;
SerialNo byAlias = serialNoDao.getByAlias(alias);
String rule = byAlias.getRegulation();
int step = byAlias.getStep();
Integer curValue = byAlias.getCurValue();
if (curValue == null) {
curValue = byAlias.getInitValue();
}
List<SerialNo> tempList = new ArrayList<>();
for (int i = 0; i < genNum; i++) {
SerialNo serialNo = new SerialNo();
if (i > 0) {
curValue += step;
}
String rtn = getByRule(rule, byAlias.getNoLength(), curValue);
serialNo.setId(curValue.toString());
serialNo.setCurIdenValue(rtn);
tempList.add(serialNo);
}
return tempList;
}
@Override
public R listJson(Map<String, Object> params, SerialNoQuery serialNoQuery) {
IPage<SerialNo> page = new Query<SerialNo>().getPage(params);
IPage<SerialNo> ucRolesList = serialNoDao.getAllPage(page,params,serialNoQuery);
return R.ok().put(Constants.COUNT, ucRolesList.getTotal()).put(Constants.DATA, ucRolesList.getRecords());
}
@Override
public void save(SerialNo serialNo) {
serialNoDao.save(serialNo);
}
@Override
public void batchRemove(List<String> list) {
serialNoDao.batchRemove(list);
}
@Override
public R getCurById(String id) {
SerialNo serialNo= serialNoDao.getById(id);
return R.ok().put(Constants.DATA,serialNo);
}
@Override
public void update(SerialNo serialNo) {
serialNoDao.update(serialNo);
}
public class Result {
private int rtn = 0;
private String idNo = "";
private int curValue = 0;
public Result(int rtn, String idNo, int curValue) {
this.rtn = rtn;
this.idNo = idNo;
this.setCurValue(curValue);
}
public int getRtn() {
return rtn;
}
public void setRtn(int rtn) {
this.rtn = rtn;
}
public String getIdNo() {
return idNo;
}
public void setIdNo(String idNo) {
this.idNo = idNo;
}
public int getCurValue() {
return curValue;
}
public void setCurValue(int curValue) {
this.curValue = curValue;
}
}
}
3、流水号DAO使用
public interface SerialNoDao {
/**
* 判读流水号别名是否已经存在
*
* @param id
* id为null 表明是新增的流水号,否则为更新流水号
* @param alias
* @return
*/
Integer isAliasExisted(@Param("id") String id, @Param("alias") String alias);
/**
* 根据别名获取流水号数据(数据库锁定了对应的行数据)
*
* @param alias
* @return
*/
SerialNo getByAlias(String alias);
/**
* 根据流程别名 。
*
* @param SerialNo
* void
*/
int updByAlias(SerialNo serialNo);
/**
*
* @param page
* @param params
* @param serialNoQuery
* @return
*/
IPage<SerialNo> getAllPage(IPage<SerialNo> page, @Param("params") Map<String, Object> params, @Param("serialNoQuery") SerialNoQuery serialNoQuery);
void save(SerialNo serialNo);
void batchRemove(@Param("list")List<String> list);
SerialNo getById(String id);
void update(SerialNo serialNo);
}
4、对应的xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pangubpm.modules.data.dao.SerialNoDao">
<resultMap id="SerialNo" type="SerialNo">
<id property="id" column="id_" jdbcType="VARCHAR"/>
<result property="name" column="name_" jdbcType="VARCHAR"/>
<result property="alias" column="alias_" jdbcType="VARCHAR"/>
<result property="regulation" column="regulation_" jdbcType="VARCHAR"/>
<result property="genType" column="gen_type_" jdbcType="NUMERIC"/>
<result property="noLength" column="no_length_" jdbcType="NUMERIC"/>
<result property="curDate" column="cur_date_" jdbcType="VARCHAR"/>
<result property="initValue" column="init_value_" jdbcType="NUMERIC"/>
<result property="curValue" column="cur_value_" jdbcType="NUMERIC"/>
<result property="step" column="step_" jdbcType="NUMERIC"/>
<result column="DELETED" jdbcType="BOOLEAN" property="deleted" />
</resultMap>
<sql id="columns">
id_,name_,alias_,regulation_,gen_type_,no_length_,cur_date_,init_value_,cur_value_,step_
</sql>
<update id="batchRemove" parameterType="java.util.List">
update sys_serialno
set
deleted=1
where id_ in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item}
</foreach>
</update>
<select id="getAllPage" parameterType="Map" resultMap="SerialNo">
SELECT * FROM sys_serialno
WHERE
DELETED=0
<if test="serialNoQuery.name!=null and serialNoQuery.name!='' ">
and name_ like CONCAT('%', #{serialNoQuery.name,jdbcType=VARCHAR},'%')
</if>
<if test="serialNoQuery.alias!=null and serialNoQuery.alias!='' ">
and alias_ like CONCAT('%', #{serialNoQuery.alias,jdbcType=VARCHAR},'%')
</if>
order by UPDATE_TIME desc
</select>
<update id="update" parameterType="com.pangubpm.modules.data.entity.SerialNo">
UPDATE sys_serialno SET
name_=#{name,jdbcType=VARCHAR},
alias_=#{alias,jdbcType=VARCHAR},
regulation_=#{regulation,jdbcType=VARCHAR},
gen_type_=#{genType,jdbcType=NUMERIC},
no_length_=#{noLength,jdbcType=NUMERIC},
cur_date_=#{curDate,jdbcType=VARCHAR},
init_value_=#{initValue,jdbcType=NUMERIC},
cur_value_=#{curValue,jdbcType=NUMERIC},
step_=#{step,jdbcType=NUMERIC},
UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP}
WHERE
id_=#{id}
</update>
<update id="updByAlias" parameterType="com.pangubpm.modules.data.entity.SerialNo">
UPDATE sys_serialno SET
cur_date_=#{curDate,jdbcType=VARCHAR},
cur_value_=#{newCurValue,jdbcType=NUMERIC},
UPDATE_TIME=#{updateTime,jdbcType=TIMESTAMP}
WHERE alias_=#{alias,jdbcType=VARCHAR}
and cur_value_=#{curValue,jdbcType=NUMERIC}
</update>
<select id="isAliasExisted" resultType="java.lang.Integer">
select count(*) from sys_serialno where alias_=#{alias}
<if test="id!=null">AND id_ !=#{id}</if>
</select>
<select id="getByAlias" parameterType="String" resultMap="SerialNo">
SELECT
<include refid="columns"/>
FROM sys_serialno
WHERE
alias_=#{alias}
</select>
<select id="getById" parameterType="String" resultMap="SerialNo">
SELECT
<include refid="columns"/>
FROM sys_serialno
WHERE
id_=#{id}
</select>
<insert id="save" parameterType="com.pangubpm.modules.data.entity.SerialNo">
insert into sys_serialno
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
ID_,
</if>
<if test="name != null">
NAME_,
</if>
<if test="alias != null">
ALIAS_,
</if>
<if test="regulation != null">
regulation_,
</if>
<if test="genType != null">
gen_type_,
</if>
<if test="noLength != null">
no_length_,
</if>
<if test="curDate != null">
cur_date_,
</if>
<if test="initValue != null">
init_value_,
</if>
<if test="curValue != null">
cur_value_,
</if>
<if test="step != null">
step_,
</if>
<if test="deleted != null">
DELETED,
</if>
<if test="updateTime != null">
UPDATE_TIME ,
</if>
<if test="createTime!= null">
CREATE_TIME ,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=VARCHAR},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="alias != null">
#{alias,jdbcType=VARCHAR},
</if>
<if test="regulation != null">
#{regulation,jdbcType=VARCHAR},
</if>
<if test="genType != null">
#{genType,jdbcType=DECIMAL},
</if>
<if test="noLength != null">
#{noLength,jdbcType=NUMERIC},
</if>
<if test="curDate != null">
#{curDate,jdbcType=VARCHAR},
</if>
<if test="initValue != null">
#{initValue,jdbcType=NUMERIC},
</if>
<if test="curValue != null">
#{curValue,jdbcType=NUMERIC},
</if>
<if test="step != null">
#{step,jdbcType=NUMERIC},
</if>
<if test="deleted != null">
#{deleted,jdbcType=BOOLEAN},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="createTime!= null">
#{createTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
</mapper>
5、涉及到的枚举定义
public enum PanGuColumnType {
/**
* 大文本
*/
CLOB("clob", "大文本", new String[] { "text", "clob", "blob", "mediumblob", "mediumtext", "longblob", "longtext" }),
/**
* 数字型
*/
NUMBER("number", "数字型", new String[] { "bit","tinyint", "number", "smallint", "mediumint", "int", "integer", "bigint", "float", "double", "decimal", "numeric" }),
/**
* 字符串
*/
VARCHAR("varchar", "字符串", new String[] { "varchar", "varchar2", "char", "tinyblob", "tinytext" }),
/**
* 日期型
*/
DATE("date", "日期型", new String[] { "date", "time", "year", "datetime", "timestamp" });
public static JSONArray toJson(){
JSONArray jsonArray = new JSONArray();
for (PanGuColumnType e : PanGuColumnType.values()) {
JSONObject object = new JSONObject();
object.put("key", e.getKey());
object.put("desc", e.getDesc());
object.put("supports", e.getSupports());
jsonArray.add(object);
}
return jsonArray;
}
private String key;
private String desc;
private String[] supports;
private PanGuColumnType(String key, String desc, String[] supports) {
this.key = key;
this.desc = desc;
this.supports = supports;
}
public String getKey() {
return key;
}
public String getDesc() {
return desc;
}
public String[] getSupports() {
return supports;
}
/**
* <pre>
* 根据key来判断是否跟当前一致
* </pre>
*
* @param key
* @return
*/
public boolean equalsWithKey(String key) {
return this.key.equals(key);
}
public static PanGuColumnType getByKey(String key) {
for (PanGuColumnType type : PanGuColumnType.values()) {
if (type.getKey().equals(key)) {
return type;
}
}
return null;
}
/**
* <pre>
* 根据数据库的字段类型获取type
* 无视大小写
* </pre>
*
* @param dbDataType
* 数据库的字段类型
* @return
*/
public static PanGuColumnType getByDbDataType(String dbDataType, String errMsgApp) {
for (PanGuColumnType type : PanGuColumnType.values()) {
for (String support : Arrays.asList(type.supports)) {
if (dbDataType.toLowerCase().contains(support.toLowerCase())) {
return type;
}
}
}
throw new BusinessException(errMsgApp);
}
public static PanGuColumnType getByDbDataType(String dbDataType) {
return getByDbDataType(dbDataType, "");
}
}