Mybatis ResultMap传递参数给子Collection对应查询的ResultMap中

一、问题描述最近在做项目时，需要使用Mybatis的ResultMap高级映射查询“校区 - 楼栋 - 楼层”树结构的数据，前端提供很多过滤条件都是子查询楼层的一些字段属性，并不是所有的参数都是最外层查询的过滤条件，但是ResultMap本身不支持将参数从外层的查询一直传递到子查询。举个简单的例子，mapper接口传递了一些参数到mapper.xml中，如果不额外处理，这些参数只能在最外面...

每天都要进步一点点

8388人浏览 · 2019-11-12 15:57:40

每天都要进步一点点 · 2019-11-12 15:57:40 发布

一、问题描述

最近在做项目时，需要使用Mybatis的ResultMap高级映射查询“校区 - 楼栋 - 楼层”树结构的数据，前端提供很多过滤条件都是子查询楼层的一些字段属性，并不是所有的参数都是最外层查询的过滤条件，但是ResultMap本身不支持将参数从外层的查询一直传递到子查询。

举个简单的例子，mapper接口传递了一些参数到mapper.xml中，如果不额外处理，这些参数只能在最外面一层resultMap对应的sql中获取到进行使用，那么很多情况下我们是resultMap下面还包含了collection子查询，子查询也用到这些过滤条件，本文总结的就是如何将参数一直传递到最后面一层查询。

二、解决方法

在外层查询的时候，将参数作为返回结果的一列放在sql中，使用resultMap的collection的column列传递参数时一并将我们构造的假的结果列传递过去，这样子查询就可以使用到外层传递的参数，以此类推，不管有多少层嵌套查询，最外层的参数都可以一直传递到最下面一层查询。具体见代码：

【a】Mapper.java接口

public List<CwDataSourceTzVO> getCwTreeDataSource(@Param("xqids") String xqidFilterStr, @Param("ldids") String ldidFilterStr,
	        @Param("filterParamMap") Map<String, Object> filterParamMap);

【b】Mapper.xml完整代码：通过ResultMap查询树结构数据 "校区 - 楼栋 - 楼层"

<select id="getCwTreeDataSource" resultMap="xqResultMap">
		select r.*,
		       case
		         when '${ldids}' IS NULL then
		          NULL
		         else
		          '${ldids}'
		       end as ldids,
		       case
		         when '${filterParamMap.cyid}' IS NULL then
		          NULL
		         else
		          '${filterParamMap.cyid}'
		       end as cyid,
		       case
		         when '${filterParamMap.type}' IS NULL then
		          NULL
		         else
		          '${filterParamMap.type}'
		       end as type,
		       case
		         when '${filterParamMap.ssnj}' IS NULL then
		          NULL
		         else
		          '${filterParamMap.ssnj}'
		       end as ssnj,
		       'xq' as cj,
		       r.label as title
		  from (select distinct t1.XQID as value,
		                        t1.XQMC as label,
		                        t1.PXH,
		                        nvl(sum(case
		                                  when (t6.rzzt = '2' or t6.rzzt = '1') then
		                                   1
		                                  else
		                                   0
		                                end),
		                            0) as yrzcws,
		                        nvl(count(t5.pkid), 0) as zcws
		          from vc_zhxg_xtgl_jcsj_xqxx t1
		          left join zhxg_gy_jcsj_ldxx t2
		            on t2.xqid = t1.XQID
		          left join zhxg_gy_jcsj_lcxx t3
		            on t2.pkid = t3.ldid
		          left join zhxg_gy_jcsj_fjxx t4
		            on t3.pkid = t4.lcid
		          left join zhxg_gy_jcsj_cwxx t5
		            on t4.pkid = t5.fjid
		          left join zhxg_gy_ssfp_cwfp t6
		            on t6.cwid = t5.pkid
		         where 1 = 1 
		         <if test="filterParamMap.cyid != null and filterParamMap.cyid != ''">
		         	<choose>
		         		<when test="filterParamMap.type == '1'.toString()">
		         			and t6.bjid in 
		         			<foreach item="item" index="index" collection="filterParamMap.cyid.split(',')"  open="(" separator="," close=")">
		                		#{item}
		    				</foreach>
		         		</when>
		         		<when test="filterParamMap.type == '2'.toString()">
		         			and t6.zyid in 
		         			<foreach item="item" index="index" collection="filterParamMap.cyid.split(',')"  open="(" separator="," close=")">
		                		#{item}
		    				</foreach>
		         		</when>
		         		<when test="filterParamMap.type == '3'.toString()">
		         			and t6.bmid in 
		         			<foreach item="item" index="index" collection="filterParamMap.cyid.split(',')"  open="(" separator="," close=")">
		                		#{item}
		    				</foreach>
		         		</when>
		         	</choose>
		         </if>
		         <if test="filterParamMap.ssnj != null and filterParamMap.ssnj != ''">
		         	and t6.nj in 
		         	<foreach item="item" index="index" collection="filterParamMap.ssnj.split(',')"  open="(" separator="," close=")">
                		#{item}
    				</foreach>
		         </if>
		         <if test="xqids != null and xqids != ''">
					and t1.XQID in  
					<foreach item="xqid" index="index" collection="xqids.split(',')"  open="(" separator="," close=")">
		                #{xqid}
		    		</foreach>
				</if>
		         group by t1.XQID, t1.XQMC, t1.PXH) r
		 		 order by r.pxh
	</select>

	<resultMap id="xqResultMap"
		type="com.ly.cloud.vo.ssfp.visualAllocation.CwDataSourceTzVO">
		<id property="value" column="value"></id>
		<result property="label" column="label"></result>
		<result property="yrzcws" column="yrzcws"></result>
		<result property="zcws" column="zcws"></result>
		<collection property="children"
			ofType="com.ly.cloud.vo.ssfp.visualAllocation.CwDataSourceTzVO"
			column="{value=value,ldids=ldids,cyid=cyid,type=type,ssnj=ssnj}" select="getLdByXqid">
		</collection>
	</resultMap>

	<!-- 根据校区查询楼栋信息 -->
	<select id="getLdByXqid" resultMap="ldResultMap">
		select r.*,
		       case
		         when '${cyid}' IS NULL then
		          NULL
		         else
		          '${cyid}'
		       end as cyid,
		       case
		         when '${type}' IS NULL then
		          NULL
		         else
		          '${type}'
		       end as type,
		       case
		         when '${ssnj}' IS NULL then
		          NULL
		         else
		          '${ssnj}'
		       end as ssnj,
		       'ld' as cj,
		       r.xqmc || ' - ' || r.label as title
		  from (select t1.pkid as value,
		               t1.ldmc as label,
		               t1.pxh,
		               nvl(sum(case
		                         when (t5.rzzt = '2' or t5.rzzt = '1') then
		                          1
		                         else
		                          0
		                       end),
		                   0) as yrzcws,
		               nvl(count(t4.pkid), 0) as zcws,
		               t6.XQMC
		          from zhxg_gy_jcsj_ldxx t1
		          left join vc_zhxg_xtgl_jcsj_xqxx t6
		            on t1.xqid = t6.XQID
		          left join zhxg_gy_jcsj_lcxx t2
		            on t1.pkid = t2.ldid
		          left join zhxg_gy_jcsj_fjxx t3
		            on t2.pkid = t3.lcid
		          left join zhxg_gy_jcsj_cwxx t4
		            on t3.pkid = t4.fjid
		          left join zhxg_gy_ssfp_cwfp t5
		            on t4.pkid = t5.cwid
		         where 1 = 1
				 and t1.xqid = #{value}
				 <if test="cyid != null and cyid != ''">
		         	<choose>
		         		<when test="type == '1'.toString()">
		         			and t5.bjid in 
		         			<foreach item="item" index="index" collection="cyid.split(',')"  open="(" separator="," close=")">
		                		#{item}
		    				</foreach>
		         		</when>
		         		<when test="type == '2'.toString()">
		         			and t5.zyid in 
		         			<foreach item="item" index="index" collection="cyid.split(',')"  open="(" separator="," close=")">
		                		#{item}
		    				</foreach>
		         		</when>
		         		<when test="type == '3'.toString()">
		         			and t5.bmid in 
		         			<foreach item="item" index="index" collection="cyid.split(',')"  open="(" separator="," close=")">
		                		#{item}
		    				</foreach>
		         		</when>
		         	</choose>
		         </if>
		         <if test="ssnj != null and ssnj != ''">
		         	and t5.nj in 
		         	<foreach item="item" index="index" collection="ssnj.split(',')"  open="(" separator="," close=")">
                		#{item}
    				</foreach>
		         </if>
				 <if test="ldids != null and ldids != ''">
					and t1.pkid in  
					<foreach item="ldid" index="index" collection="ldids.split(',')"  open="(" separator="," close=")">
		                #{ldid}
		    		</foreach>
				</if>
		         group by t1.pkid, t1.ldmc, t1.pxh,t6.XQMC) r
		         order by r.pxh
	</select>

	<resultMap id="ldResultMap"
		type="com.ly.cloud.vo.ssfp.visualAllocation.CwDataSourceTzVO">
		<id property="value" column="value"></id>
		<result property="label" column="label"></result>
		<result property="yrzcws" column="yrzcws"></result>
		<result property="zcws" column="zcws"></result>
		<collection property="children"
			ofType="com.ly.cloud.vo.ssfp.visualAllocation.CwDataSourceTzVO"
			column="{value=value,,cyid=cyid,type=type,ssnj=ssnj}" select="getLcByLdid">
		</collection>
	</resultMap>

	<!-- 根据楼栋查询楼层信息 -->
	<select id="getLcByLdid" resultType="com.ly.cloud.vo.ssfp.visualAllocation.CwDataSourceTzVO">
      	select t1.pkid as value,
		       t1.lcmc as label,
		       t1.pxh,
		       nvl(sum(case
		                 when (t5.rzzt = '2' or t5.rzzt = '1') then
		                  1
		                 else
		                  0
		               end),
		           0) as yrzcws,
		       nvl(count(t4.pkid), 0) as zcws,
		       'lc' as cj,
		       t6.XQMC || ' - ' || t2.ldmc || ' - ' || t1.lcmc as title
		  from zhxg_gy_jcsj_lcxx t1
		  left join zhxg_gy_jcsj_ldxx t2
		    on t1.ldid = t2.pkid
		  left join vc_zhxg_xtgl_jcsj_xqxx t6
    		on t2.xqid = t6.XQID
		  left join zhxg_gy_jcsj_fjxx t3
		    on t1.pkid = t3.lcid
		  left join zhxg_gy_jcsj_cwxx t4
		    on t3.pkid = t4.fjid
		  left join zhxg_gy_ssfp_cwfp t5
		    on t4.pkid = t5.cwid
		 where 1 = 1
		 <if test="cyid != null and cyid != ''">
         	<choose>
         		<when test="type == '1'.toString()">
         			and t5.bjid in 
         			<foreach item="item" index="index" collection="cyid.split(',')"  open="(" separator="," close=")">
                		#{item}
    				</foreach>
         		</when>
         		<when test="type == '2'.toString()">
         			and t5.zyid in 
         			<foreach item="item" index="index" collection="cyid.split(',')"  open="(" separator="," close=")">
                		#{item}
    				</foreach>
         		</when>
         		<when test="type == '3'.toString()">
         			and t5.bmid in 
         			<foreach item="item" index="index" collection="cyid.split(',')"  open="(" separator="," close=")">
                		#{item}
    				</foreach>
         		</when>
         	</choose>
         </if>
         <if test="ssnj != null and ssnj != ''">
         	and t5.nj in 
         	<foreach item="item" index="index" collection="ssnj.split(',')"  open="(" separator="," close=")">
           		#{item}
			</foreach>
         </if>
		 and t1.ldid = #{value}
		 group by t1.pkid, t1.lcmc, t1.pxh,t6.XQMC,t2.ldmc
		 order by t1.pxh
    </select>

【c】分析

如上，外层查询并没有使用到ldids这个过滤条件，ldids是在ResultMap中collection中子查询的过滤条件，我们这里构造一个假的结果列，

case
    when '${ldids}' IS NULL then
    NULL
else
    '${ldids}'
end as ldids,

然后传递ldids参数给子查询getLdByXqid：

<collection property="children"
	ofType="com.ly.cloud.vo.ssfp.visualAllocation.CwDataSourceTzVO"
	column="{value=value,ldids=ldids,cyid=cyid,type=type,ssnj=ssnj}" select="getLdByXqid">
</collection>

这样在这个方法中就可以使用到ldids这个过滤条件了：

<if test="ldids != null and ldids != ''">
	and t1.pkid in  
	<foreach item="ldid" index="index" collection="ldids.split(',')"  open="(" separator="," close=")">
		#{ldid}
	</foreach>
</if>

以此类推，getldByXqid还能将参数继续传递下去，直到最下面一层查询。

注意：这里返回的假结果列不能是List类型的值，我们可以先将他们转换为String再进行传递，千万别踩坑，否则会报错(因为在数据库中，没有和List想对应的数据类型)，笔者已经踩过了，切记切记。好了，就总结到这里，方便后面忘记了回来查看。

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

基于分布式模型预测控制的多智能体点对点过渡轨迹生成研究（Matlab代码实现）

随着多智能体系统（MAS）在无人机编队、自动驾驶车队、机器人协同操作等领域的广泛应用，如何实现高效、安全、协同的点对点轨迹生成成为核心挑战。分布式模型预测控制（DMPC）通过将集中式优化问题分解为局部子问题，结合预测模型与分布式通信机制，为大规模多智能体系统的轨迹规划提供了有效解决方案。本文系统梳理了DMPC在多智能体点对点过渡中的关键技术，包括模型构建、约束处理、协调机制及优化算法，分析了其可扩