mybatis中 association，collection

collection标签解决sql与集合的映射

1.关联的嵌套结果映射

<resultMap id="MallExtVoResultMap" type="com.yp.b2b.model.biz.vo.ec.item.admin.ItemExtVo"
             extends="com.yp.b2b.dao.biz.mapper.ec.item.ItemMapper.ItemResultMap">
      <association property="fullDesc" javaType="com.yp.b2b.model.biz.vo.ec.file.admin.FullDescExtVo" columnPrefix="full_">
          <result property="id" column="id"/>
          <result property="descp" column="descp"/>
      </association>
      <collection property="pictures" ofType="com.yp.b2b.model.biz.bo.ec.file.File" javaType="ArrayList"
                  columnPrefix="file_">
          <result property="id" column="id"/>
          <result property="remark" column="remark"/>
          <result property="sourceType" column="source_type"/>
          <result property="sourceId" column="source_id"/>
          <result property="path" column="path"/>
          <result property="name" column="name"/>
          <result property="sortIndex" column="sort_index"/>
      </collection>
  </resultMap>

  SELECT
         t.*,
        d.id          AS full_id,
        d.source_id   AS full_source_id,
        d.remark      AS full_remark,
        d.source_type AS full_source_type,
        d.descp       AS full_descp,
        f.id          AS file_id,
        f.remark      AS file_remark,
        f.source_type AS file_source_type,
        f.source_id   AS file_source_id,
        f.path        AS file_path,
        f.NAME        AS file_name,
        f.sort_index  AS file_sort_index
        FROM b2b_item t
        LEFT JOIN b2b_item_class c on c.id =t.item_class_id
        LEFT JOIN b2b_file f ON t.id = f.source_id AND f.source_type = 1 AND f.active_status = 'ACTIVE'
        LEFT JOIN b2b_full_desc d ON t.id = d.source_id AND d.source_type = 1 AND d.active_status = 'ACTIVE'
        where c.active_status ='ACTIVE'

总结：不适用分页查询，注意区分主表和字表的字段，执行效率?

2.关联的嵌套 Select 查询

    <resultMap id="OrderExtMap" type="com.yp.b2b.model.biz.vo.ec.order.OrderExtVo"
               extends="com.yp.b2b.dao.biz.mapper.ec.order.OrderMapper.OrderResultMap">
        <collection property="details" ofType="com.yp.b2b.model.biz.bo.ec.order.OrderDetail" javaType="ArrayList"
                    column="id" select="getOrderDetails" >
        </collection>
    </resultMap>

    <resultMap id="OrderExtDetailMap" type="com.yp.b2b.model.biz.bo.ec.order.OrderDetail"
               extends="com.yp.b2b.dao.biz.mapper.ec.order.OrderDetailMapper.OrderDetailResultMap">
    </resultMap>

    <select id="getOrderExtVo" resultMap="OrderExtMap">
     SELECT
    t.*
    FROM
    b2b_order t
    <include refid="soSqlFilter"/>
    order by t.id DESC
    </select>

    <select id="getOrderDetails" resultMap="OrderExtDetailMap">
        SELECT
          *
        FROM
        b2b_order_detail d
        where  d.order_id=#{order_id}
        order by d.id DESC
    </select>

总结：可以用于分页需求的查询，执行日志大概这样子：

image.png

column是主表传递给嵌套子查询的列
mybatis文档:

image.png

<resultMap id="findCountryCityAddressMap" type="map">
    <result property="country" column="country"/>
    <collection property="cityList"
                column="{cityId=city_id,adr=addressCol, dis=districtCol}" //adr作为第二个sql查询条件key,即prop1属性
                ofType="map"                                              //addressCol即为虚拟列名
                javaType="java.util.List" select="selectAddressByCityId"/>
</resultMap>

3.discriminator 鉴别器的使用

个数据库查询可能会返回多个不同的结果集（但总体上还是有一定的联系的）。 鉴别器（discriminator）元素就是被设计来应对这种情况的，另外也能处理其它情况，例如类的继承层次结构。 鉴别器的概念很好理解——它很像 Java 语言中的 switch 语句。

<discriminator javaType="int" column="vehicle_type">
    <case value="1" resultMap="carResult"/>
    <case value="2" resultMap="truckResult"/>
    <case value="3" resultMap="vanResult"/>
    <case value="4" resultMap="suvResult"/>
  </discriminator>

官方文档讲解比较详细
https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps