转载

Mybatis高级-resultMap之collection聚集

版权声明：本文由 低调小熊猫 发表于 低调小熊猫的博客

转载声明：自由转载-非商用-非衍生-保持署名，非商业转载请注明作者及出处，商业转载请联系作者本人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>

低调小熊猫独家解析

先给大家看一张图，我就是靠这一张图学会的

Mybatis高级-resultMap之collection聚集

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

原文  https://aodeng.cc/archives/mybatisgaoji
正文到此结束
所属分类： 编程技术 Java

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：676 人
  • 运行天数：4,408天
  • 最后更新：2024年11月21日23点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG