数据库多表查询 resultMap

数据库多表查询可以一次查询完成，也可以分步完成。分多步完成都应该会了，今天介绍一下使用sql语句联合查询。

• 首先看一下项目结构
  

• 查询套餐 MobileMapper.xml，套餐包括检查组，检查组又包括检查项

  <resultMap id="baseResultMap" type="cn.demo.health.pojo.Setmeal">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="code" property="code"/>
        <result column="helpCode" property="helpCode"/>
        <result column="sex" property="sex"/>
        <result column="age" property="age"/>
        <result column="price" property="price"/>
        <result column="remark" property="remark"/>
        <result column="attention" property="attention"/>
        <result column="img" property="img"/>
    </resultMap>

    <resultMap id="findByIdResultMap" type="cn.demo.health.pojo.Setmeal" extends="baseResultMap">
<!--        套餐和检查组多对多映射-->
        <collection property="checkGroups"
                    ofType="cn.demo.health.pojo.CheckGroup"
                    select="cn.demo.health.mapper.CheckItemMapper.findCheckGroupById"
                    column="id"
        >

        </collection>
    </resultMap>

    <select id="getSetMeal" resultType="cn.demo.health.pojo.Setmeal">
        select * from t_setmeal
    </select>

    <!--根据id查询套餐信息-->
    <select id="getSetMealDetail" parameterType="String" resultMap="findByIdResultMap">
        select * from t_setmeal where id = #{id}
    </select>

• 查询检查组和检查项 CheckItemMapper.xml, 这儿检查项和检查组在一个mapper.xml中。

  <resultMap type="cn.demo.health.pojo.CheckGroup" id="baseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="code" property="code"/>
        <result column="helpCode" property="helpCode"/>
        <result column="sex" property="sex"/>
        <result column="remark" property="remark"/>
        <result column="attention" property="attention"/>
    </resultMap>
    <resultMap type="cn.demo.health.pojo.CheckGroup" id="findByIdResultMap" extends="baseResultMap">
<!--       检查组和检查项多对多映射-->
        <collection property="checkItems"
                    ofType="cn.demo.health.pojo.CheckItem"
                    column="id"
                    select="cn.demo.health.mapper.CheckItemMapper.findCheckItemById">
        </collection>
    </resultMap>

    <!--根据id查询检查项信息  -->
    <select id="findCheckItemById" resultType="cn.demo.health.pojo.CheckItem">
        select * from t_checkitem
        where id
  	    in (select checkitem_id from t_checkgroup_checkitem where checkgroup_id=#{id})
    </select>

    <!--根据id查询检查组信息-->
    <select id="findCheckGroupById" parameterType="int" resultMap="findByIdResultMap">
        select * from t_checkgroup where id in (select checkgroup_id from t_setmeal_checkgroup where setmeal_id = #{setmeal_id})
    </select>