Mybatis从入门到精通——鉴别器使用（16）

一、鉴别器

Mybatis的鉴别器（discriminator）是ResultMap的子标签，它提供了可以根据不同结果信息执行不同查询操作，只能用于分步查询中。

二、使用说明

1.查询结果集：

<resultMap id="testResultMap" type="xxx" extends="xxx">

    <id property="" column="" />

    <result property="" column=""/>

    <discriminator javaType="需要进行鉴别参数类型" column="需要进行鉴别的列名">

        <case value="值1" resultMap="AResultMap（使用该结果集的resultMap进行分步查询）"></case>

        <case value="值2" resultMap="BResultMap（使用该结果集的resultMap进行分步查询）"></case>

    </discriminator>

</resultMap>

2.分步查询结果集（必须继承查询结果集，且type要和查询结果集一致）：

结果集1：

<resultMap id="AResultMap"  type="domai.User" extends="testResultMap">

    <collection property="xxx" column="传递查询结果的列名" select="xxx方法">     

    </collection>

</resultMap>

结果集2：

<resultMap id="BResultMap"  type="domai.User" extends="testResultMap">

    <collection property="xxx" column="传递查询结果的列名" select="xxx方法">     

    </collection>

</resultMap>

注意：

    1.鉴别器除了需要使用discriminator标签进行判断之外，其它的都和分步查询类似。

    2.分步查询结果集必须继承查询结果集，且type属性值要和查询结果集一致。

 

三、表关系说明

user表结构和数据：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(60) DEFAULT NULL COMMENT '用户名称',
  `real_name` varchar(60) DEFAULT NULL COMMENT '真实名称',
  `sex` char(1) NOT NULL DEFAULT '1' COMMENT '性别',
  `mobile` varchar(20) DEFAULT NULL COMMENT '电话',
  `email` varchar(60) DEFAULT NULL COMMENT '邮箱',
  `note` varchar(200) DEFAULT NULL COMMENT '备注',
  `position_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_4` (`position_id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', 'lison', '李小宇', '1', '18232344223', 'lison@qq.com', 'lison的备注', '1');
INSERT INTO `user` VALUES ('2', 'james', '陈大雷', '1', '18454656125', 'james@qq.com', 'james的备注', '2');
INSERT INTO `user` VALUES ('3', 'cindy', '王美丽', '0', '14556656512', 'xxoo@163.com', 'cindy\'s note', '1');
INSERT INTO `user` VALUES ('126', 'mark', '毛毛', '0', '18635457815', 'xxoo@163.com', 'mark\'s note', '1');

health_repormale表结构和数据（男性体检报告表）：

DROP TABLE IF EXISTS `health_repormale`;
CREATE TABLE `health_repormale` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `check_project` varchar(50) DEFAULT NULL,
  `detail` varchar(100) DEFAULT NULL,
  `user_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `health_repormale` VALUES ('1', '男人项目1', '达标', '1');
INSERT INTO `health_repormale` VALUES ('2', '男人项目2', '达标', '1');
INSERT INTO `health_repormale` VALUES ('3', '男人项目3', '达标', '1');

health_reporfemale表结构和数据（女性体检报告表）：

DROP TABLE IF EXISTS `health_reporfemale`;
CREATE TABLE `health_reporfemale` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `item` varchar(50) DEFAULT NULL,
  `score` decimal(10,2) DEFAULT NULL,
  `user_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `health_reporfemale` VALUES ('1', '女生项目1', '80.00', '3');
INSERT INTO `health_reporfemale` VALUES ('2', '女生项目2', '60.00', '3');
INSERT INTO `health_reporfemale` VALUES ('3', '女生项目3', '90.00', '3');

三、案例

案例说明：体检报告分为男性和女性，当用户的sex为1时查询男性体检报告表并返回结果，为用户的sex为0时查询女性的体检报告表并返回结果。

mapper映射文件：

UserMapper.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.my.mapper.UserMapper">

  <resultMap id="BaseResultMap" type="com.my.entity.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="real_name" jdbcType="VARCHAR" property="realName" />
    <result column="sex" jdbcType="CHAR" property="sex" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="note" jdbcType="VARCHAR" property="note" />
    <result column="position_id" jdbcType="INTEGER" property="positionId" />
  </resultMap>

  <sql id="Base_Column_List">
    id, user_name, real_name, sex, mobile, email, note, position_id
  </sql>

  <resultMap id="UserAndHealthReportsResultMap" type="com.my.entity.User" extends="BaseResultMap">
    <!--  鉴别器使用  -->
    <discriminator javaType="String" column="sex">
      <case value="1" resultMap="HealthReportMaleResultMap"></case>
      <case value="0" resultMap="HealthReportFemale"></case>
    </discriminator>
  </resultMap>

  <!-- 鉴别器分步查询1，当sex的值为1时使用 -->
  <resultMap id="HealthReportMaleResultMap" type="com.my.entity.User" extends="UserAndHealthReportsResultMap">
    <collection property="healthReports" column="id" select="com.my.mapper.HealthReportMaleMapper.selectByUserId"></collection>
  </resultMap>

  <!-- 鉴别器分步查询2，当sex的值为0时使用 -->
  <resultMap id="HealthReportFemale" type="com.my.entity.User" extends="UserAndHealthReportsResultMap">
    <collection property="healthReports" column="id" select="com.my.mapper.HealthReportFemaleMapper.selectByUserId"></collection>
  </resultMap>

  <select id="selectUserAndHealthReportsById" parameterType="Integer" resultMap="UserAndHealthReportsResultMap">
    select
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>

</mapper>

HealthReportMaleMapper.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.my.mapper.HealthReportMaleMapper">
  <resultMap id="BaseResultMap" type="com.my.entity.HealthReportMale">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="check_project" jdbcType="VARCHAR" property="checkProject" />
    <result column="detail" jdbcType="VARCHAR" property="detail" />
    <result column="user_id" jdbcType="INTEGER" property="userId" />
  </resultMap>

    <sql id="Base_Column_List">
    id, check_project, detail, user_id
  </sql>

    <select id="selectByUserId" parameterType="int" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"></include>
        from
        health_repormale
        <where>
            user_id = #{userId}
        </where>
    </select>
</mapper>

HealthReportFemaleMapper.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.my.mapper.HealthReportFemaleMapper">
  <resultMap id="BaseResultMap" type="com.my.entity.HealthReportFemale">
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="item" jdbcType="VARCHAR" property="item" />
    <result column="score" jdbcType="DECIMAL" property="score" />
    <result column="user_id" jdbcType="INTEGER" property="userId" />
  </resultMap>


  <sql id="Base_Column_List">
    id, item, score, user_id
  </sql>

  <select id="selectByUserId" parameterType="int" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"></include>
    from
    health_reporfemale
    <where>
      user_id = #{userId}
    </where>
  </select>

</mapper>

User类属性：

public class User {
    private Integer id;

    private String userName;

    private String realName;

    private String sex;

    private String mobile;

    private String email;

    private String note;

    private Integer positionId;

    private List<HealthReport>  healthReports;


}

HealthReport类：

public abstract class HealthReport {
	
	private int id;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}
	
	public abstract String getDescription();
	
}

HealthReportFemale类：

public class HealthReportFemale extends HealthReport{

    private String item;

    private Double score;

    private Integer userId;


    @Override
    public String getDescription() {
        String rank = score >= 60 ? "达标" : "不达标";
        return "检查项目："+item+",成绩："+rank;
    }

}

HealthReportMale类：

public class HealthReportMale extends HealthReport{

    private String checkProject;

    private String detail;

    private Integer userId;

    @Override
    public String getDescription() {
        return "检查项目："+checkProject+",成绩："+detail;
    }

}

说明：为了阅读方便，以上几个实体类省略了一些get/set和toString方法。

测试代码：

public class MybatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        //1.使用mybatis的工具读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //2.创建sqlSessionFactory
        sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        inputStream.close();
    }

    /**
     * 测试鉴别器
     */
    @Test
    public void testDiscriminator() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserAndHealthReportsById(1);
        System.out.println(user);
        User user1 = userMapper.selectUserAndHealthReportsById(3);
        System.out.println(user1);
    }
    
}