mybatis ＜foreach＞标签在oracle批量插入失效问题

最新推荐文章于 2024-05-05 09:29:57 发布

@Ycoder

最新推荐文章于 2024-05-05 09:29:57 发布

阅读量384

点赞数

文章标签： mybatis oracle 数据库

本文链接：https://blog.csdn.net/weixin_45163291/article/details/134135774

版权

sql命令未正常结束

oracle数据库不支持批量插入时按照逗号分隔，需修改语句

<select id="test" >
    INSERT INTO test(TENANTID,PAYAMOUNT,PROVINCEID,CHANNELNAME,
    MASTERORDERID,CITYID,CITYNAME,USEREMAIL,PROVINCENAME,ISPOSTPAID,
    TENANTNAME,AMOUNT,USERNAME,CREATEDATE,BIZID,OFFERNAME,ORDERID,OFFERID)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (
        #{item.tenantId},
        #{item.payAmount},
        #{item.provinceId},
        #{item.channelName},
        #{item.masterOrderId},
        #{item.cityId},
        #{item.cityName},
        #{item.userEmail},
        #{item.provinceName},
        #{item.isPostPaid},
        #{item.tenantName},
        #{item.amount},
        #{item.userName},
        #{item.createDate},
        #{item.bizId},
        #{item.offerName},
        #{item.orderId},
        #{item.offerId}
        )
    </foreach>
</select>

修改为

<select id="test" >
    INSERT INTO test(TENANTID,PAYAMOUNT,PROVINCEID,CHANNELNAME,
    MASTERORDERID,CITYID,CITYNAME,USEREMAIL,PROVINCENAME,ISPOSTPAID,
    TENANTNAME,AMOUNT,USERNAME,CREATEDATE,BIZID,OFFERNAME,ORDERID,OFFERID)
    <foreach collection="list" item="item" separator="UNION ALL">
        select
        #{item.tenantId},
        #{item.payAmount},
        #{item.provinceId},
        #{item.channelName},
        #{item.masterOrderId},
        #{item.cityId},
        #{item.cityName},
        #{item.userEmail},
        #{item.provinceName},
        #{item.isPostPaid},
        #{item.tenantName},
        #{item.amount},
        #{item.userName},
        #{item.createDate},
        #{item.bizId},
        #{item.offerName},
        #{item.orderId},
        #{item.offerId}
        from dual
    </foreach>
</select>