mybatis+mysql批量插入和批量更新
一、批量插入
批量插入資料使用的sql語句是:
insert into table (字段一,字段二,字段三) values(xx,xx,xx),(oo,oo,oo)
mybatis中mapper.xml的代碼如下:
<!-- 批量插入資料 -->
<insert id="insertBatch" parameterType="java.util.List"
useGeneratedKeys="true">
<selectKey resultType="long" keyProperty="id" order="AFTER">
SELECT
LAST_INSERT_ID()
</selectKey>
insert into wd_solr
(fayu_id, tablename,
name,logo,description,section_no,look_count,favorite_count,create_uid,create_time,update_time,timestamp)
values
<foreach collection="list" item="wdSolr" index="index"
separator=",">
(
#{wdSolr.fayuId},#{wdSolr.tablename},#{wdSolr.name},#{wdSolr.logo},
#{wdSolr.description},#{wdSolr.sectionNo},#{wdSolr.lookCount},#{wdSolr.favoriteCount},
#{wdSolr.createUid},#{wdSolr.createTime},#{wdSolr.updateTime},#{wdSolr.timestamp}
)
</foreach>
</insert>
二、批量更新
批量更新資料使用的sql語句是:
UPDATE table
SET aa = CASE id
WHEN 1 THEN 'oo'
WHEN 2 THEN 'pp'
WHEN 3 THEN 'qq'
END
,SET bb = CASE id
WHEN 1 THEN 'xx'
WHEN 2 THEN 'yy'
WHEN 3 THEN 'zz'
END
WHERE id IN (1,2,3)
上面這一條mysql語句可以更新多條記錄,mybatis中mapper.xml的代碼如下:
<!-- 批量更新資料 -->
<update id="updateBatch">
update wd_solr set
name =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then
#{wdSolr.name}
</foreach>
,logo =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then
#{wdSolr.logo}
</foreach>
,timestamp =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then #{wdSolr.timestamp}
</foreach>
where id in
<foreach collection="list" item="wdSolr" index="index"
separator="," open="(" close=")">
#{wdSolr.id}
</foreach>
</update>
三、SELECT LAST_INSERT_ID() 的使用和注意事項
轉載自:https://blog.csdn.net/czd3355/article/details/71302441
總體解釋:将插入資料的主鍵傳回到 user 對象中。
具體解釋:
SELECT LAST_INSERT_ID():得到剛 insert 進去記錄的主鍵值,隻适用與自增主鍵
keyProperty:将查詢到主鍵值設定到 parameterType 指定的對象的那個屬性
order:SELECT LAST_INSERT_ID() 執行順序,相對于 insert 語句來說它的執行順序
resultType:指定 SELECTLAST_INSERT_ID() 的結果類型
---------------------------------------------------------------------------------------
-- 存在即更新,不存在就插入(根據ID)
insert into `vclb_mm_inventory` (`ID_`, `STOCK_ID_`, `ITEM_ID_`, `AMOUNT_`)
values ('489734716803514367', '倉庫一', '水杯', 44)
ON DUPLICATE KEY UPDATE `AMOUNT_` = `AMOUNT_` + 44;
---------------------
原文:https://blog.csdn.net/zhang135687/article/details/82686991
<!-- 批量更新資料 -->
<update id="updateBatch">
update wd_solr set
name =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then
#{wdSolr.name}
</foreach>
,logo =
#{wdSolr.logo}
</foreach>
,timestamp =
when #{wdSolr.id} then #{wdSolr.timestamp}
where id in
<foreach collection="list" item="wdSolr" index="index"
separator="," open="(" close=")">
#{wdSolr.id}
</update>
-----------------------------------------------------------------------
SQL如何實作存在即更新,沒有就插入
SQL Server,今天遇到個問題就是SQL如何實作存在即更新,沒有就插入。
我用mybatis寫的:意思是就是判斷類和位置同時存在才更新,隻有一個或都不存在就插入。但是我寫的結果卻是隻要(類别和positionID)其中一個存在,就把資料庫中的所有有關的給跟新了,請問各位大俠有什麼好的解決辦法嗎?
如果存在(SELECT * FROM T_Mobilie_BackstageCommon其中類别=#{類别,jdbcType為VARCHAR}和positionId =#{positionId,jdbcType為整數})
開始
更新T_Mobilie_BackstageCommon設定類别=#{類别,jdbcType為VARCHAR},pathStr =#{pathStr ,jdbcType為VARCHAR},jumpToUrl =#{jumpToUrl,jdbcType為VARCHAR},标題=#{标題,jdbcType為VARCHAR},備忘錄=#{備忘錄,jdbcType為VARCHAR},expansion_one =#{expansion_one,jdbcType為VARCHAR}, expansion_two =#{expansion_two,jdbcType = VARCHAR}
end
else
begin
插入到T_Mobilie_BackstageCommon(category,pathStr,jumpToUrl,title,needToLogin,positionId,memo,expansion_one,expansion_two)
值(#{category,jdbcType = VARCHAR},#{pathStr,jdbcType = VARCHAR},#{jumpToUrl,jdbcType = VARCHAR} ,#{title,jdbcType = VARCHAR},#{needToLogin,jdbcType = INTEGER},#{positionId,jdbcType = INTEGER},#{memo,jdbcType = VARCHAR},#{expansion_one,jdbcType = VARCHAR},#{expansion_two, jdbcType = VARCHAR})
結束
------------------------------------------------------------------------------
侵删 聯系[email protected]