7.MyBatis <association>

<association>：一对一，结果封装成一个对象。
<collection>：一对多，结果封装成一个对象的集合。

-- 我们查询四个表的信息 tb_node 、tb_vending_machine 、tb_region 、tb_partner 
-- 查询并显示点位表所有的字段信息,同时显示每个点位的设备数量
SELECT
    n.id,
    n.node_name,
    n.address,
    n.business_type,
    n.region_id,
    n.partner_id,
    n.create_time,
    n.update_time,
    n.create_by,
    n.update_by,
    n.remark,
    COUNT(v.id) AS vm_count
FROM
    tb_node n
LEFT JOIN
    tb_vending_machine v ON n.id = v.node_id
GROUP BY
    n.id;

-- 根据区域id查询区域信息
select * from tb_region where id=1;
-- 根据合作商id查询合作商信息
select * from tb_partner where id=1;

1.简历NodeVo 基础信息就是继承了Node

@Data
public class NodeVo extends Node {

    // 设备数量
    private int vmCount;

    // 区域
    private Region region;

    // 合作商
    private Partner partner;
}

2.Mapper

/**
 * 查询点位管理列表
 * @param node
 * @return NodeVo集合
 */
public List<NodeVo> selectNodeVoList(Node node);

3.NodeMapper.xml 重点
1).首先 更改 resultMap="NodeVoResult
2).定义 <resultMap type="NodeVo" id="NodeVoResult">
3).property当前实体类中，用来接收关联对象的属性名
column 用作关联条件的列名（传递给子查询或用于结果嵌套就是我们数据库定义的名字）

 <result property="id"    column="id"    /> 

4).select指定另一个查询的 id，或者Mapper中的查询方法 (全类名)用于嵌套查询（懒加载）
javaType 关联对象的 Java 类型

这里的意思就是通过查询出来的 region_id 去查询区域详细信息
<association property="region" javaType="Region" column="region_id" select="com.dkd.manage.mapper.RegionMapper.selectRegionById"/>

<resultMap type="NodeVo" id="NodeVoResult">
    <result property="id"    column="id"    />
    <result property="nodeName"    column="node_name"    />
    <result property="address"    column="address"    />
    <result property="businessType"    column="business_type"    />
    <result property="regionId"    column="region_id"    />
    <result property="partnerId"    column="partner_id"    />
    <result property="createTime"    column="create_time"    />
    <result property="updateTime"    column="update_time"    />
    <result property="createBy"    column="create_by"    />
    <result property="updateBy"    column="update_by"    />
    <result property="remark"    column="remark"    />
    <result property="vmCount"    column="vm_count"    />
   <association property="region" javaType="Region" column="region_id" select="com.dkd.manage.mapper.RegionMapper.selectRegionById"/>
   <association property="partner" javaType="Partner" column="partner_id" select="com.dkd.manage.mapper.PartnerMapper.selectPartnerById"/>
</resultMap>

<select id="selectNodeVoList" resultMap="NodeVoResult">
    SELECT
    n.id,
    n.node_name,
    n.address,
    n.business_type,
    n.region_id,
    n.partner_id,
    n.create_time,
    n.update_time,
    n.create_by,
    n.update_by,
    n.remark,
    COUNT(v.id) AS vm_count
    FROM
    tb_node n
    LEFT JOIN
    tb_vending_machine v ON n.id = v.node_id
    <where>
        <if test="nodeName != null  and nodeName != ''"> and n.node_name like concat('%', #{nodeName}, '%')</if>

        <if test="regionId != null "> and n.region_id = #{regionId}</if>

        <if test="partnerId != null "> and n.partner_id = #{partnerId}</if>

    </where>

    GROUP BY
    n.id
</select>

虽然这样可以完成业务但是大批量数据时候容易造成内存溢出情况造成N+1循环查询问题

优化之后里面各个表的公共字段需要重新命名 并且需要实体类修改这些自己修改吧

    <select id="selectNodeVoList" resultMap="NodeVoResult">
        SELECT
        n.id,
        n.node_name,
        n.address,
        n.business_type,
        n.region_id,
        n.partner_id,
        n.create_time,
        n.update_time,
        n.create_by,
        n.update_by,
        n.remark,
        COUNT(v.id) AS vm_count,
        <!-- 4. 显式查询关联表字段，并加上表别名 -->
        r.id AS R_region_id,
        r.region_name,
        r.create_time AS region_create_time,
        r.update_time AS region_update_time,
        r.create_by AS region_create_by,
        r.update_by AS region_update_by,
        r.remark AS region_remark,
        p.id AS P_partner_id,
        p.partner_name,
        p.contact_person,
        p.contact_phone,
        p.profit_ratio,
        p.account,
        p.password,
        p.create_time AS partner_create_time,
        p.update_time AS partner_update_time,
        p.create_by AS partner_create_by,
        p.update_by AS partner_update_by,
        p.remark AS partner_remark
        <!-- 如果还需要其他字段，请在此处添加，例如 r.create_time, p.status 等 -->
        FROM
        tb_node n
        LEFT JOIN
        tb_vending_machine v ON n.id = v.node_id
        LEFT JOIN
        tb_region r ON n.region_id = r.id
        LEFT JOIN
        tb_partner p ON n.partner_id = p.id
        <where>
            <if test="nodeName != null  and nodeName != ''">
                and n.node_name like concat('%', #{nodeName}, '%')
            </if>
            <if test="regionId != null ">
                and n.region_id = #{regionId}
            </if>
            <if test="partnerId != null ">
                and n.partner_id = #{partnerId}
            </if>
        </where>
        GROUP BY
        n.id
    </select>
 <resultMap type="NodeVo" id="NodeVoResult">
        <!-- 主表字段映射 -->
        <result property="id"    column="id"    />
        <result property="nodeName"    column="node_name"    />
        <result property="address"    column="address"    />
        <result property="businessType"    column="business_type"    />
        <result property="regionId"    column="region_id"    />
        <result property="partnerId"    column="partner_id"    />
        <result property="createTime"    column="create_time"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
        <result property="remark"    column="remark"    />
        <result property="vmCount"    column="vm_count"    />
        <!-- 2. 关联对象映射：不再使用 select，而是嵌套 result -->
        <!-- 映射 Region 对象 -->
        <association property="region" javaType="Region">
            <!-- 注意：这里需要指定 Region 表的具体列，并建议使用别名防止字段名冲突 -->
<!--            <id property="id" column="region_id"/>-->
            <result property="regionName" column="region_name"/>
            <!-- 请根据 Region 实体类的实际字段补充其他映射 -->
        </association>
        <!-- 映射 Partner 对象 -->
        <association property="partner" javaType="Partner">
<!--            <id property="id" column="partner_id"/>-->
            <result property="partnerName" column="partner_name"/>
            <result property="contactPerson" column="contact_person"/>
            <result property="contactPhone" column="contact_phone"/>
            <result property="profitRatio" column="profit_ratio"/>
            <!-- 请根据 Partner 实体类的实际字段补充其他映射 -->
        </association>
    </resultMap>