MyBatis collection的两种形式——MyBatis学习笔记之一

进度百分之三十
3,717 阅读2分钟

前言

最近在学做Java项目，但是很多东西都是看了忘了，忘了又看，而且关于mybatis也是遇到了新的东西，之前我使用collection，就是只使用到了ofType和property这个两个属性，但是今天有接触到了新的两个属性，column和select属性，所以就去了解了一下这个两个属性的用法，所以做此纪录，以防后面忘了又想不起来，与association一样，collection元素也有两种形式，现介绍如下：

方式一：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.abc.mapper.TeacherMapper">
    <select id="getById" parameterType="int" resultMap="supervisorResultMap">
        select t.id t_id,
               t.name t_name,
               t.gender t_gender,
               t.research_area t_research_area,
               t.title t_title,
               s.id,
               s.name,
               s.gender,
               s.major,
               s.grade
        from teacher t
        left join student s
        on 1=1
        where t.id = #{id}
          and s.supervisor_id = t.id
    </select>
    <!--教师实体映射-->
    <resultMap id="supervisorResultMap" type="Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
        <result property="gender" column="t_gender"/>
        <result property="researchArea" column="t_research_area"/>
        <result property="title" column="t_title"/>
        <!--需要注意的是，上面的select语句中学生的字段名/别名应与 下面的column属性一致。ofType指collection包含的元素的类型， 此属性不可少-->
        <collection property="supStudents" ofType="Student">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="gender" column="gender"/>
            <result property="major" column="major"/>
            <result property="grade" column="grade"/>
            <!--映射学生的指导教师属性，用到了 supervisorResultMap本身-->
            <association property="supervisor" resultMap="supervisorResultMap"/>
        </collection>
    </resultMap>
</mapper>

这种方式是最基本的，collection中只有property和ofType属性，其中property的supStudents表示Teacher这个实体中的一个supStudents集合，该实体类为这样，然后collection里面那些property的字段和Student这个类里面的字段对应，当然也要和查询里面的字段对应，s.id,s.name,s.gender,s.major,s.grade，其实也和Student这个类对应了，所以不用担心这个问题

package com.imooc.pojo.bo;

public class Teacher {
    private Integer id;
    private String name;
    private String gender;
    private String researchArea;
    private String title;

    private List<Student> supStudents;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getResearchArea() {
        return researchArea;
    }

    public void setResearchArea(String researchArea) {
        this.researchArea = researchArea;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public List<Student> getSupStudents() {
        return supStudents;
    }

    public void setSupStudents(List<Student> supStudents) {
        this.supStudents = supStudents;
    }
}

缺点: 与以前的写法相比，这种写法的缺点是学生实体映射被嵌入到教师实体映射中，因此学生实体映射不能被重用。

方式2

这种方式是使用一条单独的select语句来加载关联的实体，然后在collection元素中引用此select语句，方式二中的collection中的字段多了column和select这两个字段，ofType指collection包含的元素的类型，此属性不可少。 column属性指把前面的myOrdersVO的select语句中的MyordersVO的orderId列的值作为参数传递给将要引用到的下述的getSubItems的select语句，此属性不可少,引用的形式为：命名空间.select语句id,这里因为在同一个命名空间里面，所以直接引用id就行了

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.imooc.mapper.OrdersMapperCustom">

    <resultMap id="myOrdersVO" type="com.imooc.pojo.vo.MyOrdersVO">
        <id column="orderId" property="orderId"/>
        <result column="createdTime" property="createdTime"/>
        <result column="payMethod" property="payMethod"/>
        <result column="realPayAmount" property="realPayAmount"/>
        <result column="postAmount" property="postAmount"/>
        <result column="orderStatus" property="orderStatus"/>
        <result column="isComment" property="isComment"/>

        <collection property="subOrderItemList"
                    select="getSubItems"
                    column="orderId"
                    ofType="com.imooc.pojo.vo.MySubOrderItemVO">
            <result column="itemId" property="itemId"/>
            <result column="itemName" property="itemName"/>
            <result column="itemImg" property="itemImg"/>
            <result column="itemSpecId" property="itemSpecId"/>
            <result column="itemSpecName" property="itemSpecName"/>
            <result column="buyCounts" property="buyCounts"/>
            <result column="price" property="price"/>
        </collection>
    </resultMap>

    <select id="queryMyOrders" resultMap="myOrdersVO" parameterType="Map">
        SELECT
        od.id as orderId,
        od.created_time as createdTime,
        od.pay_method as payMethod,
        od.real_pay_amount as realPayAmount,
        od.post_amount as postAmount,
        os.order_status as orderStatus,
        od.is_comment as isComment
        FROM
        orders od
        LEFT JOIN
        order_status os
        on od.id = os.order_id
        WHERE
        od.user_id = #{paramsMap.userId}
        AND
        od.is_delete = 0
        <if test="paramsMap.orderStatus != null">
            and os.order_status = #{paramsMap.orderStatus}
        </if>
        ORDER BY
        od.updated_time ASC
    </select>


    <select id="getSubItems" parameterType="String" resultType="com.imooc.pojo.vo.MySubOrderItemVO">

        select oi.item_id        as itemId,
               oi.item_name      as itemName,
               oi.item_img       as itemImg,
               oi.item_spec_name as itemSpecName,
               oi.buy_counts     as buyCounts,
               oi.price          as price
        from order_items oi
        where oi.order_id = #{orderId}

    </select>


    <select id="queryMyOrdersDoNotUse" resultMap="myOrdersVO" parameterType="Map">
        SELECT
        od.id as orderId,
        od.created_time as createdTime,
        od.pay_method as payMethod,
        od.real_pay_amount as realPayAmount,
        od.post_amount as postAmount,
        os.order_status as orderStatus,
        oi.item_id as itemId,
        oi.item_name as itemName,
        oi.item_img as itemImg,
        oi.item_spec_name as itemSpecName,
        oi.buy_counts as buyCounts,
        oi.price as price
        FROM
        orders od
        LEFT JOIN
        order_status os
        on od.id = os.order_id
        LEFT JOIN
        order_items oi
        ON od.id = oi.order_id
        WHERE
        od.user_id = #{paramsMap.userId}
        AND
        od.is_delete = 0
        <if test="paramsMap.orderStatus != null">
            and os.order_status = #{paramsMap.orderStatus}
        </if>
        ORDER BY
        od.updated_time ASC
    </select>


    <select id="getMyOrderStatusCounts" parameterType="Map" resultType="int">
        SELECT
        count(1)
        FROM
        orders o
        LEFT JOIN
        order_status os
        on
        o.id = os.order_id
        WHERE
        o.user_id = #{paramsMap.userId}
        AND
        os.order_status = #{paramsMap.orderStatus}
        <if test="paramsMap.isComment != null">
            and o.is_comment = #{paramsMap.isComment}
        </if>
    </select>


    <select id="getMyOrderTrend" parameterType="Map" resultType="com.imooc.pojo.OrderStatus">

        SELECT os.order_id     as orderId,
               os.order_status as orderStatus,
               os.created_time as createdTime,
               os.pay_time     as payTime,
               os.deliver_time as deliverTime,
               os.success_time as successTime,
               os.close_time   as closeTime,
               os.comment_time as commentTime
        FROM orders o
                 LEFT JOIN
             order_status os
             on
                 o.id = os.order_id
        WHERE o.is_delete = 0
          AND o.user_id = #{paramsMap.userId}
          AND os.order_status in (20, 30, 40)
        ORDER BY os.order_id
            DESC
    </select>
</mapper>
avatar
文章
阅读
粉丝