SQL 1 BULK Insert
<insert id= "Batchinsert" usegeneratedkeys= "true" parametertype= "Java.util.List" >
<selectkey resulttype= "Long" keyproperty= "id" order= "after" >
SELECT
LAST_INSERT_ID ()
</selectKey>
Insert into User_contacts_info (Id,user_id,old_id,cont_type,
Cont_ship, Cont_status, Cont_source,
Cont_user_name, Cont_id_card, Cont_mobile1,
Cont_mobile2, Cont_mobile3, Cont_tell,
Cont_addr, CONT_ADDR_HK, cont_addr_com,
Cont_tell_com, E_Mail, remark, Create_time,
Cont_name_com,update_time)
Values
<foreach collection= "List" item= "item" index= "index" separator= "," >
(#{item.id,jdbctype=integer},#{item.userid,jdbctype=integer},#{item.oldid,jdbctype=integer}, #{item.contType, Jdbctype=varchar},
#{item.contship,jdbctype=varchar}, #{item.contstatus,jdbctype=varchar}, #{item.contsource,jdbctype=varchar},
#{item.contusername,jdbctype=varchar}, #{item.contidcard,jdbctype=varchar}, #{item.contmobile1,jdbctype=varchar} ,
#{item.contmobile2,jdbctype=varchar}, #{item.contmobile3,jdbctype=varchar}, #{item.conttell,jdbctype=varchar},
#{item.contaddr,jdbctype=varchar}, #{item.contaddrhk,jdbctype=varchar}, #{item.contaddrcom,jdbctype=varchar},
#{item.conttellcom,jdbctype=varchar},#{item.email,jdbctype=varchar}, #{item.remark,jdbctype=varchar},
#{item.createtime,jdbctype=timestamp}, #{item.contnamecom,jdbctype=varchar},
#{item.updatetime,jdbctype=timestamp})
</foreach>
</insert>
SQL 2 BULK INSERT, table set as primary key self-growing
<insert id= "Insertbybatch" parametertype= "Java.util.List" >
INSERT INTO Monitor_log
(monitor_id, Monitor_date, Monitor_stats,
Monitor_info,
monitor_product_id, Monitor_repair_date,
Monitor_repair_userid)
Values
<foreach collection= "List" item= "item" index= "Index"
Separator= "," >
(#{item.monitorid,jdbctype=integer},
#{item.monitordate,jdbctype=timestamp},
#{item.monitorstats,jdbctype=tinyint},
#{item.monitorinfo,jdbctype=varchar},
#{item.monitorproductid,jdbctype=integer},
#{item.monitorrepairdate,jdbctype=timestamp},
#{item.monitorrepairuserid,jdbctype=integer})
</foreach>
</insert>
SQL 3 Batch Modification
<update id= "UpdateBatch" parametertype= "Java.util.List" >
Update Monitor_log
<trim prefix= "Set" suffixoverrides= "," >
<trim prefix= "Monitor_date =case" suffix= "End," >
<foreach collection= "List" item= "item" index= "Index" >
<if test= "Item.monitordate!=null" >
When Monitor_id=#{item.monitorid} then #{item.monitordate,jdbctype=timestamp}
</if>
</foreach>
</trim>
<trim prefix= "Monitor_stats =case" suffix= "End," >
<foreach collection= "List" item= "item" index= "Index" >
<if test= "Item.monitorstats!=null" >
When Monitor_id=#{item.monitorid} then #{item.monitorstats,jdbctype=tinyint}
</if>
</foreach>
</trim>
<trim prefix= "Monitor_info =case" suffix= "End," >
<foreach collection= "List" item= "item" index= "Index" >
<if test= "Item.monitorinfo!=null" >
When Monitor_id=#{item.monitorid} then #{item.monitorinfo,jdbctype=varchar}
</if>
</foreach>
</trim>
<trim prefix= "monitor_product_id =case" suffix= "End," >
<foreach collection= "List" item= "item" index= "Index" >
<if test= "Item.monitorproductid!=null" >
When Monitor_id=#{item.monitorid} then #{item.monitorproductid,jdbctype=integer}
</if>
</foreach>
</trim>
where monitor_id in
<foreach collection= "list" index= "index" item= "item"
Separator= "," open= "(" close= ")" >
#{item.monitorid,jdbctype=bigint}
</foreach>
</update>
Bulk INSERT, bulk-Modified SQL