resultMap使用注意

mybatis中resultMap标签的使用注意

resultMap 元素是 MyBatis 中最重要最强大的元素。它可以让你从 90% 的 JDBC ResultSets 数据提取代码中解放出来，并在一些情形下允许你进行一些 JDBC 不支持的操作。实际上，在为一些比如连接的复杂语句编写映射代码的时候，一份 resultMap 能够代替实现同等功能的数千行代码。ResultMap 的设计思想是，对简单的语句做到零配置，对于复杂一点的语句，只需要描述语句之间的关系就行了。

假设在mapper.xml文件中编写一个复杂查询，需要使用到resultMap进行结果映射，例如这样的查询

<select id="currDateDishesDetailByYear" resultMap="currDateDishesDetailResultMap">
    select
        fdt1.food_daydishes_id,
        fdrt2.food_dishes_id,
        fdrt2.food_daydishes_relation_id,
        date_format(fdt1.daydishes_date, '%Y') formatDate,
        sum( fdt1.dishes_sales) sumDishesSales,
        fdt4.dishes_name,
        fdt4.dishes_price,
        fdt4.dishes_category,
        fdt4.dishes_spicy,
        fdt4.dishes_status
    from food_daydishes fdt1
    left join food_daydishes_relation fdrt2 on fdt1.food_daydishes_id = fdrt2.food_daydishes_id
    left join food_three_meals ftmt3 on ftmt3.food_daydishes_relation_id = fdrt2.food_daydishes_relation_id
    left join food_dishes fdt4 on fdt4.food_dishes_id = fdrt2.food_dishes_id
    <where>
        <if test="dateDishesDetailVo.type== 1 and dateDishesDetailVo.monthOrYearDishesDate != null and dateDishesDetailVo.monthOrYearDishesDate != ' '">
            and date_format(fdt1.daydishes_date, '%Y') = #{dateDishesDetailVo.monthOrYearDishesDate}
        </if>

        <if test="dateDishesDetailVo.threeMealsIdList !=null and dateDishesDetailVo.threeMealsIdList.size()>0">
            and ftmt3.tree_meals in
            <foreach collection="dateDishesDetailVo.threeMealsIdList" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </where>
    group by
        fdt1.food_daydishes_id,
        fdrt2.food_dishes_id,
        fdrt2.food_daydishes_relation_id,
        fdt1.daydishes_date,
        fdt1.dishes_sales,
        fdt4.dishes_name,
        fdt4.dishes_price,
        fdt4.dishes_category,
        fdt4.dishes_spicy,
        fdt4.dishes_status
        order by  sumDishesSales desc
</select>

• select标签中的id属性要和所编写的mapper层的接口方法保持一致，需要进行结果映射的resultMap要和resultMap标签中的id属性保持一致

然后是resultMap编写时要注意的

<resultMap id="currDateDishesDetailResultMap" type="com.ryzw.intelligent.cy.dto.CurrSalesDetailDto">
    <id column="food_daydishes_id" property="foodDaydishesId"/>
    <result column="formatDate" property="daydishesDate"/>
    <result column="food_dishes_id" property="foodDishesId"/>
    <result column="food_daydishes_relation_id" property="foodDaydishesRelationId"/>
    <result column="dishes_name" property="dishesName"/>
    <result column="sumDishesSales" property="dishesSales"/>
    <result column="dishes_price" property="dishesPrice"/>
    <result column="dishes_category" property="dishesCategory"/>
    <result column="dishes_spicy" property="dishesSpicy"/>
    <result column="dishes_status" property="dishesStatus"/>

    <collection property="imgUrlList" ofType="java.lang.String" select="getImgUrlList" column="food_dishes_id">
        <id column="food_dishes_img_id" property="foodDishesImgId"/>
        <result column="img_url" property="imgUrlList"/>
    </collection>
    <collection property="threeMealsIdList" ofType="java.lang.Integer" select="getTreeMealsList" column="food_daydishes_relation_id">
        <id column="food_three_meals_id" property="foodThreeMealsId"/>
        <result column="tree_meals" property="threeMealsIdList"/>
    </collection>
</resultMap>
<!--getImgUrlList-->
<select id="getImgUrlList" resultType="com.ryzw.intelligent.cy.dto.DishesImgDto">
    SELECT
    food_dishes_img_id,
    img_url,
    img_name
    FROM
    food_dishes_img
    where
    food_dishes_id = #{food_dishes_id}
</select>

<!--getTreeMealsList-->
<select id="getTreeMealsList" resultType="java.lang.Integer">
    SELECT
    tree_meals
    FROM
    food_three_meals
    where
    food_daydishes_relation_id = #{food_daydishes_relation_id}
</select>

• resultMap中的<id>、<result>的column属性一定要和查询语句中字段或该字段重命名保持一致
• <collection>的``property`属性为需要用到的查询语句的ID