批量插入对比-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 intotest_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>