Mybatis的collection、association用法

简述: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>

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值