Mybatis高级功能

最新推荐文章于 2023-03-23 17:56:31 发布

qianzhi008

最新推荐文章于 2023-03-23 17:56:31 发布

阅读量161

点赞数

分类专栏： java 文章标签： java 数据库

本文链接：https://blog.csdn.net/qianzhi008/article/details/84651235

版权

java 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

应用场景：
应用所取的结果集中需要嵌入别的SQL的结果集。


<resultMap type="com.kevin.wallet.dto.CardsDetailResponse" id="cardsDetailResponse"> 
    	<result property="walletID" column="wallet_id"/>
    	<result property="totalAmount" column="totalAmount"/>
    	<result property="pageNumber" column=""/>
    	<result property="totalNumber" column="totalNumber"/>
    	[color=red]<association property="bailianAmount"  column="{id=WALLET_ID}" select="getBailianAmount" />
    	<association property="okAmount" column="{id=WALLET_ID}" select="okAmount" />[/color]
    	[color=yellow]<collection property="cards" ofType="com.kevin.wallet.dto.ListCardInforResponse" >
    	 <result property="cardNo" column="CARD_ID"/>  
		   <result property="cardName" column="CARD_NAME"/>  
		   <result property="balanceValue" column="BALANCE_VALUE"/>  
		   <result property="validateTill" column="FROZEN_AT"/>  
		   <result property="cardType" column="TYPE"/>  
		   <result property="isForbidden" column="IS_FORBIDDEN"/>  
		   <result property="alias" column="CARD_ALIAS"/>  
		   <result property="status" column="STATUS"/>
		   <result property="order" column="CARD_ORDER"/>  
    	</collection>[/color]

    </resultMap>


<select id="getBailianAmount" parameterType="java.util.Map" resultType="java.math.BigDecimal">

     	 SELECT  
            SUM(BALANCE_VALUE) as BAILIANAMOUNT 
         FROM( 
          SELECT 
            *
          FROM
            AVALIABLE_MEMBER_CARD member
          WHERE
            member.WALLET_ID = [color=red]#{id}[/color]
          UNION
          SELECT 
            *	
          FROM
            AVALIABLE_PRODUCT_CARD product
          where
            product.WALLET_ID = [color=red] #{id}[/color]
          )where TYPE=1 or TYPE=3 
          GROUP BY  WALLET_ID

    </select>

[b]association [/b]是对应数据库中一对一的关系，它会将getBailianAmount sql语句的返回值赋给bailianAmount这个属性。column这个属性用于设置getBailianAmount sql语句的传入参数。[color=red]column="{id=WALLET_ID}" [/color]意思是将WALLET_ID列作为getBailianAmount sql语句的参数，id对应map的key， sql语句通过#{id}来获取该key的值。