mybatis批量插入数据(oracle)

最新推荐文章于 2024-03-08 14:05:57 发布

WANG_LEI222

最新推荐文章于 2024-03-08 14:05:57 发布

阅读量198

点赞数

分类专栏： ORACLE JAVA 文章标签： oracle mybatis

本文链接：https://blog.csdn.net/WANG_LEI222/article/details/107956775

版权

ORACLE 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

JAVA

2 篇文章 0 订阅

订阅专栏

项目中，考虑到性能问题，想使用mybaits的批量插入功能，但是一直报错。

错误sql如下

<insert id="insertGroupRecord" parameterType="List">
        INSERT INTO INFO_GROUP_RECORD
            (ID
            , SEND_RECORD_ID
            , GROUP_NAME
            , SUBSCRIBER_ID
            , SUBSCRIBER_NAME
            , SEND_RESULT
            , USER_ID
            , CREATE_TIME
            , UPDATE_TIME
            , CREATE_BY
            , UPDATE_BY
            , REMARK
            , GROUP_ID
            , SUBSCRIBER_PHONE) VALUES
        <foreach collection="list" item="infoGroupRecordList" index="index" separator=",">
            (
            #{infoGroupRecordList.id, jdbcType=VARCHAR}
            , #{infoGroupRecordList.sendRecordId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.groupName, jdbcType=VARCHAR}
            , #{infoGroupRecordList.subscriberId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.subscriberName, jdbcType=VARCHAR}
            , #{infoGroupRecordList.sendResult, jdbcType=CHAR}
            , #{infoGroupRecordList.userId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.createTime}
            , #{infoGroupRecordList.updateTime}
            , #{infoGroupRecordList.createBy, jdbcType=VARCHAR}
            , #{infoGroupRecordList.updateBy, jdbcType=VARCHAR}
            , #{infoGroupRecordList.remark, jdbcType=VARCHAR}
            , #{infoGroupRecordList.groupId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.subscriberPhone, jdbcType=VARCHAR}
            )
        </foreach>
    </insert>

上面的错误提示是，命令未正确结束

因为oracle批量Insert的时候，不支持insert…value(),()的语法

正确批量insert语句如下

<insert id="insertGroupRecord" parameterType="List">
        INSERT INTO INFO_GROUP_RECORD
            (ID
            , SEND_RECORD_ID
            , GROUP_NAME
            , SUBSCRIBER_ID
            , SUBSCRIBER_NAME
            , SEND_RESULT
            , USER_ID
            , CREATE_TIME
            , UPDATE_TIME
            , CREATE_BY
            , UPDATE_BY
            , REMARK
            , GROUP_ID
            , SUBSCRIBER_PHONE) (
        <foreach collection="list" item="infoGroupRecordList" index="index" separator="union all">
            select
            #{infoGroupRecordList.id, jdbcType=VARCHAR}
            , #{infoGroupRecordList.sendRecordId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.groupName, jdbcType=VARCHAR}
            , #{infoGroupRecordList.subscriberId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.subscriberName, jdbcType=VARCHAR}
            , #{infoGroupRecordList.sendResult, jdbcType=CHAR}
            , #{infoGroupRecordList.userId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.createTime}
            , #{infoGroupRecordList.updateTime}
            , #{infoGroupRecordList.createBy, jdbcType=VARCHAR}
            , #{infoGroupRecordList.updateBy, jdbcType=VARCHAR}
            , #{infoGroupRecordList.remark, jdbcType=VARCHAR}
            , #{infoGroupRecordList.groupId, jdbcType=VARCHAR}
            , #{infoGroupRecordList.subscriberPhone, jdbcType=VARCHAR}
            from dual
        </foreach>
        )
    </insert>