mybatis中collecton标签的两种使用方式区别

第一种：查询多次，查询部门的时候会根据部门的编号去查询部门下的高基表 n+1

<resultMap id="ClassifyVOResultMap" type="com.ly.business.entity.highbasetable.ClassifyVO">

<id column="GJBBH" jdbcType="VARCHAR" property="link" />

<result column="GJBMC" jdbcType="VARCHAR" property="title" />

</resultMap>

<resultMap id="DeptVOResultMap" type="com.ly.business.entity.highbasetable.ClassifyVO">

<id column="BMBH" jdbcType="VARCHAR" property="link" />

<result column="BMMC" jdbcType="VARCHAR" property="title" />

<collection property="children" column="BMBH" javaType="ArrayList" ofType="com.ly.business.entity.highbasetable.ClassifyVO"

select="selectDeptClassify">

</collection>

</resultMap>

 

<!-- 查询部门信息 -->

<select id="selectDeptAll" resultMap="DeptVOResultMap">

SELECT T.BMBH,T.BMMC FROM (SELECT BMBH,BMMC FROM LY_XTGL_BMXXB WHERE FBMBH ='000') T

</select>

<!-- 查询部门下的高基表信息 -->

<select id="selectDeptClassify" resultMap="ClassifyVOResultMap" parameterType="java.lang.String">

SELECT T.BMBH,T.BMMC,T2.GJBBH,T2.GJBMC FROM

(SELECT BMBH,BMMC FROM LY_XTGL_BMXXB WHERE FBMBH ='000') T LEFT JOIN LY_GJBB_GJBBMZJB T1 ON T.BMBH=T1.BMBH

LEFT JOIN LY_GJBB_GJBXX T2 ON T1.GJBBH = T2.GJBBH

WHERE T.BMBH = #{BMBH} ORDER BY T.BMBH

</select>

第二种：查询一次

<resultMap id="DeptVOResultMap" type="com.ly.business.entity.highbasetable.ClassifyVO">

<id column="BMBH" jdbcType="VARCHAR" property="link" />

<result column="BMMC" jdbcType="VARCHAR" property="title" />

<collection property="children" javaType="ArrayList" ofType="com.ly.business.entity.highbasetable.ClassifyVO">

<id column="GJBBH" jdbcType="VARCHAR" property="link" />

<result column="GJBMC" jdbcType="VARCHAR" property="title" />

</collection>

</resultMap>

<!-- 查询部门分类下高基表信息 -->

<select id="selectDeptClassify" resultMap="DeptVOResultMap" parameterType="java.lang.String">

SELECT T.BMBH,T.BMMC,T2.GJBBH,T2.GJBMC FROM

(SELECT BMBH,BMMC FROM LY_XTGL_BMXXB WHERE FBMBH ='000') T LEFT JOIN LY_GJBB_GJBBMZJB T1 ON T.BMBH=T1.BMBH

LEFT JOIN LY_GJBB_GJBXX T2 ON T1.GJBBH = T2.GJBBH

ORDER BY T.BMBH

</select>