批量插入对比-mysql-oracle-sqlserver

单个插入mysql

//单个  根据有值就插入，无值不改动  
<insert id="insertOne" keyColumn="id" keyProperty="id"
            parameterType="com.test.log" useGeneratedKeys="true">
        insert into test_mysql_tab
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="log != null">log,</if>
            <if test="createTime != null">create_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id,jdbcType=BIGINT},</if>
            <if test="log != null">#{log,jdbcType=VARCHAR},</if>
            <if test="createTime != null">#{createTime,jdbcType=TIMESTAMP},</if>
        </trim>
    </insert>


// 批量

批量插入

mysql

    <insert id="insertListNew">
        insert into
        test_mysql_tab (c1,c2,c3)values
            <foreach collection="list" item="item" index="index" separator="," >
                (
                #{item.c1}, #{item.c2},#{item.c3},#{item.c3}
                )
            </foreach>
    </insert>

sqlServer

   <insert id="saveBatchManual">
INSERT INTO test_tab (ca1, ca2, ca3, ca4, ca5)
select
    <foreach collection="list" index="index" item="item">
    <if test="index != 0">
    union select
    </if>	#{item.ca1},#{item.ca2},#{item.ca3,jdbcType=TIMESTAMP},#{item.ca4},#{item.ca5}
    </foreach>
</insert>

Oracle

    <insert id="batchInsertOracleInfo" parameterType="com.test.ParamObj" >
        insert all
        <foreach collection="list" item="item" index="index">
            into test_oracle_tab(c1,c2,c3,c4) values (#{item.c1},#{item.c2},#{item.c3},#{item.c4})
        </foreach>
        select 1 from dual
    </insert>

 oracle另一种方式

批量插入注意定义别名，否则可能会提示不能明确定义的列

 <insert id="insertBatchOracle" parameterType="com.test.batch.TestParam">
insert into test_oracle_tab(id,name)
<foreach collection="list" item="i" separator="union all" open="select * from (" close=")">
	select #{i.id} id,#{i.name} name from dual
</foreach>
</insert>


<!-- 方法2 -->
 <insert id="insertBatchOracle" parameterType="com.test.batch.TestParam">
insert into test_oracle_tab(id,name)
<foreach collection="list" item="i" separator="union all">
	select #{i.id} id,#{i.name} name from dual
</foreach>
</insert>