记一次mybatis关联映射之两条sql映射到一个resultMap中「建议收藏」

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

目标：在一个查询结果中嵌入另一个Sql的结果集ArrayList。用第一条Sql的结果集中的某些字段值为参数传给第二条Sql（其实是传给第二条Sql的Dao接口，传的参数要对应Dao接口中的形参）

第一条SQL

<select id="elsQueryAll" resultMap="CompanyResultMapDto">
    SELECT
        id,
        comp_name,
        comp_area_id,
        (select complete_path from rz_area where id = comp_area_id) as comp_area_name ,
        comp_area_info,
        create_time,
        update_time,
        conn_user_name,
        conn_user_tel,
        data_type,
        balance,
        frozen_balance,
        STATUS,
        delete_flag,
        business_license,
        cs_phone
        main_products,
        lat_x,
        lng_y,
        'sys_company' as target_table
    FROM
	    sys_company
	limit #{start},#{rows}
    </select>

第一条SQL的映射

<resultMap id="CompanyResultMapDto" type="com.rz.dto.RzCompanyDto">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="comp_name" jdbcType="VARCHAR" property="compName" />
        <result column="comp_area_id" jdbcType="VARCHAR" property="compAreaId" />
        <result column="comp_area_name" jdbcType="VARCHAR" property="compAreaName" />
        <result column="comp_area_info" jdbcType="VARCHAR" property="compAreaInfo" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="conn_user_name" jdbcType="VARCHAR" property="connUserName" />
        <result column="conn_user_tel" jdbcType="VARCHAR" property="connUserTel" />
        <result column="data_type" jdbcType="CHAR" property="dataType" />
        <result column="balance" jdbcType="DECIMAL" property="balance" />
        <result column="frozen_balance" jdbcType="DECIMAL" property="frozenBalance" />
        <result column="status" jdbcType="CHAR" property="status" />
        <result column="delete_flag" jdbcType="CHAR" property="deleteFlag" />
        <result column="business_license" jdbcType="BIGINT" property="businessLicense" />
        <result column="cs_phone" jdbcType="VARCHAR" property="csPhone" />
        <result column="main_products" jdbcType="VARCHAR" property="mainProducts" />
        <result column="lat_x" jdbcType="VARCHAR" property="latX" />
        <result column="lng_y" jdbcType="VARCHAR" property="lngY" />
        <result column="targetTable" jdbcType="VARCHAR" property="target_table" />
        <collection property="pictures" select="com.rz.dao.RzPicInfoMapper.selectByTargetTableAndTableId" fetchType="eager" column="{targetTable=target_table,tableId=id}"></collection>
    </resultMap>

第二条SQL及Dao

<select id="selectByTargetTableAndTableId" resultType="com.rz.domain.RzPicInfo">
      select * from rz_pic_info where target_table=#{targetTable} and table_id=#{tableId}
    </select>

/**
     * 该方法已经作为SysCompanyMapper中的联合查询（使用方式参考模板），删除请谨慎
     * @param targetTable
     * @param tableId
     * @return
     */
    List<RzPicInfo> selectByTargetTableAndTableId(@Param("targetTable") String targetTable, @Param("tableId") Long tableId);