• mybatis的resultMap与association等联合查询,多个参数无法传递


    我用这个mybatis自带的resultMap是因为它对查询树状结构,组织架构有良好的支持而不需要去做递归或其它繁琐操作,

    直接可以一步到位

    进入正题

    <resultMap id="Organ" type="com.pskj.GSLZ.pojo.zfzb.Organ" >
            <result column="OR_ID" property="OR_ID" jdbcType="VARCHAR"></result>
            <result column="OR_NAME" property="OR_NAME" jdbcType="VARCHAR"></result>
            <result column="PARENT_ID" property="PARENT_ID" jdbcType="VARCHAR"></result>
            <association column="OR_ID" property="count" javaType="Integer" select="count"/>
            <collection column="OR_ID"  property="subOrgan" ofType="Organ" javaType="java.util.ArrayList" select="selectChildren"></collection>
        </resultMap>
    
        <!--查询一级组织架构-->
        <select id="checkList" parameterType="pd" resultMap="Organ" useCache="false">
            SELECT *
            FROM sys_organ t
            WHERE t.OR_ID = #{OR_ID} AND t.OR_STATE = '2'
        </select>
    
        <!--查询下级组织架构-->
        <select id="selectChildren" parameterType="pd" resultMap="Organ"  useCache="false">
            SELECT *
            FROM sys_organ t WHERE t.PARENT_ID = #{OR_ID} AND t.OR_STATE = '2'
        </select>
    
        <!--统计各支队故障数量-->
        <select id="count" parameterType="pd" resultType="Integer" useCache="false">
            SELECT IFNULL(sum(num),0) count from zfzb_subcheck zsk LEFT JOIN zfzb_equip_check zec on zec.EC_ID=zsk.EC_ID
            <where>
                zec.STATE='1'
                <!--查询下级组织所有id,这是写的一个MySQL查询函数-->
                and ORGAN_ID in(SELECT OR_ID from sys_organ where OR_STATE='2' and FIND_IN_SET(OR_ID,findOrgan(#{OR_ID})))
            </where>
        </select>

    resultMap怎么用应该不用我去具体介绍吧,百度一下你就知道,主要看<association/>,<collection/>中的select对应的查询,

    还用我的parameterType="pd"中pd用的map封装的,可以把它认为一个map

    当然我没展示全部,主要就是查询组织架构的树状结构及统计

     <!--统计各支队故障数量-->
        <select id="count" parameterType="pd" resultType="Integer" useCache="false">
            SELECT IFNULL(sum(num),0) count from zfzb_subcheck zsk LEFT JOIN zfzb_equip_check zec on zec.EC_ID=zsk.EC_ID
            <where>
                zec.STATE='1'
                <!--查询下级组织所有id,这是写的一个MySQL查询函数-->
                and ORGAN_ID in(SELECT OR_ID from sys_organ where OR_STATE='2' and FIND_IN_SET(OR_ID,findOrgan(#{OR_ID})))
                <if test="OR_ID!=null and OR_ID!=''"><!-- 1-->
    
                </if>
            </where>
        </select>

    用组织id做if判断

    出现了报错

    <resultMap id="Organ" type="com.pskj.GSLZ.pojo.zfzb.Organ" >
            <result column="OR_ID" property="OR_ID" jdbcType="VARCHAR"></result>
            <result column="OR_NAME" property="OR_NAME" jdbcType="VARCHAR"></result>
            <result column="PARENT_ID" property="PARENT_ID" jdbcType="VARCHAR"></result>
            <association column="{OR_ID=OR_ID}" property="count" javaType="Integer" select="count"/>
            <collection column="OR_ID"  property="subOrgan" ofType="Organ" javaType="java.util.ArrayList" select="selectChildren"></collection>
        </resultMap>

    这里主要将association的column改成了多参数方式传递就可以正常查询了,随后我试验了OR_NAME等字段都可以正常查询,但有时候我们需要时间查询,关键字查询怎么办,

    将startDate和endDate两个时间查询的参数放进去试试

    <resultMap id="Organ" type="com.pskj.GSLZ.pojo.zfzb.Organ" >
            <result column="OR_ID" property="OR_ID" jdbcType="VARCHAR"></result>
            <result column="OR_NAME" property="OR_NAME" jdbcType="VARCHAR"></result>
            <result column="PARENT_ID" property="PARENT_ID" jdbcType="VARCHAR"></result>
            <!-- 这里我想实现将时间查询的参数能够传递至统计查询里,并且每次遍历时参数的值不会改变-->
            <association column="{OR_ID=OR_ID,startDate=startDate,endDate=endDate}" property="count" javaType="Integer" select="count"/>
            <collection column="OR_ID"  property="subOrgan" ofType="Organ" javaType="java.util.ArrayList" select="selectChildren"></collection>
        </resultMap>

    直接报错,startDate找不到,这样一想也确实,startDate和endDate本来就不是组织实体类里的字段,换一种思路

       <resultMap id="Organ" type="com.pskj.GSLZ.pojo.zfzb.Organ" >
            <result column="OR_ID" property="OR_ID" jdbcType="VARCHAR"></result>
            <result column="OR_NAME" property="OR_NAME" jdbcType="VARCHAR"></result>
            <result column="PARENT_ID" property="PARENT_ID" jdbcType="VARCHAR"></result>
            <!-- 这里我想实现将时间查询的参数能够传递至统计查询里,并且每次遍历时参数的值不会改变-->
            <association column="{OR_ID=OR_ID,startDate=startDate,endDate=endDate}" property="count" javaType="Integer" select="count"/>
            <collection column="OR_ID"  property="subOrgan" ofType="Organ" javaType="java.util.ArrayList" select="selectChildren"></collection>
        </resultMap>
    
        <!--查询一级组织架构   startDate和endDate为构造的虚拟字段-->
        <select id="checkList" parameterType="pd" resultMap="Organ" useCache="false">
            SELECT OR_ID,OR_NAME,PARENT_ID,
            IFNULL(#{startDate},'') startDate,
            IFNULL(#{endDate},'') endDate
            FROM sys_organ t
            WHERE t.OR_ID = #{OR_ID} AND t.OR_STATE = '2'
        </select>
    
        <!--查询下级组织架构   这里的startDate和endDate是接受自一级组织传来的值并一级一级往下传递-->
        <select id="selectChildren" parameterType="pd" resultMap="Organ"  useCache="false">
            SELECT OR_ID,OR_NAME,PARENT_ID,
            IFNULL(#{startDate},'') startDate,
            IFNULL(#{endDate},'') endDate
            FROM sys_organ t WHERE t.PARENT_ID = #{OR_ID} AND t.OR_STATE = '2'
        </select>
    
        <!--统计各支队故障数量-->
        <select id="count" parameterType="pd" resultType="Integer" useCache="false">
            SELECT IFNULL(sum(num),0) count from zfzb_subcheck zsk LEFT JOIN zfzb_equip_check zec on zec.EC_ID=zsk.EC_ID
            <where>
                zec.STATE='1'
                <!--查询下级组织所有id,这是写的一个MySQL查询函数-->
                and ORGAN_ID in(SELECT OR_ID from sys_organ where OR_STATE='2' and FIND_IN_SET(OR_ID,findOrgan(#{OR_ID})))
                <if test="startDate!=null and startDate!=''"><!--开始时间 -->
                    and zec.CHECK_TIME  &gt;= #{startDate}
                </if>
                <if test="endDate!=null and endDate!=''"><!--结束时间-->
                    and zec.CHECK_TIME &lt;= #{endDate}
                </if>
            </where>
        </select>

    这样就可以解决resultMap中的嵌套查询多参数无法传递的问题了,当然还有许多不足的地方,如果有好的建议和方法欢迎指出

  • 相关阅读:
    使用Vim写LaTeX代码(Vim+Vimtex+Skim)
    如何安装vim自动补全插件YouCompleteMe(YCM)
    我的vimrc配置
    囚徒问题(100 prisoners problem)的python验证
    tomcat下运行多个项目
    转使用chrome命令行:disable-web-security 实现浏览器跨域
    Spring Session
    nodejs 之 nvm和pm2
    chrome地址栏命令
    Koa2和相关资料
  • 原文地址:https://www.cnblogs.com/magepi/p/10382736.html
Copyright © 2020-2023  润新知