Mybatis高级-resultMap之collection聚集

以你之姓@ 2022-02-04 08:23 307阅读 0赞

From: [https://aodeng.cc/archives/mybatisgaoji][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.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，先看代码，我下面解释**

**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+**  
![mybatis20180926102344641.jpg][]  
**ok，这样聪明的估计就学会了，不会的看上面代码吧  
额，还是解释几个地方**

**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片段里面条件接收值的字段**

[https_aodeng.cc_archives_mybatisgaoji]: https://aodeng.cc/archives/mybatisgaoji
[mybatis20180926102344641.jpg]: https://aodeng.cc/upload/2018/8/mybatis20180926102344641.jpg