批量插入,批量修改的sql
2024-10-18 18:27:08
sql 1 批量插入
<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 批量插入,表设置为主键自增长
<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 批量修改
<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>
最新文章
- 后台发送POST,DELETE,GET,PUT请求
- Web前端面试笔试题总结
- RTX发送消息提醒实现以及注意事项
- Magento Service Temporarily Unavailable解决方法
- POJ 1940
- EditText 光标不显示问题
- C语言学习笔记--结构体
- 搭建Android环境
- js你真的了解offsetWidth吗
- NGUI实现一个背包功能
- FineUIMvc随笔(4)自定义回发参数与自定义回发
- java版飞机大战 实战项目详细步骤.md
- CSS中line-height继承问题
- Element UI 树形表格,TreeGrid或TreeTable实现
- 【Python】2.x与3​​.x版本的选用&;版本间的区别
- Android 使alertDialog.builder不会点击外面和按返回键消失
- mmc生产任务分配问题续
- webApi2 结合uploadify 上传报错解决办法
- spring-mvc注解配置小记
- 宏观看restframework序列化