mybatis嵌套collection

一：不用指定collection的column行，使用外连接查询，只能两重嵌套 

<resultMap id="groupsMap" type="im.qingtui.app.qbee.shift.controller.dto.template.service.TemplatePointDTO" >
    <id column="id" property="groupId" jdbcType="BIGINT" />
    <result column="group_name" property="groupName" jdbcType="VARCHAR" />
    <collection property="propertys" javaType="ArrayList" resultMap="groupsPointMap"/>
</resultMap>

<resultMap id="groupsPointMap" type="im.qingtui.app.qbee.shift.controller.dto.template.service.TemplatePointPropertiesDTO">
    <result property="key" column="point_key" />
    <result property="label" column="point_label" />
    <result property="unit" column="point_unit" />
</resultMap>

<!--根据模板id查询分组信息-->
<select id="selectGroupList" resultMap="groupsMap">
    select
      gp.id,
      gp.group_name,
      p.point_key,
      p.point_label,
      p.point_unit
    from
      shift_template_group_point gp
      left join template_point p on gp.id = p.group_id and p.is_delete = 0
    where
      gp.template_id = #{templateId} and gp.is_delete = 0
</select>

 

二：指定collection的column行，使用多条sql语句查询，可以实现多重嵌套

<resultMap id="groupsMap" type="im.qingtui.app.qbee.shift.controller.dto.template.service.TemplatePointDTO" >
    <id column="id" property="groupId" jdbcType="BIGINT" />
    <result column="group_name" property="groupName" jdbcType="VARCHAR" />
    //column属性，可以为多个，一个的话，可以写成column="id",多个可以写成 column="{groupId = id,xxx=xxx}"
    //column里面的字段，为主表的列，id为TemplatePointDTO对应表的列
    <collection property="propertys" column="{groupId = id}" javaType="ArrayList"
                ofType="im.qingtui.app.qbee.shift.controller.dto.template.service.TemplatePointPropertiesDTO" select="getGroupsPointMap"/>
</resultMap>
//collcetion映射的map
<resultMap id="groupsPointMap" type="im.qingtui.app.qbee.shift.controller.dto.template.service.TemplatePointPropertiesDTO">
    <result property="key" column="point_key" />
    <result property="label" column="point_label" />
    <result property="unit" column="point_unit" />
    //这种写法，可以再嵌套
</resultMap>

<!--主表查询-->
<select id="selectGroupList" resultMap="groupsMap">
    select id,group_name
    from template_group_point
    where template_id = #{templateId} and is_delete = 0
</select>

<select id="getGroupsPointMap" resultMap="groupsPointMap">
    select point_key,point_label,point_unit
    from shift_template_point
    //这里的groupId，是collection属性column
    where group_id = ${groupId} and is_delete = 0
</select>

association: 处理一对一、多对一
collection: 处理一对多