Mybatis的collection、association用法

最新推荐文章于 2024-08-11 20:12:40 发布

milkhq

最新推荐文章于 2024-08-11 20:12:40 发布

阅读量634

点赞数

分类专栏： mybatis 文章标签： Mybatis collection association

本文链接：https://blog.csdn.net/milkhq/article/details/79727610

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

简述：mapper.xml实现1对1、1对多查询时，返回的resultmap需要用到association和collection。

示例：

1）1对1写法

	<resultMap id="BaseResultMap" type="com.hb.model.stock.vo.Stock">
		<result property="id" column="id" />
		<result property="distributorName" column="DISTRIBUTOR_NAME" />
		<result property="distributorId" column="DISTRIBUTOR_ID" />
		... ...
	</resultMap>
	<resultMap id="extBaseResultMap" type="com.hb.model.stock.vo.Stock" extends="BaseResultMap">  
		<association property="oilDistributorInfo"  column="distributorId"  select="getDistributorInfo" > </association>
	</resultMap>
	
	<select id="queryPage" parameterType="com.hb.model.stock.vo.Stock" resultMap="extBaseResultMap" resultType="java.util.List">
	  	<![CDATA[
			SELECT 
		]]>
		<include refid="queryColumns" />
		<![CDATA[
			FROM t_stock WHERE 1=1
		]]>
		<include refid="dynamicWhere" />
		<include refid="dynamicWhere2" />
		order by t_stock.id desc
	</select>
	
	<select id="getDistributorInfo"  resultMap="OilDistributorInfoResultMap">
	    <![CDATA[
	    	select 
        	hbs_oil_distributor_info.distributor_id AS distributorId ,
        	... ...
        	from hbs_oil_distributor_info
   				 left join em_region p on p.EM_REGION_ID = hbs_oil_distributor_info.PROVINCE_ID
				 left join em_region c on c.EM_REGION_ID = hbs_oil_distributor_info.CITY_ID
				 left join em_region d on d.EM_REGION_ID = hbs_oil_distributor_info.DIST_ID
        	where 
				hbs_oil_distributor_info.distributor_id = #{distributorId}
	    ]]>
	</select>

2）1对多写法

方式1：

	<resultMap id="BaseResultMap" type="com.unionpay.crm.mchntpool.dao.entity.TblMrmMarketProjectInf">
		<id column="market_id" jdbcType="BIGINT" property="marketId" />
		<result column="market_no" jdbcType="VARCHAR" property="marketNo" />
		... ...
	</resultMap>
	
	<resultMap id="extResultWithActivitys" type="com.unionpay.crm.mchntpool.dao.entity.vo.TblMrmMarketProjectInfVo"  extends="BaseResultMap">
		<collection property="tblMrmMarketActivityInfList" column="{xx=market_id}" select="com.unionpay.crm.mchntpool.dao.mapper.TblMrmMarketActivityInfMapper.selectByConditionBase"></collection>
	</resultMap>
	
	<select id="queryMarketProjectWithActivitys" resultMap="extResultWithActivitys" parameterType="com.unionpay.crm.mchntpool.dao.entity.TblMrmMarketProjectInf">
		select
			<include refid="Base_Column_List" />
		from 
			tbl_mrm_market_project_inf 
		<trim prefix="where" prefixOverrides="and |or">
			<if test="marketNo != null and marketNo != '' ">
				and tbl_mrm_market_project_inf.market_no = #{marketNo}
			</if>
			<if test="marketNm != null and marketNm != '' ">
				and tbl_mrm_market_project_inf.market_nm like concat(concat('%',#{marketNm}),'%')
			</if>
			<if test="marketStartDt != null and marketStartDt != ''">
				and <![CDATA[tbl_mrm_market_project_inf.market_start_dt >= #{marketStartDt} ]]>
			</if>
			<if test="marketEndDt != null and marketEndDt != ''">
				and <![CDATA[tbl_mrm_market_project_inf.market_end_dt <= #{marketEndDt} ]]>
			</if>
		</trim>
	</select>
  
	(需注意：下面这个子查询里不要有parameterType，不写的情况下mybatis会自动解析类型，写了反而会报java.lang.NoSuchMethodException)
  	<select id="selectByConditionBase" resultMap="BaseResultMap" >
		select
			<include refid="Base_Column_List" />
		from 
			tbl_mrm_market_activity_inf
		where 
			tbl_mrm_market_activity_inf.market_id = #{xx,jdbcType=BIGINT}
	</select>

方式2：

	<resultMap id="BaseResultMap" type="com.nfswuxi.recruitment.access.model.RecruitmentRole" >
		<id column="role_id" property="roleId" jdbcType="VARCHAR" />
		<result column="role_name" property="roleName" jdbcType="VARCHAR" />
		<result column="description" property="description" jdbcType="VARCHAR" />
	</resultMap>
  
	<resultMap type="com.nfswuxi.recruitment.access.model.RecruitmentRole" id="roleWithOpcodes" extends="com.nfswuxi.recruitment.access.dao.RecruitmentRoleMapper.BaseResultMap">
		<collection property="operations" javaType="ArrayList" ofType="com.nfswuxi.recruitment.access.model.RecruitmentOperation" resultMap="com.nfswuxi.recruitment.access.dao.RecruitmentOperationMapper.BaseResultMap"/>
	</resultMap>
	
	（需注意：当两个集合里除了关联字段外存在重名的字段，则需要取别名，否则查询结果子集合只有一条记录）
	<select id="selectRoleAndOpcodeByPrimaryKey" resultMap="roleWithOpcodes" parameterType="java.lang.String" >
                select
      	            a.*, c.*
                  from 
      	            recruitment_Role a
                  left join
      	            recruitment_RoleOperation b
                  on 
      	            a.role_id = b.role_id
                  left join
      	            recruitment_Operation c
                  on
      	            b.opcode = c.opcode
   	          where
   	  	    a.role_id = #{value,jdbcType=VARCHAR}
	</select>