Mybatis resultMap discriminator(鉴定器) 实操

文章目录

• • • 实体对象
    • Mapper 文件配置
    • 测试用例
    • 接口调用响应
    • 数据库数据
    • 数据库表结构

实体对象

@Data
public class SysUser {private Long id;private String userName;/*** 1： 男* 2： 女* */private Integer sex;/*** 1： 小学* 2： 初中* 3： 高中* 4： 大学* 5： 研究生* 6： 博士* 7： 博士后* */private Integer education;private String address;private String phone;/*** 根据性别不同, 查询的表不同, mapper sql 映射文件中使用的是 discriminator* */private List<Pairs> pairs;/*** 学历为 高中 的才有, 需要使用 discriminator(鉴别器), 目前 mapper sql 映射文件中使用的是 association* */private List<SysCourse> courses;/*** 0： 启用* 1： 禁用* 9： 删除* */private Integer status;
}

@Data
public class SysCourse {private Long id;private String courseName;
}

@Data
public class Pairs {private Long id;private String name;private String value;
}

Mapper 文件配置

<sql id="SysUserColumns">${prefix}.id, ${prefix}.user_name, ${prefix}.sex, ${prefix}.education, ${prefix}.address, ${prefix}.phone, ${prefix}.status
sql><resultMap id="UsersMap" type="com.answer.ai.entity.SysUser" autoMapping="true"><id column="id" property="id" jdbcType="BIGINT" /><association property="courses" select="findCourse" column="{userId=id, status=course_status}" /><discriminator javaType="int" column="sex"><case value="1"><association property="pairs" select="findMensInfo" column="id" />case><case value="2"><association property="pairs" select="findWomensInfo" column="id" />case>discriminator>
resultMap><select id="findUsers" resultMap="UsersMap">SELECT<include refid="SysUserColumns"><property name="prefix" value="su" />include> , 1 AS course_statusFROM ai_sys_user suWHERE su.status = 1
select><select id="findMensInfo" resultType="com.answer.ai.entity.Pairs">SELECT id, name, valueFROM ai_sys_menWHERE user_id = #{userId}
select><select id="findWomensInfo" resultType="com.answer.ai.entity.Pairs">SELECT id, name, valueFROM ai_sys_womenWHERE user_id = #{userId}
select><select id="findCourse" resultType="com.answer.ai.entity.SysCourse">SELECT id, course_nameFROM ai_sys_courseWHERE user_id = #{userId} AND status = #{status}
select>

注意： 一个 resultMap 标签中无法使用两个 discriminator(鉴定器) 标签

测试用例

@Test
public void doJob() {List<SysUser> users = userService.findUsers();System.out.println();users.forEach(e -> System.out.println(JSON.toJSONString(e) + "\n"));System.out.println();
}

程序运行结果

{"address":"pt","courses":[{"courseName":"数学","id":2},{"courseName":"物理","id":4},{"courseName":"化学","id":5},{"courseName":"生物","id":6}],"education":3,"id":1,"pairs":[{"id":1,"name":"学历","value":"本科"},{"id":2,"name":"职业","value":"程序猿"},{"id":3,"name":"居住地","value":"深圳"}],"phone":"135****4327","sex":1,"status":1,"userName":"answer"}{"address":"as","courses":[{"courseName":"高数","id":13}],"education":5,"id":2,"pairs":[{"id":4,"name":"身高","value":"175"},{"id":5,"name":"爱好","value":"篮球"}],"phone":"135****2011","sex":1,"status":1,"userName":"zhangsan"}{"address":"st","courses":[{"courseName":"语文","id":7},{"courseName":"英语","id":9},{"courseName":"历史","id":10},{"courseName":"地理","id":11},{"courseName":"政治","id":12}],"education":3,"id":3,"pairs":[{"id":1,"name":"身高","value":"170"},{"id":2,"name":"体重","value":"45"}],"phone":"136****5142","sex":2,"status":1,"userName":"linxue"}

接口调用响应

{"code": 10000,"desc": "success","data": [{"id": 1,"userName": "answer","sex": 1,"education": 3,"address": "pt","phone": "135****4327","pairs": [{"id": 1,"name": "学历","value": "本科"},{"id": 2,"name": "职业","value": "程序猿"},{"id": 3,"name": "居住地","value": "深圳"}],"courses": [{"id": 2,"courseName": "数学"},{"id": 4,"courseName": "物理"},{"id": 5,"courseName": "化学"},{"id": 6,"courseName": "生物"}],"status": 1},{"id": 2,"userName": "zhangsan","sex": 1,"education": 5,"address": "as","phone": "135****2011","pairs": [{"id": 4,"name": "身高","value": "175"},{"id": 5,"name": "爱好","value": "篮球"}],"courses": [{"id": 13,"courseName": "高数"}],"status": 1},{"id": 3,"userName": "linxue","sex": 2,"education": 3,"address": "st","phone": "136****5142","pairs": [{"id": 1,"name": "身高","value": "170"},{"id": 2,"name": "体重","value": "45"}],"courses": [{"id": 7,"courseName": "语文"},{"id": 9,"courseName": "英语"},{"id": 10,"courseName": "历史"},{"id": 11,"courseName": "地理"},{"id": 12,"courseName": "政治"}],"status": 1}]
}

数据库数据

mysql> select * from ai_sys_user;
+----+-----------+-----+-----------+---------+-------------+--------+
| id | user_name | sex | education | address | phone       | status |
+----+-----------+-----+-----------+---------+-------------+--------+
|  1 | answer    |   1 |         3 | pt      | 135****4327 |      1 |
|  2 | zhangsan  |   1 |         5 | as      | 135****2011 |      1 |
|  3 | linxue    |   2 |         3 | st      | 136****5142 |      1 |
+----+-----------+-----+-----------+---------+-------------+--------+mysql> select * from ai_sys_men;
+----+---------+--------+--------+
| id | user_id | name   | value  |
+----+---------+--------+--------+
|  1 |       1 | 学历   | 本科   |
|  2 |       1 | 职业   | 程序猿 |
|  3 |       1 | 居住地 | 深圳   |
|  4 |       2 | 身高   | 175    |
|  5 |       2 | 爱好   | 篮球   |
+----+---------+--------+--------+mysql> select * from ai_sys_women;
+----+---------+------+-------+
| id | user_id | name | value |
+----+---------+------+-------+
|  1 |       3 | 身高 | 170   |
|  2 |       3 | 体重 | 45    |
+----+---------+------+-------+mysql> select * from ai_sys_course;
+----+-------------+---------+--------+
| id | course_name | user_id | status |
+----+-------------+---------+--------+
|  1 | 语文        |       1 |      0 |
|  2 | 数学        |       1 |      1 |
|  3 | 英语        |       1 |      0 |
|  4 | 物理        |       1 |      1 |
|  5 | 化学        |       1 |      1 |
|  6 | 生物        |       1 |      1 |
|  7 | 语文        |       3 |      1 |
|  8 | 数学        |       3 |      0 |
|  9 | 英语        |       3 |      1 |
| 10 | 历史        |       3 |      1 |
| 11 | 地理        |       3 |      1 |
| 12 | 政治        |       3 |      1 |
| 13 | 高数        |       2 |      1 |
+----+-------------+---------+--------+

数据库表结构

/*
Navicat MySQL Data TransferSource Server         : localhost
Source Server Version : 50722
Source Host           : 127.0.0.1:3306
Source Database       : dc_config_dbTarget Server Type    : MYSQL
Target Server Version : 50722
File Encoding         : 65001Date: 2019-07-05 17:11:18
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `ai_sys_course`
-- ----------------------------
DROP TABLE IF EXISTS `ai_sys_course`;
CREATE TABLE `ai_sys_course` (`id` bigint(18) NOT NULL AUTO_INCREMENT,`course_name` varchar(25) NOT NULL,`user_id` bigint(18) NOT NULL,`status` tinyint(1) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of ai_sys_course
-- ----------------------------
INSERT INTO `ai_sys_course` VALUES ('1', '语文', '1', '0');
INSERT INTO `ai_sys_course` VALUES ('2', '数学', '1', '1');
INSERT INTO `ai_sys_course` VALUES ('3', '英语', '1', '0');
INSERT INTO `ai_sys_course` VALUES ('4', '物理', '1', '1');
INSERT INTO `ai_sys_course` VALUES ('5', '化学', '1', '1');
INSERT INTO `ai_sys_course` VALUES ('6', '生物', '1', '1');
INSERT INTO `ai_sys_course` VALUES ('7', '语文', '3', '1');
INSERT INTO `ai_sys_course` VALUES ('8', '数学', '3', '0');
INSERT INTO `ai_sys_course` VALUES ('9', '英语', '3', '1');
INSERT INTO `ai_sys_course` VALUES ('10', '历史', '3', '1');
INSERT INTO `ai_sys_course` VALUES ('11', '地理', '3', '1');
INSERT INTO `ai_sys_course` VALUES ('12', '政治', '3', '1');
INSERT INTO `ai_sys_course` VALUES ('13', '高数', '2', '1');-- ----------------------------
-- Table structure for `ai_sys_men`
-- ----------------------------
DROP TABLE IF EXISTS `ai_sys_men`;
CREATE TABLE `ai_sys_men` (`id` bigint(18) NOT NULL AUTO_INCREMENT,`user_id` bigint(18) NOT NULL,`name` varchar(25) DEFAULT NULL,`value` varchar(25) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of ai_sys_men
-- ----------------------------
INSERT INTO `ai_sys_men` VALUES ('1', '1', '学历', '本科');
INSERT INTO `ai_sys_men` VALUES ('2', '1', '职业', '程序猿');
INSERT INTO `ai_sys_men` VALUES ('3', '1', '居住地', '深圳');
INSERT INTO `ai_sys_men` VALUES ('4', '2', '身高', '175');
INSERT INTO `ai_sys_men` VALUES ('5', '2', '爱好', '篮球');-- ----------------------------
-- Table structure for `ai_sys_user`
-- ----------------------------
DROP TABLE IF EXISTS `ai_sys_user`;
CREATE TABLE `ai_sys_user` (`id` bigint(18) NOT NULL AUTO_INCREMENT,`user_name` varchar(55) NOT NULL,`sex` tinyint(1) NOT NULL COMMENT '1： 男\r\n2： 女',`education` tinyint(4) DEFAULT NULL COMMENT '1： 小学\r\n2： 初中\r\n3： 高中\r\n4： 大学\r\n5： 研究生\r\n6： 博士\r\n7： 博士后',`address` varchar(55) DEFAULT NULL,`phone` varchar(20) DEFAULT NULL,`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0： 启用\r\n1： 禁用\r\n9： 删除',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of ai_sys_user
-- ----------------------------
INSERT INTO `ai_sys_user` VALUES ('1', 'answer', '1', '3', 'pt', '135****4327', '1');
INSERT INTO `ai_sys_user` VALUES ('2', 'zhangsan', '1', '5', 'as', '135****2011', '1');
INSERT INTO `ai_sys_user` VALUES ('3', 'linxue', '2', '3', 'st', '136****5142', '1');-- ----------------------------
-- Table structure for `ai_sys_women`
-- ----------------------------
DROP TABLE IF EXISTS `ai_sys_women`;
CREATE TABLE `ai_sys_women` (`id` bigint(18) NOT NULL AUTO_INCREMENT,`user_id` bigint(18) NOT NULL,`name` varchar(25) DEFAULT NULL,`value` varchar(25) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;-- ----------------------------
-- Records of ai_sys_women
-- ----------------------------
INSERT INTO `ai_sys_women` VALUES ('1', '3', '身高', '170');
INSERT INTO `ai_sys_women` VALUES ('2', '3', '体重', '45');

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！