我用这个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 >= #{startDate} </if> <if test="endDate!=null and endDate!=‘‘"><!--结束时间--> and zec.CHECK_TIME <= #{endDate} </if> </where> </select>
这样就可以解决resultMap中的嵌套查询多参数无法传递的问题了,当然还有许多不足的地方,如果有好的建议和方法欢迎指出
用mybatis自带的resultMap进行查询,在resultMap中嵌套一个内查询时多个参数无法传递
原文：https://www.cnblogs.com/magepi/p/10382736.html