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中的嵌套查询多参数无法传递的问题了,当然还有许多不足的地方,如果有好的建议和方法欢迎指出

转载于:https://www.cnblogs.com/magepi/p/10382736.html