我要评论前言
mybatis的xml文件编写动态sql是从mapper中获取传入的参数，但是如果是嵌套的子查询中，子查询动态sql所需的参数不能像常规的那样直接从mapper中获取， 因为嵌套子查询中能获取的传参仅能来源于主查询中的结果，如下文所示，即如何去解决这一问题
一、实体类
1、主类
import io.swagger.v3.oas.annotations.media.schema;
import lombok.*;
import java.time.localdatetime;
import java.util.list;
@schema(description = "返回结果实体 response vo")
@data
@equalsandhashcode(callsuper = true)
@tostring(callsuper = true)
public class maindatarespvo extends maindatabasevo {
@schema(description = "主键id")
private long id;
@schema(description = "创建时间")
private localdatetime createtime;
@schema(description = "子类详情列表")
private list<subdatarespvo> subdatalist;
}2、子类
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 = "创建时间"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, <!-- 将外部参数作为常量列 -->
#{userid} as userid <!-- 将外部参数作为常量列 -->
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>四、详解
如下图所示，将mapper中需要传入子查询中的动态sql参数，放到主查询的查询列表中去，取别名，别名即是传入到子查询中的动态sql参数
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论