Mapper中多表的嵌套组合查询

Mapper中多表的嵌套组合查询

<!--手动映射封装，实现多表的嵌套组合查询-->
<resultMap id="NodeVoResult" type="NodeVo">
    <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"    />
    <!--嵌套查询
    property="region" 映射的属性名
    javaType="Region" 属性名的类型,java的类型
    column="region_id" 映射的列名,从上面拿到的值
    select="" 嵌套查询的sql,调用对应的查询方法-->
    <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>

<?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.dkd.manage.mapper.NodeMapper">
    <!--手动映射封装，实现多表的嵌套组合查询-->
    <resultMap id="NodeVoResult" type="NodeVo">
        <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"    />
        <!--嵌套查询
        property="region" 映射的属性名
        javaType="Region" 属性名的类型,java的类型
        column="region_id" 映射的列名,从上面拿到的值
        select="" 嵌套查询的sql,调用对应的查询方法-->
        <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>
    <resultMap type="Node" id="NodeResult">
        <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"    />
    </resultMap>

    <sql id="selectNodeVo">
        select id, node_name, address, business_type, region_id, partner_id, create_time, update_time, create_by, update_by, remark from tb_node
    </sql>

    <select id="selectNodeList" parameterType="Node" resultMap="NodeResult">
        <include refid="selectNodeVo"/>
        <where>  
            <if test="nodeName != null  and nodeName != ''"> and node_name like concat('%', #{nodeName}, '%')</if>
            <if test="regionId != null "> and region_id = #{regionId}</if>
            <if test="partnerId != null "> and partner_id = #{partnerId}</if>
        </where>
    </select>
    
    <select id="selectNodeById" parameterType="Long" resultMap="NodeResult">
        <include refid="selectNodeVo"/>
        where id = #{id}
    </select>
    <select id="selectNodeVoList" resultMap="NodeVoResult">
        SELECT n.*, COUNT(vm.node_id) AS vm_count
        FROM tb_node n
                 LEFT JOIN tb_vending_machine vm ON n.id = vm.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>

    <insert id="insertNode" parameterType="Node" useGeneratedKeys="true" keyProperty="id">
        insert into tb_node
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="nodeName != null and nodeName != ''">node_name,</if>
            <if test="address != null and address != ''">address,</if>
            <if test="businessType != null">business_type,</if>
            <if test="regionId != null">region_id,</if>
            <if test="partnerId != null">partner_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="nodeName != null and nodeName != ''">#{nodeName},</if>
            <if test="address != null and address != ''">#{address},</if>
            <if test="businessType != null">#{businessType},</if>
            <if test="regionId != null">#{regionId},</if>
            <if test="partnerId != null">#{partnerId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="remark != null">#{remark},</if>
         </trim>
    </insert>

    <update id="updateNode" parameterType="Node">
        update tb_node
        <trim prefix="SET" suffixOverrides=",">
            <if test="nodeName != null and nodeName != ''">node_name = #{nodeName},</if>
            <if test="address != null and address != ''">address = #{address},</if>
            <if test="businessType != null">business_type = #{businessType},</if>
            <if test="regionId != null">region_id = #{regionId},</if>
            <if test="partnerId != null">partner_id = #{partnerId},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteNodeById" parameterType="Long">
        delete from tb_node where id = #{id}
    </delete>

    <delete id="deleteNodeByIds" parameterType="String">
        delete from tb_node where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>

实例记录

在 LyPositionClockAddress 中增加嵌套查询，通过 clock_range_id 查询 LyPositionClockRange 的数据

实体类1

package com.ruoyi.positionClock.domain;

        import com.ruoyi.common.core.domain.BaseCrmEntity;
        import lombok.Data;
        import lombok.Getter;
        import lombok.Setter;
        import org.apache.commons.lang3.builder.ToStringBuilder;
        import org.apache.commons.lang3.builder.ToStringStyle;
        import com.ruoyi.common.annotation.Excel;
        import com.ruoyi.common.core.domain.BaseEntity;

        import java.util.List;

        /**
        * 打卡地址对象 ly_position_clock_address
        *
        * @author wuJiaWei
        * @date 2024-12-04
        */
        @Data
        public class LyPositionClockAddress extends BaseCrmEntity
        {
        private static final long serialVersionUID = 1L;

        /** 打卡地址表 */
        private Long id;

        /** 打卡策略表id */
        private Long clockStrategyId;

        /** 用工方id */
        private Long customerId;

        /** 自定义地址名称 */
        private String addressName;

        /** 简略地址 */
        private String briefAddress;

        /** 详细地址 */
        private String detailedAddress;

        /** 纬度 */
        private String latitude;

        /** 经度 */
        private String longitude;

        /** 打卡范围id */
        private Integer clockRangeId;

        /** 状态 */
        private Integer status;

        /** 假删除 */
        private Integer isDel;
    }

实体类2

package com.ruoyi.positionClock.domain;

import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
* 打卡地址对象 ly_position_clock_address
*
* @author wuJiaWei
* @date 2024-12-04
*/
@Data
public class LyPositionClockRange implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@ApiModelProperty(value = "主键", name = "id")
@JsonProperty("clockRangeId") // 设置JSON序列化时使用的字段名
private Long id;

/**
* 打卡范围名称
*/
@ApiModelProperty(value = "打卡范围名称", name = "rangeName")
private String rangeName;

/**
* 打卡范围
*/
@ApiModelProperty(value = "打卡范围", name = "rangeValue")
private Integer rangeValue;

}

Mapper

<?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.ruoyi.positionClock.mapper.LyPositionClockAddressMapper">

    <resultMap type="LyPositionClockAddress" id="LyPositionClockAddressResult">
        <result property="id"    column="id"    />
        <result property="clockStrategyId"    column="clock_strategy_id"    />
        <result property="customerId"    column="customer_id"    />
        <result property="addressName"    column="address_name"    />
        <result property="briefAddress"    column="brief_address"    />
        <result property="detailedAddress"    column="detailed_address"    />
        <result property="latitude"    column="latitude"    />
        <result property="longitude"    column="longitude"    />
        <result property="clockRangeId"    column="clock_range_id"    />
        <result property="status"    column="status"    />
        <result property="isDel"    column="is_del"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectLyPositionClockAddressVo">
        select id, clock_strategy_id, customer_id, address_name, brief_address, detailed_address, latitude, longitude, clock_range_id, status, is_del, create_time, update_time from ly_position_clock_address
    </sql>

    <select id="selectLyPositionClockAddressList" parameterType="LyPositionClockAddress" resultMap="LyPositionClockAddressResult">
        <include refid="selectLyPositionClockAddressVo"/>
        <where>
            <if test="clockStrategyId != null "> and clock_strategy_id = #{clockStrategyId}</if>
        </where>
    </select>
</mapper>

修改实体类

首先，在 LyPositionClockAddress 类中添加一个属性，用于保存关联的 LyPositionClockRange 对象或者对象列表（取决于您的业务逻辑）。

// 如果是单个打卡范围关联，则使用对象 
private LyPositionClockRange lyPositionClockRange; 

// 如果是多个打卡范围关联，则使用列表 
private List<LyPositionClockRange> lyPositionClockRangeList;

修改 MyBatis 映射文件

接下来，你需要在 MyBatis 的映射文件中添加一个新的 <resultMap> 或者修改现有的 LyPositionClockAddressResult 来映射新的关系。这里我们假设每个地址仅对应一个打卡范围，因此我们将使用单个对象而不是列表。

<resultMap type="LyPositionClockAddress" id="LyPositionClockAddressResult">
    <!-- 现有的结果映射 -->

    <!-- 新增关联对象的结果映射 -->
    <association property="lyPositionClockRange" javaType="LyPositionClockRange" select="selectLyPositionClockRangeById" column="clock_range_id"/>
</resultMap>

        <!-- 添加一个新查询，用于根据 clock_range_id 获取 LyPositionClockRange 对象 -->
<select id="selectLyPositionClockRangeById" resultType="LyPositionClockRange" parameterType="Long">
SELECT * FROM ly_position_clock_range WHERE id = #{id}
</select>

如果每个 LyPositionClockAddress 可能与多个 LyPositionClockRange 相关联，你应该使用 <collection> 标签代替 <association> 并调整 javaType 属性为 List<LyPositionClockRange>。

更新查询语句

最后，确保所有相关的查询语句都正确地包含了对 clock_range_id 的处理，这样当执行查询时，MyBatis 会自动加载对应的 LyPositionClockRange 数据并将其填充到 LyPositionClockAddress 实例中。

现在，当你调用相应的查询方法时，MyBatis 将执行嵌套查询，并将相关联的 LyPositionClockRange 数据填充到 LyPositionClockAddress 对象中。