mybatis批量添加/修改

最新推荐文章于 2024-04-25 13:00:56 发布

咸鱼也要鱼咸

最新推荐文章于 2024-04-25 13:00:56 发布

阅读量281

点赞数

文章标签： mybatis oracle 数据库

本文链接：https://blog.csdn.net/weixin_42668497/article/details/129277826

版权

Oracle

Mapper.xml。

<insert id="insertBatchAgentShareDetail" parameterType="java.util.ArrayList">
        <![CDATA[
        insert all 
        ]]>
        <foreach collection="list" item="detail" index="index"> 
            <![CDATA[ 
                INTO UCF_AGENT_SHARE_DETAIL (ID,TOTAL_AMOUNT,TOTAL_FEE_AMOUNT,TOTAL_AGENT_FEE_AMOUNT,TOTAL_SHARE_AMOUNT,
                    SHARE_RECORD_ID,PRODUCT_CODE,CARD_TYPE,TRADE_COUNT,SETTLE_DETAIL_ID) 
                VALUES(#{detail.id,jdbcType=VARCHAR},#{detail.totalAmount,jdbcType=NUMERIC},#{detail.totalFeeAmount,jdbcType=NUMERIC},#{detail.totalAgentFeeAmount,jdbcType=NUMERIC},
                    #{detail.totalShareAmount,jdbcType=NUMERIC},#{detail.shareRecordId,jdbcType=VARCHAR},#{detail.productCode,jdbcType=VARCHAR},
                    #{detail.cardType,jdbcType=VARCHAR},#{detail.tradeCount,jdbcType=NUMERIC},#{detail.settleDetailId,jdbcType=VARCHAR})
            ]]>
        </foreach>
        <![CDATA[
        select * from dual
        ]]>
    </insert>

sql语句

insert all
into TABLE (ID, TEXT, STAUTS) values (xx, xx, xx)
into TABLE (ID, TEXT, STAUTS) values (xx, xx, xx)
into TABLE (ID, TEXT, STAUTS) values (xx, xx, xx)
select * from dual

mysql

mapper.xml

<insert id="batchInsertUcfUserBatchImport" parameterType="java.util.List">
    <![CDATA[
        INSERT INTO UCF_USER_BATCH_IMPORT (ID,TEXT,STSTUS) VALUES
    ]]>
    <foreach collection="list" item="item" index="index" separator="," >
        <![CDATA[
            (
            #{item.id,jdbcType=NUMERIC},
            #{item.text,jdbcType=VARCHAR},
            #{item.status,jdbcType=VARCHAR}
            )
        ]]>
    </foreach> 
</insert>

sql

insert into TABLE (ID, TEXT, STAUTS) values
(xx, xx, xx), 
(xx, xx, xx), 
(xx, xx, xx)

批量修改

mapper.xml

<update id="updateBatchMerchantSettleRecord" parameterType="java.util.ArrayList">
        <foreach collection="list" item="record" index="index" open="begin" close=";end;" separator=";"> 
            <![CDATA[ UPDATE UCF_MERCHANT_SETTLE_RECORD ]]>
            <trim prefix="SET" suffixOverrides=",">
                <if test="null != record.shareStatus  and '' != record.shareStatus ">
                    <![CDATA[   SHARE_STATUS = #{record.shareStatus} ,]]>
                </if>
            </trim>
            <trim prefix="WHERE" prefixOverrides="AND">
                <if test="null != record.id and '' != record.id">
                    <![CDATA[ AND ID = #{record.id} ]]>
                </if>
            </trim>
        </foreach>
</update>