问题描述
<insertid="insertAttractionsBatch"parameterType="java.util.List">MERGEINTOTM_USER_CART_INFOT1USING(<foreachcollection="list"item="item"index="index"separator="union">select(SELECTT2.F_CART_IDFROMTM_USER_CART_INFOT2WHERENOTEXISTS(SELECT''FROMTM_USER_CART_MARKETT3WHERET3.F_CART_ID=T2.F_CART_ID)ANDT2.F_CHANNEL_NUM=#{item.channelNum}ANDT2.F_USER_ID=#{item.userId}ANDT2.F_GOODS_NUM=#{item.goodsNum}ANDT2.F_REGION_NUM=#{item.regionNum})F_CART_ID,#{item.goodsNum}F_GOODS_NUMfromdual</foreach>)TON(T1.F_CART_ID=T.F_CART_IDANDT1.F_GOODS_NUM=T.F_GOODS_NUM)WHENMATCHEDTHENUPDATESETT1.F_GOODS_COUNT=T1.F_GOODS_COUNT+#{item.goodsCount},T1.F_UPDATE_TIME=(SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL)WHENNOTMATCHEDTHENINSERT(F_CART_ID,F_CHANNEL_NUM,F_MERCHANT_NUM,F_USER_ID,F_GOODS_NUM,F_GOODS_COUNT,F_ADD_TIME,F_REGION_NUM)VALUES(#{item.cartId},#{item.channelNum},'',#{item.userId},#{item.goodsNum},#{item.goodsCount},(SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL),#{item.regionNum})</insert>update和insert部分参数应该怎么填写？我试过#{item.cartId}发现使用的是用一个值，直接#{cartId}会找不到。求大神指点。
解决方案
解决方案二：
{item.cartId},没在foreach里面？
解决方案三：
问题解决了。insert和update中所有的数据都需要从using中获取，这样就能达到批量mergeinto的效果了MERGEINTOTM_USER_CART_INFOT1USING(<foreachcollection="list"item="item"index="index"separator="union">SELECTCASEWHENTEMP1.F_CART_IDISNULLTHENTEMP.F_CART_IDELSETEMP1.F_CART_IDENDF_CART_ID,TEMP.F_GOODS_NUM,TEMP.F_USER_ID,TEMP.F_REGION_NUM,TEMP.F_CHANNEL_NUM,TEMP.F_GOODS_COUNTFROM(SELECT#{cartId}F_CART_ID,#{goodsNum}F_GOODS_NUM,#{userId}F_USER_ID,#{regionNum}F_REGION_NUM,#{channelNum}F_CHANNEL_NUM,#{goodsCount}F_GOODS_COUNTFROMDUAL)TEMPLEFTJOIN(SELECT*FROMTM_USER_CART_INFOT2WHERE1=1ANDT2.F_USER_ID=#{userId}ANDT2.F_GOODS_NUM=#{goodsNum}ANDT2.F_REGION_NUM=#{regionNum}ANDT2.F_CHANNEL_NUM=#{channelNum}andNOTEXISTS(SELECT''FROMTM_USER_CART_MARKETT3WHERET3.F_CART_ID=T2.F_CART_ID))TEMP1ONTEMP1.F_GOODS_NUM=TEMP.F_GOODS_NUM</foreach>)TON(T1.F_CART_ID=T.F_CART_ID)WHENMATCHEDTHENUPDATESETT1.F_GOODS_COUNT=T1.F_GOODS_COUNT+T.F_GOODS_COUNT,T1.F_UPDATE_TIME=(SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL)WHENNOTMATCHEDTHENINSERT(F_CART_ID,F_CHANNEL_NUM,F_MERCHANT_NUM,F_USER_ID,F_GOODS_NUM,F_GOODS_COUNT,F_ADD_TIME,F_REGION_NUM)VALUES(T.F_CART_ID,T.F_CHANNEL_NUM,'',T.F_USER_ID,T.F_GOODS_NUM,T.F_GOODS_COUNT,(SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL),T.F_REGION_NUM)
解决方案四：
上面写错了，#{xxxx}改成#{item.xxxx}