Mybatis嵌套子查詢動態(tài)SQL編寫實踐

更新時間：2025年05月30日 14:25:30 作者：振宇i

這篇文章主要介紹了Mybatis嵌套子查詢動態(tài)SQL編寫方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

前言

Mybatis的xml文件編寫動態(tài)SQL是從mapper中獲取傳入的參數(shù)，但是如果是嵌套的子查詢中，子查詢動態(tài)SQL所需的參數(shù)不能像常規(guī)的那樣直接從mapper中獲取，因為嵌套子查詢中能獲取的傳參僅能來源于主查詢中的結(jié)果，如下文所示，即如何去解決這一問題

一、實體類

主類

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.*;
import java.time.LocalDateTime;
import java.util.List;

@Schema(description = "返回結(jié)果實體 Response VO")
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class MainDataRespVO extends MainDataBaseVO {

    @Schema(description = "主鍵ID")
    private Long id;

    @Schema(description = "創(chuàng)建時間")
    private LocalDateTime createTime;

    @Schema(description = "子類詳情列表")
    private List<SubDataRespVO> subDataList;
}

子類

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.*;
import java.time.LocalDateTime;

@Schema(description = "管理后臺 - 子類實體信息 Response VO")
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class SubDataRespVO extends SubDataBaseVO {

    @Schema(description = "主鍵ID")
    private Long subDataId;

    @Schema(description = "創(chuàng)建時間"D)
    private LocalDateTime createTime;
}

二、Mapper

List<MainDataRespVO> getMainDataList( @Param("localDateStart") String localDateStart,
                                            @Param("localDateEnd") String localDateEnd,
                                            @Param("shiftType") String shiftType,
                                            @Param("userId") Long userId);

三、XML

<?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="xxx.MainDataMapper">

    <resultMap id="selectShiftDateList" type="xxx.MainDataRespVO">
        <id property="id" column="id"/>
        <result property="workDate" column="work_date"/>
        <result property="createTime" column="create_time"/>
        <collection property="subDataList"
                    javaType="list"
                    ofType="xxx.vo.SubDataRespVO"
                    select="selectSubDataList"
                    column="{id=id, shiftType=shiftType, userId=userId}">
        </collection>
    </resultMap>

    <resultMap id="selectSubDataListMap" type="xxx.vo.SubDataRespVO">
        <result property="subDataId" column="id"/>
        <result property="createTime" column="create_time"/>
        <result property="userName" column="userName"/>
        <result property="shiftType" column="shift_type"/>
        <result property="userId" column="user_id"/>
        <result property="shiftDateId" column="shift_date_id"/>
    </resultMap>

    <select id="selectSubDataList" resultMap="selectSubDataListMap">
        select
        t2.id,
        t2.shift_date_id,
        t2.shift_type,
        t2.create_time,
        t2.user_id
        from sub_data t2
        where t2.main_data_id = #{id} and t2.deleted = 0
        <if test="shiftType!=null and shiftType != ''">
            and t2.shift_type = #{shiftType}
        </if>
        <if test="userId!=null and userId != ''">
            and t2.user_id =  #{userId}
        </if>
        order by t2.create_time asc
    </select>

    <select id="getMainDataList" resultMap="selectMainDataList">
        select
        t1.id,
        t1.work_date,
        t1.create_time,
        #{shiftType} as shiftType,  <!-- 將外部參數(shù)作為常量列 -->
        #{userId} as userId        <!-- 將外部參數(shù)作為常量列 -->
        from main_data t1
        where t1.deleted = 0
        <if test="localDateStart!=null and localDateStart != ''">
            and t1.work_date >=  #{localDateStart}
        </if>
        <if test="localDateEnd!=null and localDateEnd != ''">
            and #{localDateEnd} >= t1.work_date
        </if>
        order by t1.work_date asc
    </select>

</mapper>