MyBatis批量操作SQL

悠悠 2022-05-14 02:41 270阅读 0赞

### 批量插入 ###

> 适用于MySQL

<insert id="addGroupInfoBatch" useGeneratedKeys="true" parameterType="java.util.List">
         INSERT INTO group_info (userid,liveuserid,groupid,nickname,headpic)
         VALUES
         <foreach collection="list" item="info" index="index"
             separator=",">
             (#{info.userid},#{info.liveuserid},#{info.groupid},#{info.nickname},#{info.headpic})
         </foreach>
     </insert>

## 批量更新 ##

> 适用于Oracle

<update id="updateRookieCustomer" parameterType="map">
    	<if test="birdList!=null and birdList.size()>0">
        	UPDATE TAB_ROOKIECUSTOMER SET ISPUSH = 1,pushdate = sysdate
        	<where>
        		<foreach collection="birdList" item="cus" open="( " separator=") or (" close=" )">
    	    		SELLERID = #{cus.custNo} AND BRANCHCODE = #{cus.orgCode}
    	    	</foreach>
        	</where>
       	</if>
     </update>

## 批量更新 ##

> 适用于Oracle

<update id="updateCustomer" parameterType="map" >
        <if test="custNo!=null and custNo.size()>0">
            update tab_vip客户资料表 set ISPUSH = 1,pushdate = sysdate
            WHERE 客户编号 in
            <foreach item="item" index="index" collection="custNo" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </update>

## 批量更新 ##

> 适用于Oracle

<update id="updateRookieCustomer" parameterType="map">
    	<if test="birdList!=null and birdList.size()>0">
    		UPDATE TAB_ROOKIECUSTOMER SET ISPUSH = 1,pushdate = sysdate
    		WHERE SELLERID||'@@'||BRANCHCODE IN
    		<foreach collection="birdList"  index="index" item="cus" open="(" separator="," close=")">
    			 #{cus.custNo}||'@@'||#{cus.orgCode}
    		</foreach>
    	</if>
    </update>

## 批量删除 ##

> 适用于Oracle

<delete id="deleteDetailBillcodeByCode">
    	delete from tab_vip_面单领取明细表  where 运单编号  in 
    	<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
               #{item}
           </foreach>	
    </delete>

## 批量新增 ##

> 适用于Oracle

<insert id="saveShipmentSetlSumbyday" parameterType="java.util.List" useGeneratedKeys="false">
    		INSERT INTO
    		SHIPMENT_SUMBYDAY (ID, ACCOUNT_DT, PAY_ORG_NAME,SETL_ORG_NAME, END_TRSF_ORG_NAME,TOTAL_NO,TOTAL_FEE,TOTAL_WEIGHT)
    		<foreach collection="list" item="item" index="index" separator="UNION ALL">
    			SELECT
    			#{item.id,jdbcType=VARCHAR},
    			#{item.accountDt,jdbcType=DATE},
    			#{item.payOrgName,jdbcType=VARCHAR},
    			#{item.setlOrgName,jdbcType=VARCHAR},
    			#{item.endTrsfOrgName,jdbcType=VARCHAR},
    			#{item.totalNo,jdbcType=INTEGER},
    			#{item.totalFee,jdbcType=DECIMAL},
    			#{item.totalWeight,jdbcType=DECIMAL}
    			FROM DUAL
    		</foreach>
    	</insert>

## 批量新增 ##

*  [SQL-技巧总结][SQL-]

[SQL-]: https://blog.csdn.net/panweiwei1994/article/details/60466967