MyBatis批量操作SQL

批量插入

适用于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-技巧總結