MyBatis子查询

 

一、父查询BaseChildResultMap：

<?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.dayhr.web.module.hr.td.elearn.mapper.TrainerMapper" >
    
    <!-- 表中字段 -->
      <resultMap id="BaseResultMap" type="com.dayhr.web.module.hr.td.elearn.model.Trainer" >
        <id column="id" property="id" jdbcType="CHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="title" property="title" jdbcType="VARCHAR" />
        <result column="recommend" property="recommend" jdbcType="INTEGER" />
        <result column="trainer_type_id" property="trainerTypeId" jdbcType="CHAR" />
        <result column="phone" property="phone" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="address" property="address" jdbcType="VARCHAR" />
        <result column="pro_field" property="proField" jdbcType="VARCHAR" />
        <result column="intro" property="intro" jdbcType="VARCHAR" />
        <result column="head_img" property="headImg" jdbcType="VARCHAR" />
        <result column="labels" property="labels" jdbcType="VARCHAR" />
        <result column="corp_id" property="corpId" jdbcType="INTEGER" />
        <result column="creater_id" property="createrId" jdbcType="INTEGER" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <result column="modifier_id" property="modifierId" jdbcType="INTEGER" />
        <result column="modify_time" property="modifyTime" jdbcType="TIMESTAMP" />
      </resultMap>
      
      <!-- 返回页面Bean -->
      <resultMap id="BaseResultRespMap" type="com.dayhr.web.module.hr.td.elearn.response.TrainerResp" >
        <id column="id" property="id" jdbcType="CHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="title" property="title" jdbcType="VARCHAR" />
        <result column="recommend" property="recommend" jdbcType="INTEGER" />
        <result column="trainer_type_id" property="trainerTypeId" jdbcType="CHAR" />
        <result column="phone" property="phone" jdbcType="VARCHAR" />
        <result column="email" property="email" jdbcType="VARCHAR" />
        <result column="address" property="address" jdbcType="VARCHAR" />
        <result column="pro_field" property="proField" jdbcType="VARCHAR" />
        <result column="intro" property="intro" jdbcType="VARCHAR" />
        <result column="head_img" property="headImg" jdbcType="VARCHAR" />
        <result column="labels" property="labels" jdbcType="VARCHAR" />
        <result column="corp_id" property="corpId" jdbcType="INTEGER" />
        <result column="creater_id" property="createrId" jdbcType="INTEGER" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <result column="modifier_id" property="modifierId" jdbcType="INTEGER" />
        <result column="modify_time" property="modifyTime" jdbcType="TIMESTAMP" />
        
        <result column="trainerTypeName" property="trainerTypeName" />
        <result column="trainerLabels" property="trainerLabels" />
      </resultMap>
      
      <!-- 子查询 -->
    <resultMap id="BaseChildResultMap" type="com.dayhr.web.module.hr.td.elearn.response.TrainerResp" extends="BaseResultRespMap">
        <collection property="trainerLabels" 
            ofType="com.dayhr.web.module.hr.td.elearn.response.TrainerLabelResp" 
            select="com.dayhr.web.module.hr.td.elearn.mapper.TrainerLabelMapper.selecLabels" column="labels">
        </collection>
    </resultMap>
    
      <!-- 表中基础字段 -->
    <sql id="Base_Column_List">
        t.id, t.name, t.title, t.recommend, t.trainer_type_id, t1.name AS trainerTypeName, 
        t.phone, t.email, t.address, t.pro_field, t.intro, t.head_img, t.labels, t.corp_id,
        t.creater_id, t.create_time, t.modifier_id, t.modify_time
    </sql>
    
    <!-- 查询条件  -->
    <sql id="select_Where_Clause">    
       <where>    
            1=1
            <if test="id != null and id != ''">
                AND t.id = #{id }    
            </if>
            <if test="name != null and name != ''">
                AND t.name like CONCAT('%',#{name },'%' )    
            </if>
            <if test="corpId != null and corpId != ''">
                AND corp_id = #{corpId }    
            </if>
            <if test="trainerTypeId != null and trainerTypeId != ''">
                AND t.trainer_type_id = #{trainerTypeId }    
            </if>        
            <if test="orderBy != null and orderBy != ''">
                ORDER BY ${orderBy } ${sortType }
            </if>    
       </where>
    </sql>
      
      <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
        delete from t_hr_td_trainer
        where id = #{id }
      </delete>
      
      <insert id="insert" parameterType="com.dayhr.web.module.hr.td.elearn.model.Trainer" useGeneratedKeys="true" keyProperty="id">
        insert into t_hr_td_trainer (id, name, title, 
          recommend, trainer_type_id, phone, 
          email, address, pro_field, 
          intro, head_img, labels, 
          corp_id, creater_id, create_time, 
          modifier_id, modify_time)
        values (#{id,jdbcType=CHAR}, #{name,jdbcType=VARCHAR}, #{title,jdbcType=VARCHAR}, 
          #{recommend,jdbcType=INTEGER}, #{trainerTypeId,jdbcType=CHAR}, #{phone,jdbcType=VARCHAR}, 
          #{email,jdbcType=VARCHAR}, #{address,jdbcType=VARCHAR}, #{proField,jdbcType=VARCHAR}, 
          #{intro,jdbcType=VARCHAR}, #{headImg,jdbcType=VARCHAR}, #{labels,jdbcType=VARCHAR}, 
          #{corpId,jdbcType=INTEGER}, #{createrId,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, 
          #{modifierId,jdbcType=INTEGER}, #{modifyTime,jdbcType=TIMESTAMP})
      </insert>
      
      <update id="updateByPrimaryKey" parameterType="com.dayhr.web.module.hr.td.elearn.model.Trainer" >
          UPDATE t_hr_td_trainer
        <set>
            <if test="name != null and name != ''">
                name = #{name }, 
            </if>
            <if test="title != null and title != ''">
                title = #{title },
            </if>    
            <if test="recommend != null and recommend != ''">
                recommend = #{recommend },
            </if>
            <if test="trainerTypeId != null and trainerTypeId != ''">
                trainer_type_id = #{trainerTypeId },
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone },
            </if>
            <if test="email != null and email != ''">
                email = #{email },
            </if>
            <if test="address != null and address != ''">
                address = #{address },
            </if>
            <if test="proField != null and proField != ''">
                pro_field = #{proField },
            </if>
            <if test="intro != null and intro != ''">
                intro = #{intro },
            </if>
            <if test="headImg != null and headImg != ''">
                head_img = #{headImg },
            </if>
            <if test="labels != null and labels != ''">
                labels = #{labels },
            </if>
            <if test="corpId != null and corpId != ''">
                corp_id = #{corpId },
            </if>
            <if test="createrId != null and createrId != ''">
                creater_id = #{createrId },
            </if>    
            <if test="createTime != null and createTime != ''">
                create_time = #{createTime },
            </if>    
            <if test="modifierId != null and modifierId != ''">
                modifier_id = #{modifierId },
            </if>
            <if test="modifyTime != null and modifyTime != ''">
                modify_time = #{modifyTime }
            </if>
        </set>
        WHERE id = #{id }
      </update>
      
      <select id="selectByPrimaryKey" resultMap="BaseChildResultMap" parameterType="java.lang.String" >
        SELECT
        <include refid="Base_Column_List" />
        FROM t_hr_td_trainer t
        LEFT JOIN t_hr_td_trainertype t1 ON t.trainer_type_id = t1.id
        WHERE t.id = #{id }
      </select>
      
      <select id="selectAll" resultMap="BaseResultMap" >
        select 
            id, name, title, recommend, trainer_type_id, phone, email, address, pro_field, 
            intro, head_img, labels, corp_id, creater_id, create_time, modifier_id, modify_time
        from t_hr_td_trainer
      </select>
      

    <select id="selectListByCondition" parameterType="com.dayhr.web.module.hr.td.elearn.param.TrainerQueryParam" resultMap="BaseChildResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM t_hr_td_trainer t
        LEFT JOIN t_hr_td_trainertype t1 ON t.trainer_type_id = t1.id          
        <include refid="select_Where_Clause" />
    </select>
    
    
</mapper>

二：子查询id="selecLabels"：注意此处value对应父查询结果中labels：

<?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.dayhr.web.module.hr.td.elearn.mapper.TrainerLabelMapper" >
    
    <!-- 对应表中字段 -->
      <resultMap id="BaseResultMap" type="com.dayhr.web.module.hr.td.elearn.model.TrainerLabel" >
        <id column="id" property="id" />
        <result column="trainer_label" property="trainerLabel" jdbcType="VARCHAR" />
        <result column="built_in" property="builtIn" jdbcType="INTEGER" />
        <result column="corp_id" property="corpId" jdbcType="INTEGER" />
        <result column="creater_id" property="createrId" jdbcType="INTEGER" />
        <result column="creater_time" property="createrTime" jdbcType="TIMESTAMP" />
        <result column="modifier_id" property="modifierId" jdbcType="INTEGER" />
        <result column="modify_time" property="modifyTime" jdbcType="TIMESTAMP" />
      </resultMap>
      
      <!-- 返回字段 -->
      <resultMap id="BaseResultRespMap" type="com.dayhr.web.module.hr.td.elearn.response.TrainerLabelResp" >
        <id column="id" property="id" />
        <result column="trainer_label" property="trainerLabel" jdbcType="VARCHAR" />
        <result column="built_in" property="builtIn" jdbcType="INTEGER" />
        <result column="corp_id" property="corpId" jdbcType="INTEGER" />
        <result column="creater_id" property="createrId" jdbcType="INTEGER" />
        <result column="creater_time" property="createrTime" jdbcType="TIMESTAMP" />
        <result column="modifier_id" property="modifierId" jdbcType="INTEGER" />
        <result column="modify_time" property="modifyTime" jdbcType="TIMESTAMP" />
        <result column="labels" property="labels" />
      </resultMap>
      
      <!-- 表中基础字段 -->
    <sql id="Base_Column_List">
        id, trainer_label, built_in, corp_id, creater_id, creater_time, modifier_id, modify_time
    </sql>
    
    <!-- 查询条件  -->
    <sql id="select_Where_Clause">    
       <where>    
            1=1
            <if test="id != null and id != ''">
                AND id = #{id }    
            </if>
            <if test="corpId != null and corpId != ''">
                AND corp_id = #{corpId }    
            </if>
            <if test="trainerLabel != null and trainerLabel != ''">
                AND trainer_label like CONCAT('%',#{trainerLabel },'%' )    
            </if>    
            <if test="orderBy != null and orderBy != ''">
                ORDER BY ${orderBy } ${sortType }
            </if>    
       </where>
    </sql>
      
      <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
        delete from t_hr_td_trainerlabel
        where id = #{id,jdbcType=CHAR}
      </delete>
      
      <insert id="insert" parameterType="com.dayhr.web.module.hr.td.elearn.model.TrainerLabel" useGeneratedKeys="true" keyProperty="id">
        insert into t_hr_td_trainerlabel (id, trainer_label, built_in, corp_id, 
          creater_id, creater_time, modifier_id, 
          modify_time)
        values (#{id },#{trainerLabel,jdbcType=VARCHAR}, #{builtIn,jdbcType=INTEGER}, #{corpId,jdbcType=INTEGER}, 
          #{createrId,jdbcType=INTEGER}, #{createrTime,jdbcType=TIMESTAMP}, #{modifierId,jdbcType=INTEGER}, 
          #{modifyTime,jdbcType=TIMESTAMP})
      </insert>
      
      <update id="updateByPrimaryKey" parameterType="com.dayhr.web.module.hr.td.elearn.model.TrainerLabel" >
        UPDATE t_hr_td_trainerlabel
        <set>
            <if test="trainerLabel != null and trainerLabel != ''">
                trainer_label = #{trainerLabel }, 
            </if>
            <if test="builtIn != null and builtIn != ''">
                built_in = #{builtIn },
            </if>
            <if test="corpId != null and corpId != ''">
                corp_id = #{corpId },
            </if>
            <if test="createrId != null and createrId != ''">
                creater_id = #{createrId },
            </if>    
            <if test="createTime != null and createTime != ''">
                create_time = #{createTime },
            </if>    
            <if test="modifierId != null and modifierId != ''">
                modifier_id = #{modifierId },
            </if>
            <if test="modifyTime != null and modifyTime != ''">
                modify_time = #{modifyTime }
            </if>
        </set>
        WHERE id = #{id }
      </update>
      
      <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
        select 
            id, trainer_label, built_in, corp_id, creater_id, creater_time, modifier_id, modify_time
        from t_hr_td_trainerlabel
        where id = #{id }
      </select>
      
      <select id="selectAll" resultMap="BaseResultMap" >
        select 
            id, trainer_label, built_in, corp_id, creater_id, creater_time, modifier_id, modify_time
        from t_hr_td_trainerlabel
      </select>
      
      <!-- 验证标签是否存在 -->
    <select id="checkLabel" parameterType="com.dayhr.web.module.hr.td.elearn.param.TrainerLabelQueryParam" resultType="Integer">
        SELECT COUNT(*)
        FROM t_hr_td_trainerlabel 
        <include refid="select_Where_Clause" />
    </select>
    
    <!-- 按条件查询标签 -->
    <select id="selectLabelList" resultMap="BaseResultRespMap" parameterType="com.dayhr.web.module.hr.td.elearn.param.TrainerLabelQueryParam" >
        SELECT 
        <include refid="Base_Column_List" />
        FROM t_hr_td_trainerlabel 
        <include refid="select_Where_Clause" />
      </select>
      
      <!-- 子查询 in()会报错，加'0'处理 -->
      <select id="selecLabels" resultMap="BaseResultRespMap" >
        select 
            id, trainer_label, built_in, corp_id, creater_id, creater_time, modifier_id, modify_time
        from t_hr_td_trainerlabel
        where id in ('0'
  　　　 <if test="value != null and value.length !=0">
        ,${value }
        </if>
        )
      </select>
      
</mapper>