1.基本定义

先说明下两个标签的含义：

• collection（集合）：用于一对多。将关联查询信息映射到集合对象中。 标签注意使用ofType指定关联映射的List中pojo的类型。
• association（关联）：用于多对一，一对一。将关联查询信息映射到单个对象中。 标签注意使用javaType指定关联映射的对象类型 。

有个坑要十分注意，假如在Model里加入了有参构造函数，那么必须再加入一个无参构造函数，否则会造成无法解析。

2.Collection 一对多查询

定义ResultMap：

 <!--映射CdnDomain表-->
    <resultMap id="Domain" type="com.cztv.cdnschedulingsystem.cdn.model.CdnDomain">
        <id column="domain_id" jdbcType="INTEGER" property="domainId"/>
        <result column="type" jdbcType="VARCHAR" property="type"/>
        <result column="domain" jdbcType="VARCHAR" property="domain"/>
        <result column="cname" jdbcType="VARCHAR" property="cname"/>
        <result column="platform" jdbcType="VARCHAR" property="platform"/>
    </resultMap>
    <!--映射CdnProvince表-->
    <resultMap id="Province" type="com.cztv.cdnschedulingsystem.cdn.model.CdnProvince">
        <id column="province_id" jdbcType="INTEGER" property="provinceId"/>
        <result column="province" jdbcType="VARCHAR" property="province"/>
    </resultMap>
    <!--映射SupplierDetailModel表，并确认supplier_id与CdnDomain，CdnProvince表的一对多的关系-->
    <resultMap id="supplierDetail" type="com.cztv.cdnschedulingsystem.cdn.outmodel.SupplierDetailModel">
        <id column="supplier_id" property="id"/>
        <result column="name" property="name"/>
        <collection property="domains" javaType="list" resultMap="Domain"
                    ofType="com.cztv.cdnschedulingsystem.cdn.model.CdnDomain"/>
        <collection property="provinces" javaType="list" resultMap="Province"
                    ofType="com.cztv.cdnschedulingsystem.cdn.model.CdnProvince"/>
    </resultMap>

SQL语句以及定义

    <!--查询CDN厂商列表-->
    <select id="getSupplierDetailList" resultMap="supplierDetail">
    SELECT cdn_supplier.*,cdn_domain.*,cdn_province.*
    FROM cdn_supplier
    LEFT JOIN cdn_supplier_domain
    ON cdn_supplier.supplier_id=cdn_supplier_domain.supplier_id
    LEFT JOIN cdn_domain
    ON cdn_domain.domain_id=cdn_supplier_domain.domain_id
    LEFT JOIN cdn_province_supplier
    ON cdn_supplier.supplier_id=cdn_province_supplier.supplier_id
    LEFT JOIN cdn_province
    ON cdn_province.province_id=cdn_province_supplier.province_id
    ORDER BY cdn_supplier.name
    </select>

输出结构：

[
    {
        "id": 1,
        "name": "云帆",
        "domains": [
            {
                "domainId": 1,
                "type": "点播",
                "domain": "www.sina.com",
                "cname": "tv.cztv.com.yf.com",
                "platform": "H5"
            },
            {
                "domainId": 2,
                "type": "直播",
                "domain": "tv1.cztv.com",
                "cname": "tv.cztv.com.lx.com",
                "platform": "H5"
            }
        ],
        "provinces": [
            {
                "provinceId": 1,
                "province": "浙江"
            },
            {
                "provinceId": 4,
                "province": "北京"
            }
        ]
    },
    {
        "id": 2,
        "name": "蓝汛",
        "domains": [
            {
                "domainId": 1,
                "type": "点播",
                "domain": "www.sina.com",
                "cname": "tv.cztv.com.yf.com",
                "platform": "H5"
            },
            {
                "domainId": 2,
                "type": "直播",
                "domain": "tv1.cztv.com",
                "cname": "tv.cztv.com.lx.com",
                "platform": "H5"
            }
        ],
        "provinces": [
            {
                "provinceId": 2,
                "province": "江苏"
            },
            {
                "provinceId": 3,
                "province": "广州"
            }
        ]
    }
]

2.Association 多对一查询

定义ResultMap:

 <!--映射CdnSupplier表-->
 <resultMap id="Supplier" type="com.cztv.cdnschedulingsystem.cdn.model.CdnSupplier">
        <id column="supplier_id" jdbcType="INTEGER" property="supplierId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="status" jdbcType="VARCHAR" property="status"/>
    </resultMap>

    <resultMap id="supplierDetail" type="com.cztv.cdnschedulingsystem.cdn.outmodel.SupplierDomainModel">
        <result column="domain_id" jdbcType="INTEGER" property="domain_id"/>
        <result column="domain" jdbcType="VARCHAR" property="domain"/>
        <association property="supplier" resultMap="Supplier"/>
    </resultMap>

SQL语句：

  <select id="getSupplierDetailList" resultMap="supplierDetail">
    SELECT
    cdn_domain.domain_id,
    cdn_domain.domain,
    cdn_supplier.*
    FROM cdn_supplier
    LEFT JOIN cdn_supplier_domain
    ON cdn_supplier.supplier_id=cdn_supplier_domain.supplier_id
    LEFT JOIN cdn_domain
    ON cdn_domain.domain_id=cdn_supplier_domain.domain_id
    LEFT JOIN cdn_province_supplier
    ON cdn_supplier.supplier_id=cdn_province_supplier.supplier_id
    LEFT JOIN cdn_province
    ON cdn_province.province_id=cdn_province_supplier.province_id
     WHERE cdn_supplier.supplier_id=1
    ORDER BY cdn_supplier.name
    </select>

输出结构：

[
    {
        "domain_id": 1,
        "domain": "www.sina.com",
        "supplier": {
            "supplierId": 2,
            "name": "蓝汛",
            "status": "在用"
        }
    },
    {
        "domain_id": 2,
        "domain": "tv1.cztv.com",
        "supplier": {
            "supplierId": 2,
            "name": "蓝汛",
            "status": "在用"
        }
    }
]