Mybatis resultMap用法之系统数据字典实现

本文主要是介绍Mybatis resultMap用法之系统数据字典实现，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

实体对象

字典实体

@Data
@Alias("Dict")
public class SysDict {// 字典代码private String dictCode;// 字典名称private String dictName;// 0系统 9解析private Integer dictType;// 字典项列表private List<SysDictItem> dictItems;
}

字典项实体

@Data
@Alias("DictItem")
public class SysDictItem {// 字典子项代码private String dictItemCode;// 字典子项展示值private String dictItemValue;// 字典子项详细描述private String dictItemDesc;// 自定义json字符串属性private Map<String, Object> itemAttrs;// 将数据库里json字符串转为map对象public void setItemAttrs(String itemAttrs) {if (!StringUtils.isEmpty(itemAttrs)) {// json字符串转mapthis.itemAttrs = MapperUtils.jsonToMap(itemAttrs);}}
}

Mapper文件

<?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.jaemon.app.mapper.SysDictMapper"><resultMap id="DictResultMap" type="Dict"><result column="dict_code" property="dictCode" jdbcType="VARCHAR" /><result column="dict_name" property="dictName" jdbcType="VARCHAR" /><collection property="dictItems" ofType="DictItem" select="queryDictById" column="{dictId=id}" /></resultMap><select id="queryAllDict" resultMap="DictResultMap">select sd.id, sd.dict_code, sd.dict_namefrom sys_dict sdwhere sd.dict_status = 1</select><select id="queryDictById" resultType="DictItem">select sdi.dict_item_code, sdi.dict_item_value, sdi.dict_item_desc, sdi.item_attrsfrom sys_dict_item sdiwhere sdi.item_status = 1 and sdi.dict_id = #{dictId}</select>
</mapper>

resultMap标签具体属性含义解释参见： Mybatis resultMap用法之系统菜单实现

输出案例

{"code": 0,"msg": "成功","result": [{"dictCode": "houseStructure","dictName": "房屋结构","dictType": null,"dictItems": [{"dictItemCode": "1","dictItemValue": "钢筋混凝土结构","dictItemDesc": "钢筋混凝土结构","itemAttrs": {"left": "(","right": ")"}},{"dictItemCode": "2","dictItemValue": "砖混结构","dictItemDesc": "砖混结构","itemAttrs": null},{"dictItemCode": "3","dictItemValue": "砖木结构","dictItemDesc": "砖木结构","itemAttrs": null},{"dictItemCode": "4","dictItemValue": "简易结构","dictItemDesc": "简易结构","itemAttrs": null}]}]
}

数据库

表结构

DROP TABLE IF EXISTS `sys_dict`;
CREATE TABLE `sys_dict`  (`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '字典ID',`dict_code` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典代码',`dict_name` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典名称',`dict_status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '字典状态\r\n0： 停用\r\n1： 启用',`dict_type` tinyint(4) NULL DEFAULT 0 COMMENT '字典类型(0-系统字典 5-公共 9-解析字典)',PRIMARY KEY (`id`) USING BTREE,UNIQUE INDEX `idx`(`dict_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 70 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统-字典表' ROW_FORMAT = Dynamic;DROP TABLE IF EXISTS `sys_dict_item`;
CREATE TABLE `sys_dict_item`  (`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '字典子项id',`dict_id` bigint(18) NOT NULL COMMENT '字典ID',`dict_item_code` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典子项代码',`dict_item_value` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典子项展示值',`dict_item_desc` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '字典子项描述',`item_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '字典子项状态\r\n0： 停用\r\n1： 启用',`item_attrs` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自定义json字符串属性',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 283 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统-字典子项表' ROW_FORMAT = Dynamic;

表数据

INSERT INTO `sys_dict` VALUES (1, 'houseStructure', '房屋结构', 1, 5);INSERT INTO `sys_dict_item` VALUES (1, 1, '1', '钢筋混凝土结构', '钢筋混凝土结构', 1, '{\"left\": \"(\", \"right\": \")\"}');
INSERT INTO `sys_dict_item` VALUES (2, 1, '2', '砖混结构', '砖混结构', 1, NULL);
INSERT INTO `sys_dict_item` VALUES (3, 1, '3', '砖木结构', '砖木结构', 1, NULL);
INSERT INTO `sys_dict_item` VALUES (4, 1, '4', '简易结构', '简易结构', 1, NULL);

Mapper写法2

	<resultMap id="DictResultMap" type="Dict"><result column="dict_code" property="dictCode" jdbcType="VARCHAR"/><result column="dict_name" property="dictName" jdbcType="VARCHAR"/><result column="dict_type" property="dictType" jdbcType="TINYINT"/><collection property="dictItems" resultMap="DictItemsMap"/></resultMap><resultMap id="DictItemsMap" type="DictItem"><result column="dict_item_code" property="dictItemCode" jdbcType="VARCHAR"/><result column="dict_item_value" property="dictItemValue" jdbcType="VARCHAR"/><result column="dict_item_desc" property="dictItemDesc" jdbcType="VARCHAR"/></resultMap><select id="findDicts" resultMap="DictResultMap">SELECTdd.dict_code, dd.dict_name, dd.dict_type,ddi.dict_item_code, ddi.dict_item_value, ddi.dict_item_descFROM sys_dict ddLEFT JOIN sys_dict_item ddi ON dd.id = ddi.dict_idWHERE dd.dict_status = 1 AND ddi.item_status = 1<if test="dictCode != null">AND dd.dict_code IN<foreach collection="dictCodes" item="item" index="index" open="(" separator="," close=")">#{item}</foreach></if>ORDER BY dd.id, ddi.id</select>