版权声明

本作品采用知识共享署名 4.0 国际许可协议进行许可。 版权声明:本文由 低调小熊猫 发表于 低调小熊猫的博客 转载声明:自由转载-非商用-非衍生-保持署名,非商业转载请注明作者及出处,商业转载请联系作者本人qq:2696284032 文章链接:https://aodeng.cc/archives/mybatisgaoji

简介

聚集元素用来处理“一对多”的关系。需要指定映射的Java实体类的属性,属性的javaType(一般为ArrayList);列表中对象的类型ofType(Java实体类);对应的数据库表的列名称; 额,估计这样说大家听不懂,简单的意思就是把两张表联系起来,用于解决一些奇怪的需求

代码

1.定义简单的sql片段

<!-- 基本查询-->
	<sql id="vo_select">
		SELECT
			vo.expenseId,
			vo.projectId,
			vo.expenseUserId,
			sua.realName as expenseUserName,
			vo.expenseTime,
			vo.expenseTypeId,
			vo.beneficialDepartmentId,
			sd.name as beneficialDepartmentName,
			vo.currencyId,
			vo.money,
			vo.paperCheckerId,
			vo.paperCheckerTime,
			vo.paidDepartmentId,
			vo.paidUserId,
			vo.paidTime,
			vo.expenseNote,
			vo.description,
			vo.currentStatus,
			vo.invoiceAmount,
			vo.paperFlag,
			vo.recordFlag,
			vo.createUserId,
			vo.createTime
		FROM p_expense vo
		INNER JOIN sys_user_archive sua ON sua.userId=vo.expenseUserId
		INNER JOIN sys_department sd ON sd.departmentId=vo.beneficialDepartmentId
	</sql> 

2.查询条件拼接,返回resultMap

<select id="findAll" parameterType="Pasv" resultMap="pexpenseMap">
		<include refid="vo_select"/>
		WHERE 1=1
		<!--根据登陆人判断流程审核步骤的人是否一样 -->
		<if test="vo.userId !=null and vo.userId !=''">
		AND
		(
		 EXISTS(
		 	select 0 from p_expense_flow pef 
		 	where pef.flag=0 and pef.checkUserIds like concat('%#',#{vo.userId},'#%')
		 	AND pef.expenseID=vo.expenseId
		 )
		)
		</if>
		 
		<!-- 根据时间查询 -->
		<if test="vo.searchStartTime !=null and vo.searchStartTime !=''">
			AND vo.createTime >=DATE_FORMAT(#{vo.searchStartTime},'%Y-%m-%d')
		</if>
		<if test="vo.searchEndTime !=null and vo.searchEndTime !=''">
			AND vo.createTime <DATE_FORMAT(date_add(#{vo.searchEndTime}, INTERVAL 1 day),'%Y-%m-%d')
		</if>
		<!-- 注释掉是避免从我的任务中查询显示一条数据-->
		<!-- <if test="null!=vo.expenseId and ''!=vo.expenseId">
			AND vo.expenseId = #{vo.expenseId}
		</if> -->
		<if test="null!=vo.projectId and ''!=vo.projectId">
			AND vo.projectId = #{vo.projectId}
		</if>
		<if test="null!=vo.expenseUserId and ''!=vo.expenseUserId">
			AND vo.expenseUserId = #{vo.expenseUserId}
		</if>
		<if test="null!=vo.expenseTime and ''!=vo.expenseTime">
			AND vo.expenseTime = #{vo.expenseTime}
		</if>
		<if test="null!=vo.expenseTypeId and ''!=vo.expenseTypeId">
			AND vo.expenseTypeId = #{vo.expenseTypeId}
		</if>
		<if test="null!=vo.beneficialDepartmentId and ''!=vo.beneficialDepartmentId">
			AND vo.beneficialDepartmentId = #{vo.beneficialDepartmentId}
		</if>
		<if test="null!=vo.currencyId and ''!=vo.currencyId">
			AND vo.currencyId = #{vo.currencyId}
		</if>
		<if test="null!=vo.money and ''!=vo.money">
			AND vo.money = #{vo.money}
		</if>
		<!-- 根据报销人 -->
		<if test="vo.searchName !=null and vo.searchName !=''">
			AND sua.realName LIKE CONCAT(CONCAT('%', #{vo.searchName}),'%')
		</if>
		<if test="null!=vo.paperCheckerId and ''!=vo.paperCheckerId">
			AND vo.paperCheckerId = #{vo.paperCheckerId}
		</if>
		<if test="null!=vo.paperCheckerTime and ''!=vo.paperCheckerTime">
			AND vo.paperCheckerTime = #{vo.paperCheckerTime}
		</if>
		<if test="null!=vo.paidDepartmentId and ''!=vo.paidDepartmentId">
			AND vo.paidDepartmentId = #{vo.paidDepartmentId}
		</if>
		<if test="null!=vo.paidUserId and ''!=vo.paidUserId">
			AND vo.paidUserId = #{vo.paidUserId}
		</if>
		<if test="null!=vo.paidTime and ''!=vo.paidTime">
			AND vo.paidTime = #{vo.paidTime}
		</if>
		<if test="null!=vo.description and ''!=vo.description">
			AND vo.description = #{vo.description}
		</if>
		<if test="null!=vo.currentStatus and ''!=vo.currentStatus">
			AND vo.currentStatus = #{vo.currentStatus}
		</if>
		<if test="null!=vo.invoiceAmount and ''!=vo.invoiceAmount">
			AND vo.invoiceAmount = #{vo.invoiceAmount}
		</if>
		<if test="null!=vo.paperFlag and ''!=vo.paperFlag">
			AND vo.paperFlag = #{vo.paperFlag}
		</if>
		<if test="null!=vo.recordFlag and ''!=vo.recordFlag">
			AND vo.recordFlag = #{vo.recordFlag}
		</if>
		<if test="null!=vo.createUserId and ''!=vo.createUserId">
			AND vo.createUserId = #{vo.createUserId}
		</if>
		<if test="null!=vo.createTime and ''!=vo.createTime">
			AND vo.createTime = #{vo.createTime}
		</if>
		<if test="null!=vo.enabled and ''!=vo.enabled">
			AND vo.enabled = #{vo.enabled}
		</if>
		<!-- 根据报销金额范围查询 -->
		<if test="vo.searchStartMoney !=null and vo.searchStartMoney !='' and vo.searchStartMoney!=0">
		 and vo.money <![CDATA[ >= ]]> #{vo.searchStartMoney}
		</if>
		<if test="vo.searchEndMoney !=null and vo.searchEndMoney !='' and vo.searchEndMoney!=0">
		 and vo.money <![CDATA[ <= ]]> #{vo.searchEndMoney}
		</if>
	</select>

3.定义resultMap,用于上面返回的resultMap,重点在于collection,先看代码,我下面解释

<resultMap type="com.account.web.vo.project.PExpenseVo" id="pexpenseMap">
		<id column="expenseId" property="expenseId"/>
		<result column="projectId" property="projectId"/>
		<result column="expenseUserId" property="expenseUserId"/>
		<result column="expenseUserName" property="expenseUserName"/>
		<result column="expenseTime" property="expenseTime"/>
		<result column="expenseTypeId" property="expenseTypeId"/>
		<result column="beneficialDepartmentId" property="beneficialDepartmentId"/>
		<result column="beneficialDepartmentName" property="beneficialDepartmentName"/>
		<result column="currencyId" property="currencyId"/>
		<result column="money" property="money"/>
		<result column="paperCheckerId" property="paperCheckerId"/>
		<result column="paperCheckerTime" property="paperCheckerTime"/>
		<result column="paidDepartmentId" property="paidDepartmentId"/>
		<result column="paidUserId" property="paidUserId"/>
		<result column="paidTime" property="paidTime"/>
		<result column="expenseNote" property="expenseNote"/>
		<result column="description" property="description"/>
		<result column="currentStatus" property="currentStatus"/>
		<result column="invoiceAmount" property="invoiceAmount"/>
		<result column="paperFlag" property="paperFlag"/>
		<result column="recordFlag" property="recordFlag"/>
		<result column="createUserId" property="createUserId"/>
		<result column="createTime" property="createTime"/>
		<collection property="checkers"  ofType="com.account.web.vo.admin.system.SysUserArchiveVo"
		 select="findChecker3" column="{expenseId2=expenseId}">
		</collection>
	</resultMap>

4.定义collection用的sql片段

<select id="findChecker3" resultType="com.account.web.vo.admin.system.SysUserArchiveVo">
		select pef.expenseFlowId,sua.userId,sua.realName,pef.folwMomentId as folwMomentId
		from sys_user_archive sua
		INNER JOIN p_expense_flow pef ON pef.checkUserId =sua.userId 
		AND pef.expenseid=#{expenseId2}
	</select>

低调小熊猫独家解析

先给大家看一张图,我就是靠这一张图学会的,不要说图看不清楚,自己ctrl+ ok,这样聪明的估计就学会了,不会的看上面代码吧 额,还是解释几个地方

<collection property="checkers"  ofType="com.account.web.vo.admin.system.SysUserArchiveVo"
		 select="findChecker3" column="{expenseId2=expenseId}">
		</collection>

1.property="checkers"就是上面那个resultMap返回的实体类里面封装的一个集合属性。

2.ofType="com.account.web.vo.admin.system.SysUserArchiveVo"就是集合的类型

3.select="findChecker3"就是第四步使用的sql片段的id

4.column="{expenseId2=expenseId}"那,这个就比较重要了,expenseId就是上面resultMap的字段的名字,expenseId2就是下面sql片段里面条件接收值的字段