Mybatis复杂查询结果映射之collection

最新推荐文章于 2022-08-18 15:23:32 发布

小陈不是小程

最新推荐文章于 2022-08-18 15:23:32 发布

阅读量488

点赞数

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

本文链接：https://blog.csdn.net/a15906449458/article/details/125849692

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

如若看不懂可查看官网文档
https://mybatis.net.cn/sqlmap-xml.html#Result_Maps
方式一:查一次

<resultMap id="inLeaseMap" type="cfContract">
        <result property="id" column="id"/>
        <result property="contractSn" column="contract_Sn"/>
        <result property="recordSn" column="record_sn"/>
        <collection property="customerList" ofType="实体类" javaType="ArrayList" resultMap="customerMap"/>
        //list对象
    </resultMap>

//list对象的映射关系

<resultMap id="customerMap" type="cfCustomer">
        <result property="id" column="cu_id"/>
        <result property="customerSn" column="customer_sn"/>
        <result property="customerType" column="customer_type"/>
        <result property="customerName" column="customer_name"/>
        <result property="customerStar" column="customer_star"/>
        <result property="identityCard" column="identity_card"/>
        <result property="contactName" column="contact_name"/>
    </resultMap>

//mybatis会自动归集结果,比如说有两条合同信息，但是是同一份合同有两个客户信息
则mybatis会处理数据结构为
{
//合同信息
id:
contract_sn:
customerList:[
{//客户1信息},{//客户2信息}
]
}

 <select  id="inLeaseList" resultMap="inLeaseMap">
        select
            c.id,
            cu.id as c_id,
            c.contract_sn,
            cu.customer_sn, cu.customer_type, cu.customer_name, cu.customer_star, cu.identity_card, cu.contact_name
        from cf_contract as c left join cf_contract_customer as cc on c.id = cc.contract_id
                              left join cf_customer as cu on cc.customer_id = cu.id
    </select>

方式二: 指定子查询sql 将inLeaseList返回的id字段作为参数传入CustomerListByContract查询
并将CustomerListByContract查询结果集作为list放入inLeaseMap中的customerList内查两次

合同信息映射

<resultMap id="inLeaseMap" type="cfContract">
        <result property="id" column="id"/>
        <result property="contractSn" column="contract_Sn"/>
        <result property="recordSn" column="record_sn"/>
        <collection property="customerList" javaType="ArrayList" column="id" select="CustomerListByContract" ofType="CfCustomer"/>
    </resultMap>

客户信息映射

<resultMap id="customerMap" type="cfCustomer">
        <result property="id" column="id"/>
        <result property="customerSn" column="customer_sn"/>
        <result property="customerType" column="customer_type"/>
        <result property="customerName" column="customer_name"/>
        <result property="customerStar" column="customer_star"/>
        <result property="identityCard" column="identity_card"/>
        <result property="contactName" column="contact_name"/>
    </resultMap>

主查询查出合同信息

  <select id="inLeaseList" resultMap="inLeaseMap">
        select *
        from cf_contract
    </select>

子查询查出合同信息对应的客户信息

select cc.customer_sn, cc.customer_type, cc.customer_name, cc.customer_star, cc.identity_card, cc.contact_name
from cf_contract_customer as c
left join cf_customer as cc on c.customer_id = cc.id
where c.contract_id = #{id}