mybatis 之association用法

最新推荐文章于 2023-04-15 16:28:51 发布

每天进步一点_点

最新推荐文章于 2023-04-15 16:28:51 发布

阅读量2k

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/zhangxiaoyang0/article/details/53612328

版权

mybatis 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

这几天要用mybatis的association关联映射，好久没用忘了好多，记下来备用。
最基本的用法网上到处都是，就不写了，简单说说用的时候遇到的坑。

//要映射的类
public class TourGroupForCarCar implements Serializable {
    private static final long serialVersionUID = 2412973487524417016L;
    private TourGroup tourGroup;
    private String operatorMobile;
    private Integer guideId;
    private String guideName;
    private String guideMobile;

    public String getOperatorMobile() {
        return operatorMobile;
    }

    public void setOperatorMobile(String operatorMobile) {
        this.operatorMobile = operatorMobile;
    }

    public String getGuideMobile() {
        return guideMobile;
    }

    public void setGuideMobile(String guideMobile) {
        this.guideMobile = guideMobile;
    }

    public Integer getGuideId() {
        return guideId;
    }

    public void setGuideId(Integer guideId) {
        this.guideId = guideId;
    }

    public String getGuideName() {
        return guideName;
    }

    public void setGuideName(String guideName) {
        this.guideName = guideName;
    }

    public TourGroup getTourGroup() {
        return tourGroup;
    }

    public void setTourGroup(TourGroup tourGroup) {
        this.tourGroup = tourGroup;
    }
}
//mapper文件
<resultMap id="tourGroupForCarCar" type="com.yimayhd.erpcenter.dal.sales.client.sales.po.TourGroupForCarCar">

    <result property="guideId" column="guide_id" />
    <result property="guideName" column="guide_name" />
    <result property="guideMobile" column="guide_mobile" />
    <association property="tourGroup" resultMap="BaseResultMap" />
        <!--<id column="id" property="id" jdbcType="INTEGER" />
        <result column="operator_name" property="operatorName" jdbcType="VARCHAR" />
        <result column="operator_id" property="operatorId" jdbcType="INTEGER" />
        <result column="total_adult" property="totalAdult" jdbcType="INTEGER" />
        <result column="total_child" property="totalChild" jdbcType="INTEGER" />
        <result column="total_guide" property="totalGuide" jdbcType="INTEGER" />
        <result column="group_mode" property="groupMode" jdbcType="INTEGER" />
        <result column="group_code" property="groupCode" jdbcType="VARCHAR" />
        <result column="date_start" property="dateStart" jdbcType="DATE" />
        <result column="date_end" property="dateEnd" jdbcType="DATE" />
        <result column="remark" property="remark" jdbcType="VARCHAR" />
        <result column="prudct_brand_id" property="prudctBrandId"
                jdbcType="INTEGER" />
        <result column="product_brand_name" property="productBrandName"
                jdbcType="VARCHAR" />
        <result column="product_id" property="productId" jdbcType="INTEGER" />
        <result column="product_name" property="productName" jdbcType="VARCHAR" />
        <result column="daynum" property="daynum" jdbcType="INTEGER" />
        <result column="order_num" property="orderNum" jdbcType="INTEGER" />
    </association>-->
</resultMap>
//BaseResultMap是自己定义的另一个resultMap。如果写成注释的样子，association里就得用javaType属性了。
//sql语句
<select id="selectGroupInfoWithArrangedTransForCarCar" resultMap="tourGroupForCarCar" parameterType="com.yihg.mybatis.utility.PageBean" >
        select tg.id,group_mode, prudct_brand_id,
        product_brand_name,
        product_id,
        product_name, date_start, date_end,daynum,
        ifnull(total_adult,0) total_adult,
        ifnull(total_child,0) total_child,
        ifnull(total_guide,0) total_guide, tg.remark,
         tg.operator_id, tg.operator_name,order_num,bg.`guide_id`,bg.`guide_name`,bg.`guide_mobile`
        from tour_group tg join booking_delivery bd on tg.id=bd.group_id
        JOIN booking_guide bg ON  tg.`id`= bg.`group_id`
         where tg.create_time <![CDATA[>=]]> #{page.parameter.startTime}
          and tg.create_time <![CDATA[<=]]> #{page.parameter.endTime}
        and bd.supplier_name like concat ('%','${page.parameter.supplierName}','%')
        AND bg.`is_default` = 1
    </select>
//('%','${page.parameter.supplierName}','%')中间的参数不加引号的话会报错：未知的列。

public class GroupOrderForCarCar implements Serializable {
    private static final long serialVersionUID = -707282179483459490L;

    private List<GroupOrder> groupOrderList;
    private String groupCode;
    private String guestReresentativeName;

    public List<GroupOrder> getGroupOrderList() {
        return groupOrderList;
    }

    public void setGroupOrderList(List<GroupOrder> groupOrderList) {
        this.groupOrderList = groupOrderList;
    }

    public String getGroupCode() {
        return groupCode;
    }

    public void setGroupCode(String groupCode) {
        this.groupCode = groupCode;
    }

    public String getGuestReresentativeName() {
        return guestReresentativeName;
    }

    public void setGuestReresentativeName(String guestReresentativeName) {
        this.guestReresentativeName = guestReresentativeName;
    }
}
//mapper文件<resultMap id="groupOrderForCarCar" type="com.yimayhd.erpcenter.dal.sales.client.sales.po.GroupOrderForCarCar">

        <result property="groupCode" column="group_code"/>
        <collection  ofType="com.yimayhd.erpcenter.dal.sales.client.sales.po.GroupOrder" property="groupOrderList">
            <id column="id" property="id" jdbcType="INTEGER" />
            <result column="product_name" property="productName" jdbcType="VARCHAR" />
            <result column="product_brand_name" property="productBrandName" jdbcType="VARCHAR" />
            <result column="num_adult" property="numAdult" jdbcType="INTEGER" />
            <result column="num_child" property="numChild" jdbcType="INTEGER" />
            <result column="num_guide" property="numGuide" jdbcType="INTEGER" />
            <result column="supplier_name" property="supplierName" jdbcType="VARCHAR" />
            <result column="supplier_id" property="supplierId" jdbcType="INTEGER" />
            <result column="receive_mode" property="receiveMode" jdbcType="VARCHAR" />
            <result column="group_id" property="groupId" jdbcType="INTEGER" />
            <result column="order_type" property="orderType" jdbcType="INTEGER" />
            <result column="departure_date" property="departureDate" jdbcType="VARCHAR" />
            <result column="product_id" property="productId" jdbcType="INTEGER" />
            <result column="product_brand_id" property="productBrandId" jdbcType="INTEGER" />
            <result column="type" property="type" jdbcType="INTEGER" />
            <result column="remark" property="remark" jdbcType="VARCHAR" />
            <result column="total" property="total" jdbcType="DECIMAL" />
            <result column="contact_name" property="contactName" jdbcType="VARCHAR" />
            <result column="contact_tel" property="contactTel" jdbcType="VARCHAR" />
            <result column="contact_mobile" property="contactMobile" jdbcType="VARCHAR" />
            <result column="contact_fax" property="contactFax" jdbcType="VARCHAR" />
            <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
        </collection>
    </resultMap>
<select id="selectGroupOrdersInOneGroupForCarCar" resultMap="groupOrderForCarCar" parameterType="com.yihg.mybatis.utility.PageBean">
        select go.id, group_id, supplier_id,supplier_name,
        order_no,order_type,`type`,
        total,
        contact_name,
        contact_tel,
        contact_mobile, contact_fax,
        num_adult,
        num_child,
        num_guide,
        departure_date, go.product_id,
        go.product_brand_id,
        go.product_brand_name,
        go.product_name,
        receive_mode,
        go.remark,tg.group_code
        from group_order go left join tour_group tg on go.group_id = tg.id
        where   group_id IN <foreach item="item" index="index" collection="page.parameter.groupIdSet" open="("
                 separator="," close=")">
            #{item}
        </foreach>
    </select>
//上面的实体类中的groupOrder刚开始用的单个对象GroupOrder，我用sql查出来的记录是12条，但是mybatis却一直返回2条，想了好一会儿，才意识到mybatis的映射结果和sql语句直接查询的结果是不一样的：mybatis中，两表关联后，使用in语法查出来的记录数，应该是小于等于groupIdSet集合的大小的。tour_group和group_order是一对多的关系，而上面的实体类和tour_group中的group_code（在tour_group表中唯一）是一对一的关系，因此映射时上面的实体类和group_order也应该是一对多的关系，返回的group_order映射到了对应的groupOrderList集合中。所以实际确实是返回两条记录，每条记录中包含一个group_order的集合，所有这样的集合总大小是12。而不会如sql查询那样直接返回12条记录。